Incrementally Updated Backups

In a standby configuration, it is possible to offload the expense of taking periodic base backups from the primary server; instead base backups can be made by backing up a standby server's files. This concept is generally known as incrementally updated backups, log change accumulation, or more simply, change accumulation.

Stopping the standby server

If we take a valid file system backup of the standby server's data directory, we will be able to use that new backup to create a new standby, or for continuous archive recovery of the master. We then no longer need to keep WAL files from before the new backup. If recovery is needed, it will be faster to recover from the incrementally updated backup than from the original base backup.

The easiest way to take a valid file system backup of the standby server is to completely stop the standby server (with, for example, pg_ctl stop, verifying that it did in fact stop), take a cold file-system backup of it and the WAL archive directory, and restart the standby.

To check if the server really stopped one could do:

   pg_controldata /path/to/data | grep "Database cluster state: *shut down" >/dev/null
   if [ $? -ne 0 ]; then
       # do some error handling
   fi

Without stopping the standby server

If stopping the standby is undesirable (for example, because it is a hot standby, or because it would then take too long to fail over should the primary fail), it is possible to take a file system backup while it is still processing logs shipped from the primary. However, this procedure is quite complicated and it must be followed carefully or the backup will not be valid. The steps are:

1. Perform pg_start_backup() on the master, noting the WAL filename and offset reported.

2. Copy the backup_label file from the primary's data directory to a temporary location:

     cp /path/to/master/backup_label /tmp

3. Run pg_controldata on the standby and wait for "Latest checkpoint's REDO location" to equal or exceed the WAL information reported by pg_start_backup(). You might need to call pg_switch_xlog() on the master to force the required WAL file to be sent to the standby.

4. Perform a file system backup of the standby's data directory.

5. Perform pg_stop_backup() on the master.

6. Move the saved backup_label file to the new backup directory:

     mv /tmp/backup_label /path/to/new_backup

Existing implementation

[https://github.com/omniti-labs/omnipitr

OmniPITR] set of tools for WAL replication does backups on slave server using similar technique, but without running any queries on master server. Instead it gets necessary information using pg_controldata calls on slave, and it builds backup_label (and backup "segment") files on its own.

Incrementally Updated Backups

Contents