• Article for your preferred language does not exist. Below is international version of the article.

Article ID: 118890, created on Nov 28, 2013, last review on May 16, 2016

  • Applies to:
  • Plesk Automation

Question

How to create backups of Plesk Automation (PA) databases?

Answer

Plesk Automation uses MySQL and PostgreSQL databases on the Management Node.

1. Backup and restore MySQL databases

1.1. To create a backup of the MySQL database, use the following command:

    mysqldump -uadmin -p`cat /etc/psa/.psa.shadow` psa > psa.mysql.backup.`date +%F`

1.2. To create a backup of one table in the MySQL database, add the -t table.name parameter. For example:

    mysqldump -uadmin -p`cat /etc/psa/.psa.shadow` psa -t db_users > psa.mysql.db_users.backup.`date +%F`

1.3. To restore the required database or table, use the following command:

    mysql -uadmin -p`cat /etc/psa/.psa.shadow` psa < <backup_file>

2. Backup and restore PostgreSQL databases

2.1. Find the IP address PostgreSQL allows to connect from. You can find this in the /var/lib/pgsql/9.0/data/pg_hba.conf file. For example:

    [root@ppa ~]# cat /var/lib/pgsql/9.0/data/pg_hba.conf | grep plesk | awk {'print $4'}
    192.0.2.2

2.2. To create a backup of the PostgreSQL database, use the following command:

    pg_dump -U plesk -h `hostname` plesk > plesk.psql.`date +%F`

NOTE: that 'pg_dump' utility can create 3 types of backup: custom, tar and plain-text. You can set the backup type using the switch -F (--format) c|t|p (for example, 'pg_dump -U plesk -h hostname plesk -F c' will create custom-type backup). By default, 'pg_dump' is executed with -F p option, creating plain-text dump. Such dumps can not be restored with 'pg_restore' utility.

PEM service should be stopped prior to take the backup. Leaving it in running state may cause data inconsystency.

    service stop pem

If PA version is 11.6 or above, then PAU service should be stopped as well:

    service pem stop
    service pau stop

2.3. To create a backup of one table in the PostgreSQL database, add the --table table.name parameter. For example:

    pg_dump -U plesk -h `hostname` plesk --table pg_database > plesk.psql.dns_access_points.`date +%F`

2.4. To restore the required database in PostgreSQL, first stop the 'PEM' service (and PAU in case of PA version is 11.6 or higher) on the management node:

    service pem stop
    service pau stop

NOTE: restoring backup with 'PEM' service running can lead to database integrity damage! Drop the required table from the database and then restore it from your backup file:

    psql -U plesk -h `hostname` plesk -c "drop table dns_access_points;"
    psql -U plesk -h `hostname` plesk < <backup_file>

If you have taken not the particular page backup, but full database backup instead - then you should drop database before restoring the full backup.

To restore backup created in a plain-text format, use following syntax(you should probably drop plesk database before executing this):

    cat <backup_file> | psql plesk

Then start 'PEM' service:

    service pem start

For PA version 11.6 and higher the sequence of services start should be the following:

    service pau start
    service pem start

Search Words

PPA backup

backup

how to create backup of PA data

database

backup PA databases

e0aff7830fa22f92062ee4db78133079 caea8340e2d186a540518d08602aa065

Email subscription for changes to this article
Save as PDF