使用函數將資料對象一鍵轉冷存至OSS-雲原生資料庫 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);