Use pg_resetxlog simulate tuple disappear within PostgreSQL
背景
刚才francs问我由于PostgreSQL的MVCC机制，是不是数据库维护会导致数据库的记录消失。
当然不会。
PostgreSQL是有保护机制的，有兴趣的朋友可以参考数据库的maintenance章节和相关的freeze参数。
不过有一种情况，可以模拟记录”消失”的情况，那就是使用pg_resetxlog去修改控制文件的NextXID的值。(警告，一般情况下请勿使用)
postgres@db5-> psql -h 127.0.0.1 digoal digoal
创建测试表
digoal=> create table resetxlog_test (id int);
CREATE TABLE
digoal=> insert into resetxlog_test values (1);
INSERT 0 1
digoal=> insert into resetxlog_test values (2);
INSERT 0 1
digoal=> insert into resetxlog_test values (3);
INSERT 0 1
digoal=> insert into resetxlog_test values (4);
INSERT 0 1
digoal=> insert into resetxlog_test values (5);
INSERT 0 1
digoal=> insert into resetxlog_test values (6);
INSERT 0 1
插入6条测试数据后查看记录的xmin和当前的txid.注意我们现在的PostgreSQL epoch是0 (可以从pg_controldata的输出结果看出来) ，所以现在txid和xid相等。
注意PostgreSQL 的epoch和 UNIX的epoch (from 1970)不要混淆。
digoal=> select id,xmin,txid_current() from resetxlog_test ;
id | xmin | txid_current
----+------+--------------
1 | 1941 | 1947
2 | 1942 | 1947
3 | 1943 | 1947
4 | 1944 | 1947
5 | 1945 | 1947
6 | 1946 | 1947
(6 rows)
再次插入几条测试数据，一会你会发现这些记录不存在了。
digoal=> insert into resetxlog_test values (7);
INSERT 0 1
digoal=> insert into resetxlog_test values (8);
INSERT 0 1
digoal=> insert into resetxlog_test values (9);
INSERT 0 1
digoal=> select id,xmin,txid_current() from resetxlog_test ;
id | xmin | txid_current
----+------+--------------
1 | 1941 | 1951
2 | 1942 | 1951
3 | 1943 | 1951
4 | 1944 | 1951
5 | 1945 | 1951
6 | 1946 | 1951
7 | 1948 | 1951
8 | 1949 | 1951
9 | 1950 | 1951
(9 rows)
新插入的3条记录的xmin分别是 1948, 1949, 1950 .
digoal=> \q
停库，准备模拟记录”消失”。
postgres@db5-> pg_ctl stop
waiting for server to shut down..... done
server stopped
postgres@db5-> pg_controldata
pg_control version number: 903
Catalog version number: 201105231
Database system identifier: 5652407581121182719
Database cluster state: shut down
pg_control last modified: Fri 30 Sep 2011 04:25:26 PM CST
Latest checkpoint location: 2/40000020
Prior checkpoint location: 2/3C000020
Latest checkpoint's REDO location: 2/40000020
Latest checkpoint's TimeLineID: 7
Latest checkpoint's NextXID: 0/1952
Latest checkpoint's NextOID: 24723
Latest checkpoint's NextMultiXactId: 1
Latest checkpoint's NextMultiOffset: 0
Latest checkpoint's oldestXID: 1672
Latest checkpoint's oldestXID's DB: 24686
Latest checkpoint's oldestActiveXID: 0
Time of latest checkpoint: Fri 30 Sep 2011 04:25:25 PM CST
Minimum recovery ending location: 0/0
Backup start location: 0/0
Current wal_level setting: hot_standby
Current max_connections setting: 1000
Current max_prepared_xacts setting: 0
Current max_locks_per_xact setting: 64
Maximum data alignment: 8
Database block size: 8192
Blocks per segment of large relation: 131072
WAL block size: 8192
Bytes per WAL segment: 67108864
Maximum length of identifiers: 64
Maximum columns in an index: 32
Maximum size of a TOAST chunk: 1996
Date/time type storage: 64-bit integers
Float4 argument passing: by value
Float8 argument passing: by value
从这里可以看到NextXID = 1952
下面我们把这个值改成1947。
修改完后记录 id in ( 7,8,9 )应该会 “消失” .
postgres@db5-> pg_resetxlog -x 1947 $PGDATA
Transaction log reset
postgres@db5-> pg_ctl start
server starting
postgres@db5-> LOG: loaded library "pg_stat_statements"
postgres@db5-> psql -h 127.0.0.1 digoal digoal
psql (9.1.0)
Type "help" for help.
查询，当然是”消失”的。
digoal=> select id,xmin,txid_current() from resetxlog_test ;
id | xmin | txid_current
----+------+--------------
1 | 1941 | 1947
2 | 1942 | 1947
3 | 1943 | 1947
4 | 1944 | 1947
5 | 1945 | 1947
6 | 1946 | 1947
(6 rows)
继续查询 :
digoal=> select id,xmin,txid_current() from resetxlog_test ;
id | xmin | txid_current
----+------+--------------
1 | 1941 | 1948
2 | 1942 | 1948
3 | 1943 | 1948
4 | 1944 | 1948
5 | 1945 | 1948
6 | 1946 | 1948
(6 rows)
继续查询，当txid大于记录的xmin后，记录随之出现，这里的7,8就出现了 :
digoal=> select id,xmin,txid_current() from resetxlog_test ;
id | xmin | txid_current
----+------+--------------
1 | 1941 | 1950
2 | 1942 | 1950
3 | 1943 | 1950
4 | 1944 | 1950
5 | 1945 | 1950
6 | 1946 | 1950
7 | 1948 | 1950
8 | 1949 | 1950
(8 rows)
继续查询，当txid大于记录的xmin后，记录随之出现，这里的9就出现了 :
digoal=> select id,xmin,txid_current() from resetxlog_test ;
id | xmin | txid_current
----+------+--------------
1 | 1941 | 1951
2 | 1942 | 1951
3 | 1943 | 1951
4 | 1944 | 1951
5 | 1945 | 1951
6 | 1946 | 1951
7 | 1948 | 1951
8 | 1949 | 1951
9 | 1950 | 1951
(9 rows)
另外一个pg_resetxlog的参数-e 是修改控制文件的epoch值。
Latest checkpoint's NextXID: 0/1952
比如这个是0，输入pg_resetxlog -e 1 后 , PostgreSQL epoch就改为1了。
然后在数据库执行txid_current()的时候会等于xid+2^32次方。
如下 :
postgres@db5-> pg_ctl stop
waiting for server to shut down..... done
server stopped
postgres@db5-> pg_resetxlog -e 1 $PGDATA
pTransaction log reset
postgres@db5-> pg_ctl start
server starting
postgres@db5-> LOG: loaded library "pg_stat_statements"
postgres@db5-> psql -h 127.0.0.1 digoal digoal
psql (9.1.0)
Type "help" for help.
digoal=> select txid_current();
txid_current
--------------
4294969250
(1 row)
参考
MVCC