When the Amanda Enterprise Edition 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 (level 0) include the data and archive log files (Postgres WALs). Level 1 backups contain just the Write Ahead Logs. Level 1 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.
Note that backing up PostgreSQL tablespaces is not supported.
These instructions assume you have already installed and licensed the Amanda Enterprise server and the PostgreSQL server being backed up. There are a number of additional requirements:
property "TMPDIR" "Path_to_temp_dir"
property "STATEDIR" "Path_to_state_dir"
Path_to_temp_dir
Temporary directory. Must have enough capacity to temporarily store the backup data for a full backup of the PostgreSQL server. Defaults to /tmp. The amandabackup user (Amanda client user) must have read, write and execute privileges on this directory. Change the value of TMPDIR because the default value, /tmp may not have sufficient space. Please make the modification to the value as shown above.
Path_to_state_dir
Directory to store information about what has been backed up (i.e., the state of the backup). It requires only about 20KB for each backup object/DLE. The amandabackup user must have read, write and execute privileges on this directory. Default is /var/lib/amanda/gnutar-lists.
archive_command = 'cp %p /var/lib/amanda/postgres/%f'
In this example, Path_to_PSQL_Archive_Dir (see next bullet) is /var/lib/amanda/postgres/
On the PostgreSQL server, edit the /etc/amanda/backup_set_name/amanda-client.conf file (you may have to create /etc/amanda/backup_set_name directory) to include the following lines. You can make the settings global to all backup sets by making changes to /etc/amanda/amanda-client.conf instead of backup set specific directory. The explanation for the settings are given below.
property "PG-DATADIR" "Path_to_PSQL_Data_Dir"
property "PSQL-PATH" "Path_to_PSQL_Binary"
property "PG-ARCHIVEDIR" "Path_to_PSQL_Archive_Dir"
property "PG-CLEANUPWAL" "Whether_to_clean_up_WAL_Yes_or_No"
PostgreSQL 8.1 and later
property "PG-PASSFILE" "Path_to_PSQL_Password_File" (for PostgreSQL 8.1 or later)
PostgreSQL 8.0
property "PG-HOST" "hostname_or_directory_of_socket_file"
property "PG-PORT""TCP_port_to_connect_to. Default: 5432"
property "PG-USER" "PostgreSQL_username"
property "PG-PASSWORD" "PSQL_Password"
property "PG-DB" "Database_name"
To specify multiple databases, add a prefix to the property name that corresponds to the diskname, followed by a dash. For example:
property "PG-USER" "amandabackup"
becomes:
property "/path/to/data/dir-PG-USER" "amandabackup"
Path_to_PSQL_Data_Dir
The path to the PostgreSQL data directory.
Path_to_PSQL_Archive_Dir
The path to the PostgreSQL archive directory. Specify the path where the archive command copies files and stores them between full backup runs. The PostgeSQL user must have read, write and execute privileges in this directory. Zmanda recommends using system groups to manage permissions rather than granting access to all users.This value should be same as the directory specified for the archive_command in the PostgreSQL configuration file.
hostname_or_directory_of_socket_file
Specify the hostname (localhost if that is appropriate) or the directory where a socket file is located. Entries beginning with / are interpreted as a socket file directory (just the directory, for example, /tmp, not /tmp/.s.PGSQL.5432). If a directory is used, the PostgreSQL server and Amanda backup server must reside on the same machine.
Path_to_PSQL_Binary
The path to the PostgreSQL psql binary executable file.
PostgreSQL_username
The PostgreSQL database user to connect as, which must have superuser privileges.
Path_to_PSQL_Password_File
Passfile to use for Postgre 8.1 or greater. See http://www.postgresql.org/docs/8.1/static/libpq-pgpass.html. The file must be owned by the amandabackup user and the file must be readable only by that user (e.g. 0600), as noted in the PostgreSQL documentation.
PSQL_Password
The PostgreSQL password (deprecated in PostgreSQL 8.1).
Whether_to_clean_up_WAL
Whether or not to remove old WAL segment files during full backups. WAL archive files are removed from PG_ARCHIVEDIR location after full backup is completed. Default is yes.
Database_name
The database to connect to. The default value is "template1" that exists in default PostgreSQL installations.
For further details on application properties, see amanda-client.conf(5). For specific details on PostgreSQL agent properties, see ampgsql(8).
Directory | Amanda | Postgres |
TMPDIR | rwx | |
STATEDIR | rwx | |
PG-DATADIR | rwx | |
PG-ARCHIVEDIR | rwx | rwx |
PG-PASSFILE | r |
CREATE ROLE amandabackup WITH SUPERUSER LOGIN PASSWORD 'password';
TYPE DATABASE USER CIDR-ADDRESS METHOD local all all md5
Create a dedicated backup set for each PostreSQL server you intend to back up. On the Backup What page you are prompted to select what type of object you want to back up. Choose PostgreSQL, and the following options are displayed:
After you have set the options, click the Add/Update button to add/update the PostgreSQL server to the backup set. You can configure the backup set just as you would any other by setting the options on Backup Where, Backup How, and Backup When, etc.
For Postgres backup sets with larger databases, it may be necessary to increase data timeout in ZMC Backup How page.
Make sure that PostgreSQL is installed in the same location as when the backup was run. The databases and logs file locations should also match the original configuration.
Either select the desired PostgreSQL backup from one of the Reports, or go directly to the Restore Where page and select a PostgreSQL backup for restore. The Select & Explore button lets you select from the most recent backups. If you want restore the complete backup image, you can use Express Restore button.
When you have selected the backup object that includes the PostgreSQL server for restore, the Restore What page displays the following options:
Select the databases you wish to restore. Choose All for a full restore up to the last backup. Click Next Step when you are done, and the Restore Where options are displayed:
Set the restore options as desired. 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/cluster directory as a destination, especially if PostgreSQL is running and files currently in the data/cluster directory have not been first moved to an alternate location (see next section).
After reviewing the entries, click Restore to start the restore process. The backups will be restored to /root/recovered directory. There will be two sub-directories data and archive. The data sub-directory contains the data files and archive sub-directory will have WALs
When the ZMC restore process is complete, the restored files will reside on the specified host and destination directory. Completing the recovery is accomplished using the PostgreSQL commands as described in the section below.
Instead of using Zmanda Management Console, you can recover PostgreSQL database backups to the client by running amrecover command on the client. The procedure is to restore the database to an alternate location or directly to the database directory (default /var/lib/pgsql). PostgreSQL database will perform the recovery when the service is started. Recovery from WAL can also performed using recovery.conf as described in the previous section.
An example of recovery using amrecover command from a full backup. For complete set of amrecover command options, please see amrecover man page. This steps are valid for RedHat Enterprise Linux/CentOS. Other platforms will require modifications to the PostgreSQL service name and database directory location.
# service postgresql stop
# amrecover <backup set name>
amrecover> setdisk <DLE-name>
amrecover> add <name of Postgres backup file>
amrecover> lcd /var/lib/pgsql
amrecover> extract
amrecover> quit
This section describes the steps to do point-in-time recovery using the database and WAL logs restored using ZMC. You can perform database restoration using amrecover command which is described in the next section. Please note that this example assumes the PostgreSQL data directory is /opt/PostgresPlus/8.3.
mkdir /opt/postgres-restore/safeguard mv /opt/PostgresPlus/8.3/data/ /opt/postgres-restore/safeguard
Move the restored data to the correct PostgreSQL data directory and make sure all ownership is correct.
# mv /root/recovered/* /opt/PostgresPlus/8.3
Instead of using Zmanda Management Console, you can recover PostgreSQL database backups to the client by running amrecover command on the client. The procedure is to restore the database to an alternate location or directly to the database directory (default /var/lib/pgsql). PostgreSQL database will perform the recovery when the service is started. Recovery from WAL can also performed using recovery.conf as described in the previous section.
An example of recovery using amrecover command from a full backup. For complete set of amrecover command options, please see amrecover man page. This steps are valid for RedHat Enterprise Linux/CentOS. Other platforms will require modifications to the PostgreSQL service name and database directory location.
# service postgresql stop
# amrecover <backup set name>
amrecover> setdisk <DLE-name>
amrecover> add <name of Postgres backup file>
amrecover> lcd /var/lib/pgsql
amrecover> extract
amrecover> quit
Commands to change ownership and permissions for recovery.conf
# chown postgres:postgres /var/lib/pgsql/data/recovery.conf
# chmod 0600 /var/lib/pgsql/data/recovery.conf
# service postgresql start
If the checks or backups are failing due to Postgresql login problems, check that the pg_hba.conf file (a PostgreSQL configuration file located in in the database cluster's data directory) is set up to allow the amandabackup user to log in to the database using the PG-USER and PG-PASSWORD specified. For further information, see the following PostgreSQL documentation:
http://www.postgresql.org/docs/8.3/static/client-authentication.html
http://www.postgresql.org/docs/8.3/static/auth-methods.html#AUTH-IDENT-MAPS