Backing up PostgreSQL using Amanda Enterprise
Must be one of the following versions of PostgreSQL:
- PostgreSQL 8.x, 9.x
- PostgreSQL Plus 8.3
Client running PostgreSQL must have GNU tar 1.23 or later.
Changes for the Amanda Enterprise Server
If backup temporary directory and state directory are different than the Amanda default values, edit /etc/zmanda/zmc/zmc_aee/zmc_user_dumptypes to add the following lines to app_ampgsql_user:
property "TMPDIR" "Path_to_temp_dir"property "STATEDIR" "Path_to_state_dir"
Changes for the Amanda Client running PostgreSQL server
Create a directory for your write ahead log and make sure amandabackup is able to read/write:
# mkdir /var/lib/amanda/postgres
# chown amandabackup:postgres /var/lib/amanda/postgres
Edit PostgreSQL configuration file ($pgdatadir/postgres.conf).
archive_mode = on
archive_command = 'cp %p /var/lib/amanda/postgres/%f'
wal_level = archive # only required on Postgres 9.x or greater, acceptable options being archive or hot_standby
Edit amanda-client.conf configuration file, this can be either in /etc/amanda/backup_set_name/amanda-client.conf (may need to create backup_set_name directory) or globally at /etc/amanda/amanda-client.conf. Explanation of settings 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"
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 a prefix 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.
PSQL_Password
The PostgreSQL password.
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 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.
Create a LOGIN PostgreSQL role called amandabackup with SUPERUSER privileges and a password that matches PG-PASSWORD. For example the following command can be run inside the PostgreSQL database as a superuser:
CREATE ROLE amandabackup WITH SUPERUSER LOGIN PASSWORD 'password';
To allow Amanda Enterprise Server access to the PostgreSQL server modify the pg_hba.conf file An example that would allow the amandabackup user to connect from any IP in 10.0.0.x would be as follows:
TYPE | DATABASE | USER | CIDR-ADDRESS | METHOD |
|---|---|---|---|---|
Host | all | amandabackup | 10.0.0.0/25 | md5 |
For further information please see: http://docs.zmanda.com/Project:Amanda_Enterprise_3.3/Zmanda_App_modules/PostgreSQL
