PostgreSQL PITR THREE recovery target MODE: name,xid,time USE CASE - 1

26 minute read

背景

前面一篇BLOG分析了PostgreSQL PITR的停止点recoveryStopsHere,

PostgreSQL recovery target introduce

我们知道停止点的判断首先要满足xl_info是以下值之一 :

XLOG_XACT_COMMIT  
XLOG_XACT_ABORT  
XLOG_XACT_COMMIT_COMPACT  

也就是事务结束(提交或者回滚)的位置.

src/include/access/xact.h

/*  
 * XLOG allows to store some information in high 4 bits of log  
 * record xl_info field  
 */  
#define XLOG_XACT_COMMIT                        0x00  
#define XLOG_XACT_PREPARE                       0x10  
#define XLOG_XACT_ABORT                         0x20  
#define XLOG_XACT_COMMIT_PREPARED       0x30  
#define XLOG_XACT_ABORT_PREPARED        0x40  
#define XLOG_XACT_ASSIGNMENT            0x50  
#define XLOG_XACT_COMMIT_COMPACT        0x60  

或者是一个还原点 :

XLOG_RESTORE_POINT  

也就是使用pg_create_restore_point创建的还原点.

src/include/catalog/pg_control.h

/* XLOG info values for XLOG rmgr */  
#define XLOG_CHECKPOINT_SHUTDOWN                0x00  
#define XLOG_CHECKPOINT_ONLINE                  0x10  
#define XLOG_NOOP                                               0x20  
#define XLOG_NEXTOID                                    0x30  
#define XLOG_SWITCH                                             0x40  
#define XLOG_BACKUP_END                                 0x50  
#define XLOG_PARAMETER_CHANGE                   0x60  
#define XLOG_RESTORE_POINT                              0x70  
#define XLOG_FPW_CHANGE                         0x80  

注意

恢复PAUSE 的处理在判断recovery_target_inclusive前, 所以如果recovery.conf中使用了pause_at_recovery_target, 那么recovery_target_inclusive将不起作用.

src/backend/access/transam/xlog.c

				/*  
                                 * Have we reached our recovery target?  
                                 */  
                                if (recoveryStopsHere(record, &recoveryApply))  
                                {  
                                        if (recoveryPauseAtTarget)  
                                        {  
                                                SetRecoveryPause(true);  
                                                recoveryPausesHere();  
                                        }  
                                        reachedStopPoint = true;        /* see below */  
                                        recoveryContinue = false;  
  
                                        /* Exit loop if we reached non-inclusive recovery target */  
                                        if (!recoveryApply)  
                                                break;  
                                }  

下面以PostgreSQL 9.2.1为例, 来解释一下停止点的含义.

测试环境 :

$PGDATA=/data05/ocz/pg_root  
backupdir=/data05/ocz/pgbak  
archdir=/data05/ocz/pgbak/arch  
  
postgresql.conf  
wal_level = hot_standby  
archive_mode = on  
archive_command = 'cp %p /data05/ocz/pgbak/arch/%f'  
hot_standby = on  
  
recovery.conf  
restore_command = 'cp /data05/ocz/pgbak/arch/%f %p'  
#recovery_target_name = ''  
#recovery_target_time = ''      # e.g. '2004-07-14 22:39:00 EST'  
#recovery_target_xid = '1687'  
#recovery_target_inclusive = true  # 默认true  
recovery_target_timeline = 'latest'  
pause_at_recovery_target = true  
standby_mode = on  

1.

基础备份, 将$PGDATA拷贝到/data05/ocz/pgbak/pg_root

select pg_start_backup(now()::text);  
cp -r $PGDATA /data05/ocz/pgbak/pg_root  
select pg_stop_backup();  

2.

创建测试表 :

测试表 :

create table a(id int, crt_time timestamp);  
create table b(id int, crt_time timestamp);  
create table c(id int, crt_time timestamp);  

3.

请按顺序执行以下SQL, 注意每一个PITR恢复停止点.

一个长事务在执行过程中, 可能已经有几个新的事务执行并提交了.

根据恢复停止点的概念, 恢复到一个小的XID, 数据库打开的时候, 可能已经到了大的xid. 原因就是如此.

SESSION A :   
  
postgres=# begin;  
BEGIN  
postgres=# insert into a (id, crt_time) values (1, clock_timestamp()) returning id,crt_time;  
 id |          crt_time            
----+----------------------------  
  1 | 2013-02-04 15:30:44.091741  
(1 row)  
INSERT 0 1  
  
SESSION B :   
  
postgres=# begin;  
BEGIN  
postgres=# insert into b (id, crt_time) values (1, clock_timestamp()) returning id,crt_time;  
 id |         crt_time            
