VerticaRestorePointsQuery custom resource definition
Important
Beta Feature — For Test Environments OnlyThe 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
- This is a Beta feature.
- 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 namedvdb
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 toFalse
. If you want to trigger another restore point in the future, you only need to update thestatus
field toTrue
without changing the other fields.
- 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
-
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 that are in Beta:
CREATE ARCHIVE
statementSAVE RESTORE POINT TO ARCHIVE
statementARCHIVE_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 namednightly
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 thenightly
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 thenightly_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 thenightly
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 currentStatus
.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 |
False |
| ||
Querying |
The operator is running the query. |
True |
Started |
False |
| ||
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
toRevive
, and add the restore point information. You might have to setignore-cluster-lease
totrue
: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