The PostgreSQL 9.0 Reference Manual - Volume 3 - Server Administration Guide by The PostgreSQL Global Development Group Paperback (6"x9"), 274 pages ISBN 9781906966072 RRP £9.95 ($14.95) Sales of this book support the PostgreSQL project! Get a printed copy>>>

PostgreSQL 9.0 Reference Manual Volume 3 Buy the book here! >>> support free software

10.3.5.1 Standalone hot backups

It is possible to use PostgreSQL's backup facilities to produce standalone hot backups. These are backups that cannot be used for point-in-time recovery, yet are typically much faster to backup and restore than pg_dump dumps. (They are also much larger than pg_dump dumps, so in some cases the speed advantage might be negated.)

To prepare for standalone hot backups, set wal_level to archive (or hot_standby), archive_mode to on, and set up an archive_command that performs archiving only when a switch file exists. For example:

archive_command = 'test ! -f /var/lib/pgsql/backup_in_progress 
  || cp -i %p /var/lib/pgsql/archive/%f < /dev/null'

This command will perform archiving when ‘/var/lib/pgsql/backup_in_progress’ exists, and otherwise silently return zero exit status (allowing PostgreSQL to recycle the unwanted WAL file).

With this preparation, a backup can be taken using a script like the following:

touch /var/lib/pgsql/backup_in_progress
psql -c "select pg_start_backup('hot_backup');"
tar -cf /var/lib/pgsql/backup.tar /var/lib/pgsql/data/
psql -c "select pg_stop_backup();"
rm /var/lib/pgsql/backup_in_progress
tar -rf /var/lib/pgsql/backup.tar /var/lib/pgsql/archive/

The switch file ‘/var/lib/pgsql/backup_in_progress’ is created first, enabling archiving of completed WAL files to occur. After the backup the switch file is removed. Archived WAL files are then added to the backup so that both base backup and all required WAL files are part of the same tar file. Please remember to add error handling to your backup scripts.

If archive storage size is a concern, use pg_compresslog, http://pglesslog.projects.postgresql.org, to remove unnecessary full_page_writes and trailing space from the WAL files. You can then use gzip to further compress the output of pg_compresslog:

archive_command = 'pg_compresslog %p - | gzip > 
  /var/lib/pgsql/archive/%f'

You will then need to use gunzip and pg_decompresslog during recovery:

restore_command = 'gunzip < /mnt/server/archivedir/%f | 
  pg_decompresslog - %p'