PostgreSQL对象重组工具【pg_reorg】

前端之家收集整理的这篇文章主要介绍了PostgreSQL对象重组工具【pg_reorg】前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。


@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@psql (9.3.4)

@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@pg_reorg执行的过程中查询数据:

@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@

注意:

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

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

    @H_502_6@

  3. @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

猜你在找的Postgre SQL相关文章