This topic describes how to back up and restore VMware Postgres Operator.
The VMware Postgres Operator allows you to backup instances on-demand, schedule automated backups, restore from a backup in-place or to new Postgres instances, and clone.
The supported locations for uploading and retrieving backup artifacts are Amazon S3 (or other S3-compatible data stores like Minio), or Azure Blob Storage.
VMware Postgres Operator backup and restore uses four Custom Resource Definitions (CRDs):
PostgresBackup: References a Postgres backup artifact that exists in an external blobstore such as S3 or Minio. Every time you generate an on-demand or scheduled backup, the VMware Postgres Operator creates a new PostgresBackup resource.
PostgresBackupLocation: References an external blobstore and the necessary credentials for blobstore access.
PostgresBackupSchedule: Represents a CronJob schedule specifying when to perform backups.
PostgresRestore: References a Postgres restore artifact that receives a PostgresBackup resource and restores the data from the backup to a new Postgres instance or to the same postgres instance (an in-place restore).
For detailed information about the CRDs, see Backup and Restore CRD API Reference.
Before creating a VMware Postgres Operator backup you need:
kubectl
command line tool installed on your local client, with access permissions to the Kubernetes cluster.pgdata
persistent volume (PV) backups will be stored.kubectl get namespaces
for a list of available namespaces.Create on-demand or scheduled backups by configuring the PostgresBackupLocation CRD, which specifies the details of the location and access credentials to the external blobstore.
To take a backup to an external S3 or Azure location, create a PostgresBackupLocation resource:
Locate the backuplocation.yaml
deployment yaml in the ./samples
directory of your downloaded release, and create a copy with a unique name. For example:
cp ~/Downloads/postgres-for-kubernetes-v1.8.0/samples/backuplocation.yaml testbackuplocation.yaml
Edit the file using the configuration details of your external S3 bucket or Azure container. The same file contains the properties of your backup credentials secret.
Note: Namespaces should not share the same bucket or bucketPath. Use a unique spec.storage.s3.bucket
or spec.storage.s3.bucketPath
(or for Azure spec.storage.azure.container
or spec.storage.azure.repoPath
) per namespace.
For example:
---
apiVersion: sql.tanzu.vmware.com/v1
kind: PostgresBackupLocation
metadata:
name: backuplocation-sample
spec:
retentionPolicy:
fullRetention:
type: count
number: 9999999
diffRetention:
number: 9999999
storage: #Either Azure or S3 storage can be configured
# azure:
# container: "postgresql-backups"
# repoPath: "/my-repo-path"
# endpoint: "blob.core.windows.net"
# keyType: "shared"
# forcePathStyle: false
# enableSSL: false
# secret:
# name: my-azure-backuplocation-secret
s3:
bucket: "name-of-bucket"
bucketPath: "/my-bucket-path"
region: "us-east-1"
endpoint: "custom-endpoint"
forcePathStyle: false
enableSSL: true
secret:
name: backuplocation-creds-sample
additionalParameters: {}
backupSync:
enabled: true
backupIntegrityValidation:
enabled: true
---
apiVersion: v1
kind: Secret
metadata:
name: backuplocation-creds-sample
type: generic
stringData:
# accountName: "azure-storage-account-name" #azure account name
# accountKey: "azure-storage-key" #azure account key
accessKeyId: "my-access-key-id"
secretAccessKey: "my-secret-access-key"
For details on the fields and properties see Backup and Restore CRD API Reference and Secret Properties.
Create the PostgresBackupLocation resource in the Postgres instance namespace:
kubectl apply -f FILENAME -n DEVELOPMENT-NAMESPACE
where:
FILENAME
is the name of the configuration file you created in Step 2 above.DEVELOPMENT-NAMESPACE
is the namespace for the Postgres instance you intend to backup.For example:
kubectl apply -f testbackuplocation.yaml -n my-namespace
postgresbackuplocation.sql.tanzu.vmware.com/backuplocation-sample created
secret/backuplocation-creds-sample configured
View the created PostgresBackupLocation by running:
kubectl get postgresbackuplocation <BACKUP-LOCATION-NAME> \
-o jsonpath={.spec} -n DEVELOPMENT-NAMESPACE
For example:
kubectl get postgresbackuplocation backuplocation-sample -o jsonpath={.spec} -n my-namespace
If your storage is an S3 bucket, the command returns similar to:
{
"storage":{
"s3":{
"bucket":"name-of-bucket",
"bucketPath":"/my-bucket-path",
"enableSSL":true,
"endpoint":"custom-endpoint",
"forcePathStyle":false,
"region":"us-east-1",
"secret":{
"name":"backuplocation-creds-sample"
}
}
}
}
Update the Postgres instance manifest with the PostgresBackupLocation field. Go to the location where you have stored the VMware Postgres Operator instance manifest file. For example:
cd ./postgres-for-kubernetes-v<version>
Edit the manifest yaml file you used to deploy the instance; in this example the file is called postgres.yaml
. Provide a value for the backupLocation
attribute.
For example:
apiVersion: sql.tanzu.vmware.com/v1
kind: Postgres
metadata:
name: postgres-sample
spec:
storageClassName: standard
storageSize: 800M
cpu: "0.8"
memory: 800Mi
monitorStorageClassName: standard
monitorStorageSize: 1G
pgConfig:
dbname: postgres-sample
username: pgadmin
serviceType: ClusterIP
highAvailability:
enabled: false
backupLocation:
name: backuplocation-sample
Execute the kubectl apply
command, specifying the manifest file you edited. For example:
kubectl apply -f ./postgres.yaml --wait=false
postgres.sql.tanzu.vmware.com "postgres-sample" configured
If the manifest file contains any incorrectly formatted values or unrecognized field names, an error message is displayed identifying the issue. Edit the manifest to correct the error and run the command again.
Verify the updated configuration by viewing the backupLocation fields of the instance object:
kubectl get postgres/postgres-sample -o jsonpath='{.spec.backupLocation}'
{"name":"backuplocation-sample"}
From VMware Postgres Operator 1.7.0 backups are not automatically encrypted. VMware recommends configuring server-side encryption, using the tools offered by your storage provider. Users can optionally configure client-side encryption using the steps in this topic.
Note: The backups performed using the current backup location can still be restored as long as the backuplocation resource is not deleted from Kubernetes.
Identify the backuplocation attached to the instance you'll like to set up encryption for:
kubectl get postgres postgres-sample -n <namespace>
NAME STATUS DB VERSION BACKUP LOCATION AGE
postgres-sample Running 14.2 backuplocation-sample 17h
where BACKUP LOCATION
lists the name of the desired backup location.
Output the contents of the backuplocation to a temporary file:
kubectl get postgresbackuplocation backuplocation-sample -n default -o yaml > /tmp/backuplocation-to-encrypt.yaml
Edit the /tmp/backuplocation-to-encrypt.yaml
file to reflect a new unique metadata name, a cipher, and a new bucket or bucket path for s3 (or new container or repoPath for azure). In the S3 specific example below, we're using /sample-backup-path
as the new bucket path, my-simple-cipher
as the encryption key, and encrypted-backup-location
as the unique backuplocation name:
---
apiVersion: sql.tanzu.vmware.com/v1
kind: PostgresBackupLocation
metadata:
name: encrypted-backup-location
spec:
storage:
s3:
bucket: "name-of-bucket"
bucketPath: "/sample-backup-path"
region: "us-east-1"
endpoint: "custom-endpoint"
forcePathStyle: false
enableSSL: true
secret:
name: backuplocation-creds-sample
additionalParameters:
repo1-cipher-pass: "my-simple-cipher"
repo1-cipher-type: "aes-256-cbc"
Execute the kubectl apply
command, specifying the manifest file you edited. For example:
kubectl apply -f /tmp/backuplocation-to-encrypt.yaml
postgresbackuplocation.sql.tanzu.vmware.com/encrypted-backup-location created
Edit the backupLocation
attribute in the manifest yaml file you used to deploy the instance to reflect the recently created backuplocation.
For example:
apiVersion: sql.tanzu.vmware.com/v1
kind: Postgres
metadata:
name: postgres-sample
spec:
storageClassName: standard
storageSize: 800M
cpu: "0.8"
memory: 800Mi
monitorStorageClassName: standard
monitorStorageSize: 1G
pgConfig:
dbname: postgres-sample
username: pgadmin
serviceType: ClusterIP
highAvailability:
enabled: false
backupLocation:
name: encrypted-backup-location
Execute the kubectl apply
command, specifying the manifest file you edited. For example:
kubectl apply -f ./postgres.yaml --wait=false
postgres.sql.tanzu.vmware.com "postgres-sample" configured
If the manifest file contains any incorrectly formatted values or unrecognized field names, an error message is displayed identifying the issue. Edit the manifest to correct the error and run the command again.
Verify the updated configuration by viewing the backupLocation fields of the instance object:
kubectl get postgres/postgres-sample -o jsonpath='{.spec.backupLocation}'
{"name":"encrypted-backup-location"}
The data pod will then be restarted to pick up the new changes to the backuplocation. Once the pods are up and running, use the following command to confirm that cipher is now in use
kubectl exec -t postgres-sample-0 -- bash -c 'pgbackrest info --stanza=$BACKUP_STANZA_NAME | grep cipher'
The output looks similar to:
Defaulted container "pg-container" out of: pg-container, instance-logging, reconfigure-instance, postgres-metrics-exporter, postgres-sidecar
cipher: aes-256-cbc
Prerequisites: Ensure the Postgres instance you want to backup is not configured as a continuous restore target.
To take a manual on-demand backup follow these steps:
Locate the backup.yaml
deployment template located in the ./samples
directory of the downloaded release file.
Create a copy of the backup.yaml
file and give it a unique name. For example:
cp ~/Downloads/postgres-for-kubernetes-v1.8.0/backup.yaml testbackup.yaml
Edit the file according to your environment. For details on the properties of the PostgresBackup resource, see Properties for the PostgresBackup Resource.
Trigger the backup by creating the PostgresBackup resource in the instance namespace, by running:
kubectl apply -f FILENAME -n DEVELOPMENT-NAMESPACE
where FILENAME
is the name of the configuration file you created in Step 3 above.
For example:
kubectl apply -f testbackup.yaml -n my-namespace
postgresbackup.sql.tanzu.vmware.com/backup-sample created
Verify that the backup has been generated, and track its progress by using:
kubectl get postgresbackup backup-sample -n DEVELOPMENT-NAMESPACE
For example:
kubectl get postgresbackup backup-sample -n my-namespace
NAME STATUS SOURCE INSTANCE SOURCE NAMESPACE TYPE TIME STARTED TIME COMPLETED
backup-sample Succeeded postgres-sample my-namespace full 2021-08-31T14:29:14Z 2021-08-31T14:29:14Z
For further details on the above output, see List Existing PostgresBackup Resources below.
Prerequisites: Ensure the Postgres instance you want to backup is not configured as a continuous restore target.
To create scheduled backups, create a PostgresBackupSchedule resource:
Locate the backupschedule.yaml
template in the ./samples
directory of the release download, and copy to a new file. For example:
cp ~/Downloads/postgres-for-kubernetes-v3.0.0/samples/backupschedule.yaml testbackupschedule.yaml
Edit the file with the name of the Postgres instance you want to backup. For example:
apiVersion: sql.tanzu.vmware.com/v1
kind: PostgresBackupSchedule
metadata:
name: backupschedule-sample
spec:
backupTemplate:
spec:
sourceInstance:
name: postgres-sample
type: full
schedule: "0 0 * * SAT"
where:
postgres-sample
is the instance you're planning to backup.type
is full.schedule
is a cron job schedule; in this example it is planned for every Saturday at 00:00:00.For details on the PostgresBackupSchedule properties, see PostgresBackupSchedule Resource.
In the same namespace of the instance referenced in the PostgresBackupSchedule file, create the backup schedule resource:
kubectl apply -f FILENAME -n DEVELOPMENT-NAMESPACE`
where:
FILENAME
is the name of the configuration file you created in Step 1.DEVELOPMENT-NAMESPACE
is the namespace for the Postgres instance you intend to backup.For example:
kubectl apply -f testbackupschedule.yaml -n my-namespace
postgresbackupschedule.sql.tanzu.vmware.com/backupschedule-sample created
Verify that the PostgresBackupSchedule has been created by running:
kubectl get postgresbackupschedule backupschedule-sample -o jsonpath={.spec} -n DEVELOPMENT-NAMESPACE
For example:
kubectl get postgresbackupschedule backupschedule-sample -o jsonpath={.spec} -n my-namespace
{
"backupTemplate": {
"spec": {
"sourceInstance": {
"name": "postgres-sample"
},
"type": "full"
}
},
"schedule": "@daily"
}
After configuring the PostgresBackupLocation
resource and the PostgresBackupSchedule
resource for an existing Postgres instance, backups will be generated and uploaded to the external blobstore at the scheduled time.
The PostgresBackupSchedule generates PostgresBackup resources that have a name format like: SCHEDULE-NAME-TIMESTAMP
. For example, if the PostgresBackup resource on the Kubernetes cluster is named pgbackupschedule-sample
, and a backup was taken on Thursday, December 10, 2020 at 8:51:03 PM GMT, the PostgresBackup resource name is pgbackupschedule-sample-20201210-205103
.
Checkt the `status.message field in PostgresBackupSchedule CR:
kubectl get postgresbackupschedule backupschedule-sample -o jsonpath={.status.message}
You might want to list existing PostgresBackup resources for various reasons, for example:
List existing PostgresBackup resources by running:
kubectl get postgresbackup
NAME STATUS SOURCE INSTANCE SOURCE NAMESPACE TYPE TIME STARTED TIME COMPLETED
backup-sample Succeeded postgres-sample my-namespace full 2021-08-31T14:29:14Z 2021-08-31T14:29:14Z
Where:
STATUS
Represents the current status of the backup. Allowed values are:
SOURCE INSTANCE
is the Postgres instance the backup was taken from.TYPE
is the type of Postgres backup that was executed.TIME STARTED
is the time that the backup process started.TIME COMPLETED
is the time that the backup process finished. If the backup fails, this value is empty.Use the pgbackrest
command directly on the primary pod to review all existing backups, independent of version. For example, login into the pod and run:
postgres@postgres-sample-0:/$ pgbackrest info --stanza=${BACKUP_STANZA_NAME}
If the BACKUP_STANZA_NAME
is default-postgres-sample
, the output would be similar to:
stanza: default-postgres-sample
status: ok
cipher: aes-256-cbc
db (current)
wal archive min/max (11): 000000010000000000000004/000000010000000000000009
full backup: 20210915-140558F
timestamp start/stop: 2021-09-15 14:05:58 / 2021-09-15 14:06:04
wal start/stop: 000000010000000000000004 / 000000010000000000000004
database size: 31.0MB, database backup size: 31.0MB
repo1: backup set size: 3.7MB, backup size: 3.7MB
full backup: 20210916-143321F
timestamp start/stop: 2021-09-16 14:33:21 / 2021-09-16 14:33:41
wal start/stop: 000000010000000000000009 / 000000010000000000000009
database size: 31MB, database backup size: 31MB
repo1: backup set size: 3.7MB, backup size: 3.7MB
To list backups related to a specific Postgres instance in the cluster, use:
kubectl get postgresbackups -l postgres-instance=postgres-sample
with output similar to:
NAME STATUS SOURCE INSTANCE SOURCE NAMESPACE TYPE TIME STARTED TIME COMPLETED
backup-sample Succeeded postgres-sample my-namespace full 2021-10-05T21:17:34Z 2021-10-05T21:17:41Z
backup-sample-1 Succeeded postgres-sample my-namespace full 2021-10-05T21:28:46Z 2021-10-05T21:28:54Z
backup-sample-2 Succeeded postgres-sample my-namespace full 2021-10-05T21:29:44Z 2021-10-05T21:29:51Z
backup-sample-3 Succeeded postgres-sample my-namespace differential 2021-10-05T21:36:43Z 2021-10-05T21:36:49Z
backup-sample-4 Succeeded postgres-sample my-namespace differential 2021-10-05T21:37:20Z 2021-10-05T21:37:26Z
backup-sample-5 Succeeded postgres-sample my-namespace differential 2021-10-05T21:37:39Z 2021-10-05T21:37:45Z
backup-sample-6 Succeeded postgres-sample my-namespace full 2021-10-05T21:43:35Z 2021-10-05T21:43:42Z
backup-sample-7 Succeeded postgres-sample my-namespace full 2021-10-05T21:49:33Z 2021-10-05T21:49:41Z
backup-sample-8 Succeeded postgres-sample my-namespace full 2021-10-05T22:07:43Z 2021-10-05T22:07:50Z
To delete a backup and its artifacts, you can either:
The VMware Postgres Operator for Kubernetes supports backup retention policies via the PostgresBackupLocation CRD. For details see Configure the Backup Location.
IMPORTANT: All Postgres instances that utilize the associated PostgresBackupLocation will be restarted when you apply the change.
Use kubectl get
to get output the contents of your backup location to a file:
kubectl get postgresbackuplocation -o yaml > /tmp/my-backup-location.yaml
Use a text editor of your choice to update the retentionPolicy section to reflect your desired policy:
vim /tmp/my-backup-location.yaml
spec:
retentionPolicy:
diffRetention:
number: 3
fullRetention:
number: 3
type: count
storage:
s3:
bucket: postgresql-backups
bucketPath: /my-bucket-path
enableSSL: false
endpoint: minio.minio.svc.cluster.local:9000
forcePathStyle: true
region: us-east-1
secret:
name: my-backuplocation-secret
The file above is configured to retain 3 full backups and 3 differential backups.
Use kubectl apply
to configure the impacted instances that will start using new retention policy:
kubectl apply -f /tmp/my-backup-location.yaml
postgresbackuplocation.sql.tanzu.vmware.com/my-backup-location configured
Use kubectl get
to monitor when the instances are back up and running:
kubectl get postgres postgres-sample -n <namespace>
NAME STATUS DB VERSION BACKUP LOCATION AGE
postgres-sample Running 14.3 my-backup-location 23m
When the instances are up and running, and new backups are taken, the older backups will be deleted according to the configured retention policy. The corresponding Kubernetes PostgresBackup object will be automatically deleted within a few minutes of the backup being expired.
To remove a specific backup from the storage location, set .spec.expire=true
in the postgresbackup
CRD:
kubectl patch postgresbackup backup-sample -p '{"spec":{"expire":true}}' --type merge
Setting the field to true
creates a Kubernetes job that removes the backup from the backup location using pgbackrest expire
. The backup will be removed from all Kubernetes namespaces that use the same storage location.
The VMware Postgres Operator allows you to perform three types of data restore:
Customers on VMware Postgres Operator 1.7.x that wish to restore backups from prior versions, see Restoring Backups taken prior to VMware Postgres Operator 1.7.0.
Locate the restore.yaml
deployment yaml in the ./samples
directory of your downloaded release, and create a copy with a unique name. For example:
cp ~/Downloads/postgres-for-kubernetes-v3.0.0/samples/restore.yaml testrestore.yaml
Locate all the backups of the existing instance. To list all backups executed against a Postgres instance called postgres-sample
use the following command:
kubectl get postgresbackups -n my-namespace -l postgres-instance=postgres-sample
where my-namespace
specifies the namespace the instance was created in.
NAME STATUS SOURCE INSTANCE SOURCE NAMESPACE TYPE TIME STARTED TIME COMPLETED
backup-sample-4 Succeeded postgres-sample my-namespace full 2021-09-24T19:40:17Z 2021-09-24T19:40:24Z
backup-sample-5 Succeeded postgres-sample my-namespace full 2021-09-25T16:54:55Z 2021-09-25T16:55:02Z
backup-sample-6 Succeeded postgres-sample my-namespace full 2021-09-24T19:48:41Z 2021-09-24T19:48:48Z
backup-sample-7 Succeeded postgres-sample my-namespace full 2021-09-27T23:04:06Z 2021-09-27T23:04:13Z
backup-sample-8 Succeeded postgres-sample my-namespace full 2021-09-27T23:19:51Z 2021-09-27T23:19:58Z
Locate the backup you'll like to restore, and edit the testrestore.yaml
accordingly. For information about the PostgresRestore resource properties, see Backup and Restore CRD API Reference.
apiVersion: sql.tanzu.vmware.com/v1
kind: PostgresRestore
metadata:
name: restore-sample
spec:
sourceBackup:
name: backup-sample
targetInstance:
name: postgres-sample
spec:
postgresVersion:
name: postgres-15
When restoring to a new instance, there is no Postgres resource with the name populated in the spec.targetInstance.name
field.
Trigger the restore by creating the PostgresRestore resource in the same namespace as the PostgresBackup and PostgresBackupLocation by running:
kubectl apply -f FILENAME -n DEVELOPMENT-NAMESPACE
Where FILENAME
is the name of the yaml configuration file you created for the restore.
For example:
kubectl apply -f testrestore.yaml -n my-namespace
postgresrestores.sql.tanzu.vmware.com/restore-sample created
Verify that a restore has been triggered and track the progress of your restore by running:
kubectl get postgresrestore restore-sample -n my-namespace
NAME STATUS SOURCE TARGET INSTANCE TIME STARTED TIME COMPLETED
restore-sample Succeeded backup-sample postgres-sample 2022-07-27T23:34:13Z 2022-07-27T23:34:26Z
The table below explains the output:
Column Name | Meaning |
---|---|
STATUS |
Represents the current status of the restore process. Allowed values are:
|
SOURCE |
The name of the backup used for the restore. |
TARGET INSTANCE |
The name and spec of the Postgres instance that we will be using or creating for restore. |
TIME STARTED |
The time that the restore process started. |
TIME COMPLETED |
The time that the restore process finished. |
This feature synchronizes the backupLocation and backups of a single instance across different namespaces, and allows users to restore a VMware Postgres Operator instance to a different namespace than the namespace it was backed up in. Using this feature, users can perform the restore even if the original instance has been accidentally deleted.
In this topic, the namespace where the backup was created is referred to as the "source" namespace. The namespace for the restore is referred to as the "target" namespace.
Important: This feature was introduced in VMware Postgres Operator 1.7.0. Customers on previous releases, with existing backups, cannot utilize this feature without upgrading. Customers on VMware Postgres Operator 1.6.0 and earlier, are advised to upgrade their instances to 1.7.0, and perform a full backup after the upgrade. This full backup and any future backups will be able to be used for restores to a different namespace.
This feature is enabled by default and can be disabled by setting the `backupSync.enabled` field introduced in VMware Postgres Operator 2.1.0 to `false`.
Ensure that:
On the source namespace, identify the backupLocation name of the backup you wish to restore:
kubectl get postgresbackup backup-sample -n <namespace-source> -o jsonpath='{.status.backupLocation}'
where backup-sample
is the name of the backup resource created for the source instance backup. The output is similar to:
my-backup-location
On the target namespace, create a backuplocation.yaml
, using as content the output you receive from:
kubectl get postgresbackuplocation <name of backup location> -n <namespace-source> -o yaml
Edit the namespace field to match the target namespace before applying the CRD. Ensure that the backupSync.enabled
field is either absent from the location spec or set to true
.
At this point any backups taken by the VMware Postgres Operator 1.7.0 (or later), for this specific postgresbackuplocation
resource, will start to synchronize.
Create the secret that is attached to that postgresbackupLocation
CRD. To retrieve the secret name, issue a command similar to:
kubectl get postgresbackuplocation <name-of-backup-location> -n <namespace-origin> -o jsonpath='{.spec.storage.*.secret.name}'
which returns an output similar to:
my-backuplocation-secret
Create the secret on the target namespace, using the content you receive from the output of the following command:
kubectl get secret <name-of-secret-retrieved-above> -n <namespace-source> -o yaml
Edit the namespace field to match the target namespace before applying the CRD.
List the synchronized backups by using the following command:
kubectl get postgresbackup -n <namespace-target> -l sql.tanzu.vmware.com/recovered-from-backuplocation=true
The output would be similar to:
NAMESPACE NAME STATUS SOURCE INSTANCE SOURCE NAMESPACE TYPE TIME STARTED TIME COMPLETED
target-namespace sync-20220414-143642f-2aff7501 Succeeded my-postgres my-namespace full 2022-04-14T14:36:42Z 2022-04-14T14:37:01Z
Select which backup you wish to restore.
Edit the target namespace instance restore yaml, and edit it to reflect the backup you’ll like to restore, and the definition of the Postgres instance you’ll like to restore that backup, Create the PostgresRestore yaml
. A sample restore yaml is shown below:
apiVersion: sql.tanzu.vmware.com/v1
kind: PostgresRestore
metadata:
name: <provide-name-for-restore>
spec:
sourceBackup:
name: <provide-the-backup-name>
targetInstance:
name: <provide-the-instance-name>
spec: <provide-the-spec-of-the-target-instance>
Perform the restore by using:
kubectl apply -f restore.yaml
Validate that the restore succeeded by using the following command:
kubectl get postgresrestore.sql.tanzu.vmware.com/restore-sample -n <namespace-target>
which will show an output similar to:
NAME STATUS SOURCE TARGET INSTANCE TIME STARTED TIME COMPLETED
restore-sample Succeeded sync-20220415-201444f-2aff7501 my-postgres 2022-04-15T20:44:50Z 2022-04-15T20:45:31Z
Where the SOURCE
uniquely identifies the synchronized backup object by using the date (for example 20220415 ), time (for example 201444 represents 20:14:44), type of backup (f - full, d - differential, i - incremental), and part of the instance UUID number.
Validate that the VMware Postgres Operator instance in your target namespace is running, using:
kubectl get postgres.sql.tanzu.vmware.com/postgres-sample -n <namespace-target>
WARNING: Use only in development or test environments. An in-place restore is potentially destructive and irreversible.
In this scenario, you can use a previous backup to override data in an existing instance.
Before you restore from a backup, you must have:
To restore from a full backup:
Locate the restore.yaml
deployment yaml in the ./samples
directory of your downloaded release, and create a copy with a unique name. For example:
cp ~/Downloads/postgres-for-kubernetes-v3.0.0/samples/restore.yaml testrestore.yaml
Locate all backups of your existing instance. For example, for all the backups executed against a Postgres instance called 'postgres-sample' use:
kubectl get postgresbackups -n NAMESPACE -l postgres-instance=postgres-sample
NAME STATUS SOURCE INSTANCE SOURCE NAMESPACE TYPE TIME STARTED TIME COMPLETED
backup-sample-4 Succeeded postgres-sample my-namespace full 2021-09-24T19:40:17Z 2021-09-24T19:40:24Z
backup-sample-5 Succeeded postgres-sample my-namespace full 2021-09-25T16:54:55Z 2021-09-25T16:55:02Z
backup-sample-6 Succeeded postgres-sample my-namespace full 2021-09-24T19:48:41Z 2021-09-24T19:48:48Z
backup-sample-7 Succeeded postgres-sample my-namespace full 2021-09-27T23:04:06Z 2021-09-27T23:04:13Z
backup-sample-8 Succeeded postgres-sample my-namespace full 2021-09-27T23:19:51Z 2021-09-27T23:19:58Z
Locate the backup you'll like to restore, and edit the 'restore.yaml' with your information. For information about the PostgresRestore resource properties, see Backup and Restore CRD API Reference.
apiVersion: sql.tanzu.vmware.com/v1
kind: PostgresRestore
metadata:
name: restore-sample
spec:
sourceBackup:
name: backup-sample
targetInstance:
name: postgres-sample
For in-place restore, ensure that the sourceBackup
was performed on the targetInstance
. Refer to step 2 for validation.
Trigger the restore by creating the PostgresRestore resource in the same namespace as the PostgresBackup and PostgresBackupLocation. Run:
kubectl apply -f FILENAME -n DEVELOPMENT-NAMESPACE
where FILENAME
is the name of the configuration file you created in Step 2 above.
For example:
kubectl apply -f testrestore.yaml -n my-namespace
postgresrestores.sql.tanzu.vmware.com/restore-sample created
Verify that a restore has been triggered and track the progress of your restore by running:
kubectl get postgresrestore restore-sample -n DEVELOPMENT-NAMESPACE
For example:
kubectl get postgresrestore restore-sample -n my-namespace
NAME STATUS SOURCE TARGET INSTANCE TIME STARTED TIME COMPLETED
restore-sample Succeeded backup-sample postgres-sample 2021-09-27T23:34:13Z 2021-09-27T23:34:26Z
To understand the output, see the table below:
Column Name | Meaning |
---|---|
STATUS |
Represents the current status of the restore process. Allowed values are:
|
SOURCE |
The name of the backup being restored. |
TARGET INSTANCE |
The name and spec of the Postgres instance that we will be using or creating for restore. |
TIME STARTED |
The time that the restore process started. |
TIME COMPLETED |
The time that the restore process finished. |
VMware Postgres Operator supports point-in-time recovery (PITR); in-place, or to a new instance. The VMware Postgres Operator PITR uses the Postgres write-ahead-log (WAL) files and a continuous WAL archive. For more details on the Postgres PITR, see Timelines in the Open Source Postgres documentation.
The most important aspect of a point-in-time recovery is finding a valid time to restore to, that can avoid getting the cluster into an invalid state. The restore time should meet the following criteria:
The source PostgreSQL instance must have a backup that completed before the restore time.
There must have been transactions on the PostgreSQL instance after the selected time.
A WAL rotation must have been performed after the selected recovery time. To verify the WAL rotation, examine the logs for all the successful archive operations using the following command:
kubectl logs --tail=-1 -l postgres-instance=my-postgres-source,type=data -c instance-logging | grep "archive-push command end: completed successfully"
2021-09-27 17:08:00.411 P00 INFO: archive-push command end: completed successfully (2081ms)
Select a time that you will like to restore the PostgreSQL instance to, for example September 26 2021 at 2:30pm EST
.
Convert the restore time to UTC and in the following format: YYYY-MM-DDTHH:mm:ssZ
("2021-09-26T18:30:00Z").
[Optional] Set spec.pitr.bestEffort
to true
if you have selected a potentially inaccurate time. This instructs the Operator to restore up to the latest WAL record, in-case Postgres is unable to reach the configured timestamp.
Get the stanzaName
of the source PostgreSQL instance.
If the PostgreSQL instance that performed the backups has not been deleted, use the following command:
```
kubectl get postgres my-postgres-source -n <NAMESPACE> -o jsonpath={.status.stanzaName}
```
```
default-my-postgres-source-2209234d-8585-427b-bb00-7f9f3336729d
```
Else, use the following command to list the backup that belongs to deleted PostgreSQL instance:
```
kubectl get postgresbackups -n <NAMESPACE> -l postgres-instance=my-postgres-source -o jsonpath='{.items[0].status.stanzaName}'
```
```
default-my-postgres-source-2209234d-8585-427b-bb00-7f9f3336729d
```
Get the backup location name being used by the source instance:
If the PostgresQL instance that performed the backups has not been deleted, use the following command:
```
kubectl get postgres my-postgres-source -n <NAMESPACE> -o jsonpath={.spec.backupLocation.name}
```
```
backuplocation-sample
```
Else, use the following command to list the backup that belongs to deleted PostgreSQL instance:
```
kubectl get postgresbackups -n <NAMESPACE> -l postgres-instance=my-postgres-source -o jsonpath='{.items[0].status.backupLocation}'
```
```
backuplocation-sample
```
Using the correctly-formatted recovery time, stanza name, and backup location name, and then create a PostgresRestore CR, and save it in the pitr-restore.yaml
as follows:
apiVersion: sql.tanzu.vmware.com/v1
kind: PostgresRestore
metadata:
name: pitr-restore
spec:
targetInstance:
name: <name-of-target-to-be-created>
spec:
postgresVersion:
name: <postgres-version-of-backup>
pitr:
type: time
timestamp: "2021-09-26T18:30:00Z"
sourceBackupLocation:
name: backuplocation-sample
stanzaName: stanza-name-of-the-source-instance
Apply the backup CR:
kubectl apply -f pitr-restore.yaml
which starts the restore process.
Validate that the restore succeeded by using the following command:
kubectl get postgresrestore <name-of-restore> -o jsonpath={.status.phase}
which should return a value for status.phase
:
Succeeded
or check the STATUS column in the following command:
kubectl get postgresrestore <name-of-restore>
with an output similar to:
NAME STATUS SOURCE TARGET INSTANCE TIME STARTED TIME COMPLETED
pitr-restore Succeeded backuplocation-sample postgres-sample 2023-05-12T14:31:35Z 2023-05-12T14:33:26Z
VMware Postgres Operator (from 2.2.0 and later) supports cloning an existing database instance to a new database instance. The clone functionality relies on backups of databases, and continuous WAL archiving.
The cloning requires the following:
The procedure assumes you are cloning an existing instance.
Make sure you have created at least one backup of the source instance.
Get the stanza of the source instance:
kubectl get postgres pg-source -n <NAMESPACE> -o jsonpath={.status.stanzaName}
default-pg-source-225632d-8585-427b-2588-7f9f3336729d
Get the backup location of the source instance
kubectl get postgres pg-source -n <NAMESPACE> -o jsonpath={.spec.backupLocation.name}
backuplocation-sample
Create a new PostgresRestore Custom Resource (CR) and save it in a file called clone-restore.yaml
:
apiVersion: sql.tanzu.vmware.com/v1
kind: PostgresRestore
metadata:
name: clone-restore
spec:
pitr:
type: latest
sourceBackupLocation:
name: backuplocation-sample
stanzaName: <the-stanza-of-the-source>
targetInstance:
name: <name-of-target-to-be-created>
spec:
postgresVersion:
name: <postgres-version-of-backup>
Note: The type
of the pitr
field is set to latest
to indicate cloning. Set the targetInstance.name
field to the name of the instance created in the previous step and set the pitr.sourceBackupLocation.name
to the name of the backup location that contains the stanza.
Apply the CR:
kubectl apply -f clone-restore.yaml
which starts the clone process.
Validate that the clone has succeeded by using the following command:
kubectl get postgresrestore clone-restore -o jsonpath={.status.phase}
which should return a value for status.phase
:
Succeeded
or check the STATUS column in the following command:
kubectl get postgresrestore clone-restore
with an output similar to:
NAME STATUS SOURCE TARGET INSTANCE TIME STARTED TIME COMPLETED
clone-restore Succeeded backuplocation-sample pg-target 2023-06-08T10:43:33Z 2023-06-08T10:44:15Z
VMware Postgres Operator supports point-in-time recovery (PITR) by specifying Log Sequence Number (LSN) of the write-ahead log location for the recovery process.
To perform PITR by specifying LSN, you must specify a proper LSN value of the write-ahead log file that will avoid getting the cluster into an invalid state. The restore process should meet the following criteria:
The source PostgreSQL instance must have a backup that completed before the selected LSN value.
Additional WAL entries must be available after the selected LSN value. To verify that, examine the logs for all the successful archive operations using the following command:
kubectl logs --tail=-1 -l postgres-instance=my-postgres-source,type=data -c instance-logging | grep "archive-push command end: completed successfully"
2021-09-27 17:08:00.411 P00 INFO: archive-push command end: completed successfully (2081ms)
Obtain a backup location name for PostgresRestore CR.
kubectl get postgres <source-instance> -n <NAMESPACE> -o jsonpath={.spec.backupLocation.name}
Get the stanza of the source instance for PostgresRestore CR:
kubectl get postgres <source-instance> -n <NAMESPACE> -o jsonpath={.status.stanzaName}
Choose the target LSN for the PITR process.
Create a new PostgresRestore CR with your configuration and save it in a file called lsn-based-restore.yaml
:
apiVersion: sql.tanzu.vmware.com/v1
kind: PostgresRestore
metadata:
name: lsn-based-restore
namespace: <your-namespace>
spec:
pitr:
type: lsn
target: <the-value-of-the-lsn>
sourceBackupLocation:
name: <postgres-backup-location>
stanzaName: <the-stanza-of-the-source>
targetInstance:
name: <name-of-target-to-be-created>
spec:
postgresVersion:
name: <postgres-version-of-backup>
Apply the CR:
kubectl apply -f lsn-based-restore.yaml
Validate that the restore process succeeded by using the following command:
kubectl get postgresrestore lsn-based-restore -o jsonpath={.status.phase}
that returns a value for status.phase
:
Succeeded
or check the STATUS column with the following command:
kubectl get postgresrestore lsn-based-restore
with an output similar to:
NAME STATUS SOURCE TARGET INSTANCE TIME STARTED TIME COMPLETED
lsn-based-restore Succeeded pg-source pg-target 2023-06-08T10:43:33Z 2023-06-08T10:44:15Z
VMware Postgres Operator (from 2.2.0 and later) supports PITR. It uses the Postgres write-ahead-log (WAL) files and a continuous WAL archive.
The most important aspect of a point-in-time recovery with transaction ID is to specify a proper value, that will avoid getting the cluster into an invalid state. The restore process must meet the following criteria:
The source PostgreSQL instance must have a backup that completes before the chosen transaction ID value.
If the selected transaction ID is prior to the latest full backup, specify a full back up restore label, which happened before the chosen transaction ID, in the set field. Otherwise, the restore process starts from the latest available backup.
Additional transaction entries must be available after the selected value. To verify that, examine the logs for all the successful archive operations using the following command:
kubectl logs --tail=-1 -l postgres-instance=my-postgres-source,type=data -c instance-logging | grep "archive-push command end: completed successfully"
2021-09-27 17:08:00.411 P00 INFO: archive-push command end: completed successfully (2081ms)
Obtain a backup location name for the PostgresRestore CR.
kubectl get postgres <source-instance> -n <NAMESPACE> -o jsonpath={.spec.backupLocation.name}
Get the stanza of the source instance for the PostgresRestore CR:
kubectl get postgres <source-instance> -n <NAMESPACE> -o jsonpath={.status.stanzaName}
Choose the target transaction ID for the PITR process.
In case the chosen transaction ID appears before the latest full backup, the set field must be populated with a full backup restore label that happened before the transaction.
kubectl get postgresbackup <backup-name> -n <NAMESPACE> -o jsonpath={.status.restoreLabel}
Create a new PostgresRestore CR and save it in a file called transaction-based-restore.yaml
:
apiVersion: sql.tanzu.vmware.com/v1
kind: PostgresRestore
metadata:
name: transaction-based-restore
namespace: <your-namespace>
spec:
pitr:
type: transaction
target: <the-value-of-the-transaction-id>
sourceBackupLocation:
name: <postgres-backuplocation>
stanzaName: <the-stanza-of-the-source>
# set: <backup-restore-label>
targetInstance:
name: <name-of-target-to-be-created>
spec:
postgresVersion:
name: <postgres-version-of-backup>
Apply the CR:
kubectl apply -f transaction-based-restore.yaml
Validate that the clone has succeeded using the following command:
kubectl get postgresrestore transaction-based-restore -o jsonpath={.status.phase}
that returns a value for status.phase
:
Succeeded
or check the STATUS column with the following command:
kubectl get postgresrestore transaction-based-restore
with an output similar to:
NAME STATUS SOURCE TARGET INSTANCE TIME STARTED TIME COMPLETED
transaction-based-restore Succeeded pg-source pg-target 2023-06-08T10:43:33Z 2023-06-08T10:44:15Z
Select the backups you need to restore:
kubectl get postgresbackup -n <namespace>
NAME STATUS SOURCE INSTANCE SOURCE NAMESPACE TYPE TIME STARTED TIME COMPLETED
backup-sample Succeeded postgres-sample full 2022-04-15T15:06:35Z 2022-04-15T15:07:10Z
Use kubectl edit-status to edit the status attribute of the selected PostgresBackup object to contain the following information:
kubectl edit-status postgresbackup backup-sample -n <namespace>
Attribute | Command to get the value |
---|---|
backupLocation |
|
dbName |
kubectl get postgres postgres-sample -o jsonpath={.spec.pgConfig.dbname} |
stanzaName |
kubectl exec -t pod/my-postgres-0 -c pg-container -- bash -c 'echo $OLD_BACKUP_STANZA_NAME' |
Confirm that the status fields are correctly populated:
kubectl describe postgresbackup postgres-sample -n <namespace>
Status:
Status:
Backup Location: encrypted-backup-location
Db Name: postgres-sample
Phase: Succeeded
Restore Label: 20220415-150641F
Stanza Name: default-my-postgres
Time Completed: 2022-04-15T15:07:10Z
Time Started: 2022-04-15T15:06:35Z
Once you have updated the backup you need to restore, and then edit the restore.yaml
field to reflect the said backup and the instance you need to restore to:
apiVersion: sql.tanzu.vmware.com/v1
kind: PostgresRestore
metadata:
name: restore-sample
spec:
sourceBackup:
name: backup-sample
targetInstance:
name: postgres-sample
Execute the kubectl apply
command, specifying the manifest file you edited. For example:
kubectl apply -f ./restore.yaml --wait=false
Validate that the restore has succeeded by checking the status of the PostgresRestore CR:
kubectl get postgresrestore.sql.tanzu.vmware.com/restore-sample -n <namespace>
that shows an output similar to:
NAME STATUS SOURCE TARGET INSTANCE TIME STARTED TIME COMPLETED
restore-sample Succeeded backup-sample postgres-sample 2022-04-15T20:44:50Z 2022-04-15T20:45:31Z
Validate that the target Postgres instance is in the running state using the command:
kubectl get postgres.sql.tanzu.vmware.com/postgres-sample
that shows an output similar to:
NAME STATUS BACKUP LOCATION AGE
postgres-sample Running backuplocation-sample 43h
You can also validate that the restore was successful by accessing the target instance, and checking if you can see the desired data.
To troubleshoot any issues, review the resource status, and read any messages associated with the resource events. Monitor the STATUS
column of any Postgres custom resource using kubectl get postgresbackup
, to confirm if status is Failed
, or is stuck in Pending
, Scheduled
, or Running
. Then try to investigate:
Check the status.message
field in PostgresBackupSchedule CR to understand any issues with PostgresBackupSchedule spec, backups scheduling, invalid cron schedule syntax, or errors like the backuplocation.name
not configured in the sourceInstance
.
For example:
kubectl get postgresbackupschedule backupschedule-sample -o jsonpath={.status.message}
could return an output similar to:
Instance my-postgres-1 does not exist in the default namespace
if the user inserted the wrong Postgres instance name, my-postgres-1
instead of postgres-sample
, in PostgresBackupSchedule's spec.backupTemplate.spec.sourceInstance.name
.
If the backup was successfully scheduled but the backup itself failed, then check the remaining troubleshooting information.
Check the STATUS
column in the output of kubectl get postgresbackup
command to view if a backup has failed:
kubectl get postgresbackup
NAME STATUS SOURCE INSTANCE SOURCE NAMESPACE TYPE TIME STARTED TIME COMPLETED
backup-sample Failed postgres-sample my-namespace full 2021-08-31T14:29:14Z 2021-08-31T14:29:14Z
Diagnose the issue by inspecting the status conditions on the postgresbackup
resource, using kubectl describe
, and check the Status
section.
The following are a couple of failure scenarios and their corresponding resolutions:
Error:
kubectl describe postgresbackup backup-sample
Spec:
Expire: false
Source Instance:
Name: postgres-sample
Type: full
Status:
Conditions:
Last Transition Time: 2023-08-23T19:53:38Z
Message: The target Postgres instance does not have a backuplocation specified
Reason: NoBackupLocationSpecified
Status: False
Type: BackupComplete
Phase: Failed
Time Completed: 2023-08-23T19:53:38Z
Time Started: 2023-08-23T19:53:38Z
Resolution:
In this example, the backup-sample
expects a backuplocation to be attached to the target Postgres instance. Fix this problem by creating and attaching a PostgresBackupLocation CR to the Postgres instance.
Error:
kubectl describe postgresbackup backup-sample
Spec:
Source Instance:
Name: postgres-sample
Type: full
Status:
Phase: Failed
Time Completed: 2022-02-25T19:37:07Z
Time Started: 2022-02-25T19:37:07Z
Events:
Type Reason Age From Message
---- ------ ---- ---- -------
Warning Failed 20s postgres-backup-controller WARN: environment contains invalid option 'config-version'ERROR: [095]: unable to load info file '/my-bucket-path/archive/default-postgres-sample/archive.info' or '/my-bucket-path/archive/default-postgres-sample/archive.info.copy': CryptoError: unable to verify certificate presented by 'minio.minio.svc.cluster.local:9000': [20] unable to get local issuer certificate HINT: is or was the repo encrypted? CryptoError: unable to verify certificate presented by 'minio.minio.svc.cluster.local:9000': [20] unable to get local issuer certificate HINT: is or was the repo encrypted? HINT: archive.info cannot be opened but is required to push/get WAL segments. HINT: is archive_command configured correctly in postgresql.conf? HINT: has a stanza-create been performed? HINT: use --no-archive-check to disable archive checks during backup if you have an alternate archiving scheme.
Resolution:
In this example, the PostgresBackupLocation associated with the source instance was configured with enableSSL: true
but the S3 server TLS is not properly configured (for e.g. it might be using a self-signed certificate). To resolve this issue, set the S3 server TLS appropriately. If this is a testing/demo scenario, you can set enableSSL
to false
in the PostgresBackupLocation, wait for the instance to restart, and then create a PostgresBackup again.
In this example, the kubectl get
command provides an output of a Failed
status:
kubectl get postgresrestore
NAME STATUS SOURCE TARGET INSTANCE TIME STARTED TIME COMPLETED
restore-test Failed sample-source-backup postgres-sample 2021-09-29T19:00:26Z 2021-09-29T19:00:26Z
Diagnose the issue by inspecting the status message on the resource. For example:
kubectl describe postgresrestore restore-test
Spec:
Source Backup:
Name: sample-source-backup
Target Instance:
Name: my-postgres
Status:
Message: Backup sample-source-backup does not exist in namespace default
Phase: Failed
Time Completed: 2022-02-25T19:27:27Z
Time Started: 2022-02-25T19:27:27Z
Events:
Type Reason Age From Message
---- ------ ---- ---- -------
Warning ReferencedBackupDoesNotExist 2m56s postgres-restore-controller PostgresBackup.sql.tanzu.vmware.com "sample-source-backup" not found
In the example above, the restore failed because the backup specified in the sourceInstance field does not exist.