Use polar_osfs_toolkit functions to archive data to cold storage - PolarDB - Alibaba Cloud - PolarDB

This topic describes how to archive data to cold storage with a single click.

Background

You can archive a single object to OSS storage by running the ALTER TABLE tblname SET TABLESPACE oss command, which locks the table. This process becomes tedious and error-prone for tables with multiple indexes or for partitioned tables with many subpartitions, as you must run the ALTER command on each object. To simplify this process, PolarDB for PostgreSQL and provide a set of helper functions to archive data with a single call.

Create the extension

CREATE EXTENSION IF NOT EXISTS polar_osfs_toolkit;

Function reference

polar_alter_relation_to_oss

This function archives an object, such as a non-partitioned table, a subpartitioned table, or an index, to OSS storage.

Syntax

polar_alter_relation_to_oss(text relname);

Parameters

Parameter	Description
relname	The name of the object (table or index).

Note

For a non-partitioned or subpartitioned table, this function archives the table data to OSS storage but does not move its indexes.
For a partitioned table, use the polar_alter_partitioned_to_oss function instead.
For an index, this function archives only the index data to OSS storage.

polar_alter_relation_to_oss_with_indexes

This function archives a non-partitioned table or a subpartitioned table and all its indexes to OSS storage.

Syntax

polar_alter_relation_to_oss_with_indexes(text relname);

Parameters

Parameter	Description
relname	The name of the table or index.

Note

For a non-partitioned table or a subpartitioned table, this function archives the table and all its indexes to OSS storage.
For a partitioned table, use the polar_alter_partitioned_to_oss function instead.
If an index is specified, this function archives only the index data to OSS storage, behaving identically to the polar_alter_relation_to_oss function.

polar_alter_relation_to_oss_cascade

This function archives an object, such as a table or an index, and all its subordinate objects to OSS storage.

Syntax

polar_alter_relation_to_oss_cascade(text relname);

Parameters

Parameter	Description
relname	The name of the table or index.

Note

For a non-partitioned table or a subpartitioned table, this function archives the table and all its indexes to OSS storage.
For a partitioned table, the function archives the parent table, its global indexes (if any), all its subpartitioned tables, and their indexes to OSS storage.
If an index is specified, this function archives only the index data to OSS storage, behaving identically to the polar_alter_relation_to_oss function.

polar_alter_partitioned_to_oss

This function sets the default tablespace for a partitioned table to OSS storage.

Syntax

polar_alter_partitioned_to_oss(text relname);

Parameters

Parameter	Description
relname	The name of the partitioned table (parent table).

Note

This function applies only to partitioned tables. It sets the default storage location for the partitioned table to OSS but does not move any data. Since the actual data is stored in the subpartitioned tables, their storage locations and the locations of their indexes remain unchanged.

polar_alter_subpartition_to_oss

This function archives older subpartitioned tables of a partitioned table to OSS storage and lets you specify the number of subpartitioned tables to keep in their original storage location.

Syntax

polar_alter_subpartition_to_oss(text relname, int reserved_subparts_cnt);

Parameters

Parameter	Description
relname	The name of the partitioned table (parent table).
reserved_subparts_cnt	The number of subpartitioned tables to keep in their original storage location.

Note

This function applies only to partitioned tables. It archives the data of subpartitioned tables to OSS storage, but does not move their indexes.
If reserved_subparts_cnt is 0, all subpartitioned tables are archived to OSS storage.
If reserved_subparts_cnt is greater than 0, the function sorts the subpartitioned tables by their creation time and keeps the specified number of most recent (reserved_subparts_cnt) ones in their original storage. The remaining subpartitioned tables are archived to OSS storage.

polar_alter_subpartition_to_oss_with_indexes

This function archives older subpartitioned tables of a partitioned table and their indexes to OSS storage, and lets you specify the number of subpartitioned tables to keep in their original storage location.

Syntax

polar_alter_subpartition_to_oss_with_indexes(text relname, int reserved_subparts_cnt);

Parameters

Parameter	Description
relname	The name of the partitioned table (parent table).
reserved_subparts_cnt	The number of subpartitioned tables to keep in their original storage location.

