Simplest filesystem backup of Postgres database

Question

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:

1. start-backup hook runs: psql -c "SELECT pg_start_backup('daily');"
2. backup copies postgres data files somewhere else, ignoring pg_xlog/
3. 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 wal_level to archive, 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 || (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_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.

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_start_backup() & pg_stop_backup() calls in order to get a usable backup?

Answer 1

Yes, base/ will likely be changed while the backup is occurring. And yes, you need all of the WAL segments for the time between pg_start_backup() and pg_stop_backup().

Your existing filesystem backup tools do not provide you with the tools you need to back up a live database. This is not unusual. Rather than trying to fit a square peg into a round hole, why not just tell your filesystem backup tool to ignore the database, and then use a dedicated database backup tool to backup the database? For example, use pg_basebackup in conjunction with WAL archiving, or pg_basebackup -X s as a standalone backup. Of course, the files created as the result of this backup need to themselves backed up, but at that point they are "cold" files and your regular backup system will work.