Incrementally Updated Backups

From PostgreSQL wiki

(Difference between revisions)
Jump to: navigation, search
(Fix typo)
(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 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 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.
  
The procedure for taking a file system backup of the standby server's data directory while it's processing logs shipped from the primary is:
+
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 complicated.  This procedure 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.
Line 14: Line 14:
 
       cp /path/to/master/backup_label /tmp
 
       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() to force the required WAL file to be sent to the standby.
+
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.
 
4.      Perform a file system backup of the standby's data directory.

Revision as of 20:08, 20 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 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.

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 complicated. This procedure 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