----+---------------------------  
  1 | 2013-02-04 15:31:05.93215  
(1 row)  
INSERT 0 1  
  
SESSION A :   
  
postgres=# insert into a (id, crt_time) values (2, clock_timestamp()) returning id,crt_time;  
 id |         crt_time            
----+---------------------------  
  2 | 2013-02-04 15:31:19.57222  
(1 row)  
INSERT 0 1  
  
SESSION C :   
  
postgres=# begin;  
BEGIN  
postgres=# insert into c (id, crt_time) values (1, clock_timestamp()) returning id,crt_time;  
 id |          crt_time            
----+----------------------------  
  1 | 2013-02-04 15:31:34.491555  
(1 row)  
INSERT 0 1  
  
SESSION D :   
  
postgres=# select pg_create_restore_point('test1');  
 pg_create_restore_point   
-------------------------  
 0/50026C8  
(1 row)  
-- 本例的第1个还原点. (recovery_target_name='test1')  
  
select * from a;  
select * from b;  
select * from c;  
  
-- 无数据.  
  
SESSION A :   
  
postgres=# insert into a (id, crt_time) values (3, clock_timestamp()) returning id,crt_time;  
 id |          crt_time            
----+----------------------------  
  3 | 2013-02-04 15:32:00.226317  
(1 row)  
INSERT 0 1  
  
SESSION C :   
  
postgres=# insert into c (id, crt_time) values (2, clock_timestamp()) returning id,crt_time;  
 id |          crt_time            
----+----------------------------  
  2 | 2013-02-04 15:32:19.168515  
(1 row)  
INSERT 0 1  
postgres=# select txid_current();  
 txid_current   
--------------  
         1696  
(1 row)  
postgres=# commit;  
COMMIT  
-- 本例的第2个还原点.(recovery_target_xid='1696')  
  
postgres=# select * from a;  
 id | crt_time   
----+----------  
(0 rows)  
postgres=# select * from b;  
 id | crt_time   
----+----------  
(0 rows)  
postgres=# select * from c;  
 id |          crt_time            
----+----------------------------  
  1 | 2013-02-04 15:31:34.491555  
  2 | 2013-02-04 15:32:19.168515  
(2 rows)  
  
SESSION D :   
  
postgres=# select pg_create_restore_point('test2');  
 pg_create_restore_point   
-------------------------  
 0/5002910  
(1 row)  
-- 本例的第3个还原点.(recovery_target_name='test2')  
  
postgres=# select * from a;  
 id | crt_time   
----+----------  
(0 rows)  
postgres=# select * from b;  
 id | crt_time   
----+----------  
(0 rows)  
postgres=# select * from c;  
 id |          crt_time            
----+----------------------------  
  1 | 2013-02-04 15:31:34.491555  
  2 | 2013-02-04 15:32:19.168515  
(2 rows)  
  
SESSION B :   
  
postgres=# select txid_current();  
 txid_current   
--------------  
         1695  
(1 row)  
postgres=# commit;  
COMMIT  
-- 本例的第4个还原点.(recovery_target_xid='1695')  
  
postgres=# select * from a;  
 id | crt_time   
----+----------  
(0 rows)  
postgres=# select * from b;  
 id |         crt_time            
----+---------------------------  
  1 | 2013-02-04 15:31:05.93215  
(1 row)  
postgres=# select * from c;  
 id |          crt_time            
----+----------------------------  
  1 | 2013-02-04 15:31:34.491555  
  2 | 2013-02-04 15:32:19.168515  
(2 rows)  
  
SESSION A :   
  
postgres=# insert into a (id, crt_time) values (4, clock_timestamp()) returning id,crt_time;  
 id |          crt_time            
----+----------------------------  
  4 | 2013-02-04 15:34:35.589298  
(1 row)  
INSERT 0 1  
postgres=# select txid_current();  
 txid_current   
--------------  
         1694  
(1 row)  
postgres=# commit;  
COMMIT  
-- 本例的第5个还原点.(recovery_target_xid='1694')  
  
postgres=# select * from a;  
 id |          crt_time            
----+----------------------------  
  1 | 2013-02-04 15:30:44.091741  
  2 | 2013-02-04 15:31:19.57222  
  3 | 2013-02-04 15:32:00.226317  
  4 | 2013-02-04 15:34:35.589298  
(4 rows)  
postgres=# select * from b;  
 id |         crt_time            
----+---------------------------  
  1 | 2013-02-04 15:31:05.93215  
(1 row)  
postgres=# select * from c;  
 id |          crt_time            
----+----------------------------  
  1 | 2013-02-04 15:31:34.491555  
  2 | 2013-02-04 15:32:19.168515  
