PostgreSQL's pitr Technology (point-in-time-recovery)

PostgreSQL's pitr Technology (point-in-time-recovery)
-- Seamus Dean (at PostgreSQL-8.0.2 release)

Why write this?Article?
I have read that all the PostgreSQL tutorials in China have not introduced this function in detail. On the contrary, the domestic Oracle
This article is very important. However, the official PostgreSQL documentation has a chapter that describes this content,
However, it is written too much in literature.
Indeed, the reliability and integrity of a database are very important. Otherwise, it is hard for people to accept it.

This article assumes that you have a basic understanding of PostgreSQL. If you are not familiar with PostgreSQL, we recommend that you first
Go to the http://www.postgresql.org to see its documentation.

As the most powerful open-source database, PostgreSQL has all the functions of commercial databases, even better than commercial databases.
In earlier versions, data recovery and reliability were not very good. However, after the development in recent years
Comparable.

During sql7, WAL (write Ahead Logging) was introduced, that is, pre-write logs, all changes to the database,
This log must be written before the change. In this way, even if the machine is powered off, PostgreSQL can also know from this log that the database was created before the power-off.
Which of the following operations has been done in step 1, which ensures the integrity of all transactions, but sql7 does not provide good disaster recovery?
Mechanism, once the database crashes, unless you have performed pg_dump or file system level backup on the database, your data
All data will be lost, and even if you have backed up the database, you can only restore the data at the moment you backed up.
(Especially the 24*7 production database) is intolerable.

With the launch of postgresql 8, PostgreSQL's stability and reliability have taken another epoch step.
In addition to tablespace support, postgresql8 provides support for point-in-time recovery --- pitr.
The basic principle is the same as that of Oracle Hot Backup:

First, make a backup for the database at file system level (PostgreSQL uses the pg_start_backup ('label') command first,
After that, use the entire dataproject of tardirectly tar.tar.aliyun.com, and set the name as base.tar, then pg_stop_backup (); to end hot backup.
Oracle uses alter tablespace XXX begin backup and CP data files directly );

Then, back up the relevant configuration files (PostgreSQL only needs to back up PostgreSQL. conf, pg_mirror.conf, and pg_ident.conf. In fact,
The preceding tar files have been backed up. Oracle needs to alter database backup control file ......);

Finally, back up Wal (
You can set archive_command in PostgreSQL. conf,
This command enables PostgreSQL 8 to automatically back up the archived log files elsewhere.
Note: If you want postgresql8 to call archive_command to back up Wal,
However, pitris is the only way to do this. If I have tried it, the wal generated by base.tar and archive_command can only be restored to the last one.
The wal data stored by archive_command may be stored in pg_xlog/. If the data directory of postgresql8 is completely damaged
Data loss. Therefore, we recommend that you back up the wal under pg_xlog/when writing a data backup script. See cparch. Sh below.
).

If the data database crashes, we can use the wal generated by the standby base.tar and archive_command and the wal backed up by ourselves (pg_xlog) to perform database operations.
Recovery.

The following is an example:
My PostgreSQL runs under:/home/pgsql/
The data directory is/home/pgsql/database/
Back up hot backup data files to/disk3/PostgreSQL/base/
Back up Wal to/disk3/PostgreSQL/archives/

The following archive_command is defined in PostgreSQL. conf:
Archive_command = 'cp-F % P/disk3/PostgreSQL/archives/% F'
This command will send the wal CP generated by PostgreSQL to/disk3/PostgreSQL/archives.

My hot standby script is as follows:
(1) to keep the lost data within one minute
Backup To/disk3/PostgreSQL/archives /.

Crontab:
*/1 *****/home/pgsql/bin/cparch. Sh

Cparch. sh:
#! /Bin/sh

CP-f/home/pgsql/database/pg_xlog/[0-9] */disk3/PostgreSQL/archives/

(2) Compile the Hot Standby script hotbackup. pl (I use Perl ):
#! /Usr/bin/perl

######################################## #####################
# Hotbackup. pl
# Use to hot backup the PostgreSQL database.
# Author: Seamus Dean
# Date: 2005-04-11
######################################## ######################

My ($ datadir) = "/home/pgsql/Database ";
My ($ bindir) = "/home/pgsql/bin ";
My ($ backupdir) = "/disk3/PostgreSQL/base ";
My ($ Cycler) = "ljh13 \ @ sina.com.cn ";

