PostgreSQL

PostgreSQL Server Backup

When the Amanda Enterprise Edition is configured and licensed forPostgreSQL 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 PostgreSQLwrite-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 onthe Zmanda Network Supported Platforms page.  
  • On the client running PostgreSQLserver, the systemtar(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_dumptypesto add the following lines toapp_ampgsql_userdefinition 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. Theamandabackupuser (Amanda client user) must have read, write and execute privileges on this directory. Change the value of TMPDIRbecause the default value,/tmpmay 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. Theamandabackupuser 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-levelmust be set to archive or hot standby in PostgreSQL configuration file - postgresql.conf  
    • Create/var/lib/amanda/postgresdirectory for storing WALs during backup.  
    • In the PostgreSQL configuration file (for example/var/lib/pgsql/postgresql.confon 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 PostgreSQLserver, edit the/etc/amanda/backup_set_name/amanda-client.conffile (you may have to create/etc/amanda/backup_set_namedirectory) 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"  

PostgreSQL8.1 and later  

property "PG-PASSFILE""Path_to_PSQL_Password_File" (for PostgreSQL8.1 or later)  

PostgreSQL8.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 duringbackup and then archived by the Zmanda Postgres agent. This shouldnotbeset to the PostgreSQL server's pg_xlog directory! Specify a path outside ofthe PostgreSQL data directory where the archive command will copy files to bestored between full backup runs. The PostgeSQL user must have read, write andexecute privileges in this directory. Zmanda recommends using system groups tomanage permissions rather than granting access to all users such as adding theamandabackupuser to thePostgresgroup. The specified path should be thetarget directory of the archive_command in the PostgreSQL configuration file.  

hostname_or_directory_of_socket_file  

Specify the hostname (localhostif 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 PostgreSQLpsqlbinary 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 Postgres 8.1 or greater. Seehttp://www.postgresql.org/docs/8.1/static/libpq-pgpass.html. The file must be owned by theamandabackupuser 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 WALsegment files during full backups. WAL archive files are removed from PG_ARCHIVEDIRlocation after full backup is completed. Default is yes.  

Database_name  

The database to connect to. The PG-USERshould have credentials in the PG-PASSFILEto 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, seeamanda-client.conf(5). For specific details on PostgreSQL agent properties, seeampgsql(8) 

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

 

 

Directory  

Amanda  

Postgres  

TMPDIR  

rwx  

  

STATEDIR  

rwx  

  

PG-DATADIR  

  

rwx  

PG-ARCHIVEDIR  

rwx  

rwx  

PG-PASSFILE  

 

  

 

 

  • Create a Postgresql role calledamandabackup(or whatever system user name is used for Amanda backups). The role should be created as aLOGINrole withSUPERUSERprivileges and should be generated with a password that either matches the value ofPG-PASSWORD,or is supplied in the PG password file,PG-PASSFILE. This can be accomplished using either the Postgresql system commandcreateuser, or the Postgresql database commandCREATEROLE

 
For example (using the Postgresql database command CREATE
ROLE):  

CREATE ROLE amandabackup WITH SUPERUSER LOGIN PASSWORD 'password';  

  • PostgreSQLdoes NOTallow a user to runpsqlas any other user. For example: root user cannot runpsqlcommand 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  

  • PostgreSQLdata 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 Sources page you are prompted to select what type of object you want to back up. ChoosePostgreSQL, 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, Compression and Data Deduplication  

Set these options as desired. They are described in more detailhere.  

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 theHistorical Averagecalculated from previous backups. In most cases, the default ofReliably Accurateis appropriate.  

After you have set the options, click theAdd/Updatebutton 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 onBackup Where,Backup How, andBackup When, etc.  

For Postgres backup sets with larger databases, it may be necessary to increase data timeout in ZMCBackup Howpage in Backup Sets page.  

Restoring PostgreSQLdatabase  

PostgreSQLdatabase can berestored using ZMCorusing amrecover command. After restoring database files, you can performpoint-in-time recovery of the database 

If you are restoring database files to the original location, it is important to stop the PostgreSQLserver 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 PostgreSQLservice  

# service postgresql stop  

  1. Copy the PostgreSQLdatabase directory to a temporary directory (/var/lib/pgsql-restorein this example)  

# mkdir /var/lib/pgsql-restore/safeguard 

# mv /var/lib/pgsql/data/ /var/lib/pgsql-restore/safeguard  

  1. Perform the restore operation as described in the next two sections.  
  2.  Restart the PostgreSQLservice  

# service postgresql start  

 

PostgreSQL Database Restoration Using 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 theRestore Wherepage and select a PostgreSQL backup for restore.   

 

When you have selected the backup object that includes the PostgreSQL server for restore, theRestore Whatpage displays the following options: 

 

 

Select the databases you wish to restore. ChooseAllfor a full restore up to the last backup. ClickNext Stepwhen you are done, and theRestore Whereoptions are displayed: 

 

 

Set the restore options as desired. Note that theDestination DirectoryandTemporary Directorymusteachhave 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 previous section).  

After reviewing the entries, clickRestoreto start the restore process. The backups will be restored to /root/recovered directory.There will be two sub-directoriesdataandarchive. Thedatasub-directory contains the data files andarchivesub-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 thesection below 

PostgreSQLdatabase restoration using amrecover command.  

Instead of using Zmanda Management Console, you can recover PostgreSQL database backups to the client by runningamrecovercommand on the client. The procedure is to restore the database to an alternate location or directly to the database directory (default/var/lib/pgsql). PostgreSQLdatabase will perform the recovery when the service is started. Recovery fromWALcan be performed usingrecovery.confas described in the next section.  

Below is an example of recovery using amrecover command from a full backup. For complete set of amrecover command options, please seeamrecoverman page. This steps are valid for RedHat Enterprise Linux/CentOS. Other platforms will require modifications to the PostgreSQLservice name and database directory location.  

Note: PostgreSQLserver must be stopped during this process if you are restoring to the database directory.  

  1. Remove all the contents of the directory where you are restoring to (database directory in case you are restoring to original location).  
  2. Run amrecover on the backup set containing PostgreSQL database  

# amrecover <backup set name>  

  1.  Select the backup image to be restored  

amrecover> setdisk <DLE-name>  

  1. Select the name of the backup file  

amrecover> add <name of Postgres backup file>

  1. Switch the directory to be restored to  

amrecover> lcd /var/lib/pgsql  

  1. Start the restoration process  

amrecover> extract  

  1. Exit amrecover command  

amrecover> quit  

  1. The restored files will reside in the current directory. Completing the recovery is accomplished using the PostgreSQL commands as described in thesection below 

  

Completing the PostgreSQL Database Point-in-time Recovery  

This section describes the steps to do point-in-time recovery using the database and WAL logs restored using ZMC or amrecover command as described in earlier sections. Please note that this example assumes the PostgreSQL data directory is/opt/PostgresPlus/8.3.  

Note: PostgreSQL server must be stopped during this process and started at the end of the process.  

  1. If the data was restored to alternate location, move the data files to the database directory. Move the restored data (from/root/recovereddirectory in this example) to the correct PostgreSQLdata directory and make sure all ownership is correct. 

 
         # mv /root/recovered/* /opt/PostgresPlus/8.3  

  1. The Postgres tablespace data is restored to thedata/pg_tblspc_datadirectory and should be moved to the correct location as indicated by the symlinks in thedata/pg_tblspcdirectory. \
  2. Create the filerecovery.confin the cluster data directory (see the PostgreSQL documentation'sRecovery Settings). It is also prudent to modifypg_hba.confto prevent users from connecting before successful recovery has been verified. 

 

-Edit/opt/PostgresPlus/8.3/data/recovery.confto include (at minimum) the following entry, which must specify the path to your temporaryarchivedirectory: 

restore_command = 'cp /opt/PostgresPlus/8.3/archive/%f "%p"' 
 
- Change the ownership and permissions on this file so that it is owned by the database system user, and that it is only readable and writable by this user 
 
    #chown postgres:postgres /opt/PostgresPlus/8.3/data/recovery.conf 
    #chmod 0600 /opt/PostgresPlus/8.3/data/recovery.conf  

  1. Start the server, which will automatically begin recovering from the archived WAL files. If the recovery stops on an error, restart the server to continue the recovery after you have corrected the error condition. Upon successful completion of the recovery, the server renamesrecovery.conftorecovery.doneand then starts normal database operations. 

 
     #/etc/init.d/pgplus_83 start 
  

  1. Inspect the the database to verify that it is in the expected point in time. Check pg_log sub-directory under Postgres data directory for any recovery errors. If it is not recovered to the correct point, return to step 1. After the recovery is verified, allow end-user access by restoringpg_hba.confto its production state. Further details on PostgreSQL Point-in-time recovery are available in the PostgreSQL documentation; seeRecovering using a Continuous Archive Backup 

  

Troubleshooting  

If the checks or backups are failing due to Postgres login problems, check that thepg_hba.conffile (a PostgreSQL configuration file located in in the database cluster's data directory) is set up to allow theamandabackupuser to log in to the database using thePG-USERandPG-PASSWORDspecified. 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