Archiving Partitions
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 can retrieve and restore archived partitions at any time, as described in Restoring Archived Partitions.
For general information about moving partitions, see MOVE_PARTITIONS_TO_TABLE
.
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
, you must:
- Drop any pre-join projections associated with the source 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 callingCREATE TABLE
withLIKE
andINCLUDING 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.