安装使用pg_repack

环境:

OS:Centos 7

DB:pg12

1.查看有那些可以使用的版本

[root@localhost bin]# yum list pg_repack*
Loaded plugins: fastestmirror, langpacks
Loading mirror speeds from cached hostfile
 * base: mirrors.ustc.edu.cn
 * epel: mirror.01link.hk
 * extras: mirrors.ustc.edu.cn
 * updates: mirrors.ustc.edu.cn
Available Packages
pg_repack10.x86_64                                        1.4.6-1.rhel7                                        pgdg10
pg_repack11.x86_64                                        1.4.6-1.rhel7                                        pgdg11
pg_repack12.x86_64                                        1.4.6-1.rhel7                                        pgdg12
pg_repack13.x86_64                                        1.4.6-1.rhel7                                        pgdg13
pg_repack_10.x86_64                                       1.4.8-1.rhel7                                        pgdg10
pg_repack_11.x86_64                                       1.4.8-1.rhel7                                        pgdg11
pg_repack_12.x86_64                                       1.4.8-1.rhel7                                        pgdg12
pg_repack_13.x86_64                                       1.4.8-1.rhel7                                        pgdg13
pg_repack_14.x86_64                                       1.4.8-1.rhel7                                        pgdg14
pg_repack_15.x86_64                                       1.4.8-1.rhel7                                        pgdg15

2.安装与pg一致的版本
[root@localhost bin]#yum install pg_repack12.x86_64

3.加载扩展
修改配置文件后重启postgresql
vi /opt/pg12/data/postgresql.conf
shared_preload_libraries = 'pg_repack'

[root@localhost ~]# systemctl stop postgresql-12
[root@localhost ~]# systemctl start postgresql-12

4.数据库中创建扩展
su - postgres
[postgres@localhost data]$ psql
postgres=# CREATE EXTENSION pg_repack;
CREATE EXTENSION

5.查看如何使用
[postgres@localhost ~]$ /usr/pgsql-12/bin/pg_repack -h 192.168.1.102 -U postgres --dry-run -d db_test --table tb_test
INFO: Dry run enabled, not executing repack
Password:
ERROR: pg_repack failed with error: pg_repack 1.4.8 is not installed in the database
[postgres@localhost ~]$

解决办法:需要在具体的库下面安装扩展插件

[postgres@localhost ~]$ psql -h 192.168.1.102 -U postgres -d db_test
Password for user postgres: 
psql (12.13)
Type "help" for help.

db_test=# 
db_test=# CREATE EXTENSION pg_repack;
CREATE EXTENSION

db_test=# \dx
                                  List of installed extensions
   Name    | Version |   Schema   |                         Description                          
-----------+---------+------------+--------------------------------------------------------------
 pg_repack | 1.4.8   | public     | Reorganize tables in PostgreSQL databases with minimal locks
 plpgsql   | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

再次执行(不真正执行dry-run)

[postgres@localhost ~]$ /usr/pgsql-12/bin/pg_repack -h 192.168.1.102 -U postgres --dry-run -d db_test --table tb_test
INFO: Dry run enabled, not executing repack
Password: 
INFO: repacking table "public.tb_test"

说明:表tb_test是支持pg_repack的,表必须有主键或者唯一约束

#################模拟删除表释放空间######################
1.查看当前表记录数和大小
db_test=# SELECT pg_size_pretty(pg_total_relation_size('tb_test'));
pg_size_pretty
----------------
2023 MB
(1 row)

db_test=# select count(1) from tb_test;
count
----------
13430004
(1 row)

2.尝试删除数据
db_test=# delete from tb_test;
DELETE 13430004

这个时候再次查看表占用存储大小

db_test=# SELECT pg_size_pretty(pg_total_relation_size('tb_test'));
pg_size_pretty
----------------
288 MB
(1 row)

发现表还是会占用存储空间

3.执行pg_repack
su - postgres
[postgres@localhost ~]$ /usr/pgsql-12/bin/pg_repack -h 192.168.1.102 -U postgres --no-kill-backend -d db_test --table tb_test
Password:
INFO: repacking table "public.tb_test"
WARNING: skipping invalid index: CREATE INDEX idx_name2 ON public.tb_test USING btree (name2)

db_test=# SELECT pg_size_pretty(pg_total_relation_size('tb_test'));
pg_size_pretty
----------------
8192 bytes
(1 row)

这个时候发现空间以及完成释放

上面提示索引失效

db_test=# \d+ tb_test;
                                                            Table "public.tb_test"
   Column   |            Type             | Collation | Nullable |               Default               | Storage  | S
tats target | Description 
------------+-----------------------------+-----------+----------+-------------------------------------+----------+--
------------+-------------
 id         | bigint                      |           | not null | nextval('tb_test_id_seq'::regclass) | plain    |  
            | 
 name1      | character varying(32)       |           |          |                                     | extended |  
            | 
 name2      | character varying(32)       |           |          |                                     | extended |  
            | 
 name3      | character varying(32)       |           |          |                                     | extended |  
            | 
 name4      | character varying(32)       |           |          |                                     | extended |  
            | 
 name5      | character varying(32)       |           |          |                                     | extended |  
            | 
 name6      | character varying(32)       |           |          |                                     | extended |  
            | 
 createtime | timestamp without time zone |           |          | CURRENT_TIMESTAMP                   | plain    |  
            | 
 modifytime | timestamp without time zone |           |          | CURRENT_TIMESTAMP                   | plain    |  
            | 
 name7      | character varying(32)       |           |          |                                     | extended |  
            | 
 name8      | character varying(32)       |           | not null | 'aaa'::character varying            | extended |  
            | 
Indexes:
    "tb_test_pkey" PRIMARY KEY, btree (id)
    "idx_name2" btree (name2) INVALID
Access method: heap