[Original] detailed explanation of PostgreSQL Incremental backup and related examples

PostgreSQL does not have binary logs similar to MySQL, but it has a REDOLOG similar to MySQL and does not have the REDO archiving function. Of course, REDO archiving has been implemented by MariaDB and Percona-MySQL. Then I

PostgreSQL does not have binary logs similar to MySQL, but it has a REDOLOG similar to MySQL and does not have the REDO archiving function. Of course, REDO archiving has been implemented by MariaDB and Percona-MySQL. Then I

PostgreSQL does not have binary logs similar to MySQL, but there are REDO logs similar to MySQL, and there is a REDO archiving function not available in MySQL. Of course, REDO archiving has been implemented by MariaDB and Percona-MySQL.

Then we can use PGSQL's REDO archive, also known as WAL, to implement Incremental backup.

Assume that we have completed a physical full backup and the corresponding parameters have been set. For example:

/Home/full_backup saves the full backup directory. /Home/increment_log to save the incremental directory. Go to postgresql. conf to enable WAL. wal_level = archive wal_keep_segments = 1 to enable the archive mode of WAL. archive_mode = on

Archive_command = 'test! -F/home/increment_log/% f & cp % p/home/increment_log/% f' (archive, that is, incremental)

Note that if the CRASH fails, xlogs that are not archived may not be submitted to the data file. Therefore, this part of data may be lost. Therefore, we need to copy this part of XLOG to the corresponding directory for full backup during subsequent recovery.

Recovery Method:

1. Ensure that no business accesses PG services. (PG may have crashed at this time .) 2. Stop the POSTGRESQL service first. 3. cd/home/pgsql; 4. mv data data_old6. cp-rfp/home/full_backup/*/home/pgsql (copy full backup) 7. cp-rfp data_old/pg_xlog/000 * data/pg_xlog/(copy the XLOG from the last increment to the system CRASH to the corresponding directory .) 8. cd data9. vi recovery. conf (add the recovery. conf file to make PG think it is being restored .) Restore_command = 'test! -F/home/increment_log/% f & cp/home/increment_log/% f % P' recovery_target_timeline = 'latest '10. chown-R postgres. postgres recovery. conf11. start the postgresql service.

If the recovery is correct, the recovery. conf will be automatically renamed as recovery. done.

Take my machine as an example. My version is 9.3, And the BACKUP information shown in PG_XLOG is as follows:

[Root @ postgresql-instance pg_xlog] # cat 00000000000000000000003.00000028.backupstart wal location: 0/3000028 (file 000000010000000000000003) stop wal location: 0/000000010000000000000003 B8 (file 0/3000028) checkpoint location: backup method: pg_start_backupBACKUP FROM: masterSTART TIME: 2014-01-07 03:35:02 ESTLABEL: yttSTOP TIME: 2014-01-07 03:36:27 EST

The following recovery information is displayed in the log:

<05:02:47. 361 EST> LOG: 00000: starting archive recovery <05:02:47. 361 EST> LOCATION: StartupXLOG, xlog. c: 4983 <05:02:47. 369 EST> LOG: 00000: redo starts at 0/3000028 <05:02:47. 369 EST> LOCATION: StartupXLOG, xlog. c: 5531 <05:02:47. 371 EST> LOG: 00000: consistent recovery state reached at 0/303FC88

This article is from "god, let's see it !" Blog, please keep this source