(2 rows)  
  
SESSION B :   
  
postgres=# begin;  
BEGIN  
postgres=# insert into b (id, crt_time) values (2, clock_timestamp()) returning id,crt_time;  
 id |          crt_time            
----+----------------------------  
  2 | 2013-02-04 15:35:02.348599  
(1 row)  
INSERT 0 1  
  
SESSION C :   
  
postgres=# begin;  
BEGIN  
postgres=# insert into c (id, crt_time) values (3, clock_timestamp()) returning id,crt_time;  
 id |          crt_time            
----+----------------------------  
  3 | 2013-02-04 15:35:18.284565  
(1 row)  
INSERT 0 1  
postgres=# select txid_current();  
 txid_current   
--------------  
         1698  
(1 row)  
postgres=# commit;  
COMMIT  
-- 本例的第6个还原点.(recovery_target_xid='1698')  
  
postgres=# select * from a;  
 id |          crt_time            
----+----------------------------  
  1 | 2013-02-04 15:30:44.091741  
  2 | 2013-02-04 15:31:19.57222  
  3 | 2013-02-04 15:32:00.226317  
  4 | 2013-02-04 15:34:35.589298  
(4 rows)  
postgres=# select * from b;  
 id |         crt_time            
----+---------------------------  
  1 | 2013-02-04 15:31:05.93215  
(1 row)  
postgres=# select * from c;  
 id |          crt_time            
----+----------------------------  
  1 | 2013-02-04 15:31:34.491555  
  2 | 2013-02-04 15:32:19.168515  
  3 | 2013-02-04 15:35:18.284565  
(3 rows)  
  
SESSION B :   
  
postgres=# select txid_current();  
 txid_current   
--------------  
         1697  
(1 row)  
postgres=# rollback;  
ROLLBACK  
-- 本例的第7个还原点.(recovery_target_xid='1697')  
  
postgres=# select * from a;  
 id |          crt_time            
----+----------------------------  
  1 | 2013-02-04 15:30:44.091741  
  2 | 2013-02-04 15:31:19.57222  
  3 | 2013-02-04 15:32:00.226317  
  4 | 2013-02-04 15:34:35.589298  
(4 rows)  
postgres=# select * from b;  
 id |         crt_time            
----+---------------------------  
  1 | 2013-02-04 15:31:05.93215  
(1 row)  
postgres=# select * from c;  
 id |          crt_time            
----+----------------------------  
  1 | 2013-02-04 15:31:34.491555  
  2 | 2013-02-04 15:32:19.168515  
  3 | 2013-02-04 15:35:18.284565  
(3 rows)  
  
SESSION E :   
  
postgres=# checkpoint;  
select pg_switch_xlog();  
checkpoint;  
select pg_switch_xlog();CHECKPOINT  
postgres=# select pg_switch_xlog();  
 pg_switch_xlog   
----------------  
 0/5002CB0  
(1 row)  
postgres=# checkpoint;  
CHECKPOINT  
postgres=# select pg_switch_xlog();  确保已归档  
 pg_switch_xlog   
----------------  
 0/60000D8  
(1 row)  

4.

关闭数据库 :

ocz@db-172-16-3-150-> pg_ctl stop -m fast  
waiting for server to shut down.... done  
server stopped  

5.

还原测试

注意

1. 每测试1个还原点后, 测试下一个还原点时重新从基础备份开始.

2. 还原前修改postgresql.conf的archive_command. 以免启动数据库后覆盖以前的arch.

archive_command = 'cp %p /data05/ocz/pgbak/archnew/%f'  

1

还原到第1个还原点, recovery_target_name=’test1’,

recovery_target_inclusive对于还原目标是restore name的无效, 这个在上一篇BLOG中已经讲过了.

recovery.conf

restore_command = 'cp /data05/ocz/pgbak/arch/%f %p'  
recovery_target_name = 'test1'  
recovery_target_timeline = 'latest'  
pause_at_recovery_target = false  

启动后

postgres=# create extension pageinspect;  
CREATE EXTENSION  

能看到么?

postgres=# select * from a;  
 id | crt_time   
----+----------  
(0 rows)  
postgres=# select * from b;  
 id | crt_time   
----+----------  
(0 rows)  
postgres=# select * from c;  
 id | crt_time   
----+----------  
(0 rows)  
  
-- 使用pageinspect可以看到tuple哦!  
  
postgres=# select * from heap_page_items(get_raw_page('a',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 |   8152 |        1 |     40 |   1694 |      0 |        0 | (0,1)  |           2 |       2560 |     24 |        |        
  2 |   8112 |        1 |     40 |   1694 |      0 |        0 | (0,2)  |           2 |       2560 |     24 |        |        
