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