说明：该文章转自 http://blog.163.com/digoal@126/blog/static/16387704020133274380469/ 的一位pg数据库大牛
今天一位网友给我留言能不能将unlogged table 改成normal table,也就是logged table.
答案是可以的.
unlogged table的变更不记录pg_xlog,所以这点在流复制环境或者log shipping 复制环境会遇到小小的麻烦,但是不要紧,这个是可以解决的.
以下是修改过程 :
主库,创建unlogged table :
digoal=> create unlogged table t(id int);CREATE TABLEdigoal=> insert into t values (1);INSERT 0 1digoal=> select * from pg_class where relname='t';relname | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | relallvisible | reltoastrelid | reltoastidxid | relhasindex | relisshared | relpersistence | relkind | relnatts | relchecks | relhasoids | relhaspkey | relhasrules | relhastriggers | relhassubclass | relfrozenxid | relacl | reloptions---------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+---------------+-------------+-------------+----------------+---------+----------+-----------+------------+------------+-------------+----------------+----------------+--------------+--------+------------t | 2200 | 154316 | 0 | 24602 | 0 | 154314 | 0 | 0 | 0 |0 | 0 | 0 | f | f | u | r | 1 | 0 | f| f | f | f | f | 32079149 | |(1 row)
注意
relpersistence=u,也就是unlogged的意思.
digoal => \dtList of relationsSchema | Name | Type | Owner--------+--------------+-------+------------public | t | table | digoal( 2 rows )digoal=> select * from t;ERROR: cannot access temporary or unlogged relations during recovery
在主库将t表改成logged的. 使用超级用户
digoal=> \cdigoalpostgresYou are now connected to database "postgres" as user "postgres".digoal=# update pg_class set relpersistence='p' where relname='t';UPDATE 1
此时去standby查询t表报错变了,原因是unlogged表的dml操作不会写pg_xlog,因此数据块不会复制到standby节点.
digoal=> select * from t;ERROR: could not open file "base/12788/154314": No such file or directory
此时在主节点插入一条记录
digoal=# insert into t values (1);INSERT 0 1
再到standby节点查询
digoal=> select * from t;id----11(2 rows)
正常了,这又是为什么呢,因为t表已经改成logged了,pg_xlog会记录,所以数据块被复制到standby节点了.
但是再来看一个测试 .
digoal=# drop table t;DROP TABLEdigoal=# create unlogged table t(id int);CREATE TABLEdigoal=# insert into t select generate_series(1,1000);INSERT 0 1000digoal=# select min(ctid),max(ctid) from t;min | max-------+--------(0,1) | (4,96)(1 row)
-- 注意现在t表有5个数据块. 而不是1个数据块.
digoal=# update pg_class set relpersistence='p' where relname='t';UPDATE 1digoal=# insert into t select generate_series(1,1000);INSERT 0 1000digoal=# select count(*) from t;count-------2000(1 row)digoal=# select min(ctid),max(ctid) from t;min | max-------+---------(0,1) | (8,192)(1 row)
在standby节点看看数据是否都复制过来了呢?
digoal=> \c postgres postgresYou are now connected to database "postgres" as user "postgres".digoal=# select count(*) from t;count-------1096(1 row)digoal=# select min(ctid),max(ctid) from t;min | max-------+---------(4,192)(1 row)
注意是从4号数据块开始复制过来的. 原因是t表变成logged后的DML操作只影响到了4号数据块,并且新建了几个数据块,老的数据块没有改变,所以不会记入pg_xlog,因此复制也只复制了4号数据块开始的新数据块.
遇到这个问题怎么处理呢?
方法1 :
如果表比较小,vacuum full就可以了,因为vacuum full会重建表. pg_xlog也会重新生成.
主库执行 :
digoal=# vacuum full t;VACUUMdigoal=# select count(*) from t;count-------2000(1 row)
standby库验证 :
digoal=# select min(ctid),max(ctid) from t;min | max-------+---------(0,192)(1 row)digoal=# select count(*) from t;count-------2000(1 row)
方法2 :
digoal=# select pg_relation_filepath('t'::regclass);pg_relation_filepath----------------------base/12788/154322(1 row)
复制完后解冻.
[其他注意事项]
1. 如果unlogged表涉及到索引,索引也需要修改.
digoal=# create unlogged table t(id int primary key,info text);NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t_pkey" for table "t"CREATE TABLEdigoal=# select relpersistence from pg_class where oid='t'::regclass;relpersistence----------------u(1 row)digoal=# select relpersistence from pg_class where oid='t_pkey'::regclass;relpersistence----------------u(1 row)digoal=# update pg_class set relpersistence='p' where oid='t'::regclass;UPDATE 1digoal=# update pg_class set relpersistence='p' where oid='t_pkey'::regclass;UPDATE 1
