VerticaRestorePointsQuery custom resource definition

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 can use saved restore points to roll back your database to a previous state or restore specific objects in a VerticaDB CR.

A VerticaRestorePointsQuery custom resource (CR) allows you to save, view, and restore from restore points. You can also alter or drop archives.

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.

Note

VerticaRestorePoint v1beta1 is now deprecated. You must use the v1 version of this custom resource.

Prerequisites

Deploy a VerticaDB operator
Deploy a VerticaDB CR

Note

For SaveRestorePoint and ShowRestorePoints query types, you must be running OpenText™ Analytics Database version 24.4.0-0 or later.
For AlterArchive, DropArchive, and RestoreFromArchive query types, you must be running OpenText™ Analytics Database version 26.2.0-0 or later.

Save restore points

You can create an archive and save a restore point using the VerticaDB operator.

The following example defines a VerticaRestorePointsQuery CR in a YAML-formatted file named v-save-rp.yaml. This CR creates an archive named demo_archive, sets the maximum number of restore points to 10, and saves a new restore point:

apiVersion: vertica.com/v1
kind: VerticaRestorePointsQuery
metadata:
  name: v-save-rp
spec:
  verticaDBName: vertica-db
  queryType: SaveRestorePoint
  saveOptions:
    archiveName: demo_archive
    numRestorePoints: 10

Note

verticaDBName: The VerticaDB CR to save restore points for.
If the archive already exists, a new restore point is appended to it.
When the archive reaches maximum capacity, the oldest restore point is automatically deleted.
Set numRestorePoints to 0 to allow unlimited restore points.

Apply the manifest

Apply the manifest in the same namespace as the VerticaDB CR specified by verticaDBName:

$ kubectl apply -f v-save-rp.yaml
verticarestorepointsquery.vertica.com/v-save-rp created

Verify create archive and save restore point

Describe the object to view the status and events:

$ kubectl describe vrpq v-save-rp
Name:         v-save-rp
...
Spec:
  Query Type:  SaveRestorePoint
  Save Options:
    Archive Name:        demo_archive
    Num Restore Points:  10
  Vertica DB Name:       vertica-db
Status:
  Saved Restore Point:
    Archive:          demo_archive
    End Timestamp:    2026-03-27 19:00:12.118907879
    Start Timestamp:  2026-03-27 19:00:01.808632860
  State:              Query successful
Events:
  Type    Reason                     Age   From                Message
  ----    ------                     ----  ----                -------
  Normal  CreateArchiveStart         92s   verticadb-operator  Starting create archive
  Normal  CreateArchiveSucceeded     92s   verticadb-operator  Successfully created archive. It took 0s
  Normal  SaveRestorePointStart      92s   verticadb-operator  Starting save restore point
  Normal  SaveRestorePointSucceeded  81s   verticadb-operator  Successfully saved restore point to archive: demo_archive. It took 10s

View restore points

VerticaRestorePointsQuery CR allows you to retrieve details about the saved restore points.

The following example defines the VerticaRestorePointsQuery CR as a YAML-formatted file named v-show-rp.yaml and allows you to set the filter options (archiveName, startTimestamp, and endTimestamp) to fetch the details about the restore points that were saved to the archive.

apiVersion: vertica.com/v1
kind: VerticaRestorePointsQuery
metadata:
  name: v-show-rp
spec:
  verticaDBName: vertica-db
  filterOptions:
    startTimestamp: 2026-03-27 00:00:00.000000000
    endTimestamp: 2026-03-28 23:59:59.999999999
    archiveName: demo_archive

Apply the manifest

Apply the manifest in the same namespace as the VerticaDB CR specified by verticaDBName:

$ kubectl apply -f v-show-rp.yaml
verticarestorepointsquery.vertica.com/v-show-rp created

View saved restore points

You can describe the VerticaRestorePointsQuery object which returns a Status section that describes the query activity and properties for each retrieved restore point:

$ kubectl describe vrpq v-show-rp
Name:         v-show-rp
...
Spec:
  Filter Options:
    Archive Name:     demo_archive
    End Timestamp:    2026-03-28 23:59:59.999999999
    Start Timestamp:  2026-03-27 00:00:00.000000000
  Query Type:         ShowRestorePoints
  Vertica DB Name:    vertica-db
