Situation: A backup system performs filesystem-level daily backups on a server containing a large but fairly static PostgreSQL 9.2 database. The filesystem backups are incremental and generally take <30 minutes.
My initial idea was to use the backup system hook scripts to put the database cluster into backup mode:
- start-backup hook runs:
psql -c "SELECT pg_start_backup('daily');"
- backup copies postgres data files somewhere else, ignoring
- backup-finished hook runs:
psql -c "SELECT pg_stop_backup();"
My understanding was that (1) would force a database checkpoint, meaning that you have a consistent filesystem in
base/ & friends. Further WAL segments would be written during (2) to
pg_xlog/ but would leave the
base/ files alone. Then when
pg_stop_backup() is called in (3) it applies the WAL logs from during the backup to
base/ & friends. And if you restore the backup you'd end up with the database state at (1).
But the Postgresql docs describe something different:
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.
If more flexibility in copying the backup files is needed, a lower level process can be used for standalone hot backups as well. To prepare for low level standalone hot backups, set
on, and set up an
archive_commandthat performs archiving only when a switch file exists. For example:
archive_command = 'test ! -f /var/lib/pgsql/backup_in_progress || (test ! -f /var/lib/pgsql/archive/%f && cp %p /var/lib/pgsql/archive/%f)'
This command will perform archiving when
/var/lib/pgsql/backup_in_progressexists, 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_progressis 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.
That's fine - I need to enable
wal_level=archive to get
pg_start_backup() to work anyway, and can easily create & remove a switch file. But getting the backup system to revisit the filesystem (for the final step above) isn't possible...
From what I can tell, the above approach from the Postgres documentation would get me a backup of the database state as at
pg_stop_backup() (the latter part via replaying the archived WAL segments during startup).
But it's quite possible my understanding is wrong, and
base/ does change during the backups, which means you need the archived WAL segments as well.
So, do I need to copy the WAL segments from the time between
pg_stop_backup() calls in order to get a usable backup?