Backing Up, Restoring and Cloning 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 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.

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

[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.3.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}

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 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 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 that you're aware of the Postgres version of the backup you're restoring.

Procedure

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.3.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: Running: The restore is in progress. Pending: Remediating the environment to make it conducive for restore WaitForPrimary: Wait for the primary pod to be up and running RecreatingSecondary: In case of HA target instance, the secondary pod is being created 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`	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.

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.

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

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

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.3.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: Running: The restore is in progress. Pending: Remediating the environment to make it conducive for restore WaitForPrimary: Wait for the primary pod to be up and running RecreatingSecondary: In case of HA target instance, the secondary pod is being created 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`	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.

Perform a Point in Time recovery

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.

Recovery time criteria

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)

Procedure

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

Clone an Instance

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.

Prerequisites

The cloning requires the following:

A recent backup of the instance that will be cloned (the source instance). The cloning process does not require the source instance as long as the backup data is present in the stanza.

Procedure

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

Perform a Point in Time Recovery Using Log Sequence Number

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.

LSN criteria

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)

Procedure

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

Perform a Point in Time Recovery Using Transaction ID

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.

Transaction id criteria

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)

Procedure

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

Restore Backups taken prior to VMware Postgres Operator 1.7.0

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

Validating a Successful Restore

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.

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

Failed due to no backup location

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.

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