Incrementally Updated Backups

From PostgreSQL wiki

(Difference between revisions)
Jump to: navigation, search
(Incrementally Updated Backups)
(Incrementally Updated Backups)
Line 4: Line 4:
 
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.
 
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.
  
If we take a '''valid''' file system backup of the standby server's data directory while it is processing logs shipped from the primary, we will be able to use that new backup to create a new standby or for continuous archive recovery. 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.
+
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 procedure for taking a valid file system backup of the standby server's data directory while it's processing logs shipped from the primary is quite complicatedThis procedure must be followed carefully, or the backup will not be valid.  The steps are:
+
The easiest way to take a valid file system backup of the standby server is to completely stop the standby server (with, for example, <code>pg_ctl stop</code>, verifying that it did in fact stop), take a cold file-system backup of it, and restart the standby.
 +
 
 +
If stopping the standby is undesirable (for example, because it is a hot standby), 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.
 
1.      Perform pg_start_backup() on the master, noting the WAL filename and offset reported.

Revision as of 21:47, 21 November 2012

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.

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 restart the standby.

If stopping the standby is undesirable (for example, because it is a hot standby), 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

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.

Personal tools