(2 rows)  
postgres=# select * from heap_page_items(get_raw_page('b',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 |   8152 |        1 |     40 |   1695 |      0 |        0 | (0,1)  |           2 |       2560 |     24 |        |        
(1 row)  
postgres=# select * from heap_page_items(get_raw_page('c',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 |   8152 |        1 |     40 |   1696 |      0 |        0 | (0,1)  |           2 |       2560 |     24 |        |        
(1 row)  

为什么查看不到呢?

解释t_infomask :

2560 = 0x0A00 由如下mask组成 :   
#define HEAP_XMIN_INVALID               0x0200  /* t_xmin invalid/aborted */  
#define HEAP_XMAX_INVALID               0x0800  /* t_xmax invalid/aborted */  

因为xmin 和xmax invalid的掩码都被设置了, 所以处理这些记录的事务还未提交或者已经abort. 当然是看不到的.

解释t_xmin :

就是插入这些记录的事务号. 在本例中已经使用txid_current显示出来, 大家可以对照去理解。

如果是已提交的事务, 应该有这两个标记之一 :

#define HEAP_XMIN_COMMITTED             0x0100  /* t_xmin committed */  
#define HEAP_XMAX_COMMITTED             0x0400  /* t_xmax committed */  

2

还原到第2个还原点, recovery_target_inclusive = false

recovery.conf

restore_command = 'cp /data05/ocz/pgbak/arch/%f %p'  
recovery_target_xid = '1696'  
recovery_target_inclusive = false  
recovery_target_timeline = 'latest'  
pause_at_recovery_target = false  

启动后

postgres=# create extension pageinspect;  
CREATE EXTENSION  
postgres=# select * from heap_page_items(get_raw_page('a',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 |   8152 |        1 |     40 |   1694 |      0 |        0 | (0,1)  |           2 |       2048 |     24 |        |        
  2 |   8112 |        1 |     40 |   1694 |      0 |        0 | (0,2)  |           2 |       2048 |     24 |        |        
  3 |   8072 |        1 |     40 |   1694 |      0 |        0 | (0,3)  |           2 |       2048 |     24 |        |        
(3 rows)  
postgres=# select * from heap_page_items(get_raw_page('b',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 |   8152 |        1 |     40 |   1695 |      0 |        0 | (0,1)  |           2 |       2048 |     24 |        |        
(1 row)  
postgres=# select * from heap_page_items(get_raw_page('c',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 |   8152 |        1 |     40 |   1696 |      0 |        0 | (0,1)  |           2 |       2048 |     24 |        |        
  2 |   8112 |        1 |     40 |   1696 |      0 |        1 | (0,2)  |           2 |       2048 |     24 |        |        
(2 rows)  
  
-- 注意这里的t_infomask是, 2048 = 0x0800  
-- 也就是标记了HEAP_XMAX_INVALID, 但是未标记HEAP_XMIN_INVALID以及HEAP_XMIN_COMMITTED, 所以还是不可见的.  
  
postgres=# select * from a;  
 id | crt_time   
----+----------  
(0 rows)  
postgres=# select * from b;  
 id | crt_time   
----+----------  
(0 rows)  
postgres=# select * from c;  
 id | crt_time   
----+----------  
(0 rows)  
  
-- 查询数据后, 再通过pageinspect检索, t_infomask信息已经变更成2560了.  
-- 具体原因可参考tqual.c  
  
postgres=# select * from heap_page_items(get_raw_page('a',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 |   8152 |        1 |     40 |   1694 |      0 |        0 | (0,1)  |           2 |       2560 |     24 |        |        
  2 |   8112 |        1 |     40 |   1694 |      0 |        0 | (0,2)  |           2 |       2560 |     24 |        |        
  3 |   8072 |        1 |     40 |   1694 |      0 |        0 | (0,3)  |           2 |       2560 |     24 |        |        
(3 rows)  
postgres=# select * from heap_page_items(get_raw_page('b',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 |   8152 |        1 |     40 |   1695 |      0 |        0 | (0,1)  |           2 |       2560 |     24 |        |        
(1 row)  
postgres=# select * from heap_page_items(get_raw_page('c',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 |   8152 |        1 |     40 |   1696 |      0 |        0 | (0,1)  |           2 |       2560 |     24 |        |        
  2 |   8112 |        1 |     40 |   1696 |      0 |        1 | (0,2)  |           2 |       2560 |     24 |        |        
(2 rows)  

与还原点1相比, 多了几条记录, t_infomask=2560.

虽然第二个还原点是在commit后的, 但是因为recovery_target_inclusive = false, 所以apply xlogrecdata时, 将忽略这个data.

3

