Backing up PostgreSQL using Amanda Enterprise

Backing up PostgreSQL using Amanda Enterprise

Must be one of the following versions of PostgreSQL:

• PostgreSQL 8,9,10,11,12
• 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). archivejnode = 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 (you may need to create backup_set_name directory) or globally at /etc/amanda/amanda-client .conf. Explanation of settings is 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"
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, the user must have superuser privileges.

PSQL_Password

The PostgreSQL password.

Wh et h e r_t o_clea n_u p_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 "templatel" 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:

 

For further information please see: http://docs.zmanda.com/Project:Amanda_Enterprise_3.3/Zmanda_App_modules/PostgreSQL