@H_502_6@Description：
@H_502_6@pg_reorg is an utility program toreorganize tables in Postgresql databases. Unlike clusterdb,it doesn't blockany selections and updates during reorganization. You can choose one of thefollowing methods to reorganize.
@H_502_6@
-
@H_502_6@Online CLUSTER (ordered by cluster index)
-
@H_502_6@Ordered by specified columns
- @H_502_6@Online VACUUM FULL (packing rows only)
@H_502_6@
@H_502_6@NOTICE:
- @H_502_6@Only superusers can use the utility.
- @H_502_6@Target table must have PRIMARY KEY.
@H_502_6@
@H_502_6@1、安装
编译安装
下载地址：@H_502_6@http://pgfoundry.org/frs/?group_id=1000411&release_id=2083#pg_reorg-_1.1.10-title-content
@H_502_6@
@H_502_6@[root@masterdb ~]# tar -zxvfpg_reorg-1.1.10.tar.gz
@H_502_6@[root@masterdb ~]# cd pg_reorg-1.1.10
@H_502_6@[root@masterdb pg_reorg-1.1.10]# ./home/postgres/.bash_profile
@H_502_6@[root@masterdb pg_reorg-1.1.10]# make
@H_502_6@[root@masterdb pg_reorg-1.1.10]# makeinstall
引入扩展
@H_502_6@[postgres@masterdb ~]$ createdb tt
@H_502_6@[postgres@masterdb ~]$ psql tt
@H_502_6@Type "help" for help.
@H_502_6@
@H_502_6@tt=# create extension pg_reorg ;
@H_502_6@CREATE EXTENSION
@H_502_6@tt=# \dx
@H_502_6@ List of installedextensions
@H_502_6@Name | Version | Schema| Description
@H_502_6@----------+---------+------------+------------------------------------
@H_502_6@pg_reorg | 1.1.10 | public| re-organizes a Postgresql database
@H_502_6@plpgsql| 1.0 | pg_catalog | PL/pgsqlprocedural language
@H_502_6@(2 rows)
@H_502_6@
@H_502_6@
@H_502_6@2、测试
建立测试表
@H_502_6@tt=# create table t1(id int primarykey,name text);
@H_502_6@CREATE TABLE
@H_502_6@tt=# insert into t1 select generate_series(1,5000000),'HighGo';
@H_502_6@INSERT 0 5000000
@H_502_6@tt=# \d+
@H_502_6@ List of relations
@H_502_6@Schema | Name | Type |Owner | Size| Description
@H_502_6@--------+------+-------+----------+---------+-------------
@H_502_6@public | t1| table | postgres | 211 MB |
@H_502_6@(1 row)
@H_502_6@
@H_502_6@
@H_502_6@tt=# select pg_relation_filepath('t1');
@H_502_6@pg_relation_filepath
@H_502_6@----------------------
@H_502_6@base/16812/16874
@H_502_6@(1 row)
使用@H_502_6@vacuum full
@H_502_6@tt=# \timing
@H_502_6@Timing is on.
@H_502_6@tt=# vacuum FULL VERBOSE t1;
@H_502_6@INFO:vacuuming "public.t1"
@H_502_6@
在@H_502_6@vacuum full操作进行的同时，在另一终端执行：
@H_502_6@tt=# select * from t1 limit 5;
@H_502_6@[一直等待@H_502_6@vacuum full操作完成@H_502_6@]
@H_502_6@
@H_502_6@
最终输出信息如下：
@H_502_6@tt=# vacuum FULL VERBOSE t1;
@H_502_6@INFO:vacuuming "public.t1"
@H_502_6@INFO:"t1": found 0 removable,5000000 nonremovable row versions in27028 pages
@H_502_6@DETAIL:0 dead row versions cannot be removed yet.
@H_502_6@cpu 1.11s/2.71u sec elapsed 5.39 sec.
@H_502_6@VACUUM
@H_502_6@Time: 22358.823 ms
@H_502_6@
@H_502_6@tt=# select pg_relation_filepath('t1');
@H_502_6@pg_relation_filepath
@H_502_6@----------------------
@H_502_6@base/16812/16878
@H_502_6@(1 row)
@H_502_6@[表文件发生了变化，索引文件也同时被重组@H_502_6@]
@H_502_6@
使用@H_502_6@pg_reorg
@H_502_6@[postgres@masterdb ~]$ time pg_reorg -n -tt1 -d tt -e -E DEBUG
@H_502_6@LOG: (query) SET statement_timeout = 0
@H_502_6@LOG: (query) SET search_path = pg_catalog,pg_temp,public
@H_502_6@LOG: (query) SET client_min_messages =warning
@H_502_6@LOG: (query) SELECT * FROM reorg.tablesWHERE relid = $1::regclass
@H_502_6@LOG: (param:0)= t1
@H_502_6@INFO: ---- reorganize one table with 7steps. ----
@H_502_6@INFO: target table name : t1
@H_502_6@DEBUG: target_oid : 16843
@H_502_6@DEBUG: target_toast : 16846
@H_502_6@DEBUG: target_tidx : 16848
@H_502_6@DEBUG: pkid : 16849
@H_502_6@DEBUG: ckid : 0
@H_502_6@DEBUG: create_pktype : CREATE TYPE reorg.pk_16843 AS (id integer)
@H_502_6@DEBUG: create_log : CREATE TABLE reorg.log_16843 (idbigserial PRIMARY KEY,pk reorg.pk_16843,row t1)
@H_502_6@DEBUG: create_trigger : CREATE TRIGGERz_reorg_trigger BEFORE INSERT OR DELETE OR UPDATE ON t1 FOR EACH ROW EXECUTEPROCEDURE reorg.reorg_trigger('INSERT INTO reorg.log_16843(pk,row) VALUES(CASE WHEN $1 IS NULL THEN NULL ELSE (ROW($1.id)::reorg.pk_16843) END,$2)')
@H_502_6@DEBUG: create_table : CREATE TABLE reorg.table_16843 WITH(oids=false) TABLESPACE pg_default AS SELECT id,name FROM ONLY t1
@H_502_6@DEBUG: drop_columns : (skipped)
@H_502_6@DEBUG: delete_log : DELETE FROM reorg.log_16843
@H_502_6@DEBUG: lock_table : LOCK TABLE t1 IN ACCESS EXCLUSIVE MODE
@H_502_6@DEBUG: sql_peek : SELECT * FROM reorg.log_16843 ORDER BYid LIMIT $1
@H_502_6@DEBUG: sql_insert : INSERT INTO reorg.table_16843 VALUES($1.*)
@H_502_6@DEBUG: sql_delete : DELETE FROM reorg.table_16843 WHERE (id)= ($1.id)
@H_502_6@DEBUG: sql_update : UPDATE reorg.table_16843 SET (id,name)= ($2.id,$2.name) WHERE (id) = ($1.id)
@H_502_6@DEBUG: sql_pop : DELETE FROM reorg.log_16843 WHERE id= $1
@H_502_6@INFO: ---- STEP1. setup ----
@H_502_6@INFO: This needs EXCLUSIVE LOCK against thetarget table.
@H_502_6@LOG: (query) BEGIN ISOLATION LEVEL READCOMMITTED
@H_502_6@LOG: (query) SET LOCAL statement_timeout =100
@H_502_6@LOG: (query) LOCK TABLE t1 IN ACCESSEXCLUSIVE MODE
@H_502_6@LOG: (query) RESET statement_timeout
@H_502_6@LOG: (query) SELECTreorg.conflicted_triggers($1)
@H_502_6@LOG: (param:0)= 16843
@H_502_6@LOG: (query) CREATE TYPE reorg.pk_16843 AS(id integer)
@H_502_6@LOG: (query) CREATE TABLE reorg.log_16843(id bigserial PRIMARY KEY,row t1)
@H_502_6@LOG: (query) CREATE TRIGGER z_reorg_triggerBEFORE INSERT OR DELETE OR UPDATE ON t1 FOR EACH ROW EXECUTE PROCEDUREreorg.reorg_trigger('INSERT INTO reorg.log_16843(pk,row) VALUES( CASE WHEN $1IS NULL THEN NULL ELSE (ROW($1.id)::reorg.pk_16843) END,$2)')
@H_502_6@LOG: (query) SELECTreorg.disable_autovacuum('reorg.log_16843')
@H_502_6@LOG: (query) COMMIT
@H_502_6@INFO: ---- STEP2. copy tuples into temptable----
@H_502_6@LOG: (query) BEGIN ISOLATION LEVELSERIALIZABLE
@H_502_6@LOG: (query) SELECT set_config('work_mem',current_setting('maintenance_work_mem'),true)
@H_502_6@LOG: (query) SET LOCAL synchronize_seqscans= off
@H_502_6@LOG: (query) SELECTreorg.array_accum(virtualtransaction) FROM pg_locks WHERE locktype ='virtualxid' AND pid <> pg_backend_pid() AND (virtualxid,virtualtransaction)<> ('1/1','-1/0')
@H_502_6@LOG: (query) DELETE FROM reorg.log_16843
@H_502_6@LOG: (query) CREATE TABLE reorg.table_16843WITH (oids=false) TABLESPACE pg_default AS SELECT id,name FROM ONLY t1
@H_502_6@LOG: (query) SELECTreorg.disable_autovacuum('reorg.table_16843')
@H_502_6@LOG: (query) COMMIT
@H_502_6@INFO: ---- STEP3. create indexes ----
@H_502_6@LOG: (query) SELECT indexrelid,reorg.reorg_indexdef(indexrelid,indrelid),indisvalid,pg_get_indexdef(indexrelid) FROM pg_index WHERE indrelid = $1
@H_502_6@LOG: (param:0)= 16843
@H_502_6@DEBUG: [0]
@H_502_6@DEBUG: target_oid : 16849
@H_502_6@DEBUG: create_index : CREATE UNIQUE INDEXindex_16849 ON reorg.table_16843 USING btree (id)
@H_502_6@LOG: (query) CREATE UNIQUE INDEXindex_16849 ON reorg.table_16843 USING btree (id)
@H_502_6@INFO: ---- STEP4. apply logs ----
@H_502_6@LOG: (query) SELECT reorg.reorg_apply($1,$2,$3,$4,$5,$6)
@H_502_6@LOG: (param:0)= SELECT * FROM reorg.log_16843 ORDER BY id LIMIT $1
@H_502_6@LOG: (param:1)= INSERT INTO reorg.table_16843 VALUES ($1.*)
@H_502_6@LOG: (param:2)= DELETE FROM reorg.table_16843 WHERE (id) = ($1.id)
@H_502_6@LOG: (param:3)= UPDATE reorg.table_16843 SET (id,name) = ($2.id,$2.name) WHERE (id) =($1.id)
@H_502_6@LOG: (param:4)= DELETE FROM reorg.log_16843 WHERE id = $1
@H_502_6@LOG: (param:5)= 1000
@H_502_6@LOG: (query) SELECT pid FROM pg_locks WHERElocktype = 'virtualxid' AND pid <> pg_backend_pid() AND virtualtransaction= ANY($1)
@H_502_6@LOG: (param:0)= {}
@H_502_6@INFO: ---- STEP5. swap tables ----
@H_502_6@INFO: This needs EXCLUSIVE LOCK against thetarget table.
@H_502_6@LOG: (query) BEGIN ISOLATION LEVEL READCOMMITTED
@H_502_6@LOG: (query) SET LOCAL statement_timeout =100
@H_502_6@LOG: (query) LOCK TABLE t1 IN ACCESSEXCLUSIVE MODE
@H_502_6@LOG: (query) RESET statement_timeout
@H_502_6@LOG: (query) SELECT reorg.reorg_apply($1,$2.name) WHERE (id) =($1.id)
@H_502_6@LOG: (param:4)= DELETE FROM reorg.log_16843 WHERE id = $1
@H_502_6@LOG: (param:5)= 0
@H_502_6@LOG: (query) SELECT reorg.reorg_swap($1)
@H_502_6@LOG: (param:0)= 16843
@H_502_6@LOG: (query) COMMIT
@H_502_6@INFO: ---- STEP6. drop old table----
@H_502_6@LOG: (query) BEGIN ISOLATION LEVEL READCOMMITTED
@H_502_6@LOG: (query) SELECT reorg.reorg_drop($1)
@H_502_6@LOG: (param:0)= 16843
@H_502_6@LOG: (query) COMMIT
@H_502_6@INFO: ---- STEP7. analyze ----
@H_502_6@LOG: (query) BEGIN ISOLATION LEVEL READCOMMITTED
@H_502_6@LOG: (query) ANALYZE t1
@H_502_6@LOG: (query) COMMIT
@H_502_6@
@H_502_6@real 0m21.524s
@H_502_6@user 0m0.007s
@H_502_6@sys 0m0.006s
@H_502_6@[该过程中使用到了一个中间临时表和一个中间日志表以及触发器，通过触发器将重组过程业务中发送的请求语句记录到日志表中，在完成时将日志表中记录的变更同步到中间表中，最后将中间表与实际表调换（通过调换两个表在@H_502_6@pg_class中的信息实现），结束后将触发器、中间日志表及新的中间表（原业务表）删除。具体的过程可查看后附的数据库日志。@H_502_6@]
@H_502_6@
@H_502_6@tt=# select * from t1 limit 5;
@H_502_6@id| name
@H_502_6@----+--------
@H_502_6@ 1 |HighGo
@H_502_6@ 2 |HighGo
@H_502_6@ 3 |HighGo
@H_502_6@ 4 |HighGo
@H_502_6@ 5 |HighGo
@H_502_6@(5 rows)
@H_502_6@[不会等待@H_502_6@pg_reorg执行完毕，因为在重组的过程中未一直将表锁住@H_502_6@]
@H_502_6@
@H_502_6@
@H_502_6@tt=# select pg_relation_filepath('t1');
@H_502_6@pg_relation_filepath
@H_502_6@----------------------
@H_502_6@base/16812/16900
@H_502_6@(1 row)
@H_502_6@[数据文件发生了变化，当然，索引文件也同时被重组@H_502_6@]
@H_502_6@
对比结果
| 表大小 |
索引大小 |
pg_reorg |
vacuum full |
| 211MB |
107MB |
0m21.524s |
22358.823 ms |
| 845MB |
428MB |
@H_502_6@2m42.015s |
@H_502_6@99549.960 ms |
@H_502_6@
@H_502_6@3、限制
@H_502_6@Temp tables
@H_502_6@pg_reorg cannot reorganize temp tables.
@H_502_6@
@H_502_6@GiST indexes
@H_502_6@pg_reorg cannot reorganize tables usingGiST indexes.
@H_502_6@
@H_502_6@DDL commands
@H_502_6@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 .
@H_502_6@
@H_502_6@TRUNCATE
@H_502_6@TRUNCATE islost. Deleted rows still exist after pg_reorg.
@H_502_6@CREATE INDEX
@H_502_6@It causes indexcorruptions.
@H_502_6@ALTER TABLE ... ADD COLUMN
@H_502_6@It causes lostof data. Newly added columns are initialized with NULLs.
@H_502_6@ALTER TABLE ... ALTER COLUMN TYPE
@H_502_6@It causes datacorruptions.
@H_502_6@ALTER TABLE ... SET TABLESPACE
@H_502_6@It causes datacorruptions by wrong relfilenode.
@H_502_6@
@H_502_6@
注意：
- 重组过程中会增大@H_502_6@I/O压力，执行重组时应避开系统繁忙的时间段@H_502_6@;
- 重组过程中需要创建一些临时对象，所以执行重组时应确保有足够的磁盘空间。
@H_502_6@
@H_403_2629@
附
@H_502_6@LOG:statement: SET statement_timeout = 0
@H_502_6@LOG:statement: SET search_path = pg_catalog,public
@H_502_6@LOG:statement: SET client_min_messages = warning
@H_502_6@LOG:execute <unnamed>: SELECT * FROM reorg.tables WHERE relid =$1::regclass
@H_502_6@DETAIL:parameters: $1 = 't1'
@H_502_6@LOG:statement: BEGIN ISOLATION LEVEL READ COMMITTED
@H_502_6@LOG:statement: SET LOCAL statement_timeout = 100
@H_502_6@LOG:statement: LOCK TABLE t1 IN ACCESS EXCLUSIVE MODE
@H_502_6@LOG:statement: RESET statement_timeout
@H_502_6@LOG:execute <unnamed>: SELECT reorg.conflicted_triggers($1)
@H_502_6@DETAIL:parameters: $1 = '16843'
@H_502_6@LOG:statement: CREATE TYPE reorg.pk_16843 AS (id integer)
@H_502_6@LOG:statement: CREATE TABLE reorg.log_16843 (id bigserial PRIMARY KEY,pkreorg.pk_16843,row t1)
@H_502_6@LOG:statement: CREATE TRIGGER z_reorg_trigger BEFORE INSERT OR DELETE ORUPDATE ON t1 FOR EACH ROW EXECUTE PROCEDURE reorg.reorg_trigger('INSERT INTOreorg.log_16843(pk,row) VALUES( CASE WHEN $1 IS NULL THEN NULL ELSE(ROW($1.id)::reorg.pk_16843) END,$2)')
@H_502_6@LOG:statement: SELECT reorg.disable_autovacuum('reorg.log_16843')
@H_502_6@LOG:statement: COMMIT
@H_502_6@LOG:statement: BEGIN ISOLATION LEVEL SERIALIZABLE
@H_502_6@LOG:statement: SELECT set_config('work_mem',true)
@H_502_6@LOG:statement: SET LOCAL synchronize_seqscans = off
@H_502_6@LOG:statement: SELECT reorg.array_accum(virtualtransaction) FROM pg_locksWHERE locktype = 'virtualxid' AND pid <> pg_backend_pid() AND(virtualxid,virtualtransaction) <> ('1/1','-1/0')
@H_502_6@LOG:statement: DELETE FROM reorg.log_16843
@H_502_6@LOG:statement: CREATE TABLE reorg.table_16843 WITH (oids=false) TABLESPACEpg_default AS SELECT id,name FROM ONLY t1
@H_502_6@LOG:statement: SELECT reorg.disable_autovacuum('reorg.table_16843')
@H_502_6@LOG:statement: COMMIT
@H_502_6@LOG:execute <unnamed>: SELECT indexrelid,pg_get_indexdef(indexrelid) FROM pg_index WHERE indrelid = $1
@H_502_6@DETAIL:parameters: $1 = '16843'
@H_502_6@LOG:statement: CREATE UNIQUE INDEX index_16849 ON reorg.table_16843 USINGbtree (id)
@H_502_6@LOG:execute <unnamed>: SELECT reorg.reorg_apply($1,$6)
@H_502_6@DETAIL:parameters: $1 = 'SELECT * FROM reorg.log_16843 ORDER BY id LIMIT $1',$2 = 'INSERT INTO reorg.table_16843 VALUES ($1.*)',$3 = 'DELETE FROMreorg.table_16843 WHERE (id) = ($1.id)',$4 = 'UPDATE reorg.table_16843 SET(id,$2.name) WHERE (id) = ($1.id)',$5 = 'DELETE FROMreorg.log_16843 WHERE id = $1',$6 = '1000'
@H_502_6@LOG:execute <unnamed>: SELECT pid FROM pg_locks WHERE locktype ='virtualxid' AND pid <> pg_backend_pid() AND virtualtransaction = ANY($1)
@H_502_6@DETAIL:parameters: $1 = '{}'
@H_502_6@LOG:statement: BEGIN ISOLATION LEVEL READ COMMITTED
@H_502_6@LOG:statement: SET LOCAL statement_timeout = 100
@H_502_6@LOG:statement: LOCK TABLE t1 IN ACCESS EXCLUSIVE MODE
@H_502_6@LOG:statement: RESET statement_timeout
@H_502_6@LOG:execute <unnamed>: SELECT reorg.reorg_apply($1,$6 = '0'
@H_502_6@LOG:execute <unnamed>: SELECT reorg.reorg_swap($1)
@H_502_6@DETAIL:parameters: $1 = '16843'
@H_502_6@LOG:statement: COMMIT
@H_502_6@LOG:statement: BEGIN ISOLATION LEVEL READ COMMITTED
@H_502_6@LOG:execute <unnamed>: SELECT reorg.reorg_drop($1)
@H_502_6@DETAIL:parameters: $1 = '16843'
@H_502_6@LOG:statement: COMMIT
@H_502_6@LOG:statement: BEGIN ISOLATION LEVEL READ COMMITTED
@H_502_6@LOG:statement: ANALYZE t1
@H_502_6@LOG:statement: COMMIT
