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

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

Background information

The ALTER TABLE tblname SET TABLESPACE oss statement can be used to dump a single object. However, to dump 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 (Compatible with Oracle) provides a series of auxiliary functions for dumping 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 dumps objects, such as non-partitioned tables, subpartitioned tables, and indexes to OSS storage.

Syntax

void 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 dumps 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 dumps the data in the index to OSS.

polar_alter_relation_to_oss_with_indexes

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

Syntax

void 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 dumps the table and all its indexes to OSS.
For a partitioned table, use polar_alter_partitioned_to_oss.
For an index, the function dumps 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 dumps all objects (tables and indexes) and their subordinate objects to OSS.

Syntax

void 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 dumps the table and all its indexes to OSS.
For a partitioned table, the function dumps the table, its global index, and its subpartitioned tables and their indexes to OSS.
For an index, the function dumps the data in the index to OSS, which is similar to polar_alter_relation_to_oss.

polar_alter_partitioned_to_oss

This function dumps a partitioned table to OSS.

Syntax

void 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 dumps 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 dumps partial subpartitioned tables of the partitioned table to OSS and specifies the number of subpartitioned tables whose storage location keeps unchanged.

Syntax

void 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 dumps 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 dumps 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 dumps the other subpartitioned tables to OSS.

polar_alter_subpartition_to_oss_with_indexes

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

Syntax

void 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 dumps their subpartitioned tables and indexes to OSS.
If the reserved_subparts_cnt value is 0, the function dumps 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 dumps the other subpartitioned tables to OSS.

polar_alter_subpartition_to_oss_interval

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

Syntax

void 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 dumps 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.
In databases, a month is equal to 30 days and a year is equal to 365.25 days. Therefore, to cope with months of 31 days and leap years, you must specify a larger 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