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