如何实现一键转冷存

本文介绍一键转冷存的最佳实践。

场景描述

通过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配合使用,用于确认是否使用数据库当前时间作为判断历史子分区的起始时间,其后tm_inter_value的子分区为历史分区。取值如下:

  • true:使用数据库当前时间作为判断历史分区的依据。

  • false(默认):使用最近子分区表的时间起始值作为判断历史分区的依据。

说明
  • 只适用于按时间分区的分区表,将所有时间超过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