Archiving partitions

You can move partitions from one table to another with the Vertica function MOVE_PARTITIONS_TO_TABLE.

You can move partitions from one table to another with the Vertica function MOVE_PARTITIONS_TO_TABLE. This function is useful for archiving old partitions, as part of the following procedure:

Identify the partitions to archive, and move them to a temporary staging table with MOVE_PARTITIONS_TO_TABLE.
Back up the staging table.
Drop the staging table.

You restore archived partitions at any time.

Move partitions to staging tables

You archive historical data by identifying the partitions you wish to remove from a table. You then move each partition (or group of partitions) to a temporary staging table.

Before calling MOVE_PARTITIONS_TO_TABLE:

Refresh all out-of-date projections.

The following recommendations apply to staging tables:

To facilitate the backup process, create a unique schema for the staging table of each archiving operation.
Specify new names for staging tables. This ensures that they do not contain partitions from previous move operations.

If the table does not exist, Vertica creates a table from the source table's definition, by calling CREATE TABLE with LIKE and INCLUDING PROJECTIONS clause. The new table inherits ownership from the source table. For details, see Replicating a table.
Use staging names that enable other users to easily identify partition contents. For example, if a table is partitioned by dates, use a name that specifies a date or date range.

In the following example, MOVE_PARTITIONS_TO_TABLE specifies to move a single partition to the staging table partn_backup.tradfes_200801.

=> SELECT MOVE_PARTITIONS_TO_TABLE (
          'prod_trades',
          '200801',
          '200801',
          'partn_backup.trades_200801');
MOVE_PARTITIONS_TO_TABLE
-------------------------------------------------
 1 distinct partition values moved at epoch 15.
(1 row)

Back up the staging table

After you create a staging table, you archive it through an object-level backup using a vbr configuration file. For detailed information, see Backing up and restoring the database.

Important

Vertica recommends performing a full database backup before the object-level backup, as a precaution against data loss. You can only restore object-level backups to the original database.

Drop the staging tables

After the backup is complete, you can drop the staging table as described in Dropping tables.

Restoring archived partitions

You can restore partitions that you previously moved to an intermediate table, archived as an object-level backup, and then dropped.

Note

Restoring an archived partition requires that the original table definition is unchanged since the partition was archived and dropped. If the table definition changed, you can restore an archived partition with INSERT...SELECT statements, which are not described here.

These are the steps to restoring archived partitions:

Restore the backup of the intermediate table you saved when you moved one or more partitions to archive (see Archiving partitions).
Move the restored partitions from the intermediate table to the original table.
Drop the intermediate table.