还原到第2个还原点, recovery_target_inclusive = true

recovery.conf

restore_command = 'cp /data05/ocz/pgbak/arch/%f %p'  
recovery_target_xid = '1696'  
recovery_target_inclusive = true  
recovery_target_timeline = 'latest'  
pause_at_recovery_target = false  

启动后,

postgres=# create extension pageinspect;  
CREATE EXTENSION  
postgres=# select * from heap_page_items(get_raw_page('a',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 |   8152 |        1 |     40 |   1694 |      0 |        0 | (0,1)  |           2 |       2048 |     24 |        |        
  2 |   8112 |        1 |     40 |   1694 |      0 |        0 | (0,2)  |           2 |       2048 |     24 |        |        
  3 |   8072 |        1 |     40 |   1694 |      0 |        0 | (0,3)  |           2 |       2048 |     24 |        |        
(3 rows)  
postgres=# select * from heap_page_items(get_raw_page('b',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 |   8152 |        1 |     40 |   1695 |      0 |        0 | (0,1)  |           2 |       2048 |     24 |        |        
(1 row)  
postgres=# select * from heap_page_items(get_raw_page('c',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 |   8152 |        1 |     40 |   1696 |      0 |        0 | (0,1)  |           2 |       2048 |     24 |        |        
  2 |   8112 |        1 |     40 |   1696 |      0 |        1 | (0,2)  |           2 |       2048 |     24 |        |        
(2 rows)  
postgres=# select * from a;  
 id | crt_time   
----+----------  
(0 rows)  
postgres=# select * from b;  
 id | crt_time   
----+----------  
(0 rows)  
postgres=# select * from c;  
 id |          crt_time            
----+----------------------------  
  1 | 2013-02-04 15:31:34.491555  
  2 | 2013-02-04 15:32:19.168515  
(2 rows)  
  
-- 包含该XLogRecord. C已提交. 所以记录可以看到.  
-- 同样, tuple head的信息被更新, 具体查看tqual.c  
  
postgres=# select * from heap_page_items(get_raw_page('a',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 |   8152 |        1 |     40 |   1694 |      0 |        0 | (0,1)  |           2 |       2560 |     24 |        |        
  2 |   8112 |        1 |     40 |   1694 |      0 |        0 | (0,2)  |           2 |       2560 |     24 |        |        
  3 |   8072 |        1 |     40 |   1694 |      0 |        0 | (0,3)  |           2 |       2560 |     24 |        |        
(3 rows)  
postgres=# select * from heap_page_items(get_raw_page('b',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 |   8152 |        1 |     40 |   1695 |      0 |        0 | (0,1)  |           2 |       2560 |     24 |        |        
(1 row)  
postgres=# select * from heap_page_items(get_raw_page('c',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 |   8152 |        1 |     40 |   1696 |      0 |        0 | (0,1)  |           2 |       2304 |     24 |        |        
  2 |   8112 |        1 |     40 |   1696 |      0 |        1 | (0,2)  |           2 |       2304 |     24 |        |        
(2 rows)  

4

还原到第3个还原点, recovery_target_name=’test2’

recovery.conf

restore_command = 'cp /data05/ocz/pgbak/arch/%f %p'  
recovery_target_name='test2'  
recovery_target_timeline = 'latest'  
pause_at_recovery_target = false  

启动后,

postgres=# create extension pageinspect;  
CREATE EXTENSION  
postgres=# select * from heap_page_items(get_raw_page('a',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 |   8152 |        1 |     40 |   1694 |      0 |        0 | (0,1)  |           2 |       2048 |     24 |        |        
  2 |   8112 |        1 |     40 |   1694 |      0 |        0 | (0,2)  |           2 |       2048 |     24 |        |        
  3 |   8072 |        1 |     40 |   1694 |      0 |        0 | (0,3)  |           2 |       2048 |     24 |        |        
(3 rows)  
postgres=# select * from heap_page_items(get_raw_page('b',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 |   8152 |        1 |     40 |   1695 |      0 |        0 | (0,1)  |           2 |       2048 |     24 |        |        
(1 row)  
postgres=# select * from heap_page_items(get_raw_page('c',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 |   8152 |        1 |     40 |   1696 |      0 |        0 | (0,1)  |           2 |       2048 |     24 |        |        
  2 |   8112 |        1 |     40 |   1696 |      0 |        1 | (0,2)  |           2 |       2048 |     24 |        |        
(2 rows)  
postgres=# select * from a;  
 id | crt_time   
----+----------  
(0 rows)  
postgres=# select * from b;  
 id | crt_time   
----+----------  
(0 rows)  
postgres=# select * from c;  
 id |          crt_time            
