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

Source: Internet
Author: User
Tags percona
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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.