This is the multi-page printable view of this section. Click here to print.

Return to the regular view of this page.

In-DB restore points

In-database restore points are lightweight, copy-free backups for Eon Mode databases that enable you to roll back a database to the state at which a restore point is saved.

In-database restore points are lightweight, copy-free backups for Eon Mode databases that enable you to roll back a database to the state at which the restore point was saved. Unlike vbr-based backups, restore points are stored in-database and do not require additional data copies to be stored externally, which results in fast restore point creation and low storage overhead.

Restore points are useful for cases such as the following:

  • If a user error compromises data integrity, such as unintentionally dropping a table or inserting the wrong data into a table, you can restore the database to a state before the problem transactions.
  • In the case of a failed upgrade, you can restore the database to its pre-upgrade state.
  • You want to create a number of backups while avoiding the overhead of storing copies of the database externally.

Before saving restore points, you must first create an archive to store them. You can then save restore points to the archive and select a restore point when reviving the database.

1 - 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

2 - Revive from a restore point

...

After you've saved a full-database restore point to an archive, you can use that restore point to revive a terminated or down database on a cluster with the same number of nodes as the database had when the restore point was saved. Full-database restore to a database with a different node count is not supported. The revived database is restored to the state at which the restore point was saved. You cannot revive a database from an object-level restore point. To restore objects from an object-level restore point, use RESTORE FROM ARCHIVE.

To revive your database from a restore point, you use the revive_db admintools command. This command requires that you specify the restore point's archive name and either its index in the archive or its object ID. If the database is down and you need to confirm the restore point details, you can use the show_restore_points admintools command to list all restore points and their attributes. You must provide the database name and the communal storage location's address and credentials, and optionally specify the archive from which to list restore points and whether the output is in text or JSON format:

$ admintools -t show_restore_points --database = vertica_db \\
    --communal-storage-location = s3://mybucket \\
    --communal-storage-params = auth_params.conf \\
    --output-format = text

    archive     | timestamp                  | index | id                                   | includes_database | objects_rules
-----------------+----------------------------+-------+--------------------------------------+-------------------+----------------------
 vertica-restore | 2026-08-30 14:10:31.038289 | 1     | 4ee4119b-802c-4bb4-94b0-061c8748b602 | t                 |
 object_backup   | 2026-08-10 14:10:28.717667 | 2     | bdaa4764-d8aa-4979-89e5-e642cc58d972 | f                 | INCLUDE SCHEMA store

Revive a database from a restore point

The revive_db admintools command lets you revive a database to either the last synced state of the database in communal storage or the state at which a restore point was saved. When using the revive_db command to revive from a restore point, you must specify the following arguments:

-s HOSTS, --hosts=HOSTS
Comma-separated list of hosts to which the database is revived. The number of hosts must match the number of nodes that were in the database when the restore point was saved.
--communal-storage-location=COMMUNAL_STORAGE_LOCATION
Location of the communal storage that holds the restore points.
-x COMMUNAL_STORAGE_PARAMS, --communal-storage-params=COMMUNAL_STORAGE_PARAMS
Location of the communal storage parameter file.
-d DBNAME, --database=DBNAME
Name of the database to be revived.
--restore-point-archive=RESTORE_POINT_ARCHIVE
Name of the archive that holds the restore point from which to revive.
[ --restore-point-id=RESTORE_POINT_ID | --restore-point-index=RESTORE_POINT_INDEX ]
A unique identifier of the restore point from which to revive, either the restore point's object ID or its index in the archive.

For example, the following revive_db command revives the vertica_db database using the restore point with index 1 in the vertica-restore archive:

$ admintools -t revive_db -s host-list \\
    --communal-storage-location = s3://mybucket \\
    --communal-storage-params = auth_params.conf \\
    -d vertica_db
    --restore-point-archive = `vertica-restore`
    --restore-point-index = 1

The Vertica version of the revived database must be the same as or newer than the version of the database when the restore point was saved. After a database is revived, any restore point that was saved using a Vertica version newer than the version of the revived database will be invalidated. You can remove the invalidated restore points from the database using the REMOVE RESTORE POINT FROM ARCHIVE statement.

The revive operation preserves existing archives, but their configuration, including restore point limits, are rolled back to their state when the restore point was saved. If an archive didn't exist at the time of the restore point to which the database was revived, that archive is revived with default configuration settings—no restore point limit and dbadmin as owner.