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

You can save a restore point using VerticaDB operator in Kubernetes or by using vsql in Vertica.

Save a restore point using VerticaDB operator

Important

To save a restore point, you must be running Vertica version 24.4.0-0 or later.

Use kubectl edit to open your default text editor and update the yaml file for the specified custom resource. The following command opens a custom resource named vdb for editing:
```
$ kubectl edit vdb
```
In the spec section of the custom resource, add an entry for the archive name. The VerticaDB operator creates the archive, using the spec.restorePoint.archive as the archive name to save the restore point.
```
apiVersion: vertica.com/v1
kind: VerticaDB
metadata:
  name: vertica-db
spec:
...
  restorePoint:
    archive: demo_archive
```
To save a restore point, edit the status condition as follows:
```
$ kubectl edit --subresource=status vdb/vertica-db
```
Add the following conditions to initialize save restore point:
```
apiVersion: vertica.com/v1
kind: VerticaDB
metadata:
  name: vertica-db
spec:
...
status:
...
conditions:
- lastTransitionTime: "2024-10-01T17:27:27Z"
  message: ""
  reason: Init
  status: "True"
  type: SaveRestorePointNeeded
```
Note
- You need to set all five fields as shown in the example when you trigger save restore point for the first time. After that, only the status field needs to be updated for subsequent triggers.
- The lastTransitionTime, message, and reason fields can have any value. lastTransitionTime must follow the date-time format as shown in the example. Reason must contain at least one character.
- After the restore point is saved, the status field changes to False. If you want to trigger another restore point in the future, you only need to update the status field to True without changing the other fields.

You can check the status of the restore point as follows:

$ kubectl describe vdb
Name:         vertica-db
...
Events:
 Type    Reason                     Age   From                Message
 ----    ------                     ----  ----                -------
 Normal  CreateArchiveStart         54s   verticadb-operator  Starting create archive
 Normal  CreateArchiveSucceeded     54s   verticadb-operator  Successfully create archive. It took 0s
 Normal  SaveRestorePointStart      54s   verticadb-operator  Starting save restore point
 Normal  SaveRestorePointSucceeded  33s   verticadb-operator  Successfully save restore point to archive: demo_archive. It took 20s

You can get the new archive name, start timestamp, and end timestamp from vdb status.

To retrieve details about the most recently created restore point, use these values (archive, startTimestamp, and endTimestamp) as filter options in a VerticaRestorePointsQuery CR. See, Create a VerticaRestorePointsQuery.
```
kubectl describe vdb
...
Status:
 ...
 Restore Point:
   Archive:          demo_archive
   End Timestamp:    2024-10-09 12:25:28.956094972
   Start Timestamp:  2024-10-09 12:25:19.029997424
```

Save a restore point using vsql in Vertica

Important

This section contains the following SQL elements:

CREATE ARCHIVE statement
SAVE RESTORE POINT TO ARCHIVE statement
ARCHIVE_RESTORE_POINTS system table

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.

Note

The following steps demonstrate how to retrieve restore points for a VerticaDB CR named restorepoints. Each kubectl exec command executes vsql statements in a running Vertica server container in restorepoints.

For details about kubectl exec, see the Kubernetes documentation.

Create the archive and schedule restore points

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

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

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

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)

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:

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

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:

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
```
Wait until the scheduled time elapses:
```
$ date -u
Thu Feb 29 21:00:07 UTC 2024
```
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:

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

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:

Type	Description	Status	Reason
`QueryReady`	The operator verified that the query is executable in the environment.	`True`	`Completed`
`QueryReady`		`False`	`IncompatibleDB`: CR specified by `verticaDBName` is not version 24.2 or later. `AdmintoolsNotSupported`: CR specified by `verticaDBName` does not use apiVersion `v1`. For details, see VerticaDB custom resource definition.
`Querying`	The operator is running the query.	`True`	`Started`
`Querying`	The operator is running the query.	`False`	`Failed` `Completed`
`QueryComplete`	The query is complete and the restore points are available in the `Restore Points` array.	`True`	`Completed`

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:

Delete the existing CR:

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

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

Apply the updated manifest:

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