PostgreSQL对象重组工具【pg_reorg】 - 数据库编程

= ANY($1) LOG: (param:0)= {} INFO: ---- STEP5. swap tables ---- INFO: This needs EXCLUSIVE LOCK against thetarget table. LOG: (query) BEGIN ISOLATION LEVEL READCOMMITTED LOG: (query) SET LOCAL statement_timeout =100 LOG: (query) LOCK TABLE t1 IN ACCESSEXCLUSIVE MODE LOG: (query) RESET statement_timeout LOG: (query) SELECT reorg.reorg_apply($1,$2, $3, $4, $5, $6) LOG: (param:0)= SELECT * FROM reorg.log_16843 ORDER BY id LIMIT $1 LOG: (param:1)= INSERT INTO reorg.table_16843 VALUES ($1.*) LOG: (param:2)= DELETE FROM reorg.table_16843 WHERE (id) = ($1.id) LOG: (param:3)= UPDATE reorg.table_16843 SET (id, name) = ($2.id, $2.name) WHERE (id) =($1.id) LOG: (param:4)= DELETE FROM reorg.log_16843 WHERE id = $1 LOG: (param:5)= 0 LOG: (query) SELECT reorg.reorg_swap($1) LOG: (param:0)= 16843 LOG: (query) COMMIT INFO: ---- STEP6. drop old table---- LOG: (query) BEGIN ISOLATION LEVEL READCOMMITTED LOG: (query) SELECT reorg.reorg_drop($1) LOG: (param:0)= 16843 LOG: (query) COMMIT INFO: ---- STEP7. analyze ---- LOG: (query) BEGIN ISOLATION LEVEL READCOMMITTED LOG: (query) ANALYZE t1 LOG: (query) COMMIT real 0m21.524s user 0m0.007s sys 0m0.006s [该过程中使用到了一个中间临时表和一个中间日志表以及触发器，通过触发器将重组过程业务中发送的请求语句记录到日志表中，在完成时将日志表中记录的变更同步到中间表中，最后将中间表与实际表调换（通过调换两个表在pg_class中的信息实现），结束后将触发器、中间日志表及新的中间表（原业务表）删除。具体的过程可查看后附的数据库日志。] 在pg_reorg执行的过程中查询数据： tt=# select * from t1 limit 5; id| name ----+-------- 1 |HighGo 2 |HighGo 3 |HighGo 4 |HighGo 5 |HighGo (5 rows) [不会等待pg_reorg执行完毕，因为在重组的过程中未一直将表锁住] tt=# select pg_relation_filepath('t1'); pg_relation_filepath ---------------------- base/16812/16900 (1 row) [数据文件发生了变化，当然，索引文件也同时被重组] 对比结果

表大小	索引大小	pg_reorg	vacuum full
211MB	107MB	0m21.524s	22358.823 ms
845MB	428MB	2m42.015s	99549.960 ms

3、限制

Temp tables

pg_reorg cannot reorganize temp tables.

GiST indexes

pg_reorg cannot reorganize tables usingGiST indexes.

DDL commands

You cannot do DDL commands except VACUUMand ANALYZE during pg_reorg. In many cases pg_reorg will fail and rollback collectly,but there are some cases which may result in data-corruption .

TRUNCATE

TRUNCATE islost. Deleted rows still exist after pg_reorg.

CREATE INDEX

It causes indexcorruptions.

ALTER TABLE ... ADD COLUMN

It causes lostof data. Newly added columns are initialized with NULLs.

ALTER TABLE ... ALTER COLUMN TYPE

It causes datacorruptions.

ALTER TABLE ... SET TABLESPACE

It causes datacorruptions by wrong relfilenode.

注意：

重组过程中会增大I/O压力，执行重组时应避开系统繁忙的时间段;

重组过程中需要创建一些临时对象，所以执行重组时应确保有足够的磁盘空间。

附

LOG: statement: SET statement_timeout = 0

LOG: statement: SET search_path = pg_catalog, pg_temp, public

LOG: statement: SET client_min_messages = warning

LOG: execute : SELECT * FROM reorg.tables WHERE relid =$1::regclass

DETAIL: parameters: $1 = 't1'

LOG: statement: BEGIN ISOLATION LEVEL READ COMMITTED

LOG: statement: SET LOCAL statement_timeout = 100

LOG: statement: LOCK TABLE t1 IN ACCESS EXCLUSIVE MODE

LOG: statement: RESET statement_timeout

LOG: execute : SELECT reorg.conflicted_triggers($1)

DETAIL: parameters: $1 = '16843'

LOG: statemen

PostgreSQL对象重组工具【pg_reorg】(四)