PolarDB:Archive data to cold storage with one click

polar_alter_relation_to_oss

Archives a non-partitioned table, subpartitioned table, or index to OSS. Indexes associated with the table remain at their original storage location.

Syntax

polar_alter_relation_to_oss(text relname)

Parameters

Behavior by object type

• Non-partitioned table or subpartitioned table: archives the table data to OSS; indexes remain at their original storage location.

• Partitioned table: use polar_alter_partitioned_to_oss instead.

• Index: archives the index data to OSS.

polar_alter_relation_to_oss_with_indexes

Archives a non-partitioned table or subpartitioned table together with all its indexes to OSS.

Syntax

polar_alter_relation_to_oss_with_indexes(text relname)

Parameters

Behavior by object type

• Non-partitioned table or subpartitioned table: archives the table and all its indexes to OSS.

• Partitioned table: use polar_alter_partitioned_to_oss instead.

• Index: archives only the index data to OSS (same behavior as polar_alter_relation_to_oss).

polar_alter_relation_to_oss_cascade

Archives a table or index together with all subordinate objects to OSS.

Syntax

polar_alter_relation_to_oss_cascade(text relname)

Parameters

Behavior by object type

• Non-partitioned table or subpartitioned table: archives the table and all its indexes to OSS.

• Partitioned table: archives the partitioned table, its global index, all subpartitioned tables, and their indexes to OSS.

• Index: archives only the index data to OSS (same behavior as polar_alter_relation_to_oss).

polar_alter_partitioned_to_oss

Sets the default storage location of a partitioned table (parent table) to OSS, without moving the actual data in subpartitions.

Syntax

polar_alter_partitioned_to_oss(text relname)

Parameters

This function applies only to partitioned tables. Because a partitioned table stores data in its subpartitions — not in the parent table itself — this function does not move any data. It only changes the default tablespace setting for the parent table. To archive the actual data, use one of the subpartition archiving functions.

polar_alter_subpartition_to_oss

Archives a specified number of subpartitions of a partitioned table to OSS, keeping a specified number of subpartitions on local storage.

Syntax

polar_alter_subpartition_to_oss(text relname, int reserved_subparts_cnt)

Parameters

Behavior

• Applies only to partitioned tables.

• Archives subpartitions to OSS; indexes of subpartitions remain at their original storage location.

• reserved_subparts_cnt = 0: archives all subpartitions to OSS.

• reserved_subparts_cnt > 0: keeps N subpartitions unchanged in ascending order of their retention times; archives the rest to OSS.

polar_alter_subpartition_to_oss_with_indexes

Archives a specified number of subpartitions of a partitioned table — together with their indexes — to OSS.

Syntax

polar_alter_subpartition_to_oss_with_indexes(text relname, int reserved_subparts_cnt)

Parameters

Behavior

• Applies only to partitioned tables.

• Archives subpartitions and their indexes to OSS.

• reserved_subparts_cnt = 0: archives all subpartitions and their indexes to OSS.

• reserved_subparts_cnt > 0: keeps N subpartitions unchanged in ascending order of their retention times; archives the rest — including their indexes — to OSS.

polar_alter_subpartition_to_oss_interval

Archives historical subpartitions of a time-based partitioned table to OSS, based on a time interval cutoff. Use this function with pg_cron for automated scheduled archiving.

Syntax

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

Parameters

Behavior

• Applies only to time-based partitioned tables.

• Archives historical subpartitions and their indexes to OSS.

• The database treats 1 month as 30 days and 1 year as 365.25 days. Add a buffer to tm_inter_value to account for months with 31 days or leap years.

• You can use the pg_cron extension 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.

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');

-- Archive subpartitions older than 1 day to OSS.
-- The reference point is the start time of the most recent subpartition (2024-06-03).
-- Subpartitions starting before 2024-06-02 are archived:
--   partition_day_y2024m06d01 and partition_day_y2024m06d02 are moved to OSS.
SELECT polar_alter_subpartition_to_oss_interval('partition_day', '1 day'::interval);

polar_alter_pathman_to_oss

Archives historical subpartitions of a pg_pathman-managed partitioned table to OSS, based on a time interval cutoff.

Version requirement

This function is supported only for PolarDB for PostgreSQL 14 with revision version 2.0.14.17.33.0. To check your cluster version, run SHOW polardb_version; or go to the PolarDB console. If your version does not meet the requirement, update it before using this function.

Syntax

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

Parameters

Behavior

• Applies only to time-based partitioned tables managed by pg_pathman.

• Archives all subpartitions and indexes with a start time older than tm_inter_value to OSS.

• The database treats 1 month as 30 days and 1 year as 365.25 days. Add a buffer to tm_inter_value to account for months with 31 days or leap years.

• Use with pg_cron to automate scheduled archiving.

Example

-- Step 1: Install the pg_pathman extension
CREATE EXTENSION IF NOT EXISTS pg_pathman;

-- Step 2: Create a test table and partition it
CREATE TABLE journal (
    id   SERIAL,
    dt   TIMESTAMP NOT NULL,
    lev  INTEGER,
    msg  TEXT
);

-- Create 10 daily partitions starting from 10 days ago
SELECT create_range_partitions('journal', 'dt', now() - '10 days'::interval, '1 day'::interval, 10);

-- Step 3: Archive partitions older than 3 days to OSS
SELECT polar_alter_pathman_to_oss('journal', '3 days'::interval);

If you see the error ERROR: Disable superuser UDF calls: copy_foreign_keys (18898) when creating partitions, contact support.