本文介绍一键转冷存的最佳实践。
场景描述
通过ALTER TABLE tblname SET TABLESPACE oss
锁表可以每次对单个对象进行转存,当遇到表对象有多个索引、或者分区表有多个子分区都需要转存至冷存中时,需要依次执行ALTER
命令,操作繁琐且容易遗漏。为了解决这类问题,PolarDB PostgreSQL版(兼容Oracle)提供了一系列一键转冷存的辅助函数。
创建扩展
CREATE EXTENSION IF NOT EXISTS polar_osfs_toolkit;
函数介绍
polar_alter_relation_to_oss
该函数将对象(非分区表、子分区表、索引)转为OSS存储。
语法
void polar_alter_relation_to_oss(text relname);
参数
参数名称 | 描述 |
relname | 对象(表\索引)名称 |
如果是非分区表或子分区表,则会将表本身的数据转为OSS存储,索引数据存储位置不变。
如果是分区表,请使用
polar_alter_partitioned_to_oss
。如果是索引,则会将该索引本身的数据转为OSS存储。
polar_alter_relation_to_oss_with_indexes
该函数将对象(非分区表、子分区表)及其建立的索引数据转为OSS存储。
语法
void polar_alter_relation_to_oss_with_indexes(text relname);
参数
参数名称 | 描述 |
relname | 表\索引名称 |
如果是非分区表或子分区表,会将表及其建立的所有索引数据转为OSS存储。
如果是分区表,请使用
polar_alter_partitioned_to_oss
。如果是索引,只会将该索引对应的数据转为OSS存储,执行效果与
polar_alter_relation_to_oss
相同。
polar_alter_relation_to_oss_cascade
该函数将对象(表\索引)及其附属对象全部转为OSS存储。
语法
void polar_alter_relation_to_oss_cascade(text relname);
参数
参数名称 | 描述 |
relname | 表\索引名称 |
如果是非分区表或子分区表,会将表及其建立的所有索引数据转为OSS存储。
如果是分区表,会将分区表本身、全局索引、子分区表及其所有索引数据一并转为OSS存储。
如果是索引,会将该索引对应的数据转为OSS存储,执行效果与
polar_alter_relation_to_oss
相同。
polar_alter_partitioned_to_oss
该函数将分区表转为OSS存储。
语法
void polar_alter_partitioned_to_oss(text relname);
参数
参数名称 | 描述 |
relname | 分区表(父表)名称 |
该函数只用于分区表,同时只会将分区表数据转为OSS存储,子分区表及其索引数据存储位置不变。由于分区表的数据实际存储在各个子分区表中,所以本函数实际不改变数据存储位置,只是设置分区表的默认存储位置为OSS。
polar_alter_subpartition_to_oss
该函数将分区表的部分子分区表转为OSS存储,并指定保留存储位置不变的子分区表数量。
语法
void polar_alter_subpartition_to_oss(text relname, int reserved_subparts_cnt);
参数
参数名称 | 描述 |
relname | 分区表(父表)名称 |
reserved_subparts_cnt | 存储位置不变的子分区表数量 |
该函数只用于分区表,且只会将分区表子分区表数据转为OSS存储,子分区表上的索引数据存储位置不变。
如果
reserved_subparts_cnt
为0,表示将所有子分区表都转为OSS存储。如果
reserved_subparts_cnt
大于0,则按照子分区分表建立的时间顺序进行排序,保留时间最近的reserved_subparts_cnt
个子分区表数据存储位置不变,其余子分区表数据转为OSS存储。
polar_alter_subpartition_to_oss_with_indexes
该函数将分区表的部分子分区表及其索引转为OSS存储,并指定保留存储位置不变的子分区表数量。
语法
void polar_alter_subpartition_to_oss_with_indexes(text relname, int reserved_subparts_cnt);
参数
参数名称 | 描述 |
relname | 分区表(父表)名称 |
reserved_subparts_cnt | 存储位置不变的子分区表数量 |
该函数只用于分区表,且会将子分区表及其所有索引数据转为OSS存储。
如果
reserved_subparts_cnt
为0,表示将所有子分区表及其索引都转为OSS存储。如果
reserved_subparts_cnt
大于0,则按照子分区分表建立的时间顺序进行排序,保留时间最近的reserved_subparts_cnt
个子分区表数据存储位置不变,其余子分区表及其索引数据转为OSS存储。
polar_alter_subpartition_to_oss_interval
该函数将分区表的历史子分区表转为OSS存储。
语法
void polar_alter_subpartition_to_oss_interval(text relname, interval tm_inter_value, boolean by_db_time default false);
参数
参数名称 | 描述 |
relname | 分区表(父表)名称。 |
tm_inter_value | 时间间隔值,判断为历史子分区的依据,例如'1 day'、'1 mon'、'5 hours',interval类型介绍可参考Interval。 |
by_db_time | 与
|
只适用于按时间分区的分区表,将所有时间超过tm_inter_value的子分区表及索引全部转入OSS存储。
可结合pg_cron制定按时间线自动转冷存的方案,请参考分区表按时间线自动冷存。
数据库内一个月按30天、一年按365.25天计算,所以遇到大月31天、闰年的情况需要将时间间隔值留一定的buffer。
示例
--创建按天分区的表
CREATE TABLE partition_day (
city_id int not null,
logdate date not null,
peaktemp int,
unitsales int
) PARTITION BY RANGE (logdate);
CREATE TABLE partition_day_y2024m06d01 PARTITION OF partition_day
FOR VALUES FROM ('2024-06-01') TO ('2024-06-02');
CREATE TABLE partition_day_y2024m06d02 PARTITION OF partition_day
FOR VALUES FROM ('2024-06-02') TO ('2024-06-03');
CREATE TABLE partition_day_y2024m06d03 PARTITION OF partition_day
FOR VALUES FROM ('2024-06-03') TO ('2024-06-04');
--设定时间间隔为1 day
select polar_alter_subpartition_to_oss_interval('partition_day', '1 day'::interval);
--partition_day_y2024m06d01、partition_day_y2024m06d02两张子分区表及索引数据全部转存至OSS