Status:
  Conditions:
    Last Transition Time:  2026-03-27T20:17:47Z
    Message:
    Reason:                Completed
    Status:                True
    Type:                  QueryReady
    Last Transition Time:  2026-03-27T20:17:52Z
    Message:
    Reason:                Completed
    Status:                False
    Type:                  Querying
    Last Transition Time:  2026-03-27T20:17:52Z
    Message:
    Reason:                Completed
    Status:                True
    Type:                  QueryComplete
  Restore Points:
    Archive:          demo_archive
    Id:               4103be86-df7d-41d5-8050-8931d02ad40e
    Index:            1
    Timestamp:        2026-03-27 19:00:02.705664
    vertica_version:  v24.4.0-3-5e948c75d3ff43ccbc2ef14b915f91ae727b05bf
  State:              Query successful
Events:
  Type    Reason                      Age   From                Message
  ----    ------                      ----  ----                -------
  Normal  ShowRestorePointsStarted    17s   verticadb-operator  Starting show restore points
  Normal  ShowRestorePointsSucceeded  13s   verticadb-operator  Successfully queried restore points in 3s

Alter an archive

You can modify an existing restore point archive using VerticaRestorePointsQuery CR in one of the following ways:

Assign a new owner to the archive.
Adjust the maximum number of restore points an archive can hold.

The following examples define the VerticaRestorePointsQuery CR as a YAML-formatted files named v-alter-rp-owner.yaml and v-alter-rp-max.yaml that allow you to modify the owner and maximum number of restore points of an archive named demo_archive.

v-alter-rp-owner.yaml

apiVersion: vertica.com/v1
kind: VerticaRestorePointsQuery
metadata:
  name: v-alter-rp-owner
spec:
  verticaDBName: vertica-db
  queryType: AlterArchive
  alterOptions:
    archiveName: demo_archive
    owner: mynewuser

v-alter-rp-max.yaml

apiVersion: vertica.com/v1
kind: VerticaRestorePointsQuery
metadata:
  name: v-alter-rp-max
spec:
  verticaDBName: vertica-db
  queryType: AlterArchive
  alterOptions:
    archiveName: demo_archive
    numRestorePoints: 30

Note

verticaDBName: The VerticaDB CR to alter the archive for.
You can change either the owner or the maximum restore points, but not both at the same time.
Set numRestorePoints to 0 to allow unlimited restore points.

Apply the manifest

Apply the manifest in the same namespace as the VerticaDB CR specified by verticaDBName:

$ kubectl apply -f v-alter-rp-owner.yaml  
verticarestorepointsquery.vertica.com/v-alter-rp-owner created

Verify alter archive

Describe the object to view the status and events:

$ kubectl describe vrpq v-alter-rp-owner
Name:         v-alter-rp-owner
...
Spec:
  Alter Options:
    Archive Name:   demo_archive
    Owner:          mynewuser
  Query Type:       AlterArchive
  Vertica DB Name:  vertica-db
Status:
  Conditions:
    Last Transition Time:  2026-03-28T19:50:10Z
    Message:
    Reason:                Completed
    Status:                True
    Type:                  QueryReady
    Last Transition Time:  2026-03-28T19:50:12Z
    Message:
    Reason:                Completed
    Status:                False
    Type:                  Querying
    Last Transition Time:  2026-03-28T19:50:12Z
    Message:
    Reason:                Completed
    Status:                True
    Type:                  QueryComplete
  Restore Points:
  State:  Query successful
Events:
  Type    Reason                 Age   From                Message
  ----    ------                 ----  ----                -------
  Normal  AlterArchiveStart      59s   verticadb-operator  Starting alter archive
  Normal  AlterArchiveSucceeded  58s   verticadb-operator  Successfully altered archive: demo_archive. It took 0s

Restore from archive

You can restore individual database objects (such as specific tables) or revive the entire database from a restore point saved in an archive.

The following example defines the VerticaRestorePointsQuery CR as a YAML-formatted file named v-restore-rp.yaml and allows you to restore a table named test:

apiVersion: vertica.com/v1
kind: VerticaRestorePointsQuery
metadata:
  name: v-restore-rp
spec:
  verticaDBName: vertica-db
  queryType: Restore
  restoreOptions:
    archiveName: demo_archive
    objects: "include table test"
    mode: "CREATE_OR_REPLACE"
    restorePoint: "1"
    dryRun: false

Note

verticaDBName: The VerticaDB CR to restore to.
For details about restoreOptions, see Custom resource definition parameters.

Apply the manifest

Apply the manifest in the same namespace as the VerticaDB CR specified by verticaDBName:

$ kubectl apply -f v-restore-rp.yaml
verticarestorepointsquery.vertica.com/v-restore-rp created

