PostgreSQL利用pg_repack插件解决表膨胀

安装pg_repack插件

[root@localhost local]# yum install gcc postgresql-devel postgresql-static zlib-devel readline-devel -y

把pg_repack-master.zip安装包传到/tmp下解压并给postgres权限

[root@localhost tmp]# chown -R postgres. pg_repack-master

编译安装 

[postgres@localhost tmp]$ cd pg_repack-master/

[postgres@localhost pg_repack-master]$ make

 

[postgres@pghost1 pg_repack-master]$ make install

 

[postgres@pghost1 pg_repack-master]$ pg_repack --version

创建表

postgres=#  create table tmp as select generate_series(1,1000000)::int id,'text'::varchar(100) info;

postgres=# create index idx1 on tmp (id);

postgres=# \dt+ tmp

膨胀

postgres=# update tmp set info='aaa';

postgres=# create extension pg_repack ;

法一：处理表膨胀：

vacuum full tmp;

法二：

添加主键

postgres=# alter table tmp add primary key(id);

[postgres@localhost pg_repack-master]$ pg_repack  --no-superuser-check --echo --no-order -h 192.168.2.15 -p 5432 -d postgres -U postgres --table public.tmp