Sub begin_backup ()
{
Open (Psql, "| $ bindir/Psql") or mail_user ("begin backup error.") & Exit (100 );
Print Psql "select pg_start_backup ('backupnow '); \ n ";
Close (Psql );

}

Sub end_backup ()
{
Open (Psql, "| $ bindir/Psql") or mail_user ("End backup error.") & Exit (100 );
Print Psql "select pg_end_backup (); \ n ";
Close (Psql );
}

Sub do_backup ()
{
System ("/bin/tar CVF base.tar $ datadir ");
System ("/bin/MV-F base.tar $ backupdir /");
}

Sub mail_user ()
{
My ($ MSG) = @_;
Open (Mail, "|/bin/mail-s backup-result $ author Er") or die ("can not talk to: MAIL command. \ n ");
Print mail $ MSG;
Close (Mail );
}

###################################
# Tell Psql begin our backup
###################################
& Begin_backup ();

###################################
# Do tar
###################################
& Do_backup ();

####################################
# Tell Psql end backup
####################################
& End_backup ();

####################################
# Mail the user about the result
####################################
& Mail_user ("PostgreSQL backup successfully .");

Here, the backup script is basically complete. You can periodically execute hotbackup. pl in crontab.

Even if the/home/pgsql/database directory crashes completely, we can quickly restore the data within one minute as follows:
# Cp/disk3/PostgreSQL/base/base.tar ./
# Tar xvf base.tar
# CD database/
# Vi recovery. conf
Enter the following content:
Restore_command = 'cp/disk3/PostgreSQL/archives/% F "% P "'
Then, clear the wal in/home/pgsql/database/pg_xlog.
Start PostgreSQL and we can see the following log information:
Log: cocould not create IPv6 socket: address family not supported by Protocol
Log: database system was interrupted at 23:13:28 PDT
Log: Starting archive recovery
Log: restore_command = "CP/disk3/PostgreSQL/archives/% F" % P ""
CP: cannot stat '/disk3/PostgreSQL/archives/00000001. History': no such file or directory
Log: restored Log File "0000000000000000000002e. 008efcac. Backup" from archive
Log: restored Log File "0000000000000000000002e" from archive
Log: Checkpoint record is at 0/2e8efcac
Log: redo record is at 0/2e8efcac; UNDO record is at 0/0; shutdown false
Log: Next transaction ID: 5271, And next OID: 6351357
Log: Automatic Recovery in progress
Log: redo starts at 0/2e8efce8
Log: restored Log File "0000000000000000000002f" from archive
Log: restored Log File "000000010000000000000030" from archive
Log: restored Log File "000000010000000000000031" from archive
Log: restored Log File "000000010000000000000032" from archive
Log: restored Log File "000000010000000000000033" from archive
Log: restored Log File "000000010000000000000034" from archive
Log: restored Log File "000000010000000000000035" from archive
Log: restored Log File "000000010000000000000036" from archive
Log: restored Log File "000000010000000000000037" from archive
Log: restored Log File "000000010000000000000038" from archive
Log: restored Log File "000000010000000000000039" from archive
Log: restored Log File "0000000000000000000003a" from archive
Log: restored Log File "0000000000000000000003b" from archive
Log: restored Log File "0000000000000000000003c" from archive
Log: restored Log File "0000000000000000000003d" from archive
Log: restored Log File "0000000000000000000003e" from archive
Log: restored Log File "0000000000000000000003f" from archive
Log: restored Log File "000000010000000000000040" from archive
Log: restored Log File "000000010000000000000041" from archive
Log: restored Log File "000000010000000000000042" from archive
Log: restored Log File "000000010000000000000043" from archive
Log: restored Log File "000000010000000000000044" from archive
Log: restored Log File "000000010000000000000045" from archive
Log: restored Log File "000000010000000000000046" from archive
Log: restored Log File "000000010000000000000047" from archive
Log: restored Log File "000000010000000000000048" from archive
Log: restored Log File "000000010000000000000049" from archive
Log: restored Log File "0000000000000000000004a" from archive
Log: restored Log File "0000000000000000000004b" from archive
Log: restored Log File "000000000000000004c" from archive
Log: record with zero length at 0/4c2babe4
Log: redo done at 0/4c2baba8
Log: restored Log File "000000000000000004c" from archive
Log: Archive recovery complete
Log: database system is ready

The data is successfully restored.
/Home/pgsql/database/The following recovery. conf will be changed to: recovery. Done.

conclusion:
the pitr of postgresql8 has been very successful and is likely to replace Oracle and sqlserver
. Therefore, we must have confidence in PostgreSQL!