PostgreSQL Configuration for PITR

In order to use the Point In Time Recovery feature of PostgreSQL, WAL Archiving needs to be enabled. The procedure differs between major PostgreSQL version, so we advise to read the PostgreSQL documentation corresponding to the Cluster version; for PostgreSQL version 9.1, for example, it can be found here: http://www.postgresql.org/docs/9.1/static/continuous-archiving.html

Basically, on 8.4, the archive_command and the archive_mode settings need to be configured.

# on 8.3 - 8.4
archive_mode = on
archive_command = 'test ! -f /mnt/waldir/%f && cp %p /mnt/waldir/%f'

For 9.x, it is needed to configure archive_command, wal_level, and archive_mode.

# on 9.0 - 9.1
wal_level = archive
archive_mode = on
archive_command = 'test ! -f /mnt/waldir/%f && cp %p /mnt/waldir/%f'

The /mnt/waldir directory should be purged from time to time when backup jobs are successful. We do not recommend to remove WAL files just after a backup job. If something is going wrong with the backup job and files are no longer on the database server, it will not be possible to easily re-start the backup job.

# This example will remove WAL files older than 14 days
find /mnt/waldir -type f -mtime +14 -exec rm -f {} \;

As shown below, the PostgreSQL plugin needs to know where WAL files are stored after archiving. In this example, archive_dir would point to /mnt/waldir.

Note

It may be useful to compress WAL files in the archive_command using something like:

archive_command = ‘test ! -f /mnt/waldir/%f.gz && gzip -c %p > /mnt/waldir/%f.gz’

In this case, the restore_command in recovery.conf will need to be modified during the restore, as the PostgreSQL Plugin will not be able to reverse the custom archive_command automatically. It is good practice to put the needed restore_command command as comment into the postgresql.conf.