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

23 minute read

背景

接上一篇

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

9

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

recovery.conf

restore_command = 'cp /data05/ocz/pgbak/arch/%f %p'  
recovery_target_xid = '1698'  
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 |       2304 |     24 |        |        
  2 |   8112 |        1 |     40 |   1697 |      0 |        0 | (0,2)  |           2 |       2048 |     24 |        |        
(2 rows)  
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 |        |        
  3 |   8072 |        1 |     40 |   1698 |      0 |        0 | (0,3)  |           2 |       2048 |     24 |        |        
(3 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)  

10

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

recovery.conf

restore_command = 'cp /data05/ocz/pgbak/arch/%f %p'  
recovery_target_xid = '1698'  
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 |       2304 |     24 |        |        
  2 |   8112 |        1 |     40 |   1697 |      0 |        0 | (0,2)  |           2 |       2048 |     24 |        |        
(2 rows)  
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 |        |        
  3 |   8072 |        1 |     40 |   1698 |      0 |        0 | (0,3)  |           2 |       2048 |     24 |        |        
(3 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  
  3 | 2013-02-04 15:35:18.284565  
(3 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 |        |        
  2 |   8112 |        1 |     40 |   1697 |      0 |        0 | (0,2)  |           2 |       2560 |     24 |        |        
(2 rows)  
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 |        |        
  3 |   8072 |        1 |     40 |   1698 |      0 |        0 | (0,3)  |           2 |       2304 |     24 |        |        
(3 rows)  

11

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

recovery.conf

restore_command = 'cp /data05/ocz/pgbak/arch/%f %p'  
recovery_target_xid = '1697'  
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 |       2304 |     24 |        |        
  2 |   8112 |        1 |     40 |   1697 |      0 |        0 | (0,2)  |           2 |       2048 |     24 |        |        
(2 rows)  
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 |        |        
  3 |   8072 |        1 |     40 |   1698 |      0 |        0 | (0,3)  |           2 |       2048 |     24 |        |        
(3 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  
  3 | 2013-02-04 15:35:18.284565  
(3 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 |        |        
  2 |   8112 |        1 |     40 |   1697 |      0 |        0 | (0,2)  |           2 |       2560 |     24 |        |        
(2 rows)  
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 |        |        
  3 |   8072 |        1 |     40 |   1698 |      0 |        0 | (0,3)  |           2 |       2304 |     24 |        |        
(3 rows)  

12

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

recovery.conf

restore_command = 'cp /data05/ocz/pgbak/arch/%f %p'  
recovery_target_xid = '1697'  
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 |       2304 |     24 |        |        
  2 |   8112 |        1 |     40 |   1697 |      0 |        0 | (0,2)  |           2 |       2048 |     24 |        |        
(2 rows)  
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 |        |        
  3 |   8072 |        1 |     40 |   1698 |      0 |        0 | (0,3)  |           2 |       2048 |     24 |        |        
(3 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  
  3 | 2013-02-04 15:35:18.284565  
(3 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 |        |        
  2 |   8112 |        1 |     40 |   1697 |      0 |        0 | (0,2)  |           2 |       2560 |     24 |        |        
(2 rows)  
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 |        |        
  3 |   8072 |        1 |     40 |   1698 |      0 |        0 | (0,3)  |           2 |       2304 |     24 |        |        
(3 rows)  

时间recovery_target_time作为还原目标点

说明 :

1. 注意前面提到的结束点在COMMIT, ABORT, RESTORE_NAME三种XlogRecord中选择。

2. 时间作为还原目标时, 也可以同时设置recovery_target_inclusive参数.

src/backend/access/transam/xlog.c

                /*  
                 * There can be many transactions that share the same commit time, so  
                 * we stop after the last one, if we are inclusive, or stop at the  
                 * first one if we are exclusive  
                 */  
                if (recoveryTargetInclusive)  
                        stopsHere = (recordXtime > recoveryTargetTime);  
                else  
                        stopsHere = (recordXtime >= recoveryTargetTime);  
                if (stopsHere)  
                        *includeThis = false;  

3. 与recovery_target_time进行比较的XLogRecord中的时间来自以下数据结构的xact_time或者还原点的创建时间 :

src/include/access/xact.h

typedef struct xl_xact_commit_compact  
{  
        TimestampTz xact_time;          /* time of commit */  
        int                     nsubxacts;              /* number of subtransaction XIDs */  
        /* ARRAY OF COMMITTED SUBTRANSACTION XIDs FOLLOWS */  
        TransactionId subxacts[1];      /* VARIABLE LENGTH ARRAY */  
} xl_xact_commit_compact;  
typedef struct xl_xact_commit  
{  
        TimestampTz xact_time;          /* time of commit */  
        uint32          xinfo;                  /* info flags */  
        int                     nrels;                  /* number of RelFileNodes */  
        int                     nsubxacts;              /* number of subtransaction XIDs */  
        int                     nmsgs;                  /* number of shared inval msgs */  
        Oid                     dbId;                   /* MyDatabaseId */  
        Oid                     tsId;                   /* MyDatabaseTableSpace */  
        /* Array of RelFileNode(s) to drop at commit */  
        RelFileNode xnodes[1];          /* VARIABLE LENGTH ARRAY */  
        /* ARRAY OF COMMITTED SUBTRANSACTION XIDs FOLLOWS */  
        /* ARRAY OF SHARED INVALIDATION MESSAGES FOLLOWS */  
} xl_xact_commit;  
typedef struct xl_xact_abort  
{  
        TimestampTz xact_time;          /* time of abort */  
        int                     nrels;                  /* number of RelFileNodes */  
        int                     nsubxacts;              /* number of subtransaction XIDs */  
        /* Array of RelFileNode(s) to drop at abort */  
        RelFileNode xnodes[1];          /* VARIABLE LENGTH ARRAY */  
        /* ARRAY OF ABORTED SUBTRANSACTION XIDs FOLLOWS */  
} xl_xact_abort;  

4. 创建还原点的时间记录在这里 :

src/backend/access/transam/xlog.c

/* logs restore point */  
typedef struct xl_restore_point  
{  
        TimestampTz rp_time;  
        char            rp_name[MAXFNAMELEN];  
} xl_restore_point;  

5. 在recoveryStopsHere中为时间点还原准备的时间逻辑如下 :

        if (record->xl_rmid == RM_XACT_ID && record_info == XLOG_XACT_COMMIT_COMPACT)  
        {  
                xl_xact_commit_compact *recordXactCommitData;  
  
                recordXactCommitData = (xl_xact_commit_compact *) XLogRecGetData(record);  
                recordXtime = recordXactCommitData->xact_time;  
        }  
        else if (record->xl_rmid == RM_XACT_ID && record_info == XLOG_XACT_COMMIT)  
        {  
                xl_xact_commit *recordXactCommitData;  
  
                recordXactCommitData = (xl_xact_commit *) XLogRecGetData(record);  
                recordXtime = recordXactCommitData->xact_time;  
        }  
        else if (record->xl_rmid == RM_XACT_ID && record_info == XLOG_XACT_ABORT)  
        {  
                xl_xact_abort *recordXactAbortData;  
  
                recordXactAbortData = (xl_xact_abort *) XLogRecGetData(record);  
                recordXtime = recordXactAbortData->xact_time;  
        }  
        else if (record->xl_rmid == RM_XLOG_ID && record_info == XLOG_RESTORE_POINT)  
        {  
                xl_restore_point *recordRestorePointData;  
  
                recordRestorePointData = (xl_restore_point *) XLogRecGetData(record);  
                recordXtime = recordRestorePointData->rp_time;  
                strncpy(recordRPName, recordRestorePointData->rp_name, MAXFNAMELEN);  
        }  

6. 同一个时间点可能有多个COMMIT, ABORT, 或者pg_create_restore_point()创建的还原点;

7. 因此, 结合以上描述 :

当设置了recovery_target_inclusive=true时, 将APPLY这个时间点上所有的COMMIT, ABORT或还原点的XlogRecord.

当设置了recovery_target_inclusive=false时, 将不会APPLY这个时间点上的任何COMMIT, ABORT或还原点的XlogRecord.

换句话说, 只有当recovery_target_time设置的时间点上有COMMIT,ABORT或者还原点时, recovery_target_inclusive才有实际意义.

否则肯定是一直APPLY到下一次出现COMMIT,ABORT或者还原点, 并且该XlogRecord不APPLY.

8. 时间的格式为TimestampTz, 例如 : ‘2013-02-04 23:25:54.922891+08’

测试1

recovery.conf

restore_command = 'cp /data05/ocz/pgbak/arch/%f %p'  
recovery_target_time = '2013-02-04 15:30:44.091741'  
recovery_target_inclusive = false  
recovery_target_timeline = 'latest'  
pause_at_recovery_target = false  

分析 :

2013-02-04 15:30:44.091741这个时间点如果有ABORT, COMMIT, 或者还原点的XlogRecord信息. 那么因为设置了recovery_target_inclusive = false. 所以立刻停止.

这个时间点如果没有ABORT, COMMIT, 或者还原点的XlogRecord信息. 那么在APPLY到下一个ABORT, COMMIT, 或者还原点的XlogRecord信息时停止。

本例应该是停止在第一个还原点的XlogRecord位置. 并且不APPLY该XlogRecord数据块.

启动后,

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 |        |        
(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 |       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 |        |        
(1 row)  
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)  
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)  

测试2

recovery.conf

restore_command = 'cp /data05/ocz/pgbak/arch/%f %p'  
recovery_target_time = '2013-02-04 15:32:00.226317'  
recovery_target_inclusive = false  
recovery_target_timeline = 'latest'  
pause_at_recovery_target = false  

分析 :

本例应该是停止在第2个还原点的XlogRecord位置. 并且不APPLY该XlogRecord数据块.

启动后,

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   
----+----------  
(0 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 |       2560 |     24 |        |        
  2 |   8112 |        1 |     40 |   1696 |      0 |        1 | (0,2)  |           2 |       2560 |     24 |        |        
(2 rows)  

pause_at_recovery_target的使用场景

上一篇提到了使用pause_at_recovery_target后recovery_target_inclusive将不起作用.

原因是先判断是否设置了pause, 如下 :

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;  
                                }  

验证测试
选择上一篇的第二个还原点, recovery_target_inclusive = true, pause_at_recovery_target = 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 = true  

启动后,

digoal@db-172-16-3-150-> psql  
psql (9.2.1)  
Type "help" for help.  
postgres=# create extension pageinspect;  
ERROR:  cannot execute CREATE EXTENSION in a read-only transaction  
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)  

显然与上一篇同样的测试但是pause_at_recovery_target=false不一样.

原因就是前面说的. 因为先判断是否pause.

resume后, 该XlogRecord将被APPLY.

postgres=# select pg_xlog_replay_resume();  
 pg_xlog_replay_resume   
-----------------------  
   
(1 row)  
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)  

小结

1. 使用pause时, 并且设置了recovery_target_inclusive = true时, 需要特别小心 :

看到的数据可能不是你想要的, 因为resume后会APPLY那个停止点的XLogRecord.

所以如果你要使用pause, 那么推荐你的做法是

1.1 设置recovery_target_inclusive = false, 那么resume后, 不会APPLY该XlogRecord, 也就不会造成错觉了.

1.2 或者是使用recovery_target_name作为还原目标, 因为这种XlogRecord中没有事务COMMIT或ABORT信息. 也就不存在APPLY或者不APPLY的差别了.

2. 还原目标XID的数据类型为TransactionId, 不是txid, 请注意.

src/include/c.h

00354 typedef uint32 TransactionId;  

src/backend/utils/adt/txid.c

00033 /* txid will be signed int8 in database, so must limit to 63 bits */  
00034 #define MAX_TXID   UINT64CONST(0x7FFFFFFFFFFFFFFF)  
00035   
00036 /* Use unsigned variant internally */  
00037 typedef uint64 txid;  

xid和txid的转换请参考 :

《PostgreSQL xid(int4) to txid(int8)》

参考

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

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文章入口