Note

This function applies only to partitioned tables. It archives the data of subpartitioned tables and all their indexes to OSS storage.
If reserved_subparts_cnt is 0, all subpartitioned tables and their indexes are archived to OSS storage.
If reserved_subparts_cnt is greater than 0, the function sorts the subpartitioned tables by their creation time and keeps the specified number of most recent (reserved_subparts_cnt) ones in their original storage. The remaining subpartitioned tables and their indexes are archived to OSS storage.

polar_alter_subpartition_to_oss_interval

This function archives historical subpartitioned tables and their indexes from a time-based partitioned table to OSS storage.

Syntax

polar_alter_subpartition_to_oss_interval(text relname, interval tm_inter_value, boolean by_db_time default false);

Parameters

Parameter	Description
relname	The name of the partitioned table (parent table).
tm_inter_value	The time interval that defines which subpartitions are considered historical. Examples: '1 day', '1 mon', or '5 hours'. For more information about the interval type, see Interval Input.
by_db_time	Used in conjunction with `tm_inter_value` to specify whether to use the current time of the database as the start time for identifying historical subpartitions. Subpartitions created after the time specified by `tm_inter_value` are considered historical partitions. The valid values are as follows: true: Uses the current database time as the reference point. false (Default): Uses the start time of the most recent subpartition as the reference point.

Note

This function applies only to time-based partitioned tables. It archives all subpartitioned tables and their indexes that are older than the specified tm_inter_value to OSS storage.
You can use this function with pg_cron to schedule automatic data archiving. For more information, see Schedule the archiving of partitioned table data to cold storage.
The database calculates one month as 30 days and one year as 365.25 days. You may need to add a buffer to the time interval value for months with 31 days or for leap years to avoid archiving data prematurely.

Example

-- Create a table partitioned by day
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');

-- Set the time interval to 1 day
select polar_alter_subpartition_to_oss_interval('partition_day', '1 day'::interval);

-- All data and indexes of the two subpartition tables partition_day_y2024m06d01 and partition_day_y2024m06d02 are archived to OSS

polar_alter_pathman_to_oss

This function archives historical subpartitioned tables and their indexes from a partitioned table managed by the pg_pathman extension to OSS storage.

Syntax

polar_alter_pathman_to_oss(text relname, interval tm_inter_value, boolean by_db_time default false);

Parameters

Parameter	Description
relname	The name of the partitioned table (parent table).
tm_inter_value	The time interval that defines which subpartitions are considered historical. Examples: '1 day', '1 mon', or '5 hours'. For more information about the interval type, see Interval Input.
by_db_time	Works with `tm_inter_value` to specify the reference point for identifying historical partitions. Subpartitions older than this `time interval` are considered historical. Valid values: true: Uses the current database time as the reference point. false (Default): Uses the start time of the most recent subpartition as the reference point.

Note

This function is supported only for PostgreSQL 14 (PostgreSQL 14) and clusters with revision version 2.0.14.17.33.0 or later.

You can view the revision version number in the console or by running the SHOW polardb_version; command. If your cluster does not meet the requirement, you must upgrade the revision version.
This function applies only to time-based partitioned tables. It archives all subpartitioned tables and their indexes that are older than the specified tm_inter_value to OSS storage.
You can use this function with pg_cron to schedule automatic data archiving. For more information, see Schedule the archiving of partitioned table data to cold storage.
The database calculates one month as 30 days and one year as 365.25 days. You may need to add a buffer to the time interval value for months with 31 days or for leap years to avoid archiving data prematurely.

Example

Create the pg_pathman extension.

CREATE EXTENSION IF NOT EXISTS pg_pathman;

Prepare a test table and create partitions.

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

-- Create partitions.
SELECT create_range_partitions('journal', 'dt', now()-'10 days'::interval, '1 day'::interval, 10);

Note

If you receive an error similar to ERROR: Disable superuser UDF calls: copy_foreign_keys (18898) when you create partitions, contact us.

Archive data older than 3 days to OSS storage.

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