Backing Up and Restoring VMware SQL with Postgres for Kubernetes

This topic describes how to back up and restore VMware Postgres Operator.

Overview

The VMware Postgres Operator allows you to backup instances on-demand, schedule automated backups, restore in-place, and restore from a backup to new Postgres instances.

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.

Prerequisites

Before creating a VMware Postgres Operator backup you need:

the kubectl command line tool installed on your local client, with access permissions to the Kubernetes cluster.
access permissions to a preconfigured storage where the pgdata persistent volume (PV) backups will be stored.
the access credentials that will populate the backup secret
the instance namespace, if the Postgres instance is already created. Use kubectl get namespaces for a list of available namespaces.
(optional) a pre-agreed backup schedule to be used to configure scheduled backups.

Backing Up VMware Postgres Operator

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.

Configure the Backup Location

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: {}
    ---
    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"}

[Optional] Configure Client-side Encryption for Backups

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

Perform an On-Demand Backup

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.

Create Scheduled Backups

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-v2.0.2/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}

Listing Backup Resources

You might want to list existing PostgresBackup resources for various reasons, for example:

To select a backup to restore. For steps to restore a backup, see Restoring VMware Postgres Operator.
To see the last successful backup.
To verify that scheduled backups are running as expected.
To find old backups that need to be cleaned up. For steps to delete backups, see Deleting Old Backups.

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:
- Pending: The backup has been received but not scheduled on a Postgres Pod.
- Running: The backup is being generated and streamed to the external blobstore.
- Succeeded: The backup has completed successfully.
- Failed: The backup has failed to complete. To troubleshoot a failed backup, see Troubleshoot Backup and Restore.
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

Deleting Old Backups

To delete a backup and its artifacts, you can either:

Use retention policies or
Expire backups

Use retention policies

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.

Expire Backups

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.

Restoring VMware Postgres Operator

The VMware Postgres Operator allows you to perform three types of data restore:

Restoring a full backup to a different instance in the same namespace. See Restore to a different instance.
Restoring a backup to a different namespace or cluster. See Restore to a different namespace or cluster.
Restoring from a full backup to an existing instance, overriding existing data. Use only in non-production environments. See Restore In-place to the same instance.

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.

Restore to a different instance

Prerequisites

Ensure the newly created target instance exists in the same namespace as the Postgres instance you're restoring from.

Procedure

Create a new Postgres instance. For details see Deploying a Postgres Instance.
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-v2.0.2/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 a command similar to:

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
```
When restoring to a new instance, ensure that the sourceBackup was NOT performed on the target instance.
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 BACKUP   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: Running: The restore is in progress. RecreatingNodes: The postgres nodes are being restarted as part of the restore workflow. RecreatingPrimary: In case of HA target instance, the primary pod is being restarted WaitForPrimary: Wait for the primary pod to be up and running RecreatingSecondary: In case of HA target instance, the secondary pod is being restarted Finalizing: The restore is nearly complete, waiting for the restart to be done and target instance to be up. Succeeded: The restore has completed successfully. Failed: The restore failed. To troubleshoot, see Troubleshoot Backup and Restore below.
`SOURCE BACKUP`	The name of the backup used for the restore.
`TARGET INSTANCE`	The name of the new Postgres instance where the backup is restored to.
`TIME STARTED`	The time that the restore process started.
`TIME COMPLETED`	The time that the restore process finished.

Restore to a different namespace or cluster

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.

Prerequisites

Ensure that:

You have the name of a VMware Postgres Operator instance (on version 1.7.0 and above), that exists or existed in the source namespace.
You know the backup location details of the given instance in the source namespace.
You have an existing PostgresBackup object in the source namespace. To list any existing PostgresBackup resources, see Listing Backup Resources.
You have a PostgresBackupLocation that represents the bucket where the existing backup artifact is stored.

Procedure

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.

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 a command similar to:
```
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 a command similar to:

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.
On the target namespace, create a postgres instance that you’ll like to restore the backup to:
```
kubectl apply -f postgres.yaml
```

Wait until the instance is up and running:

kubectl wait postgres postgres-sample -n <namespace-target> --for=jsonpath={.status.currentState}=Running