----+----------------------------  
  1 | 2013-02-04 15:31:34.491555  
  2 | 2013-02-04 15:32:19.168515  
(2 rows)  
postgres=# select * from heap_page_items(get_raw_page('a',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 |   8152 |        1 |     40 |   1694 |      0 |        0 | (0,1)  |           2 |       2560 |     24 |        |        
  2 |   8112 |        1 |     40 |   1694 |      0 |        0 | (0,2)  |           2 |       2560 |     24 |        |        
  3 |   8072 |        1 |     40 |   1694 |      0 |        0 | (0,3)  |           2 |       2560 |     24 |        |        
(3 rows)  
postgres=# select * from heap_page_items(get_raw_page('b',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 |   8152 |        1 |     40 |   1695 |      0 |        0 | (0,1)  |           2 |       2560 |     24 |        |        
(1 row)  
postgres=# select * from heap_page_items(get_raw_page('c',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 |   8152 |        1 |     40 |   1696 |      0 |        0 | (0,1)  |           2 |       2304 |     24 |        |        
  2 |   8112 |        1 |     40 |   1696 |      0 |        1 | (0,2)  |           2 |       2304 |     24 |        |        
(2 rows)  

5

还原到第4个还原点, recovery_target_inclusive = false

recovery.conf

restore_command = 'cp /data05/ocz/pgbak/arch/%f %p'  
recovery_target_xid = '1695'  
recovery_target_inclusive = false  
recovery_target_timeline = 'latest'  
pause_at_recovery_target = false  

启动后,

postgres=# create extension pageinspect;  
CREATE EXTENSION  
postgres=# select * from heap_page_items(get_raw_page('a',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 |   8152 |        1 |     40 |   1694 |      0 |        0 | (0,1)  |           2 |       2048 |     24 |        |        
  2 |   8112 |        1 |     40 |   1694 |      0 |        0 | (0,2)  |           2 |       2048 |     24 |        |        
  3 |   8072 |        1 |     40 |   1694 |      0 |        0 | (0,3)  |           2 |       2048 |     24 |        |        
(3 rows)  
postgres=# select * from heap_page_items(get_raw_page('b',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 |   8152 |        1 |     40 |   1695 |      0 |        0 | (0,1)  |           2 |       2048 |     24 |        |        
(1 row)  
postgres=# select * from heap_page_items(get_raw_page('c',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 |   8152 |        1 |     40 |   1696 |      0 |        0 | (0,1)  |           2 |       2048 |     24 |        |        
  2 |   8112 |        1 |     40 |   1696 |      0 |        1 | (0,2)  |           2 |       2048 |     24 |        |        
(2 rows)  
postgres=# select * from a;  
 id | crt_time   
----+----------  
(0 rows)  
postgres=# select * from b;  
 id | crt_time   
----+----------  
(0 rows)  
postgres=# select * from c;  
 id |          crt_time            
----+----------------------------  
  1 | 2013-02-04 15:31:34.491555  
  2 | 2013-02-04 15:32:19.168515  
(2 rows)  
postgres=# select * from heap_page_items(get_raw_page('a',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 |   8152 |        1 |     40 |   1694 |      0 |        0 | (0,1)  |           2 |       2560 |     24 |        |        
  2 |   8112 |        1 |     40 |   1694 |      0 |        0 | (0,2)  |           2 |       2560 |     24 |        |        
  3 |   8072 |        1 |     40 |   1694 |      0 |        0 | (0,3)  |           2 |       2560 |     24 |        |        
(3 rows)  
postgres=# select * from heap_page_items(get_raw_page('b',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 |   8152 |        1 |     40 |   1695 |      0 |        0 | (0,1)  |           2 |       2560 |     24 |        |        
(1 row)  
postgres=# select * from heap_page_items(get_raw_page('c',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 |   8152 |        1 |     40 |   1696 |      0 |        0 | (0,1)  |           2 |       2304 |     24 |        |        
  2 |   8112 |        1 |     40 |   1696 |      0 |        1 | (0,2)  |           2 |       2304 |     24 |        |        
(2 rows)  

6

还原到第4个还原点, recovery_target_inclusive = true

recovery.conf

restore_command = 'cp /data05/ocz/pgbak/arch/%f %p'  
recovery_target_xid = '1695'  
recovery_target_inclusive = true  
recovery_target_timeline = 'latest'  
pause_at_recovery_target = false  

启动后,

postgres=# create extension pageinspect;  
CREATE EXTENSION  
postgres=# select * from heap_page_items(get_raw_page('a',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 |   8152 |        1 |     40 |   1694 |      0 |        0 | (0,1)  |           2 |       2048 |     24 |        |        
  2 |   8112 |        1 |     40 |   1694 |      0 |        0 | (0,2)  |           2 |       2048 |     24 |        |        
  3 |   8072 |        1 |     40 |   1694 |      0 |        0 | (0,3)  |           2 |       2048 |     24 |        |        
