pg_squeeze使用
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 --
