环境:
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 -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
标签:postgres,安装,repack,db,pg,test,tb From: https://www.cnblogs.com/hxlasky/p/16943889.html