PostgreSQL single-user mode usage,like Repair Database
背景
PostgreSQL目前的xid还是32位的，所以每约20亿事务需要freeze，将tuple的事务状态改成freeze xid，如果你没有做，那么数据库会先提示你防止wrap，然后如果在消耗殆尽前100万个事务时，强制关闭数据库。此时就需要进入单用户才能操作。
单用户模式如何进入
PostgreSQL单用户模式通过以下语法来调用，需要停库，使用postgres命令加单用户选项启动为单用户模式:
postgres --single -D $PGDATA db_name
可选选项:
-E
echo all commands
-j
Disables use of newline as a statement delimiter.
-r $FILENAME
Send all server log output to filename. In normal multiuser mode, this option is ignored, and stderr is used by all processes.
单用户模式典型的应用,1.当多用户模式不接收所有命令时, 2.initdb时 3.修复系统表。
例如:
数据库中任何带relfrozenxid标记的记录,年龄不能超过2^31(二十亿);当数据库中存在年龄大于{(2^31)-1千万}的记录时,数据库将报类似如下提示:
WARNING: database "mydb" must be vacuumed within 177009986 transactions
HINT: To avoid a database shutdown, execute a database-wide VACUUM in "mydb".
提示需要手工的消除这些警告,如果忽略不去处理的话,后面数据库可能需要关闭来处理,下面会提到.处理的方法是使用超级用户
VACUUM mydb.
(这里用到超级用户的原因是需要更新系统表的datfrozenxid列值)
如果忽略上面的警告,当数据库中存在年龄大于{(2^31)-1百万}的记录时,数据库将报类似如下错误:
ERROR: database is not accepting commands to avoid wraparound data loss in database "mydb"
HINT: Stop the postmaster and use a standalone backend to VACUUM in "mydb".
数据库将不再新建任何新的事务,只能通过单用户模式修复。
(100万被认为是一个比较安全的临界值,管理员可以在此年龄范围内对数据库进行VACUUM修复,)
单用户使用举例
# su - postgres
（确保数据库已经关闭）
$ postgres --single mydb_name
postgres@db-bak-192-168-105-35-> postgres --single rmt_rescue
PostgreSQL stand-alone backend 9.0.1
backend> vacuum full; (不带任何参数的VACUUM将对所有当前用户有权限的表进行操作)
backend> Ctrl+D 退出单用户模式.
通常可以进入单用户模式后，查出最大年龄的表，对表进行vacuum freeze.
backend> select nspname, relname, age(relfrozenxid) from pg_class t1 , pg_namespace t2 where t1.relnamespace=t2.oid and relkind in ('r','t','m') order by age(relfrozenxid) desc limit 10;
1: nspname (typeid = 19, len = 64, typmod = -1, byval = f)
2: relname (typeid = 19, len = 64, typmod = -1, byval = f)
3: age (typeid = 23, len = 4, typmod = -1, byval = t)
----
1: nspname = "pg_catalog" (typeid = 19, len = 64, typmod = -1, byval = f)
2: relname = "pg_statistic" (typeid = 19, len = 64, typmod = -1, byval = f)
3: age = "731601622" (typeid = 23, len = 4, typmod = -1, byval = t)
----
1: nspname = "public" (typeid = 19, len = 64, typmod = -1, byval = f)
2: relname = "tbl_pos_28" (typeid = 19, len = 64, typmod = -1, byval = f)
3: age = "596839371" (typeid = 23, len = 4, typmod = -1, byval = t)
----
1: nspname = "public" (typeid = 19, len = 64, typmod = -1, byval = f)
2: relname = "tbl_pos_2" (typeid = 19, len = 64, typmod = -1, byval = f)
3: age = "592403555" (typeid = 23, len = 4, typmod = -1, byval = t)
----
1: nspname = "public" (typeid = 19, len = 64, typmod = -1, byval = f)
2: relname = "tbl_pos" (typeid = 19, len = 64, typmod = -1, byval = f)
3: age = "587929977" (typeid = 23, len = 4, typmod = -1, byval = t)
----
1: nspname = "public" (typeid = 19, len = 64, typmod = -1, byval = f)
2: relname = "tbl_pos_1" (typeid = 19, len = 64, typmod = -1, byval = f)
3: age = "587927563" (typeid = 23, len = 4, typmod = -1, byval = t)
----
1: nspname = "public" (typeid = 19, len = 64, typmod = -1, byval = f)
2: relname = "tbl_pos_5" (typeid = 19, len = 64, typmod = -1, byval = f)
3: age = "583493275" (typeid = 23, len = 4, typmod = -1, byval = t)
----
1: nspname = "public" (typeid = 19, len = 64, typmod = -1, byval = f)
2: relname = "tbl_pos_24" (typeid = 19, len = 64, typmod = -1, byval = f)
3: age = "582569633" (typeid = 23, len = 4, typmod = -1, byval = t)
----
1: nspname = "public" (typeid = 19, len = 64, typmod = -1, byval = f)
2: relname = "tbl_pos_7" (typeid = 19, len = 64, typmod = -1, byval = f)
3: age = "579071765" (typeid = 23, len = 4, typmod = -1, byval = t)
----
1: nspname = "public" (typeid = 19, len = 64, typmod = -1, byval = f)
2: relname = "tbl1" (typeid = 19, len = 64, typmod = -1, byval = f)
3: age = "577666039" (typeid = 23, len = 4, typmod = -1, byval = t)
----
1: nspname = "public" (typeid = 19, len = 64, typmod = -1, byval = f)
2: relname = "tbl_pos_16" (typeid = 19, len = 64, typmod = -1, byval = f)
3: age = "577658079" (typeid = 23, len = 4, typmod = -1, byval = t)
----
将年龄降为1
backend> set vacuum_freeze_min_age =0;
backend> vacuum freeze pg_catalog.pg_statistic;
降全库年龄
backend> set vacuum_freeze_min_age =0;
backend> vacuum freeze;
附录
1. 查看表的年龄:
rmt_rescue=> SELECT relname, age(relfrozenxid) FROM pg_class WHERE relname='tbl_test1';
relname | age
-----------+-----
tbl_test1 | 14
(1 row)
2. 查看数据库的年龄:
rmt_rescue=> select datname,age(datfrozenxid) from pg_database;
datname | age
------------+--------
template1 | 8099
template0 | 676734
postgres | 676734
rmt_rescue | 676734
(4 rows)
3. 查看表中记录的年龄:
rmt_rescue=> select id,ctid,cmin,cmax,xmin,xmax,age(xmin),age(xmax) from tbl_test where id=1;
id | ctid | cmin | cmax | xmin | xmax | age | age
----+-------+------+------+--------+------+-----+------------
1 | (0,1) | 0 | 0 | 677333 | 0 | 53 | 2147483647
(1 row)
年龄=53
rmt_rescue=> update tbl_test set name='test' where id=1;
UPDATE 1
rmt_rescue=> select id,ctid,cmin,cmax,xmin,xmax,age(xmin),age(xmax) from tbl_test where id=1;
id | ctid | cmin | cmax | xmin | xmax | age | age
----+------------+------+------+--------+------+-----+------------
1 | (4424,177) | 0 | 0 | 677387 | 0 | 1 | 2147483647
(1 row)
更新该记录后,年龄=1.
4. 年龄:
normal xid和当前事务id进行比较得到的一个值.SELECT语句也会增加数据库当前事务ID的值.
还有一种比较特殊的XID不参与比较,在任何状态下都被视为”in the past”状态，这就是frozenxid，使用vacuum freezen或vacuum且
当年龄大于vacuum_freeze_min_age时可以将tuple的XID转换为in the past状态，不过update后XID又会回到NORMAL状态.因此建议将
vacuum_freeze_min_age设置为: tuple从创建到不会被更新所经过的年龄是比较合适的.
5. age函数
List of functions
Schema | Name | Result data type | Argument data types | Type
------------+------+------------------+----------------------------------------------------------+--------
pg_catalog | age | interval | timestamp with time zone | normal
pg_catalog | age | interval | timestamp with time zone, timestamp with time zone | normal
pg_catalog | age | interval | timestamp without time zone | normal
pg_catalog | age | interval | timestamp without time zone, timestamp without time zone | normal
pg_catalog | age | integer | xid | normal
(5 rows)