Revive an Eon DB

• 1: In-DB restore points
• 1.1: Save restore points
• 1.2: Revive from a restore point
• 2: Revive with commuanal storage

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

• CREATE ARCHIVE
• SAVE RESTORE POINT TO ARCHIVE
• REMOVE RESTORE POINT FROM ARCHIVE
• Revive from a restore point

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

2 - Revive with commuanal storage

If you have terminated your Eon Mode database's cluster, but have not deleted the database's communal storage, you can use it to revive your database. Reviving the database using communal storage restores it to its pre-shutdown state. The revival process requires creating a new database cluster and configuring it to use the database's communal storage location. See Stopping, starting, terminating, and reviving Eon Mode database clusters for more information.

You can also use the revive process to restart a database when its nodes do not have persistent local storage. You may choose to configure your node's instances in your cloud-based Eon Mode cluster with non-persistent local storage to reduce cost. Cloud providers such as AWS and GCP charge less for instances when they are not required to retain data when you shut them down.

You revive a database using either the Management Console or admintools. The MC and admintools offer different revival methods:

• The MC always revives onto a newly-provision cluster that it creates itself. It cannot revive onto an existing cluster. Use the MC to revive a database when you do not have a cluster already provisioned for your database.

• admintools only revives onto an existing database cluster. You can manually create a cluster to revive your database. See Set up Vertica on-premises.

  You can also revive a database whose hosts use instance storage where data is not persistently stored between shutdowns. In this case, admintools treats the existing database cluster as a new cluster, because the hosts do not contain the database's catalog data.

• Currently, only admintools lets you revive just the primary subclusters in a database cluster. This option is useful if you want to revive the minimum number of nodes necessary to start your database. See Reviving Only Primary Subclusters below.

  The MC always revives the entire database cluster.

Reviving using the Management Console

You can use a wizard in the Management Console to provision a new cluster and revive a database onto it from a browser. For details, see:

• Reviving an Eon Mode database on AWS in MC
• Reviving an Eon Mode database on GCP in MC

Revive using admintools

You can use admintools to revive your Eon Mode database on an existing cluster.

Cluster requirements

This existing cluster must:

• Have the same version (or later version) of Vertica installed on it. You can repurpose an existing Vertica cluster whose database you have shut down. Another option is to create a cluster from scratch by manually installing Vertica (see Set up Vertica on-premises).

• Contain a number of hosts in the cluster that is equal to or greater than either:

  • The total number of nodes that the database cluster had when it shut down.

  • The total number of primary nodes the database cluster had when it shut down. When you supply a cluster that matches the number of primary nodes in the database, admintools revives just the primary nodes.

When reviving, you supply admintools with a list of the hosts in the cluster to revive the database onto. The number of hosts in this list must match either the total number of nodes or the number of primary nodes in the database when it shut down. If the number of nodes you supply does not match either of these values, admintools returns an error.

You do not need to use all of the hosts in the cluster to revive the database. You can revive a database onto a subset of the hosts in the cluster. But you must have at least enough hosts to revive all of the primary nodes.

For example, suppose you want to revive a database that had 16 nodes when it was shut down, with four of those nodes being primary nodes. In that case, you can revive:

• Just the primary nodes onto a cluster that contains at least four nodes.

• All of the 16 nodes onto a cluster that contains at least 16 nodes.

You may choose to revive your database onto a cluster with more nodes that is necessary in cases where you want to quickly add new nodes. You may also want to revive just the primary nodes in a database onto a larger cluster. In this case, you can use the extra nodes in the cluster to start one or more secondary subclusters.

Required database information

To revive the database, you must know:

• The name of the database to revive (note that the database name is case sensitive)

• The version of Vertica that created the database, so you can use the same or later version

• The total number of all nodes or the number of primary nodes in the database when it shut down

• The URL and credentials for the database's communal storage location

• The user name and password of the database administrator

• The IP addresses of all hosts in the cluster you want to revive onto

If you do not know what version of Vertica created the database or are unsure how many nodes it had, see Getting Database Details From a Communal Storage Location below.

Required database settings

Before starting the revive process, verify the following conditions are true for your Eon Mode environment:

Getting database details from a communal storage location

To revive a database, you must know:

• The version of Vertica that created it (so you can use the same or a later version)

• The total number of nodes (when reviving both primary and secondary nodes) or primary nodes (when just reviving the primary nodes) in the database's cluster when it shut down.

If you do not know these details, you can determine them based on the contents of the communal storage location.

If you are not sure which version of Vertica created the database stored in a communal storage location, examine the cluster_config.json file. This file is stored in the communal storage location in the folder named metadata/databasename. For example, suppose you have a database named mydb stored in the communal storage location s3://mybucket/mydb. Then you can download and examine the file s3://mybucket/mydb/metadata/mydb/cluster_config.json.

In the cluster_config.json, the Vertica version that created the database is stored with the JSON key named DatabaseVersion near the top of the file:

{
   "CatalogTruncationVersion" : 804,
   "ClusterLeaseExpiration" : "2020-12-21 21:52:31.005936",
   "Database" : {
      "branch" : "",
      "name" : "verticadb"
   },
   "DatabaseVersion" : "v10.1.0",
   "GlobalSettings" : {
      "TupleMoverServices" : -33,
      "appliedUpgrades" : [
 . . .

In this example, you can revive the storage location using Vertica version 10.1.0 or later.

If you do not know how many nodes or primary nodes the cluster had when it shut down, use the --display-only option of the admintools revive_db tool. Adding this option prevents admintools from reviving the database. Instead, it validates the files in the communal storage and reports details about the nodes that made up the database cluster. Parts of this report show the total number of nodes in the cluster and the number of primary nodes:

$ admintools -t revive_db --display-only --communal-storage-location \
             s3://mybucket/verticadb -d verticadb
Attempting to retrieve file: [s3://mybucket/verticadb/metadata/verticadb/cluster_config.json]

Validated 6-node database verticadb defined at communal storage s3://mybucket/verticadb.

Expected layout of database after reviving from communal storage: s3://mybucket/verticadb

== Communal location details: ==
{
 "communal_storage_url": "s3://mybucket/verticadb",
 "num_shards": "3",
 "depot_path": "/vertica/data",
   . . .
]

Number of primary nodes: 3

You can use grep to find just the relevant lines in the report:

$ admintools -t revive_db --display-only --communal-storage-location \
             s3://mybucket/verticadb -d verticadb | grep  'Validated\|primary nodes'
Validated 6-node database verticadb defined at communal storage s3://mybucket/verticadb.
Number of primary nodes: 3

Creating a parameter file

For Eon Mode deployments that are not on AWS, you must create a configuration file to pass the parameters listed in the table in the previous section to admintools. Traditionally this file is named auth_params.conf although you can choose any file name you want.

For on-premises Eon Mode databases, this parameter file is the same one you used when initially installing the database. See the following links for instructions on creating a parameter file for the communal storage solution you are using for your database:

• Pure Storage FlashBlade

• HDFS

• MinIO

For databases running on Microsoft Azure, the parameter file is only necessary if your database does not use managed identities. This file is the same format that you use to manually install an Eon Mode database. See Manually create an Eon Mode database on Azure for more information.

To revive an Eon Mode database on GCP manually, create a configuration file to hold the GCSAuth parameter and optionally, the GCSEnableHttp parameter.

You must supply the GCSAuth parameter to enable Vertica to read from the communal storage location stored in GCS. The value for this parameter is the HMAC access key and secret:

GCSAuth = HMAC_access_key:HMAC_secret_key

See Creating an HMAC Key for more information about HMAC keys.

If your Eon Mode database does not use encryption when accessing communal storage on GCS, then disable HTTPS access by adding the following line to auth_params.conf:

GCSEnableHttps = 0

Running the revive_db tool

Use the admintools revive_db tool to revive the database:

1. Use SSH to access a cluster host as an administrator.

2. Depending on your environment, run one of the following admintools commands:

   • AWS:

     $ admintools -t revive_db \
      --communal-storage-location=s3://communal_store_path \
      -s host1,... -d database_name
     

     Important

     If you revive an on-premises Eon Mode database to AWS, check the controlmode setting in /opt/vertica/config/admintools.conf. This setting must be compatible with the network messaging requirements of your Eon implementation. AWS relies on unicast messaging, which is compatible with a controlmode setting of point-to-point (pt2pt). If the source database controlmode setting was broacast and you migrate to S3/AWS communal storage, you must change controlmode with admintools:

     $ admintools -t re_ip -d dbname -T

   • On-premises and other environments:

     $ admintools -t revive_db -x auth_params.conf \
       --communal-storage-location=storage-schema://communal_store_path \
       -s host1_ip,... -d database_name
     

This example revives a six-node on-premises database:

$ admintools -t revive_db -x auth_params.conf \
   --communal-storage-location=s3://mybucket/mydir \
   -s 172.16.116.27,172.16.116.28,172.16.116.29,172.16.116.30,\
   172.16.116.31,172.16.116.32 -d VMart

The following example demonstrates reviving a three-node database hosted on GCP:

$ admintools -t revive_db -x auth_params.conf \
--communal-storage-location gs://mybucket/verticadb \
-s 10.142.0.35,10.142.0.38,10.142.0.39 -d VerticaDB

Attempting to retrieve file:
   [gs://mybucket/verticadb/metadata/VerticaDB/cluster_config.json]
Validated 3-node database VerticaDB defined at communal storage
  gs://mybucket/verticadb .
Cluster lease has expired.
Preparation succeeded all hosts
Calculated necessary addresses for all nodes.
Starting to bootstrap nodes. Please wait, databases with a large
  catalog may take a while to initialize.
>>Calling bootstrap on node v_verticadb_node0002 (10.142.0.38)
>>Calling bootstrap on node v_verticadb_node0003 (10.142.0.39)
Load Remote Catalog succeeded on all hosts
Database revived successfully.

Reviving only primary subclusters

You can revive just the primary subclusters in an Eon Mode database. Make the list of hosts you pass to the admintools revive_db tool's --hosts (or -s) argument match the number of primary nodes that were in the database when it shut down. For example, if you have a six-node Eon Mode database that had three primary nodes, you can revive just the primary nodes by supplying three hosts in the --hosts argument:

$ admintools -t revive_db --communal-storage-location=s3://verticadb -d verticadb \
             -x auth_params.conf --hosts node01,node02,node03
Attempting to retrieve file: [s3://verticadb/metadata/verticadb/cluster_config.json]
Consider reviving to only primary nodes: communal storage indicates 6 nodes, while
  3 nodes were specified

Validated 3-node database verticadb defined at communal storage s3://verticadb.
Cluster lease has expired.
Preparation succeeded all hosts

Calculated necessary addresses for all nodes.
Starting to bootstrap nodes. Please wait, databases with a large catalog may take a
  while to initialize.
>>Calling bootstrap on node v_verticadb_node0002 (192.168.56.103)
>>Calling bootstrap on node v_verticadb_node0003 (192.168.56.104)
Load Remote Catalog succeeded on all hosts

Database revived successfully.

In a database where you have revived only the primary nodes, the secondary nodes are down. Their IP address is set to 0.0.0.0 so they are not part of the database. For example, querying the NODES system table in the database revived in the previous example shows the secondary nodes are all down:

=> SELECT node_name,node_state,node_address,subcluster_name FROM NODES;
      node_name       | node_state |  node_address  |  subcluster_name
----------------------+------------+----------------+--------------------
 v_verticadb_node0001 | UP         | 192.168.56.102 | default_subcluster
 v_verticadb_node0002 | UP         | 192.168.56.103 | default_subcluster
 v_verticadb_node0003 | UP         | 192.168.56.104 | default_subcluster
 v_verticadb_node0004 | DOWN       | 0.0.0.0        | analytics
 v_verticadb_node0005 | DOWN       | 0.0.0.0        | analytics
 v_verticadb_node0006 | DOWN       | 0.0.0.0        | analytics

Because Vertica considers these unrevived nodes to be down, it may not allow you to remove them or remove their subcluster while they are in their unrevived state. The best way to remove the nodes or the secondary subcluster is to revive them first.

Reviving unrevived secondary subclusters

If you revived just the primary subclusters in your database, you can later choose to revive some or all of the secondary subclusters. Your cluster must have hosts that are not nodes in the database that Vertica can use to revive the unrevived nodes. If your cluster does not have enough of these non-node hosts, you can add more hosts. See Adding hosts to a cluster.

You revive a secondary subcluster by using the admintools' restart_subcluster tool. You supply it with the list of hosts in the --hosts argument where the nodes will be revived. The number of hosts in this list must match the number of nodes in the subcluster. You must revive all nodes in the subcluster at the same time. If you pass restart_subcluster a list with fewer or more hosts than the number of nodes defined in the subcluster, it returns an error.

The follow example demonstrates reviving the secondary subcluster named analytics shown in the previous examples.

$ admintools -t restart_subcluster -d verticadb --hosts node04,node05,node06 \
             -p 'password' -c analytics
Updating hostnames of nodes in subcluster analytics.
    Replicating configuration to all nodes
    Generating new configuration information and reloading spread
Hostnames of nodes in subcluster analytics updated successfully.
*** Restarting subcluster for database verticadb ***
    Restarting host [192.168.56.105] with catalog [v_verticadb_node0004_catalog]
    Restarting host [192.168.56.106] with catalog [v_verticadb_node0005_catalog]
    Restarting host [192.168.56.107] with catalog [v_verticadb_node0006_catalog]
    Issuing multi-node restart
    Starting nodes:
        v_verticadb_node0004 (192.168.56.105)
        v_verticadb_node0005 (192.168.56.106)
        v_verticadb_node0006 (192.168.56.107)
    Starting Vertica on all nodes. Please wait, databases with a large catalog may take a while to initialize.
    Node Status: v_verticadb_node0004: (DOWN) v_verticadb_node0005: (DOWN) v_verticadb_node0006: (DOWN)
    Node Status: v_verticadb_node0004: (DOWN) v_verticadb_node0005: (DOWN) v_verticadb_node0006: (DOWN)
    Node Status: v_verticadb_node0004: (DOWN) v_verticadb_node0005: (DOWN) v_verticadb_node0006: (DOWN)
    Node Status: v_verticadb_node0004: (INITIALIZING) v_verticadb_node0005: (INITIALIZING) v_verticadb_node0006: (INITIALIZING)
    Node Status: v_verticadb_node0004: (UP) v_verticadb_node0005: (UP) v_verticadb_node0006: (UP)
Syncing catalog on verticadb with 2000 attempts.

See also

• Stopping, starting, terminating, and reviving Eon Mode database clusters
• Terminating an Eon Mode database cluster
• Eon Mode architecture
• Adding and removing nodes from subclusters
• Altering subcluster settings