(3 rows)  
postgres=# select * from heap_page_items(get_raw_page('b',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 |   8152 |        1 |     40 |   1695 |      0 |        0 | (0,1)  |           2 |       2048 |     24 |        |        
(1 row)  
postgres=# select * from heap_page_items(get_raw_page('c',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 |   8152 |        1 |     40 |   1696 |      0 |        0 | (0,1)  |           2 |       2048 |     24 |        |        
  2 |   8112 |        1 |     40 |   1696 |      0 |        1 | (0,2)  |           2 |       2048 |     24 |        |        
(2 rows)  
postgres=# select * from a;  
 id | crt_time   
----+----------  
(0 rows)  
postgres=# select * from b;  
 id |         crt_time            
----+---------------------------  
  1 | 2013-02-04 15:31:05.93215  
(1 row)  
postgres=# select * from c;  
 id |          crt_time            
----+----------------------------  
  1 | 2013-02-04 15:31:34.491555  
  2 | 2013-02-04 15:32:19.168515  
(2 rows)  
postgres=# select * from heap_page_items(get_raw_page('a',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 |   8152 |        1 |     40 |   1694 |      0 |        0 | (0,1)  |           2 |       2560 |     24 |        |        
  2 |   8112 |        1 |     40 |   1694 |      0 |        0 | (0,2)  |           2 |       2560 |     24 |        |        
  3 |   8072 |        1 |     40 |   1694 |      0 |        0 | (0,3)  |           2 |       2560 |     24 |        |        
(3 rows)  
postgres=# select * from heap_page_items(get_raw_page('b',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 |   8152 |        1 |     40 |   1695 |      0 |        0 | (0,1)  |           2 |       2304 |     24 |        |        
(1 row)  
postgres=# select * from heap_page_items(get_raw_page('c',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 |   8152 |        1 |     40 |   1696 |      0 |        0 | (0,1)  |           2 |       2304 |     24 |        |        
  2 |   8112 |        1 |     40 |   1696 |      0 |        1 | (0,2)  |           2 |       2304 |     24 |        |        
(2 rows)  

7

还原到第5个还原点, recovery_target_inclusive = false

recovery.conf

restore_command = 'cp /data05/ocz/pgbak/arch/%f %p'  
recovery_target_xid = '1694'  
recovery_target_inclusive = false  
recovery_target_timeline = 'latest'  
pause_at_recovery_target = false  

启动后,

postgres=# create extension pageinspect;  
CREATE EXTENSION  
postgres=# select * from heap_page_items(get_raw_page('a',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 |   8152 |        1 |     40 |   1694 |      0 |        0 | (0,1)  |           2 |       2048 |     24 |        |        
  2 |   8112 |        1 |     40 |   1694 |      0 |        1 | (0,2)  |           2 |       2048 |     24 |        |        
  3 |   8072 |        1 |     40 |   1694 |      0 |        2 | (0,3)  |           2 |       2048 |     24 |        |        
  4 |   8032 |        1 |     40 |   1694 |      0 |        3 | (0,4)  |           2 |       2048 |     24 |        |        
(4 rows)  
postgres=# select * from heap_page_items(get_raw_page('b',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 |   8152 |        1 |     40 |   1695 |      0 |        0 | (0,1)  |           2 |       2048 |     24 |        |        
(1 row)  
postgres=# select * from heap_page_items(get_raw_page('c',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 |   8152 |        1 |     40 |   1696 |      0 |        0 | (0,1)  |           2 |       2048 |     24 |        |        
  2 |   8112 |        1 |     40 |   1696 |      0 |        1 | (0,2)  |           2 |       2048 |     24 |        |        
(2 rows)  
postgres=# select * from a;  
 id | crt_time   
----+----------  
(0 rows)  
postgres=# select * from b;  
 id |         crt_time            
----+---------------------------  
  1 | 2013-02-04 15:31:05.93215  
(1 row)  
postgres=# select * from c;  
 id |          crt_time            
----+----------------------------  
  1 | 2013-02-04 15:31:34.491555  
  2 | 2013-02-04 15:32:19.168515  
(2 rows)  
postgres=# select * from heap_page_items(get_raw_page('a',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 |   8152 |        1 |     40 |   1694 |      0 |        0 | (0,1)  |           2 |       2560 |     24 |        |        
  2 |   8112 |        1 |     40 |   1694 |      0 |        1 | (0,2)  |           2 |       2560 |     24 |        |        
  3 |   8072 |        1 |     40 |   1694 |      0 |        2 | (0,3)  |           2 |       2560 |     24 |        |        
  4 |   8032 |        1 |     40 |   1694 |      0 |        3 | (0,4)  |           2 |       2560 |     24 |        |        
