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