VerticaRestorePointsQuery custom resource definition

The VerticaRestorePointsQuery custom resource (CR) retrieves details about saved restore points that you can use to roll back your database to a previous state or restore specific objects in a VerticaDB CR.

A VerticaRestorePointsQuery CR defines query parameters that the VerticaDB operator uses to retrieve restore points from an archive. A restore point is a snapshot of a database at a specific point in time that can consist of an entire database or a subset of database objects. Each restore point has a unique identifier and a timestamp. An archive is a collection of chronologically organized restore points.

You specify the archive and an optional period of time, and the operator queries the archive and retrieves details about restore points saved in the archive. You can use the query results to revive a VerticaDB CR with the data saved in the restore point.

Prerequisites

• Deploy a VerticaDB CR
• Deploy a VerticaDB operator

Save restore points

Before the VerticaDB operator can retrieve restore points, you must create an archive and save restore points to that archive. You can leverage stored procedures and scheduled execution to save restore points to an archive on a regular schedule. In the following sections, you schedule a stored procedure to save restore points to an archive every night at 9:00 PM.

Create the archive and schedule restore points

Create an archive and then create a stored procedure that saves a restore point to that archive:

1. Create an archive with CREATE ARCHIVE. The following statement creates an archive named nightly because it will store restore points that are saved every night:

   $ kubectl exec -it restorepoints-primary1-0 -c server -- vsql \
   -w password \
   -c "CREATE ARCHIVE nightly;"
   CREATE ARCHIVE
   

2. Create a stored procedure that saves a restore point. The SAVE RESTORE POINT TO ARCHIVE statement creates a restore point and saves it to the nightly archive:

   $ kubectl exec -it restorepoints-primary1-0 -c server -- vsql \
     -w password \
     -c "CREATE OR REPLACE PROCEDURE take_nightly()
     LANGUAGE PLvSQL AS \$\$
     BEGIN
       EXECUTE 'SAVE RESTORE POINT TO ARCHIVE nightly';
     END;
     \$\$;"
   CREATE PROCEDURE
   

3. To test the stored procedure, execute it with the CALL statement:

   $ kubectl exec -it restorepoints-primary1-0 -c server -- vsql \
   -w password \
   -c "CALL take_nightly();"
    take_nightly
   --------------
               0
   (1 row)
   

4. To verify that the stored procedure saved the restore point, query the ARCHIVE_RESTORE_POINTS system table to return the number of restore points in the specified archive:

   $ kubectl exec -it restorepoints-primary1-0 -c server -- vsql \
   -w password \
   -c "SELECT COUNT(*) FROM ARCHIVE_RESTORE_POINTS
   WHERE ARCHIVE = 'nightly';"
    COUNT
   -------
        1
   (1 row)
   

Schedule the stored procedure

Schedule the stored procedure so that it saves a restore point to the nightly archive each night:

1. Schedule a time to execute the stored procedure with CREATE SCHEDULE. This function uses a cron expression to create a schedule at 9:00 PM each night:

   $ kubectl exec -it restorepoints-primary1-0 -c server -- vsql \
   -w password \
   -c "CREATE SCHEDULE nightly_sched USING CRON '0 21 * * *';"
   CREATE SCHEDULE
   

2. Set CREATE TRIGGER to execute the take_nightly stored procedure with the nightly_sched schedule:

   $ kubectl exec -it restorepoints-primary1-0 -c server -- vsql \
   -w password \
   -c "CREATE TRIGGER trigger_nightly_sched ON SCHEDULE nightly_sched
   EXECUTE PROCEDURE take_nightly() AS DEFINER;"
   CREATE TRIGGER
   

Verify the archive automation

After you create the stored procedure and configure its schedule, test that it executes and saves a stored procedure at the scheduled time:

1. Before the cron job is scheduled to run, verify the system time with the date shell built-in:

   $ date -u
   Thu Feb 29 20:59:15 UTC 2024
   

2. Wait until the scheduled time elapses:

   $ date -u
   Thu Feb 29 21:00:07 UTC 2024
   

3. To verify that the scheduled stored procedure executed on time, query ARCHIVE_RESTORE_POINTS system table for details about the nightly archive:

   $ kubectl exec -it restorepoints-primary1-0 -c server -- vsql \
   -w password \
   -c "SELECT COUNT(*) FROM ARCHIVE_RESTORE_POINTS WHERE ARCHIVE = 'nightly';"
    COUNT
   -------
        2
   (1 row)
   

   COUNT is incremented by one, so the stored procedure saved the restore point on schedule.