(4 rows)  
postgres=# select * from heap_page_items(get_raw_page('b',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 |   8152 |        1 |     40 |   1695 |      0 |        0 | (0,1)  |           2 |       2304 |     24 |        |        
(1 row)  
postgres=# select * from heap_page_items(get_raw_page('c',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 |   8152 |        1 |     40 |   1696 |      0 |        0 | (0,1)  |           2 |       2304 |     24 |        |        
  2 |   8112 |        1 |     40 |   1696 |      0 |        1 | (0,2)  |           2 |       2304 |     24 |        |        
(2 rows)  

8

还原到第5个还原点, recovery_target_inclusive = true

recovery.conf

restore_command = 'cp /data05/ocz/pgbak/arch/%f %p'  
recovery_target_xid = '1694'  
recovery_target_inclusive = true  
recovery_target_timeline = 'latest'  
pause_at_recovery_target = false  

启动后,

postgres=# create extension pageinspect;  
CREATE EXTENSION  
postgres=# select * from heap_page_items(get_raw_page('a',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 |   8152 |        1 |     40 |   1694 |      0 |        0 | (0,1)  |           2 |       2048 |     24 |        |        
  2 |   8112 |        1 |     40 |   1694 |      0 |        1 | (0,2)  |           2 |       2048 |     24 |        |        
  3 |   8072 |        1 |     40 |   1694 |      0 |        2 | (0,3)  |           2 |       2048 |     24 |        |        
  4 |   8032 |        1 |     40 |   1694 |      0 |        3 | (0,4)  |           2 |       2048 |     24 |        |        
(4 rows)  
postgres=# select * from heap_page_items(get_raw_page('b',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 |   8152 |        1 |     40 |   1695 |      0 |        0 | (0,1)  |           2 |       2048 |     24 |        |        
(1 row)  
postgres=# select * from heap_page_items(get_raw_page('c',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 |   8152 |        1 |     40 |   1696 |      0 |        0 | (0,1)  |           2 |       2048 |     24 |        |        
  2 |   8112 |        1 |     40 |   1696 |      0 |        1 | (0,2)  |           2 |       2048 |     24 |        |        
(2 rows)  
postgres=# select * from a;  
 id |          crt_time            
----+----------------------------  
  1 | 2013-02-04 15:30:44.091741  
  2 | 2013-02-04 15:31:19.57222  
  3 | 2013-02-04 15:32:00.226317  
  4 | 2013-02-04 15:34:35.589298  
(4 rows)  
postgres=# select * from b;  
 id |         crt_time            
----+---------------------------  
  1 | 2013-02-04 15:31:05.93215  
(1 row)  
postgres=# select * from c;  
 id |          crt_time            
----+----------------------------  
  1 | 2013-02-04 15:31:34.491555  
  2 | 2013-02-04 15:32:19.168515  
(2 rows)  
postgres=# select * from heap_page_items(get_raw_page('a',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 |   8152 |        1 |     40 |   1694 |      0 |        0 | (0,1)  |           2 |       2304 |     24 |        |        
  2 |   8112 |        1 |     40 |   1694 |      0 |        1 | (0,2)  |           2 |       2304 |     24 |        |        
  3 |   8072 |        1 |     40 |   1694 |      0 |        2 | (0,3)  |           2 |       2304 |     24 |        |        
  4 |   8032 |        1 |     40 |   1694 |      0 |        3 | (0,4)  |           2 |       2304 |     24 |        |        
(4 rows)  
postgres=# select * from heap_page_items(get_raw_page('b',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 |   8152 |        1 |     40 |   1695 |      0 |        0 | (0,1)  |           2 |       2304 |     24 |        |        
(1 row)  
postgres=# select * from heap_page_items(get_raw_page('c',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 |   8152 |        1 |     40 |   1696 |      0 |        0 | (0,1)  |           2 |       2304 |     24 |        |        
  2 |   8112 |        1 |     40 |   1696 |      0 |        1 | (0,2)  |           2 |       2304 |     24 |        |        
(2 rows)  

未完 - 见下一篇

PostgreSQL PITR THREE recovery target MODE: name,xid,time USE CASE - 2

参考

1. PostgreSQL recovery target introduce

2. src/include/access/xact.h

3. src/backend/access/transam/xlog.c

4. src/include/catalog/pg_control.h

5. src/backend/utils/time/tqual.c

Flag Counter

digoal’s 大量PostgreSQL文章入口