Verify restore from archive

Describe the object to view the status and events:

$ kubectl describe vrpq v-restore-rp
Name:         v-restore-rp
...
Spec:
  Query Type:  Restore
  Restore Options:
    Archive Name:   demo_archive
    Mode:           CREATE_OR_REPLACE
    Objects:        include table test
    Restore Point:  1
  Vertica DB Name:  vertica-db
Status:
  Conditions:
    Last Transition Time:  2026-03-27T21:25:12Z
    Message:
    Reason:                Completed
    Status:                True
    Type:                  QueryReady
    Last Transition Time:  2026-03-27T21:25:36Z
    Message:
    Reason:                Completed
    Status:                False
    Type:                  Querying
    Last Transition Time:  2026-03-27T21:25:36Z
    Message:
    Reason:                Completed
    Status:                True
    Type:                  QueryComplete
  Restore Points:
  State:  Query successful
Events:
  Type    Reason                       Age   From                Message
  ----    ------                       ----  ----                -------
  Normal  RestoreFromArchiveStart      91s   verticadb-operator  Starting restore from archive
  Normal  RestoreFromArchiveSucceeded  69s   verticadb-operator  Successfully restored from archive: demo_archive. It took 21s

Drop an archive

You can drop an existing restore point archive using VerticaRestorePointsQuery CR.

The following examples define the VerticaRestorePointsQuery CR as YAML-formatted files named v-alter-rp-owner.yaml and v-alter-rp-max.yaml that allow you to modify the owner and maximum number of restore points of an archive named demo_archive.

apiVersion: vertica.com/v1
kind: VerticaRestorePointsQuery
metadata:
  name: v-drop-rp
spec:
  verticaDBName: vertica-db
  queryType: DropArchive
  dropOptions:
    archiveName: demo_archive
    cascade: true

Note

verticaDBName: The VerticaDB CR to drop the archive from.
If the archive contains restore points and cascade is not set to true, the operation fails. Set cascade to true to drop the archive and all restore points.

Apply the manifest

Apply the manifest in the same namespace as the VerticaDB CR specified by verticaDBName:

$ kubectl apply -f v-drop-rp.yaml  
verticarestorepointsquery.vertica.com/v-drop-rp created

Verify drop archive

Describe the object to view the status and events:

$ kubectl describe vrpq v-drop-rp
Name:         v-drop-rp
...
Spec:
  Drop Options:
    Archive Name:   demo_archive
    Cascade:        true
  Query Type:       DropArchive
  Vertica DB Name:  vertica-db
Status:
  Conditions:
    Last Transition Time:  2026-03-28T20:12:40Z
    Message:
    Reason:                Completed
    Status:                True
    Type:                  QueryReady
    Last Transition Time:  2026-03-28T20:12:45Z
    Message:
    Reason:                Completed
    Status:                False
    Type:                  Querying
    Last Transition Time:  2026-03-28T20:12:45Z
    Message:
    Reason:                Completed
    Status:                True
    Type:                  QueryComplete
  Restore Points:
  State:  Query successful
Events:
  Type    Reason                Age   From                Message
  ----    ------                ----  ----                -------
  Normal  DropArchiveStart      14s   verticadb-operator  Starting drop archive
  Normal  DropArchiveSucceeded  11s   verticadb-operator  Successfully dropped archive: demo_archive. It took 3s

Understanding status details

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 a version below the minimal supported version. `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 required action (save, show, alter, or drop) is completed successfully.	`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.

Revive the database from restore point

Note

To revive a database from a version that did not support TLS to one that does, first revive the database without enabling TLS. After reviving is complete, you can configure TLS.
To revive a database with TLS enabled, add the annotation vertica.com/enable-tls-auth: "true" to the YAML file. In the same file, specify the previously used httpsNMATLS and clientServerTLS along with the TLS mode (if secret and mode are omitted, try_verify will be used by default). If the specified mode differs from the original TLS mode, it will trigger a TLS configuration update. Reviving the database will fail if these secret names are incorrect, so it is important to note or back up the correct secret names before removing a TLS-enabled database.

For more information, see TLS in Kubernetes.

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.

Back up the following before deleting the CR:
- Existing VerticaDB CR manifest.
- All secrets used in the VerticaDB CR, such as communal storage, license, and TLS credentials. You will need them when reviving the database.

Delete the existing CR:

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

Update the VerticaDB 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: "demo_archive"
    index: 1
    ...

Apply the updated manifest:

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

Create scheduled restore points with stored procedures

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.