PITR有三种方式：
1：recovery_target_time
2：recovery_target_xid
3：recovery_target_name
前两种都与recovery_target_inclusive的配置（ recovery.conf ）有关。
pause_at_recovery_target：只对recovery_target_xid和recovery_target_time 有效，对于recovery_target_name 无效。 recovery_target_inclusive：在recovery.conf中recovery_target_inclusive=true 只作用于PITR到
recovery_target_xid (XID)/recovery_target_time (时间) 恢复时，不作用于recovery_target_name 。
下面来分别演示一下这三种方式的PITR：
1：recovery_target_time：
给予时间的恢复需要通过select now();方法来获取恢复的时间点。
[pg93@localhost ms]$ mkdir data[pg93@localhost ms]$ pwd/home/pg93/ms[pg93@localhost ms]$ initdb -D data -E UTF8 --locale=C -U postgres -W [pg93@localhost ms]$ cd data[pg93@localhost data]$ pwd/home/pg93/ms/data[pg93@localhost data]$ mkdir arch[pg93@localhost data]$ vi postgresql.conf ........listen_addresses = '*' # what IP address(es) to listen on; # comma-separated list of addresses; # defaults to 'localhost'; use '*' for all # (change requires restart)port = 5432 # (change requires restart)wal_level = archive # minimal, archive, or hot_standby # (change requires restart)archive_mode = on # allows archiving to be done # (change requires restart)archive_command = 'cp %p /home/pg93/ms/data/arch/%f' # command to use to archive a logfile segment # placeholders: %p = path of file to archive # %f = file name only # e.g. 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'........[pg93@localhost data]$ cd ..[pg93@localhost ms]$ pwd/home/pg93/ms[pg93@localhost ms]$ pg_ctl start -D data#查看启动是否成功[pg93@localhost ms]$ netstat -anp|grep post(Not all processes could be identified, non-owned process info will not be shown, you would have to be root to see it all.)tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN 3055/postgres tcp 0 0 :::5432 :::* LISTEN 3055/postgres udp 0 0 ::1:52886 ::1:52886 ESTABLISHED 3055/postgres unix 2 [ ACC ] STREAM LISTENING 44439 3055/postgres /tmp/.s.PGSQL.5432#登陆数据库postgres[pg93@localhost ms]$ psql -h localhost -p 5432 postgres postgrespsql (9.3.4)Type "help" for help.postgres=# create table test(id integer); CREATE TABLEpostgres=# insert into test values(100); INSERT 0 1postgres=# select pg_start_backup('gao'); pg_start_backup ----------------- 0/2000028(1 row)#在此处开始备份（从新开启一个终端）begin[pg93@localhost ms]$ pwd/home/pg93/ms[pg93@localhost ms]$ tar -cvf ./base.tar ./data#在此处开始备份（从新开启一个终端）endpostgres=# select pg_stop_backup(); NOTICE: pg_stop_backup complete, all required WAL segments have been archived pg_stop_backup ---------------- 0/20000B8(1 row)#插入一条数据200并记录时间postgres=# insert into test values(200); INSERT 0 1postgres=# select now(); now ------------------------------- 2014-05-30 14:52:56.517052+08#隔一段时间后在插入一条记录并记录时间postgres=# insert into test values(300); INSERT 0 1postgres=# select now(); now -------------------------------2014-05-30 14:55:07.873633+08(1 row)#切换事务日志postgres=# select pg_switch_xlog(); pg_switch_xlog ---------------- 0/3000298(1 row)#kill掉此数据库（在新终端）begin[pg93@localhost ms]$ netstat -anp|grep post(Not all processes could be identified, non-owned process info will not be shown, you would have to be root to see it all.)tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN 3055/postgres tcp 0 0 :::5432 :::* LISTEN 3055/postgres tcp 0 0 ::1:5432 ::1:40192 ESTABLISHED 3066/postgres: post udp 0 0 ::1:52886 ::1:52886 ESTABLISHED 3055/postgres unix 2 [ ACC ] STREAM LISTENING 44439 3055/postgres /tmp/.s.PGSQL.5432[pg93@localhost ms]$ kill -s SIGQUIT 3055#kill掉此数据库（在新终端）end [pg93@localhost ms]$ pwd/home/pg93/ms[pg93@localhost ms]$ mv ./data ./data.bak [pg93@localhost ms]$ tar -xvf base.tar ./data [pg93@localhost ms]$ rm -rf ./data/pg_xlog [pg93@localhost ms]$ cp -r ./data.bak/pg_xlog/ ./data [pg93@localhost ms]$ cd ./data/pg_xlog/archive_status/[pg93@localhost archive_status]$ rm -f * #切换到数据库目录[pg93@localhost data]$ pwd/home/pg93/ms/data#拷贝recovery文件[pg93@localhost data]$ cp /opt/pgsql934/share/recovery.conf.sample recovery.confvi recovery.conf............recovery_target_time = '2014-05-30 14:53:56.517052+08' # e.g. '2004-07-14 22:39:00 EST'restore_command = 'cp /home/pg93/ms/data/arch/%f %p' # e.g. 'cp /mnt/server/archivedir/%f %p'............[pg93@localhost ms]$ pwd/home/pg93/ms#启动数据库[pg93@localhost ms]$ pg_ctl start -D datapg_ctl: another server might be running; trying to start server anywayserver starting[pg93@localhost ms]$ LOG: database system was interrupted; last known up at 2014-05-30 15:30:01 CSTLOG: starting point-in-time recovery to 2014-05-30 15:31:28.38385+08cp: cannot stat `/home/pg93/ms/data/arch/000000010000000000000003': No such file or directoryLOG: redo starts at 0/3000090LOG: consistent recovery state reached at 0/30000B8cp: cannot stat `/home/pg93/ms/data/arch/000000010000000000000004': No such file or directoryLOG: recovery stopping before commit of transaction 1813, time 2014-05-30 15:32:37.68031+08LOG: redo done at 0/4000148LOG: last completed transaction was at log time 2014-05-30 15:30:23.75722+08cp: cannot stat `/home/pg93/ms/data/arch/00000002.history': No such file or directoryLOG: selected new timeline ID: 2cp: cannot stat `/home/pg93/ms/data/arch/00000001.history': No such file or directoryLOG: archive recovery completeLOG: database system is ready to accept connectionsLOG: autovacuum launcher started[pg93@localhost ms]$#查看恢复是否成功[pg93@localhost arch]$ psql -h localhost -p 5432 postgres postgrespsql (9.3.4)Type "help" for help.postgres=# \z Access privileges Schema | Name | Type | Access privileges | Column access privileges --------+------+-------+-------------------+-------------------------- public | test | table | | (1 row)postgres=# select * from test; id ----- 100 200(2 rows)#结果没有300这条记录说明恢复成功。
恢复需要记录事务的xid，通过select txid_current();获取到
[pg93@localhost ms]$ mkdir data[pg93@localhost ms]$ pwd/home/pg93/ms[pg93@localhost ms]$ initdb -D data -E UTF8 --locale=C -U postgres -W [pg93@localhost ms]$ cd data[pg93@localhost data]$ pwd/home/pg93/ms/data[pg93@localhost data]$ mkdir arch[pg93@localhost data]$ vi postgresql.conf ........listen_addresses = '*' # what IP address(es) to listen on; # comma-separated list of addresses; # defaults to 'localhost'; use '*' for all # (change requires restart)port = 5432 # (change requires restart)wal_level = archive # minimal, archive, or hot_standby # (change requires restart)archive_mode = on # allows archiving to be done # (change requires restart)archive_command = 'cp %p /home/pg93/ms/data/arch/%f' # command to use to archive a logfile segment # placeholders: %p = path of file to archive # %f = file name only # e.g. 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'........[pg93@localhost data]$ cd ..[pg93@localhost ms]$ pwd/home/pg93/ms[pg93@localhost ms]$ pg_ctl start -D data#登陆数据库postgres[pg93@localhost ms]$ psql -h localhost -p 5432 postgres postgrespsql (9.3.4)Type "help" for help.postgres=# create table test(id integer); CREATE TABLEpostgres=# insert into test values(100); INSERT 0 1postgres=# select pg_start_backup('gao'); pg_start_backup ----------------- 0/2000028(1 row)#在此处开始备份（从新开启一个终端）begin[pg93@localhost ms]$ pwd/home/pg93/ms[pg93@localhost ms]$ tar -cvf ./base.tar ./data#在此处开始备份（从新开启一个终端）endpostgres=# select pg_stop_backup(); NOTICE: pg_stop_backup complete, all required WAL segments have been archived pg_stop_backup ---------------- 0/20000B8(1 row)#插入一条数据200并记录事务idpostgres=# begin;BEGINpostgres=# insert into test values(200); INSERT 0 1postgres=# insert into test values(200); INSERT 0 1postgres=# insert into test values(200); INSERT 0 1postgres=# insert into test values(200); INSERT 0 1postgres=# select txid_current(); txid_current -------------- 1812(1 row)postgres=# end;COMMITpostgres=# select pg_switch_xlog(); pg_switch_xlog ---------------- 0/30001E8(1 row)#切换事务日志postgres=# select pg_switch_xlog(); pg_switch_xlog ---------------- 0/30001E8(1 row)#kill掉此数据库（在新终端）begin通过netstat 查找到数据库的pid为3432然后kill掉.[pg93@localhost ms]$ netstat -anp|grep post[pg93@localhost ms]$ kill -s SIGQUIT 3432 #kill掉此数据库（在新终端）end 开始恢复 [pg93@localhost ms]$ pwd/home/pg93/ms[pg93@localhost ms]$ mv ./data ./data.bak [pg93@localhost ms]$ tar -xvf base.tar ./data [pg93@localhost ms]$ rm -rf ./data/pg_xlog [pg93@localhost ms]$ cp -r ./data.bak/pg_xlog/ ./data [pg93@localhost ms]$ cd ./data/pg_xlog/archive_status/[pg93@localhost archive_status]$ rm -f * #切换到数据库目录[pg93@localhost data]$ pwd/home/pg93/ms/data#拷贝recovery文件[pg93@localhost data]$ cp /opt/pgsql934/share/recovery.conf.sample recovery.confvi recovery.conf............restore_command = 'cp /home/pg93/ms/data/arch/%f %p' # e.g. 'cp /mnt/server/archivedir/%f %p'#recovery_target_name = '' # e.g. 'daily backup 2011-01-26'#recovery_target_time = '' # e.g. '2004-07-14 22:39:00 EST'recovery_target_xid = '1812'recovery_target_inclusive = falserecovery_target_timeline = 'latest'pause_at_recovery_target = false............[pg93@localhost ms]$ pwd/home/pg93/ms#启动数据库[pg93@localhost ms]$ pg_ctl start -D dataserver starting[pg93@localhost ms]$ LOG: database system was interrupted; last known up at 2014-05-30 15:57:06 CSTcp: cannot stat `/home/pg93/ms/data/arch/00000002.history': No such file or directoryLOG: starting point-in-time recovery to XID 1812cp: cannot stat `/home/pg93/ms/data/arch/000000010000000000000002': No such file or directoryLOG: redo starts at 0/2000090LOG: consistent recovery state reached at 0/20000B8cp: cannot stat `/home/pg93/ms/data/arch/000000010000000000000003': No such file or directoryLOG: recovery stopping before commit of transaction 1812, time 2014-05-30 15:57:48.846833+08LOG: redo done at 0/3000198cp: cannot stat `/home/pg93/ms/data/arch/00000002.history': No such file or directoryLOG: selected new timeline ID: 2cp: cannot stat `/home/pg93/ms/data/arch/00000001.history': No such file or directoryLOG: archive recovery completeLOG: database system is ready to accept connectionsLOG: autovacuum launcher started[pg93@localhost ms]$#查看恢复是否成功[pg93@localhost ms]$ psql -h localhost -p 5432 postgres postgrespsql (9.3.4)Type "help" for help.postgres=# select * from test; id ----- 100(1 row)此时没有查到插入进去的200数据则恢复成功。在恢复时如果recovery_target_inclusive为true则在查询的时候会出现200这条数据：
此时没有显示200的原因为：postgres=# create extension pageinspect ;CREATE EXTENSIONpostgres=# select * from heap_page_items(get_raw_page('test',0)); lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid ----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+------- 1 | 8160 | 1 | 28 | 1811 | 0 | 0 | (0,1) | 1 | 2304 | 24 | | 2 | 8128 | 1 | 28 | 1812 | 0 | 0 | (0,2) | 1 | 2560 | 24 | | 3 | 8096 | 1 | 28 | 1812 | 0 | 0 | (0,3) | 1 | 2560 | 24 | | 4 | 8064 | 1 | 28 | 1812 | 0 | 0 | (0,4) | 1 | 2560 | 24 | | 5 | 8032 | 1 | 28 | 1812 | 0 | 0 | (0,5) | 1 | 2560 | 24 | | (5 rows)解释几个参数：t_xmin：为此条记录插入insert时的事务ID。t_xmax：为此条记录更新update时的事务ID。t_infomask：为HEAP_XMIN_INVALID ， HEAP_XMAX_INVALID和HEAP_XMIN_COMMITTED ，HEAP_XMAX_COMMITTED 和或关系,如下：#define HEAP_XMIN_INVALID 0x0200 /* t_xmin invalid/aborted */#define HEAP_XMAX_INVALID 0x0800 /* t_xmax invalid/aborted */ #define HEAP_XMIN_COMMITTED 0x0100 /* t_xmin committed */#define HEAP_XMAX_COMMITTED 0x0400 /* t_xmax committed */256=0x01002048=0x0800512=0x02001024=0x04002304=0x0100|0x0800768=0x0100|0x02001280=0x0100|0x04002560=0x0800 |0x02003072=0x0800 |0x04001536= 0x0200|0x0400只有当t_infomask的值满足VALID并且COMMITTED时，此条记录才会显示。对于上面举得XID恢复的例子中recovery_target_inclusive对于xid=1812，如果是true则显示，否则不显示。 3：recovery_target_name
大部分都和上面两种恢复方式相同，区别是
1：不依赖与recovery_target_inclusive和pause_at_recovery_target。
2：需要事先 使用pg_create_restore_point 来创建一个还原点，然后在recovery.conf中将其中的recovery_target_name改变为还原点即可。