如何实现一键转冷存_云原生数据库 PolarDB(PolarDB)-阿里云帮助中心

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

场景描述

通过ALTER TABLE tblname SET TABLESPACE oss锁表可以每次对单个对象进行转存，当遇到表对象有多个索引、或者分区表有多个子分区都需要转存至冷存中时，需要依次执行ALTER命令，操作繁琐且容易遗漏。为了解决这类问题，PolarDB PostgreSQL版（兼容Oracle）提供了一系列一键转冷存的辅助函数。

创建扩展

CREATE EXTENSION IF NOT EXISTS polar_osfs_toolkit;

函数介绍

polar_alter_relation_to_oss

该函数将对象（非分区表、子分区表、索引）转为OSS存储。

语法

polar_alter_relation_to_oss(text relname);

参数

参数名称	描述
relname	对象（表\索引）名称

说明

如果是非分区表或子分区表，则会将表本身的数据转为OSS存储，索引数据存储位置不变。
如果是分区表，请使用polar_alter_partitioned_to_oss。
如果是索引，则会将该索引本身的数据转为OSS存储。

polar_alter_relation_to_oss_with_indexes

该函数将对象（非分区表、子分区表）及其建立的索引数据转为OSS存储。

语法

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存储。

语法

polar_alter_relation_to_oss_cascade(text relname);

参数

参数名称	描述
relname	表\索引名称

说明

如果是非分区表或子分区表，会将表及其建立的所有索引数据转为OSS存储。
如果是分区表，会将分区表本身、全局索引、子分区表及其所有索引数据一并转为OSS存储。
如果是索引，会将该索引对应的数据转为OSS存储，执行效果与polar_alter_relation_to_oss相同。

polar_alter_partitioned_to_oss

该函数将分区表转为OSS存储。

语法

polar_alter_partitioned_to_oss(text relname);

参数

参数名称	描述
relname	分区表（父表）名称

说明

该函数只用于分区表，同时只会将分区表数据转为OSS存储，子分区表及其索引数据存储位置不变。由于分区表的数据实际存储在各个子分区表中，所以本函数实际不改变数据存储位置，只是设置分区表的默认存储位置为OSS。

polar_alter_subpartition_to_oss

该函数将分区表的部分子分区表转为OSS存储，并指定保留存储位置不变的子分区表数量。

语法

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存储，并指定保留存储位置不变的子分区表数量。

语法

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存储。

语法

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

polar_alter_pathman_to_oss

该函数将pg_pathman分区表的历史子分区表转为OSS存储。

语法

polar_alter_pathman_to_oss (_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（默认）：使用最近子分区表的时间起始值作为判断历史分区的依据。

说明

仅Oracle语法兼容 2.0且内核小版本2.0.14.17.33.0版本支持该函数。
您可在控制台查看内核小版本号，也可以通过SHOW polardb_version;语句查看。如未满足内核小版本要求，请升级内核小版本。
仅适用于按时间分区的分区表，将所有时间超过tm_inter_value的子分区表及索引全部转入OSS存储。
可结合pg_cron制定按时间线自动转冷存的方案，请参见分区表按时间线自动冷存。
数据库内一个月按30天、一年按365.25天计算，所以当月份为31天或闰年时，需要将时间间隔值预留一定的Buffer。

示例

创建pg_pathman插件。

CREATE EXTENSION IF NOT EXISTS pg_pathman;

准备测试表并创建分区。

CREATE TABLE journal (
    id      SERIAL,
    dt      TIMESTAMP NOT NULL,
    lev     INTEGER,
    msg     TEXT);

-- 创建分区
SELECT create_range_partitions('journal', 'dt', now()-'10 days'::interval, '1 day'::interval, 10);

说明

如执行创建分区操作时返回类似ERROR: Disable superuser UDF calls: copy_foreign_keys (18898)报错，请联系我们。

将3天之前的数据迁移至OSS。

SELECT polar_alter_pathman_to_oss('journal', '3 days'::interval);