PostgreSQL PITR THREE recovery target MODE: name,xid,time USE CASE - 2
背景
接上一篇
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