Edit the target namespace instance restore yaml, and edit it to reflect the backup you’ll like to restore, and the 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>
```
Perform the restore by using:
```
kubectl apply -f restore.yaml
```
Validate that the restore succeeded by using a command similar to:
```
kubectl get postgresrestore.sql.tanzu.vmware.com/restore-sample -n <namespace-target>
```
which will show an output similar to:
```
NAME                  STATUS      SOURCE BACKUP                    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 BACKUP 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>
```

Restore in-place

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.

Prerequisites

Before you restore from a backup, you must have:

An existing PostgresBackup in your current namespace. To list the existing PostgresBackup resources, see Listing Backup Resources. You can restore from any kind of backup (full, differential, incremental) provided it follows the pgbackrest guidelines.
A PostgresBackupLocation that represents the bucket where the existing backup artifact is stored. See Configure the Backup Location above.

Procedure

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-v2.0.2/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 BACKUP     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: Running: The restore is in progress. RecreatingNodes: The postgres nodes are being restarted as part of the restore workflow. RecreatingPrimary: In case of HA target instance, the primary pod is being restarted WaitForPrimary: Wait for the primary pod to be up and running RecreatingSecondary: In case of HA target instance, the secondary pod is being restarted Finalizing: The restore is nearly complete, waiting for the restart to be done and target instance to be up. Succeeded: The restore has completed successfully. Failed: The restore failed. To troubleshoot, see Troubleshooting Backup and Restore.
`SOURCE BACKUP`	The name of the backup being restored.
`TARGET INSTANCE`	The name of the source postgres instance to be restored with the backup contents.
`TIME STARTED`	The time that the restore process started.
`TIME COMPLETED`	The time that the restore process finished.

Perform a PIT restore

VMware Postgres Operator (from 1.8.0 and later) supports point-in-time restores (PITR); in-place, or to a new instance. The VMware Postgres Operator PITR restore uses the Postgres write-ahead-log (WAL) files, and a continuous WAL archive. For general details on the Postgres point-in-time restore, see Timelines in the Open Source Postgres documentation.

Restore time criteria

The most important aspect of a point-in-time restore is finding a valid time to restore to, to avoid getting the cluster into an invalid state. The restore time should meet the following criteria:

There must have been transactions on the instance after the selected time. Enable the Postgres track_commit_timestamp to record transaction commit time. For details see track_commit_timestamp in the Postgres documentation.

A wal rotation must have been performed after the selected restore time. To determine the wal rotation, examine the logs for all the successful archive operations using a command similar to:

kubectl logs --tail=-1 -l postgres-instance=postgres-sample,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)

Procedure

Select a time you'll like to restore to, for example September 26 2021 at 2:30pm EST.
Convert the restore time to UTC and to match the following format: YYYY-MM-DDTHH:mm:ssZ ("2021-09-26T18:30:00Z")

Get a list of all the possible backups you might be restoring from:

kubectl get postgresbackups -n NAMESPACE -l postgres-instance=postgres-sample

NAME              STATUS      SOURCE INSTANCE   TYPE   TIME STARTED           TIME COMPLETED
backup-sample-4   Succeeded   postgres-sample   full   2021-09-24T19:40:17Z   2021-09-24T19:40:24Z
backup-sample-5   Succeeded   postgres-sample   full   2021-09-25T16:54:55Z   2021-09-25T16:55:02Z
backup-sample-6   Succeeded   postgres-sample   full   2021-09-24T19:48:41Z   2021-09-24T19:48:48Z
backup-sample-7   Succeeded   postgres-sample   full   2021-09-27T23:04:06Z   2021-09-27T23:04:13Z
backup-sample-8   Succeeded   postgres-sample   full   2021-09-27T23:19:51Z   2021-09-27T23:19:58Z

Select a backup source that has successfully completed (TIME COMPLETED) before the selected restore time (2021-09-26T18:30:00Z). In this case the correct backup is backup-sample-5.

Using the correctly-formatted restore time, create a PostgresRestore CR, and save it in a file called pitr-restore.yaml:

apiVersion: sql.tanzu.vmware.com/v1
kind: PostgresRestore
metadata:
  name: pitr-restore
spec:
  sourceBackup:
    name: backup-sample-5
  targetInstance:
    name: postgres-sample
  pointInTime: "2021-09-26T18:30:00Z"

Apply the backup CR:
```
kubectl apply -f pitr-restore.yaml
```
which starts the restore process.

Validate that the restore succeeded by using a command similar to:

kubectl get postgresbackup <name-of-backup> -o jsonpath={.status.phase}

which should return a value for status.phase:

Succeeded

or check the STATUS column in a command similar to:

kubectl get postgresbackup <name-of-backup>

with an output similar to:

NAME                            STATUS      SOURCE INSTANCE   SOURCE NAMESPACE   TYPE   TIME STARTED           TIME COMPLETED
my-backup-for-postgres-sample   Succeeded   postgres-sample   default            full   2022-06-22T14:04:43Z   2022-06-22T14:05:07Z

Restore Backups taken prior to VMware Postgres Operator 1.7.0

Select which backups you'll like 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`	Find backupLocation attached to the instance kubectl get postgres postgres-sample -o jsonpath={.spec.backupLocation.name} Get the cipher that'll be used for restore kubectl get secrets postgres-sample-pgbackrest-secret -o jsonpath={.data.cipher} \| base64 -d Follow the steps here to create a client-side encrypted backupLocation and provide the value retrieved above as the cipher value
`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've updated the backup you'll like to restore, edit the restore.yaml to reflect the said backup and the instance you'll like 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
```

Validating a Successful Restore

Validate that the restore succeeded by checking the status of the PostgresRestore CR:

kubectl get postgresrestore.sql.tanzu.vmware.com/restore-sample -n <namespace>

which will show an output similar to:

NAME                  STATUS      SOURCE BACKUP      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 has a status of Running using a command like:

kubectl get postgres.sql.tanzu.vmware.com/postgres-sample

which should show 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.

Troubleshooting Backup and Restore

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:

Misconfiguration issues
Problems with the external blobstore
Issues with the Postgres Operator

Backup Schedule Status

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.

FAILED status for PostgresBackup Resource

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 Kubernetes events for the postgresbackup resource, using kubectl describe, and check the Events section.

Note: By default, Kubernetes events are stored in etcd for a limited amount of time, so there may not be any events if the failure occurred several hours ago.

Below are a couple of failure events and their corresponding resolution:

Failed due to missing pgbackrest.conf file

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  22s (x2 over 27s)  postgres-backup-controller  WARN: environment contains invalid option 'config-version'ERROR: [055]: unable to open missing file '/etc/pgbackrest/pgbackrest.conf' for read

Resolution:

In the example above, the backup-sample expected a file called /etc/pgbackrest/pgbackrest.conf to exist. Fix this problem by creating and attaching a PostgresBackupLocation CR to the Postgres instance.

FAILED due to S3 server certificate validation error

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.

FAILED status for PostgresRestore

In this example, the kubectl get command outputs a Failed status:

kubectl get postgresrestore

NAME             STATUS      SOURCE BACKUP          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.