PostgreSQL对象重组工具【pg_reorg】



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