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

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 and optionally to set a limit to the number of restore points that can be saved to an archive. 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   | object_inclusion_rules | data_inclusion_rules
-----------------+-------------+-----------+------------------------+----------------------
 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 function to save a restore point:

=> SAVE RESTORE POINT TO ARCHIVE vertica-restore;
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
--------------------------------------+----------------------------+-------+-----------------+----------+-------------------------
 2616f6cd-e98c-472c-8d5c-a7f459dff82b | 2023-11-30 10:19:58.565311 |     1 | vertica-restore | COMPLETE |           v24.4.0
 116066cc-29cb-4ce5-9a27-170538ba95cf | 2023-11-10 11:02:48.059941 |     2 | vertica-restore | COMPLETE |           v24.4.0
(2 rows)

For details about using 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 restore point to an archive, you can use those restore points to revive a terminated or down database. The revived database is restored to the state at which the restore point was saved.

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
-----------------+----------------------------+-------+--------------------------------------
 vertica-restore | 2023-11-30 14:10:31.038289 | 1     | 4ee4119b-802c-4bb4-94b0-061c8748b602
 vertica-restore | 2023-11-10 14:10:28.717667 | 2     | bdaa4764-d8aa-4979-89e5-e642cc58d972

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-seperated list of hosts to which the database is revived. This can be a different number of hosts than the number of hosts 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.