PostgreSQL Backup and Restore

Backups

Nine creates daily backups of the Postgres databases on your managed server between 01:00 and 02:00 o clock. These backups are kept locally for 10 days and are transferred to our backup systems with the daily backup of your server.

With the user 'www-data' available to you, you can access the local backups in order to archive them if necessary, to use them locally on your system or to restore them directly on your system.

The backups are stored in the directory /home/database-backup/postgresql/. All backups are versioned in directories with the following time scheme: 2021-12-06-0134

The symbolic link /home/database-backup/postgresql/latest/ will take you to the latest backup.

In the folder customer you will find the backups of all databases you have created. If you are only interested in the structure of a database, you will find it in the folder structure.

Create additional backups

To allow you to create additional backups, we have enabled the script we use to create the backups to be called via sudo by the user www-data:

www-data@nine01:~ $ sudo nine-postgresql-backup
2021-12-06T09:54:19+01:00 Dumped and compressed database 'nmd_frontend_production' in 53 seconds
2021-12-06T09:55:04+01:00 Dumped and compressed database 'nmd_frontend_staging' in 45 seconds

Restore

General information

Within each backup you will find the file restore.sh, e.g. under /home/database-backup/postgresql/2021-12-06-0134/restore.sh.

All databases including the file paths of the backup are listed here. You can copy the contents and adapt them to your needs. The commented entries are intended for a restore in the original database.

The entry of the user name -U nmd_ must be completed or adapted to the desired user name.

...
# Single database restores
# zstdcat /home/database-backup/postgresql/2021-12-06-0134/customer/nmd_frontend_production/nmd_frontend_production.zst | pg_restore -d nmd_frontend_production -O -x -c --if-exists -U nmd_
# zstdcat /home/database-backup/postgresql/2021-12-06-0134/customer/nmd_frontend_staging/nmd_frontend_staging.zst | pg_restore -d nmd_frontend_staging -O -x -c --if-exists -U nmd_

Entries above the line # Single database restores are for use by Nine staff and cannot be executed by unprivileged users.

During recovery, the following warnings may occur:

pg_restore: WARNING: no privileges could be revoked for "public".
pg_restore: WARNING: no privileges were granted for "public".

These warnings can be ignored as the import is executed with the parameter -O.

Parameters

The following parameters are used in the restore.sh script:

-U (--username): The database user to use. -d (--dbname): Specifies the target database -O (--no-owner): This option suppresses all commands to set the ownership of objects. All imported objects are assigned to the database user passed by -U (--usename). -x (--no-acl / --no-privileges): This option prevents restoration of access privileges, which have been already set. -c (--clean): This option deletes existing database objects. --if-exists: This option is required if --clean is used. If the option is omitted, error messages would occur if objects do not exist in the target database.

Restore to source database

To restore a backup, you can use the following command. This restores the backup of the database 'nmd_frontend_staging' to the database with the same name. At first, all existing data of the database is removed:

zstdcat /home/database-backup/postgresql/2021-12-06-0134/customer/nmd_frontend_staging/*.zst | pg_restore -d nmd_frontend_staging -O -x -c --if-exists -U nmd_frontend_staging

The parameter -U passes the database user name to the pg_restore command. The password of the database user is requested interactively during execution.

Please note that you must specify the user name and password of the database user and that the access data of the www-data user are not requested here. If you do not have this information to hand, it can be read out from the configuration of your application.

Restore to a different database

If necessary, the restore can also be made to a different database. In this case, the name of the target database must be adapted. We also recommend that you first import the structure into the new database:

# Restore of the database structure
zstdcat /home/database-backup/postgresql/2021-12-06-0134/structure/nmd_frontend_staging.zst | pg_restore -d nmd_frontend_staging_restore -O -x -c --if-exists -U nmd_frontend_staging_restore

# Restore of the database objects
zstdcat /home/database-backup/postgresql/2021-12-06-0134/customer/nmd_frontend_staging/nmd_frontend_staging.zst | pg_restore -d nmd_frontend_staging_restore -O -x -c --if-exists -U nmd_frontend_staging_restore

If you want to restore to another database, it must already exist or be [created] via nine-manage-databases(https://docs.nine.ch/a/5GSqkU-D7z8).

Restoring individual tables

Instead of restoring all tables, a single table can also be selected and imported by passing the parameter -t and the desired table name to the restore command.

Important note: When -t is specified, pg_restore makes no attempt to restore any other database objects that the selected table(s) might depend upon. Therefore, there is no guarantee that a specific-table restore into a clean database will succeed. And, while pg_dump's -t flag will also dump subsidiary objects (such as indexes) of the selected table(s), pg_restore's -t flag does not include such subsidiary objects.

zstdcat /home/database-backup/postgresql/2021-12-06-0134/customer/nmd_frontend_staging/nmd_frontend_staging.zst | pg_restore -d nmd_frontend_staging -O -x -c --if-exists -U nmd_frontend_staging -t tablename

Additional information

Time of backups

Backups of databases usually create an increased load on your system. Therefore, we have chosen a time period during the night, which is usually less frequented. If the period between 01:00 and 02:00 should be unfavourable for your application(s), we are happy to adjust it.

Compression of backups

We use the Zstandard algorithm to compress the backups. This algorithm offers an excellent balance of speed, compression and resource requirements and in some cases significantly outperforms the established algorithms.

If you need the backups for use on a system where the zst binary packages are not available, we advise you to unpack and recompress the backups on your server yourself:

www-data@nine01:~ $ mkdir dump_recompress
www-data@nine01:~ $ cp /home/database-backup/postgresql/latest/customer/nmd_frontend_staging/* dump_recompress/ ; cd dump_recompress/

# The option "--rm" deletes the zst archives after unpacking
www-data@nine01:~/dump_recompress $ unzstd --rm *.zst
nmd_frontend_staging.zst: 3732915490 bytes
www-data@nine01:~/dump_recompress $ bzip2 *
www-data@nine01:~/dump_recompress $ ls -l *.bz2
-rw-r----- 1 www-data www-data 432683536 Dec 6 13:24 nmd_frontend_staging.bz2

Please take care not to store backups publicly accessible on the web server.