How to create backups of Parallels Plesk Automation databases

Created:

2016-11-16 13:00:47 UTC

Modified:

2017-06-05 06:04:50 UTC

0

Was this article helpful?


Have more questions?

Submit a request

How to create backups of Parallels Plesk Automation databases

Applicable to:

  • Plesk 12.5 for Linux

Question

How do I create backups of Parallels Plesk Automation (PPA) databases?

Answer

Parallels 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:

# MYSQL_PWD=`cat /etc/psa/.psa.shadow`  mysqldump -uadmin 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:

# MYSQL_PWD=`cat /etc/psa/.psa.shadow`  mysqldump -uadmin 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_PWD=`cat /etc/psa/.psa.shadow`  mysql -uadmin psa < <backup_file>

2. Backup and restore PostgreSQL databases

2.1. Find the IP address which is allowed access to PostgreSQL. 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.168.0.1

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`

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

Please note that 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

Please note, that 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
Have more questions? Submit a request
Please sign in to leave a comment.