Create a VerticaRestorePointsQuery

A VerticaRestorePointsQuery manifest specifies an archive and an optional time duration. The VerticaDB operator uses this information to retrieve details about the restore points that were saved to the archive.

Create and apply the manifest

The following manifest defines a VerticaRestorePointsQuery CR named vrqp. The vrqp CR instructs the operator to retrieve from the nightly archive all restore points saved on Feburary 29, 2024:

1. Create a file named vrpq.yaml that contains the following manifest. This CR retrieves restore points :

   apiVersion: vertica.com/v1beta1
   kind: VerticaRestorePointsQuery
   metadata:
     name: vrqp
   spec:
     verticaDBName: restorepoints
     filterOptions:
       archiveName: "nightly"
       startTimestamp: 2024-02-29
       endTimestamp: 2024-02-29
   

   The spec contains the following fields:

   • verticaDBName: Name of the VerticaDB CR that you want to retrieve restore points for.
   • filterOptions.archiveName: Archive that contains the restore points that you want to retrieve.
   • filterOptions.startTimestamp: Retrieve restore points that were saved on or after this date.
   • filterOptions.endTimestamp: Retrieve restore points that were saved on or before this date.

   For additional details about these parameters, see Custom resource definition parameters.

2. Apply the manifest in the current namespace with kubectl:

   $ kubectl apply -f vrpq.yaml
   verticarestorepointsquery.vertica.com/vrpq created
   

   After you apply the manifest, the operator begins working to retrieve the restore points.

3. Verify that the query succeeded with kubectl:

   $ kubectl get vrpq
   NAME   VERTICADB       STATE              AGE
   vrpq   restorepoints   Query successful   10s
   

View retrieved restore points

After you apply the VerticaRestorePointsQuery CR, you can view the retrieved restore points with kubectl describe. kubectl describe returns a Status section, which describes the query activity and properties for each retrieved restore point:

$ kubectl describe vrpq
Name:         vrpq
...
Status:
  Conditions:
    Last Transition Time:  2024-03-15T17:40:39Z
    Message:
    Reason:                Completed
    Status:                True
    Type:                  QueryReady
    Last Transition Time:  2024-03-15T17:40:41Z
    Message:
    Reason:                Completed
    Status:                False
    Type:                  Querying
    Last Transition Time:  2024-03-15T17:40:41Z
    Message:
    Reason:                Completed
    Status:                True
    Type:                  QueryComplete
  Restore Points:
    Archive:          nightly
    Id:               af8cd407-246a-4500-bc69-0b534e998cc6
    Index:            1
    Timestamp:        2024-02-29 21:00:00.728787
    vertica_version:  version
  State:              Query successful
...

The Status section contains relevant restore points details in the Conditions and Restore Points fields.

Conditions

The Conditions field summarizes each stage of the restore points query and contains the following fields:

• Last Transition Time: Timestamp that indicates when the status condition last changed.
• Message: This field is not in use, you can safely ignore it.
• Reason: Indicates why the query stage is in its current Status.
• Status: Boolean, indicates whether the query stage is currently in process.
• Type: The query that the VerticaDB operator is executing in this stage.

The following table describes each Conditions.Type, and all possible value combinations for its Reason and Status field values:

Restore Points

The Restore Points field lists each restore point that was retrieved from the archive and contains the following fields:

• Archive: The archive that contains this restore point.
• Id: Unique identifier for the restore point.
• Index: Restore point rank ordering in the archive, by descending timestamp. 1 is the most recent restore point.
• Timestamp: Time that indicates when the restore point was created.
• vertica_version: Database version when this restore point was saved to the archive.

Restore the database

After the operator retrieves the restore points, you can restore the database with the archive name and either the restore point Index or Id. In addition, you must set initPolicy to Revive:

1. Delete the existing CR:

   $ kubectl delete -f restorepoints.yaml
   verticadb.vertica.com "restorepoints" deleted
   

2. Update the CR. Change the initPolicy to Revive, and add the restore point information. You might have to set ignore-cluster-lease to true:

   apiVersion: vertica.com/v1
   kind: VerticaDB
   metadata:
     name: restorepoints
     annotations:
       vertica.com/ignore-cluster-lease: "true"
   spec:
     initPolicy: Revive
     restorePoint:
       archive: "nightly"
       index: 1
     ...
   

3. Apply the updated manifest:

   $ kubectl apply -f restorepoints.yaml
   verticadb.vertica.com/restorepoints created