Application Consistent PostgreSQL HA Backup and Restore

If it hasn't been done already, the bitnami Helm repository needs to be added to your local configuration:

## Add bitnami helm repo
$ helm repo add bitnami https://charts.bitnami.com/bitnami

Install the PostgreSQL HA chart from the bitnami Helm repository:

$ kubectl create namespace postgresql
$ helm install postgresql bitnami/postgresql-ha --namespace postgresql

Use Veeam Kasten to backup and restore the application.

Using Post Restore Hook Blueprint

Note

The provided example in this section serves as a blueprint template for achieving application-consistent PostgreSQL HA backup and restore workflows. Please note that these examples may need to be modified for specific production environments and setups. As a result, it is highly recommended to carefully review and modify the blueprints as needed before deploying them for production use.

If the PostgreSQL HA application is being restored into a different namespace, the secondary instance pod postgresql-postgresql-ha-postgresql-1 will go into CrashLoopBackOff since the connection info for the primary/secondary nodes in the repmgr database points to the source namespace. The following additional steps are needed to solve this issue:

1. Create a snapshot for the PostgreSQL HA application using Veeam Kasten.
2. Create a blueprint in the kasten-io namespace. This blueprint will operate as a post restore hook

Next create a file postgresql-hooks.yaml with the following contents

apiVersion: cr.kanister.io/v1alpha1
kind: Blueprint
metadata:
  name: postgresql-hooks
actions:
  postrestorehook:
    phases:
    - func: Wait
      name: WaitForPodReady
      args:
       timeout: 240s
       conditions:
          anyOf:
          - condition: '{{ if "$.status.containerStatuses[].ready"}}true{{ else }}false{{ end }}'
            objectReference:
              apiVersion: v1
              resource: pods
              name: '{{ .StatefulSet.Name }}-0'
              namespace: '{{ .StatefulSet.Namespace }}'
    - func: KubeTask
      name: PgUpdate
      objects:
        pgSecret:
          kind: Secret
          name: '{{ .StatefulSet.Name }}'
          namespace: '{{ .StatefulSet.Namespace }}'
      args:
        namespace: '{{ .StatefulSet.Namespace }}'
        image: '{{if index .Options "psqlImage" }} {{- .Options.psqlImage -}} {{else -}} bitnami/postgresql-repmgr {{- end}}'
        command:
        - bash
        - -o
        - errexit
        - -o
        - pipefail
        - -c
        - |
          export PGHOST='{{ .StatefulSet.Name }}.{{ .StatefulSet.Namespace }}.svc.cluster.local'
          export PGPASSWORD='{{ index .Phases.PgUpdate.Secrets.pgSecret.Data "password" | toString }}'
          export PGREPL='{{ index .Phases.PgUpdate.Secrets.pgSecret.Data "repmgr-password" | toString }}'
          postgresMaster=$(psql -U postgres -h $PGHOST -d repmgr  -t -c "select node_name from repmgr.nodes where type='primary'")
          postgresStandby=$(psql -U postgres -h $PGHOST -d repmgr  -t -c "select node_name from repmgr.nodes where type='standby'")
          primaryHost=''${postgresMaster}'.{{ .StatefulSet.Name }}-headless.{{ .StatefulSet.Namespace }}.svc.cluster.local'
          secondaryHost=''${postgresStandby}'.{{ .StatefulSet.Name }}-headless.{{ .StatefulSet.Namespace }}.svc.cluster.local'
          export conn_info_primary='user=repmgr password='${PGREPL}' host='${primaryHost}' dbname=repmgr port=5432 connect_timeout=5'
          export conn_info_standby='user=repmgr password='${PGREPL}' host='${secondaryHost}' dbname=repmgr port=5432 connect_timeout=5'
          psql -U postgres -h $PGHOST -d repmgr -c "update repmgr.nodes set conninfo='${conn_info_primary}' where type='primary'"
          psql -U postgres -h $PGHOST -d repmgr -c "update repmgr.nodes set conninfo='${conn_info_standby}' where type='standby'"

And then apply the file using:

$ kubectl --namespace=kasten-io create -f postgresql-hooks.yaml

3. During restore, create a different namespace - postgresql-2 and select it as the target namespace

4. Under the Pre and Post-Restore Action Hooks section, select the check box After - On Success and select the blueprint created in step 2 as the action hook with action as postRestoreHook. Select the checkbox Don't wait for workloads to be ready