在阿里云PostgreSQL中使用pg_pathman

需要保存设备上报的数据，数据量较大，但是数据的有效性是随时间递减，一般查询就是近几个月的数据，使用pg_pathman可以快速的按照数据创建时间完成分表，而不影响原有的业务。

使用的阿里云数据库有自动备份功能，设置每一年6月30号备份一次数据库，然后对上一年的数据分表进行清理，这样就可以在数据不丢失情况下，保证性能。

PostgreSQL版本对pg_pathman支持情况

参考信息来源

开启pg_pathman

要在阿里云数据库RDS上安装pg_pathman，需要执行以下步骤：

1. 登录到阿里云控制台，选择您的PostgreSQL RDS实例。

2. 在左侧导航栏中，单击“数据库管理”>“参数设置”。

3. 找到“shared_preload_libraries”参数，在后面接上"pg_pathman"，使用，分割，然后单击“确定”。

   

4. 点击提交修改，会进行重启

   

5. 重启完后，使用工具连接到PostgreSQL实例，在数据库中使用以下命令安装pg_pathman扩展：

CREATE EXTENSION pg_pathman;

6. 运行指令校验是否安装成功，显示出插件名字和版本说明安装成功

SELECT extname, extversion FROM pg_extension WHERE extname = 'pg_pathman';

使用pg_pathman

-- 创建需要分区的主表
create table test(id int, info text, crt_time timestamp not null);  -- 分区列必须有not null约束 

-- 插入一批测试数据，模拟已经有数据了的主表
insert into test select id,md5(random()::text),clock_timestamp() + (id||' hour')::interval from generate_series(1,10000) t(id); 

-- 创建分区，每个分区包含1个月的跨度数据  
select                                             
create_range_partitions('test'::regclass,             -- 主表OID
                        'crt_time',                        -- 分区列名
                        '2023-3-15 00:00:00'::timestamp,  -- 开始值
                        interval '1 month',                -- 间隔；interval 类型，用于时间分区表
                        24,                                -- 分多少个区
                        true) ;                           -- f-不迁移数据 t-迁移数据

-- 使用非堵塞式的迁移接口，表中数据量较大的话，建议把什么sql设置成false，然后输入下面这条sql进行迁移
select partition_table_concurrently('test'::regclass,  -- 主表OID
                             10000,                    -- 一个事务批量迁移多少记录
                             1.0);                     -- 获得行锁失败时，休眠多久再次获取，重试60次会退出任务。

-- 迁移结束后，主表中已经没数据了，全部在分区表中，查询结果应该为0条
select count(*) from only test;

-- 数据迁移完成后，建议禁用主表，这样执行计划就不会出现主表了
select set_enable_parent('test'::regclass, false);

-- 目前分区表跨度为2年，支持到2025年的3月15号，新增的数据超过这个时间会自动扩展分区
select append_range_partition('test'::regclass);

-- 时间保存较长的数据需要清理，可以直接删除分区表
select drop_range_partition('test_25',   -- 分区名称
                    true); -- 是否删除分区数据，如果false，表示分区数据迁移到主表。  

参考文档

云数据库 RDS文档

pg_pathman使用参考文档