|The PostgreSQL 9.0 Reference Manual - Volume 3 - Server Administration Guide
by The PostgreSQL Global Development Group
Paperback (6"x9"), 274 pages
RRP £9.95 ($14.95)
Sales of this book support the PostgreSQL project! Get a printed copy>>>
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
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,
full_page_writes and trailing
space from the WAL files. You can then use
gzip to further compress the output of
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'
|ISBN 9781906966072||The PostgreSQL 9.0 Reference Manual - Volume 3 - Server Administration Guide||See the print edition|