本文介绍分区表按时间线自动冷存的最佳实践。
场景描述
带有时间序列的数据采用分区表存储，并按时间进行分区，随着时间的推移，一段时间之前的数据访问频率大大降低（过期），为了降低存储成本，需要自动将超过某个固定时间的分区表进行冷存处理。
对于这种场景，PolarDB PostgreSQL版（兼容Oracle）支持通过创建pg_cron扩展插件的方式来实现，详细操作步骤如下。
创建扩展
CREATE EXTENSION IF NOT EXISTS pg_cron;说明
pg_cron扩展只能在postgres库中创建，用户可使用高权限账户连接到postgres库中执行创建。如果没有高权限账户，可以登录控制台界面创建高权限账户。
操作步骤
以db01数据库为例，描述分区表自动转冷存的步骤。
准备数据。
--创建分区表 db01=> CREATE TABLE traj( tr_id serial, tr_lon float, tr_lat float, tr_time timestamp(6) )PARTITION BY RANGE (tr_time); db01=> CREATE TABLE traj_202301 PARTITION OF traj FOR VALUES FROM ('2023-01-01') TO ('2023-02-01'); db01=> CREATE TABLE traj_202302 PARTITION OF traj FOR VALUES FROM ('2023-02-01') TO ('2023-03-01'); db01=> CREATE TABLE traj_202303 PARTITION OF traj FOR VALUES FROM ('2023-03-01') TO ('2023-04-01'); db01=> CREATE TABLE traj_202304 PARTITION OF traj FOR VALUES FROM ('2023-04-01') TO ('2023-05-01'); --往分区表中写入测试数据 db01=> INSERT INTO traj(tr_lon,tr_lat,tr_time) values(112.35, 37.12, '2023-01-01'); db01=> INSERT INTO traj(tr_lon,tr_lat,tr_time) values(112.35, 37.12, '2023-02-01'); db01=> INSERT INTO traj(tr_lon,tr_lat,tr_time) values(112.35, 37.12, '2023-03-01'); db01=> INSERT INTO traj(tr_lon,tr_lat,tr_time) values(112.35, 37.12, '2023-04-01'); --创建分区表索引 db01=> CREATE INDEX traj_idx on traj(tr_id);创建osfs辅助工具。
db01=> CREATE EXTENSION IF NOT EXISTS polar_osfs_toolkit;制定定时执行任务。
使用高权限账户连接到postgres数据库中执行以下SQL，为db01数据库创建一个名为task1的任务，该任务为每分钟检查子分区表数据，并自动将超过3天的子分区转为冷存，并返回任务ID：
-- 每分钟执行 postgres=> SELECT cron.schedule_in_database('task1', '* * * * *', 'select polar_alter_subpartition_to_oss_interval(''traj'', ''3 days''::interval);', 'db01'); schedule_in_database ---------------------- 1说明polar_alter_subpartition_to_oss_interval函数的使用说明可以参考polar_alter_subpartition_to_oss_interval。
pg_cron除了设置每分钟自动转存的任务外，还可以设置每天固定时间、每月固定时间等自定义规则的自动处理任务：
-- 每天的 10:00am (GMT) 执行 postgres=> SELECT cron.schedule_in_database('task2', '0 10 * * *', 'select polar_alter_subpartition_to_oss_interval(''traj'', ''3 days''::interval);', 'db01'); schedule_in_database ---------------------- 2 -- 每个月的 4号 执行 postgres=> SELECT cron.schedule_in_database('task3', '* * 4 * *', 'select polar_alter_subpartition_to_oss_interval(''traj'', ''3 days''::interval);', 'db01'); schedule_in_database ---------------------- 3说明pg_cron更多使用方法可参考pg_cron。
查看执行结果。
--任务执行的结果就是分区表转存至OSS中,查看分区表的存储位置 db01=> \d+ traj_202301 Table "public.traj_202301" Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description ---------+--------------------------------+-----------+----------+-------------------------------------+---------+-------------+--------------+------------- tr_id | integer | | not null | nextval('traj_tr_id_seq'::regclass) | plain | | | tr_lon | double precision | | | | plain | | | tr_lat | double precision | | | | plain | | | tr_time | timestamp(6) without time zone | | | | plain | | | Partition of: traj FOR VALUES FROM ('2023-01-01 00:00:00') TO ('2023-02-01 00:00:00') Partition constraint: ((tr_time IS NOT NULL) AND (tr_time >= '2023-01-01 00:00:00'::timestamp(6) without time zone) AND (tr_time < '2023-02-01 00:00:00'::timestamp(6) without time zone)) Replica Identity: FULL Tablespace: "oss" Access method: heap说明Tablespace: "oss"，说明数据已经转入冷存了。
查看定时任务历史执行记录。
postgres=> select * from cron.job_run_details ; jobid | runid | job_pid | database | username | command | status | return_message | start_time | end_time -------+-------+---------+----------+----------+----------------------------------------------------+-----------+----------------+-------------------------------+------------------------------- 1 | 1 | 469075 | db01 | user1 | select polar_alter_subpartition_to_oss_interval('traj', '3 days'::interval); | succeeded | 1 row | 2024-03-10 03:12:00.016068+00 | 2024-03-10 03:12:00.135428+00 1 | 2 | 469910 | db01 | user1 | select polar_alter_subpartition_to_oss_interval('traj', '3 days'::interval); | succeeded | 1 row | 2024-03-10 03:13:00.008358+00 | 2024-03-10 03:13:00.014189+00 1 | 3 | 470746 | db01 | user1 | select polar_alter_subpartition_to_oss_interval('traj', '3 days'::interval); | succeeded | 1 row | 2024-03-10 03:14:00.013165+00 | 2024-03-10 03:14:00.019002+00 1 | 4 | 471593 | db01 | user1 | select polar_alter_subpartition_to_oss_interval('traj', '3 days'::interval); | succeeded | 1 row | 2024-03-10 03:15:00.006494+00 | 2024-03-10 03:15:00.012056+00 (4 rows)
这样就实现了按规则将过期子分区表自动转为冷存了，冷存后的表数据全部存储在OSS上，不再占用云盘存储空间，大大降低了存储成本，同时增删改查操作也完全透明。
文档内容是否对您有帮助？