Save restore points

...

Before you can save restore points, you must first create an archive to store them. Archives store the restore points in descending chronological order based on the times at which the restore points were saved. Each restore point can be identified by its unique identifier or the index in the archive, where an index of 1 specifies the most recently saved restore point.

An archive can store full-database restore points or object-level restore points. Object-level restore points include only the specified database objects and their dependent objects, which can reduce catalog serialization and restore processing time when you need to protect or restore a subset of the database.

Create an archive

The CREATE ARCHIVE statement, which requires superuser privileges, creates an archive and assigns the creator as the archive's owner. This statement requires you to specify an archive name. You can optionally set the maximum number of restore points that the archive can store. For example, the following statement creates an archive named vertica-restore and sets the maximum restore point limit to 15:

=> CREATE ARCHIVE vertica-restore LIMIT 15;

To view all archives in your database, you can query the ARCHIVES system table:

=> SELECT * FROM ARCHIVES;
       name        | limit_count |   owner
------------------+-------------+----------
 vertica-restore | 15          | 450359926
 (1 row)

You can modify an archive's owner or change the restore point limit using the ALTER ARCHIVE statement. To drop an archive, use DROP ARCHIVE. The drop statement returns an error if the archive contains any restore points, unless you specify CASCADE, in which case the archive and any restore points it contains are all dropped.

Save restore point to an archive

After you have created an archive, you can use the SAVE RESTORE POINT TO ARCHIVE statement to save a full-database restore point:

=> SAVE RESTORE POINT TO ARCHIVE vertica-restore;
SAVE RESTORE POINT

To save an object-level restore point, specify object inclusion rules with the OBJECTS clause. For example, the following statement saves a restore point for the store schema:

=> SAVE RESTORE POINT TO ARCHIVE object_backup OBJECTS ( INCLUDE SCHEMA store );
SAVE RESTORE POINT

You can include objects by exact name or by matching names with ILIKE, and you can exclude objects from the included set. The following examples save object-level restore points with different inclusion rules:

=> SAVE RESTORE POINT TO ARCHIVE object_backup OBJECTS ( INCLUDE TABLE store.orders );
SAVE RESTORE POINT

=> SAVE RESTORE POINT TO ARCHIVE object_backup OBJECTS ( INCLUDE TABLES ILIKE store.% );
SAVE RESTORE POINT

=> SAVE RESTORE POINT TO ARCHIVE object_backup OBJECTS ( INCLUDE SCHEMA store, EXCLUDE TABLE store.stage_orders );
SAVE RESTORE POINT

If an archive contains the maximum number of restore points and a new restore point is saved, the oldest restore point in the archive is deleted. To view all restore points in your database, query the ARCHIVE_RESTORE_POINTS system table, which provides the unique identifiers for each restore point—the object ID and the index in its archive:

=> SELECT * FROM ARCHIVE_RESTORE_POINTS;

                  id                  |         save_time          | index |     archive     |   state  | vertica_version | object_inclusion_rules
--------------------------------------+----------------------------+-------+-----------------+----------+-----------------+------------------------
 2616f6cd-e98c-472c-8d5c-a7f459dff82b | 2026-08-30 10:19:58.565311 |     1 | object_backup   | COMPLETE | v26.4.0         | INCLUDE SCHEMA store
 116066cc-29cb-4ce5-9a27-170538ba95cf | 2026-08-10 11:02:48.059941 |     2 | vertica-restore | COMPLETE | v26.4.0         |
(2 rows)

To restore objects from a restore point, use RESTORE FROM ARCHIVE. For details about using full-database restore points to revive a database, see Revive from a restore point.

Remove restore points from an archive

To remove a restore point from an archive, use the REMOVE RESTORE POINT FROM ARCHIVE statement. This statement requires you to specify either the restore point's index in the archive or its unique object ID. If neither of these identifiers are provided, the newest restore point in the archive is removed:

=> REMOVE RESTORE POINT FROM ARCHIVE vertica-restore;
REMOVE RESTORE POINT

Query the ARCHIVE_RESTORE_POINTS table to confirm the status of the removal. If the remove operation is still in progress, the restore point appears with a status of REMOVING. After the operation completes, the restore point is no longer listed in the table:

=> SELECT * FROM ARCHIVE_RESTORE_POINTS;
                  id                  |         save_time          | index |  archive  |  state 
--------------------------------------+----------------------------+-------+-----------+----------
 116066cc-29cb-4ce5-9a27-170538ba95cf | 2023-11-10 11:02:48.059941 |     1 | verticadb | COMPLETE
(2 rows)

See also