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) or incremental (which depends on PosgreSQL write-ahead logs, referred to as WALs).

Note that backing up PostgreSQL tablespaces is not supported.

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:

• Make sure that your versions of Postgres conform to the tested platforms listed on the Zmanda Network Supported Platforms page.

• The amanda-client.conf file on the server must be manually edited to include the following lines:

property "PG-DATADIR" "Path_to_PSQL_Data_Dir"
property "PG-ARCHIVEDIR" "Path_to_PSQL_Archive_Dir"
property "PG-HOST" "hostname_or_directory_of_socket_file"
property "PG-TMP" "Path_to_temp_dir"
property "PSQL-PATH" "Path_to_PSQL_Binary"
property "PG-USER" "PostgreSQL_username"
property "PG-PASSFILE" "Path_to_PSQL_Password_File"
property "PG-PASSWORD" "PSQL_Password"

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. This directory must be writeable by the user that runs PostgreSQL processes.

Specify the location that has been configured in PostgreSQL for continuous Write-ahead Log (WAL) archiving (i.e. the archive_command in the PostgreSQL config file). Note that write-ahead logging must be enabled. WAL is not enabled by default in either PostgreSQL or PostgreSQL Plus.

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_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.

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

PSQL_Password

The PostgreSQL password (deprecated in PostgreSQL 8.1).

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

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.

Encryption and Compression

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

Advanced Options - Estimate 

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 button to add the PostgreSQL server to the backup set. Click Apply Plan to Server to commit the changes; you can then configure the backup set just as you would any other by setting the options on Backup Where, Backup How, and Backup When, etc.

Configuring PostgreSQL Server Restores from the ZMC Restore What 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 Explore button lets you select from the most recent backups.

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 much 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 specify the PostgreSQL data/cluster directory as a destination, especially if PostgreSQL is running.

After reviewing the entries, click Restore to start the restore process.

When the ZMC restore process is complete, the restored files will reside on the specified host and destination directory.  Completing the restore is accomplished outside the ZMC using the host operating system and PostgreSQL as described below.

Troubleshooting

[Need troubleshooting steps]