1.查看相关的版本
需要提前安装pg的yum
[root@localhost]#yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm [root@localhost]#yum install epel-release [root@localhost pg_wal]# yum list pg_squeeze* Loaded plugins: fastestmirror, langpacks Loading mirror speeds from cached hostfile * base: mirrors.huaweicloud.com * epel: hkg.mirror.rackspace.com * extras: mirrors.njupt.edu.cn * updates: mirrors.ustc.edu.cn Available Packages pg_squeeze10.x86_64 1.3.0-1.rhel7 pgdg10 pg_squeeze11.x86_64 1.3.0-1.rhel7 pgdg11 pg_squeeze12.x86_64 1.3.0-1.rhel7 pgdg12 pg_squeeze13.x86_64 1.3.0-1.rhel7 pgdg13 pg_squeeze_10.x86_64 1.4.1-2.rhel7 pgdg10 pg_squeeze_11.x86_64 1.5.0-1.rhel7 pgdg11 pg_squeeze_11-llvmjit.x86_64 1.5.0-1.rhel7 pgdg11 pg_squeeze_12.x86_64 1.5.0-1.rhel7 pgdg12 pg_squeeze_12-llvmjit.x86_64 1.5.0-1.rhel7 pgdg12 pg_squeeze_13.x86_64 1.5.0-1.rhel7 pgdg13 pg_squeeze_13-llvmjit.x86_64 1.5.0-1.rhel7 pgdg13 pg_squeeze_14.x86_64 1.5.0-1.rhel7 pgdg14 pg_squeeze_14-llvmjit.x86_64 1.5.0-1.rhel7 pgdg14 pg_squeeze_15.x86_64 1.5.0-1.rhel7 pgdg15 pg_squeeze_15-llvmjit.x86_64 1.5.0-1.rhel7 pgdg15
2.安装
[root@localhost]# yum install pg_squeeze12.x86_64
3.添加扩展
su - postgres
vi /opt/pg12/data/postgresql.conf
shared_preload_libraries = 'pg_repack,pg_squeeze' # (change requires restart)
重新启动pg
[root@localhost ~]# systemctl stop postgresql-12
[root@localhost ~]# systemctl start postgresql-12
4.添加插件
postgres=# create extension pg_squeeze; CREATE EXTENSION postgres=# \dn List of schemas Name | Owner ------------+---------- public | postgres repack | postgres squeeze | postgres tiger | postgres tiger_data | postgres topology | postgres (6 rows)
这个时候会生成一个schema=squeeze,有如下的表
postgres=# set search_path to 'squeeze'; SET postgres=# \dt List of relations Schema | Name | Type | Owner ---------+-----------------+-------+---------- squeeze | errors | table | postgres squeeze | log | table | postgres squeeze | tables | table | postgres squeeze | tables_internal | table | postgres squeeze | tasks | table | postgres (5 rows)
5.创建测试表
postgres=# create table test(id int primary key);
postgres=# insert into test select generate_series(1,5000000);
查看表的大小
postgres=# SELECT pg_size_pretty(pg_total_relation_size('test'));
pg_size_pretty
----------------
280 MB
(1 row)
6.向squeeze.tables插入一条数据,定期清理test表,5分钟一次
insert into squeeze.tables (tabschema, tabname, schedule, free_space_extra) values ('public', 'test', ('{00,59}', '{00,23}', NULL, NULL, NULL), '10');
7.启动pg_squeeze的进程需要调用
SELECT squeeze.start_worker();
关闭命令:SELECT squeeze.stop_worker();
8.查看当前表的膨胀情况
select * from squeeze.tables_internal;
此时test表大小
postgres=# \dt+ test;
List of relations
Schema | Name | Type | Owner | Size | Description
--------+------+-------+----------+--------+-------------
public | test | table | postgres | 173 MB |
(1 row)
9.删除test表一半数据
postgres=# delete from test where id < 2500000;
DELETE 2499999
再次查看表的膨胀情况
select * from squeeze.tables_internal;
-- The End --
标签:rhel7,x86,64,pg,使用,squeeze,postgres From: https://www.cnblogs.com/hxlasky/p/16951464.html