Archive data to cold storage with one click - PolarDB - Alibaba Cloud Documentation Center

This topic how to archive data to cold storage with on click.

Background information

The ALTER TABLE tblname SET TABLESPACE oss statement can be used to archive a single object. However, to archive a table and its multiple indexes or a partitioned table and its numerous subpartitioned tables, you must execute the ALTER statement multiple times. This process is not only tedious but also prone to missing errors. To resolve this issue, PolarDB for PostgreSQL provides a series of auxiliary functions for archiving data to cold storage.

Create a polar_osfs_toolkit extension

CREATE EXTENSION IF NOT EXISTS polar_osfs_toolkit;

Functions

polar_alter_relation_to_oss

This function archives objects, such as non-partitioned tables, subpartitioned tables, and indexes to OSS storage.

Syntax

polar_alter_relation_to_oss(text relname);

Parameters

Parameter	Description
relname	The object (table and index) name.

Note

For a non-partitioned table or subpartitioned table, the function archives the table data to OSS while maintaining the original storage location of the indexes.
For a partitioned table, use polar_alter_partitioned_to_oss.
For an index, the function archives the data in the index to OSS.

polar_alter_relation_to_oss_with_indexes

This function archives an object (non-partitioned table and subpartitioned table) and its index data to OSS.

Syntax

polar_alter_relation_to_oss_with_indexes(text relname);

Parameters

Parameter	Description
relname	The table name or index name.

Note

For a non-partitioned table or subpartitioned table, the function archives the table and all its indexes to OSS.
For a partitioned table, use polar_alter_partitioned_to_oss.
For an index, the function archives only the data in the index to OSS, which is similar to polar_alter_relation_to_oss.

polar_alter_relation_to_oss_cascade

This function archives all objects (tables and indexes) and their subordinate objects to OSS.

Syntax

polar_alter_relation_to_oss_cascade(text relname);

Parameters

Parameter	Description
relname	The table name or index name.

Note

For a non-partitioned table or subpartitioned table, the function archives the table and all its indexes to OSS.
For a partitioned table, the function archives the table, its global index, and its subpartitioned tables and their indexes to OSS.
For an index, the function archives the data in the index to OSS, which is similar to polar_alter_relation_to_oss.

polar_alter_partitioned_to_oss

This function archives a partitioned table to OSS.

Syntax

polar_alter_partitioned_to_oss(text relname);

Parameters

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

Note

This function applies only to partitioned tables and archives only partitioned tables to OSS, while maintaining its subpartitioned tables and their indexes unchanged. Since the data of the partitioned table is stored in each subpartitioned table, this function does not change the data storage location, but only sets the default storage location of the partitioned table to OSS.

polar_alter_subpartition_to_oss

This function archives partial subpartitioned tables of the partitioned table to OSS and specifies the number of subpartitioned tables whose storage location keeps unchanged.

Syntax

polar_alter_subpartition_to_oss(text relname, int reserved_subparts_cnt);

Parameters

Parameter	Description
relname	The partitioned table (parent table) name.
reserved_subparts_cnt	The number of subpartitioned tables whose storage location is unchanged.

Note

This function applies only to partitioned tables and archives partitioned tables and their subpartitioned tables to OSS. The storage location of index data of the subpartitioned tables remains unchanged.
If the reserved_subparts_cnt value is 0, the function archives all subpartitioned tables to OSS.
If the reserved_subparts_cnt value is greater than 0, the function sorts out the subpartitioned tables based on their retention times. It keeps the storage location of reserved_subparts_cnt subpartitioned tables unchanged in ascending order of their retention times, and archives the other subpartitioned tables to OSS.

polar_alter_subpartition_to_oss_with_indexes

This function archives partial subpartitioned tables of a partitioned table to OSS, and sets the number of subpartitioned tables whose storage location keeps unchanged.

Syntax

polar_alter_subpartition_to_oss_with_indexes(text relname, int reserved_subparts_cnt);

Parameters

Parameter	Description
relname	The partitioned table (parent table) name.
reserved_subparts_cnt	The number of subpartitioned tables whose storage location is unchanged.

Note

This function applies only to partitioned tables and archives their subpartitioned tables and indexes to OSS.
If the reserved_subparts_cnt value is 0, the function archives all subpartitioned tables and their indexes to OSS.
If the reserved_subparts_cnt value is greater than 0, the function sorts out the subpartitioned tables based on their retention times. It keeps the storage location of reserved_subparts_cnt subpartitioned tables unchanged in ascending order of their retention times, and archives the other subpartitioned tables to OSS.

polar_alter_subpartition_to_oss_interval

This function archives the historical subpartitioned tables of a partitioned table to OSS.

Syntax

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

Parameters

Parameter	Description
relname	The partitioned table (parent table) name.
tm_inter_value	The time interval, which is used to determine historical subpartitioned tables. Examples: 1 day, 1 mon, and 5 hours. For more information, see Interval Input.
by_db_time	This parameter is used with the `tm_inter_value` parameter to determine whether the current time can be used as the start time for historical subpartitioned tables. The subpartitioned tables longer than the `tm_inter_value` value are historical. Valid values: true: uses the current time of the database as the start time for historical subpartitioned tables. false (default): uses the time of the latest subpartitioned table as the start time for historical subpartitioned tables.

Note

This function applies only to time-based partitioned tables and archives historical subpartitioned tables and their indexes to OSS.
You can use the pg_cron extension together to schedule the archiving of partitioned table data to cold storage. 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. To deal with months that have 31 days or leap years, include a buffer in the time interval value.

Examples

-- Create tables that are 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);

-- The partition_day_y2024m06d01 and partition_day_y2024m06d02 subpartitioned tables and their indexes are all dumped to OSS

polar_alter_pathman_to_oss

This function archives the historical subpartitions of a pg_pathman-managed partitioned table to OSS.

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 value used to determine which subpartitions are historical, for example, 1 day, 1 mon, or 5 hours. For more information about interval types, see Interval.
by_db_time	Specifies whether to use the current database time as the starting point for determining historical subpartitions. This parameter works in conjunction with `tm_inter_value` to determine which subpartitions are historical. Subpartitions with a start time older than the specified `tm_inter_value` are identified as historical partitions. Valid values: true: uses the current database time as the starting point for determining historical subpartitions. false (default): uses the start time of the most recent subpartition as the starting point for determining historical subpartitions.

Note

This function is supported only for PolarDB for PostgreSQL 14 with revision version 2.0.14.17.33.0.
To view the version information of your cluster, go to the PolarDB console or execute the SHOW polardb_version; statement. If the revision version of your cluster does not meet the requirement, update it.
This function is applicable only to time-partitioned tables. It archives all subpartitiones and indexes with a start time older than the specified tm_inter_value to OSS.
You can use this function with the pg_cron extension to automate the archiving of historical subpartitions to OSS based on a predefined timeline. 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. To deal with months that have 31 days or leap years, include a buffer in the time interval value.

Example

Create the pg_pathman extension.

CREATE EXTENSION IF NOT EXISTS pg_pathman;

Create a test table and partition it.

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 encounter 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.

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