PostgreSQL

PostgreSQL Server Backup

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.

Requirements for PostgreSQL Server Backup and Restore

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:

• 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 (generally installed at /bin/tar) must be GNU tar 1.23 or later.
• Amanda Server configuration file changes may be required.
  • Edit the /etc/zmanda/zmc/zmc_aee/zmc_user_dumptypes to add the following lines to app_ampgsql_user definition if temporary and state directory are different from the Amanda default values (see below) :

property "TMPDIR" "Path_to_temp_dir"
property "STATEDIR" "Path_to_state_dir"

Path_to_temp_dir

Temporary directory. The space is required to store temporary files. 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.

• PostgreSQL server (Amanda client) changes

  • Write-ahead logging must be enabled for full and incremental backups. WAL is not enabled by default in either PostgreSQL or PostgreSQL Plus. For Postgres 9.0, wal-level must be set to archive or hot standby in PostgreSQL configuration file - postgresql.conf

  • Create /var/lib/amanda/postgres directory for storing WALs during backup.
  • In the PostgreSQL configuration file (for example /var/lib/pgsql/postgresql.conf on Red Hat/CentOS platform), archive_command must be configured. Example archive_command can be

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"

property "PG-USER" "PostgreSQL_username"
property "PG-PASSWORD" "PSQL_Password"

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-DB" "Database_name"

To specify parameters for more than one PostgreSQL instance on a server, add aprefix to the property name that corresponds to the backup directory. 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 where WAL segments will be cached by the PostgreSQL server during backup and then archived by the Zmanda Postgres agent.  This should not be set to the PostgreSQL server's pg_xlog directory!  Specify a path outside of the PostgreSQL data directory where the archive command will copy files to be stored 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 such as adding the amandabackup user to the Postgres group. The specified path should be the target directory of 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 PG-USER should have credentials in the PG-PASSFILE to access this database. The default value is "template1" that exists in default PostgreSQL installations.
This is not the database to backup. All databases in the postgres server are backed up. 

For further details on application properties, see amanda-client.conf(5). For specific details on PostgreSQL agent properties, see ampgsql(8).

• The pathnames referenced above must exist, with permissions set as indicated in the table below:

• Create a Postgresql role called amandabackup (or whatever system user name is used for Amanda backups). The role should be created as a LOGIN role with SUPERUSER privileges and should be generated with a password that either matches the value of PG-PASSWORD, or is supplied in the PG password file, PG-PASSFILE.  This can be accomplished using either the Postgresql system command createuser, or the Postgresql database command CREATE ROLE.
  
  For example (using the Postgresql database command CREATE ROLE):

CREATE ROLE amandabackup WITH SUPERUSER LOGIN PASSWORD 'password';

• PostgreSQL does NOT allow a user to run psql as any other user. For example: root user cannot run psql command as another user. This will cause Amanda configuration check failures.  You will have to modify /var/lib/pgsql/data/pg_hba.conf (the actual location might be different depending on Postgres installation) to allow amandabackup and root user to access all databases from the Postgres server. Following example allows all users to access all databases (you may have modify existing line in the configuration file):

TYPE       DATABASE  USER         CIDR-ADDRESS         METHOD

local        all     all                                 md5

• PostgreSQL data import should not be performed during backup process (loading data from a data file). Backups will fail.

Configuring PostgreSQL Server Backups from the ZMC Backup What Page

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:

Host Name

The name of the machine running the PostgreSQL server you intend to back up.

Data Directory

The path to the PostgreSQL data directory. For example: /var/lib/pgsql/data

Encryption and Compression

Set these options as desired. They are described in more detail here. 

Advanced Options 

If estimates are taking too long and the databases being backed up do not change in size that much from backup to backup, use the the Historical Average calculated from previous backups. In most cases, the default of Reliably Accurate is appropriate.

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.

Restoring PostgreSQL database

PostgreSQL database can be restored using ZMC or using amrecover command. After restoring database files, you can perform point-in-time recovery of the database.

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 temporary location as a precautionary measure.

1. Stop the PostgreSQL service

# service postgresql stop

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/data/ /var/lib/pgsql-restore/safeguard

3. Perform the restore operation as described in the next two sections.
4. Restart the PostgreSQL service

# service postgresql start

Troubleshooting

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