安装使用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
重建索引
db_test=# reindex index idx_name2;
REINDEX
说明：
1.pg_repack期间可以对表进行DML
