Description： 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. Online CLUSTER (ordered by cluster index) Ordered by specified columns Online VACUUM FULL (packing rows only) NOTICE: Only superusers can use the utility. Target table must have PRIMARY KEY. 1、安装 编译安装 下载地址：http://pgfoundry.org/frs/?group_id=1000411&release_id=2083#pg_reorg-_1.1.10-title-content [root@masterdb ~]# tar -zxvfpg_reorg-1.1.10.tar.gz [root@masterdb ~]# cd pg_reorg-1.1.10 [root@masterdb pg_reorg-1.1.10]# ./home/postgres/.bash_profile [root@masterdb pg_reorg-1.1.10]# make [root@masterdb pg_reorg-1.1.10]# makeinstall 引入扩展 [postgres@masterdb ~]$ createdb tt [postgres@masterdb ~]$ psql tt psql (9.3.4) Type "help" for help. tt=# create extension pg_reorg ; CREATE EXTENSION tt=# \dx List of installedextensions Name | Version | Schema | Description ----------+---------+------------+------------------------------------ pg_reorg | 1.1.10 | public | re-organizes a PostgreSQL database plpgsql | 1.0 | pg_catalog | PL/pgSQLprocedural language (2 rows) 2、测试 建立测试表 tt=# create table t1(id int primarykey,name text); CREATE TABLE tt=# insert into t1 select generate_series(1,5000000),'HighGo'; INSERT 0 5000000 tt=# \d+ List of relations Schema | Name | Type | Owner | Size | Description --------+------+-------+----------+---------+------------- public |t1 | table | postgres | 211 MB | (1 row) tt=# select pg_relation_filepath('t1'); pg_relation_filepath ---------------------- base/16812/16874 (1 row) 使用vacuum full tt=# \timing Timing is on. tt=# vacuum FULL VERBOSE t1; INFO: vacuuming "public.t1" 在vacuum full操作进行的同时，在另一终端执行： tt=# select * from t1 limit 5; [一直等待vacuum full操作完成] 最终输出信息如下： tt=# vacuum FULL VERBOSE t1; INFO: vacuuming "public.t1" INFO: "t1": found 0 removable, 5000000 nonremovable row versions in27028 pages DETAIL: 0 dead row versions cannot be removed yet. CPU 1.11s/2.71u sec elapsed 5.39 sec. VACUUM Time: 22358.823 ms tt=# select pg_relation_filepath('t1'); pg_relation_filepath ---------------------- base/16812/16878 (1 row) [表文件发生了变化，索引文件也同时被重组] 使用pg_reorg [postgres@masterdb ~]$ time pg_reorg -n -tt1 -d tt -e -E DEBUG LOG: (query) SET statement_timeout = 0 LOG: (query) SET search_path = pg_catalog,pg_temp, public LOG: (query) SET client_min_messages =warning LOG: (query) SELECT * FROM reorg.tablesWHERE relid = $1::regclass LOG: (param:0)= t1 INFO: ---- reorganize one table with 7steps. ---- INFO: target table name : t1 DEBUG: target_oid : 16843 DEBUG: target_toast : 16846 DEBUG: target_tidx : 16848 DEBUG: pkid : 16849 DEBUG: ckid : 0 DEBUG: create_pktype : CREATE TYPE reorg.pk_16843 AS (id integer) DEBUG: create_log : CREATE TABLE reorg.log_16843 (idbigserial PRIMARY KEY, pk reorg.pk_16843, row t1) DEBUG: create_trigger : CREATE TRIGGERz_reorg_trigger BEFORE INSERT OR DELETE OR UPDATE ON t1 FOR EACH ROW EXECUTEPROCEDURE reorg.reorg_trigger('INSERT INTO re