PostgreSQL Server - Linux
Everyone's favorite Elephant-inspired database is covered by Zmanda
When the Zmanda Enterprise is configured and licensed for PostgreSQL backup, the ZMC allows you to select a PostgreSQL server to back up. When a PostgreSQL server is backed up, all databases are included in the backup, which can be either full (using a copy of the data directory and PostgreSQL write-ahead logs, referred to as WALs) or incremental (just WALs).
Postgres full backups include the data and archive log files (Postgres WALs). Incremental backups contain just the Write Ahead Logs. Incremental backups contain only WALs that have written out to the log directory and will not contain changes that have not been written out to WAL file yet.
This documentation uses Postgres 11 as its example. Some of the configurations may differ depending on the version you use.
Please contact [email protected] with any questions about other Postgres versions. For our full compatibility matrix, including supported Postgres versions, click here.
These instructions assume you have already installed and licensed the Amanda Enterprise server and the PostgreSQL server being backed up. There are several additional requirements:
- PostgreSQL on the client must be one of the tested versions listed on the Zmanda Network Supported Platforms page.
- On the client running PostgreSQL server, the system tar program (generally installed at /bin/tar) must be GNU tar 1.26 or later.
- Create a new directory for storing Write Ahead Logs (WALs) during the backup. The directory should be owned by user: amandabackup and group: postgres, with permissions set to 770.
- The following changes must be made in the PostgreSQL configuration file - postgresql.conf. Any changes to this file will require a restart of the PostgreSQL service to take effect.
- 1.Write-ahead logging must be enabled for full and incremental backups. WAL is not enabled by default in PostgreSQL. The wal_level value must be set to minimal, replica, or logical.
- 2.Set the archive_mode to on and enter an archive_command using the WAL directory path.
Example:
archive_command = 'cp %p /var/lib/postgres_wal/%f'
- 1.The listen_address should include the IP address or hostname of the Zmanda server.
- PostgreSQL does NOT allow a user to run psql as any other user. For example: the root user cannot run psql command as another user. This will cause Amanda checkhost failures. You will have to modify the pg_hba.conf file to allow the amandabackup and root users to access all databases from the Postgres server. The following example allows all users to access all databases from the Postgres server.
- Connect to the database and create a Postgresql role called amandabackup (or whatever system username is used for Amanda backups). The role should be created as a LOGIN role with SUPERUSER privileges and should be generated with a password.
CREATE ROLE amandabackup WITH SUPERUSER LOGIN PASSWORD 'password';
- Edit the amanda-client.conf file present in the /etc/amanda directory to include the following changes.
property "PG-DATADIR" "Path_to_PSQL_Data_Dir"
property "PSQL-PATH" "Path_to_PSQL_Binary"
property "PG-ARCHIVEDIR" "Path_to_WAL directory"
property "PG-CLEANUPWAL" "Whether_to_clean_up_WAL_Yes_or_No"
property "PG-USER" "PostgreSQL_username"
property "PG-PASSWORD" "PSQL_Password"
Instead of adding the PG-USER and PG-PASSWORD you can add a PG-PASSFILE property.
property "PG-PASSFILE" " Path_to_PSQL_Password_File "
Sample of amanda-client.conf properties
Notes
- The PG-PASSFILE (.pgpass) must be owned by the amandabackup user and must be readable only by that user (600) . Refer https://www.postgresql.org/docs/11/libpq-pgpass.html
- PostgreSQL data import should not be performed during backup process (loading data from a data file). Backups will fail.
It is recommended to create a separate backup set for PostgreSQL sources.
To create a new source, go to the Sources tab in the ZMC and click on Add Source.
Choose the source type as Database and select PostgreSQL from the dropdown.
Enter the client details like the IP address of the PostgreSQL server and the path to the data directory. Click on Save to add the source.
Refer to the backup flow documentation to complete the rest of the backup configuration and to run the backups.
PostgreSQL Database recovery is a two-step process. The first step is to recover the data files to the destination directory on the client. The second step is to perform point-in-time recovery of the database using PostgreSQL tools.
If you are restoring database files to the original location, it is important to stop the PostgreSQL server and restart it after the restore operation. It is also recommended that the files in the database directory are copied to a temporary location as a precautionary measure.
- 1.Stop the PostgreSQL service.
- 2.Copy the PostgreSQL database directory to a temporary directory (/var/lib/pgsql-restore in this example)
# mkdir /var/lib/pgsql-restore/safeguard
# mv /var/lib/pgsql/11/data/ /var/lib/pgsql-restore/safeguard
- 1.Perform the restore operation as described in the next two sections.
- 2.Restart the PostgreSQL service.
To begin restoring the data, navigate to the Restore tab and select the backup set from the dropdown.
Select the backup that you want to restore from the ‘Restore What’ page and click on Next.
Choose the destination directory on the ‘Restore Where’ page. The Temporary location is set to /tmp by default. If you wish to change this, toggle the option to Manual and specify the directory in the field provided.
A screenshot of a computer
Description automatically generated
Note that the Destination Directory and Temporary Directory must each have enough space to hold the selected backup data. If you choose the same directory for both, make sure that the selected directory has enough space to hold two copies of the backup image. Do not specify the PostgreSQL data directory as a destination, especially if PostgreSQL is running and files currently in the data directory have not been first moved to an alternate location (see previous section).
After selecting the file and directory conflict options in the ‘Restore How’ page, go to the ‘Restore Now’ page and start the restore.
The backups will be restored to the destination directory. There will be two sub directories data and archive. The data sub-directory contains the data files and the archive sub-directory will have WALs.
Completing the recovery is accomplished using the PostgreSQL commands as described in the section below.
PostgreSQL server must be stopped during this process and started at the end of the process.
- 1.If the data was restored to alternate location, move the data files to the database directory and make sure the permissions are correct.
Example:
# mv /var/lib/pgsql/restore/data /var/lib/pgsql/11/
- 1.
It is also prudent to modify pg_hba.conf to prevent users from connecting before successful recovery has been verified.
Edit /var/lib/pgsql/11/data/recovery.conf file to include (at minimum) the following entry, which must specify the path to your temporary archive directory:
restore_command = 'cp /var/lib/pgsql/restore/archive/%f "%p"'
Change the ownership and permissions on this file so that it is owned by the database system user, and that it is only readable and writable by this user
# chown postgres:postgres /var/lib/pgsql/11/data/recovery.conf
# chmod 0600 /var/lib/pgsql/11/data/recovery.conf
- 1.Start the PostgreSQL service, which will automatically begin recovering from the archived WAL files. If the recovery stops on an error, restart the server to continue the recovery after you have corrected the error condition. Upon successful completion of the recovery, the server renames recovery.conf to recovery.done and then starts normal database operations.
- 2.Inspect the database to verify that it is at the expected point in time. Check the log sub-directory under the Postgres data directory for any recovery errors. If it is not recovered to the correct point, return to step 1. After the recovery is verified, allow end-user access by restoring pg_hba.conf to its production state.
Last modified 10d ago