Application Consistent PostgreSQL Backup

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 chart from the bitnami Helm repository:

$ kubectl create namespace postgresql
$ helm install --namespace postgresql postgres bitnami/postgresql --version 17.2.0

Note

This is an example workflow that has been validated with PostgreSQL chart version 17.2.0. Functions used in the blueprint are available since PostgreSQL version 15 and later. For earlier versions of PostgreSQL, please refer to earlier versions of this doc at https://docs.kasten.io/7.5.8/kanister/postgresql/install_app_cons For different versions of PostgreSQL and other requirements, modify the postgresql-hooks.yaml blueprint below as required.

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

apiVersion: cr.kanister.io/v1alpha1
kind: Blueprint
metadata:
  name: postgresql-hooks
actions:
  backupPrehook:
    phases:
    - name: createBackupPod
      func: KubeOps
      objects:
        pgSecret:
          kind: Secret
          name: '{{ index .Object.metadata.labels "app.kubernetes.io/instance" }}-postgresql'
          namespace: '{{ .StatefulSet.Namespace }}'
      args:
        operation: create
        namespace: '{{ .StatefulSet.Namespace }}'
        spec: |-
          apiVersion: v1
          kind: Pod
          metadata:
            name: postgres-backup-session
          spec:
            restartPolicy: Never
            containers:
              - name: container
                image: bitnami/postgresql:16
                command:
                - bash
                - -o
                - errexit
                - -o
                - pipefail
                - -c
                - |
                  export PGHOST='{{ index .Object.metadata.labels "app.kubernetes.io/instance" }}-postgresql.{{ .StatefulSet.Namespace }}.svc.cluster.local'
                  export PGUSER='postgres'
                  export PGPASSWORD='{{ index .Phases.createBackupPod.Secrets.pgSecret.Data "postgres-password" | toString }}'
                  ## Create file descriptor to send commands to psql
                  mkfifo /tmp/pg_in
                  ## Create "holder" process to keep pg_in open
                  while sleep 1; do :; done >/tmp/pg_in &
                  ## Save "holder" PID to a file to kill it later
                  echo $! > /tmp/holder_pid
                  ## Run psql session reading from pg_in and writing ot pg_out
                  ## Using tee here to keep the pod logs (might need to replace with just `> /tmp/pg_out`)
                  ## TODO: should we track stderr here?
                  cat /tmp/pg_in | psql -U ${PGUSER} | tee /tmp/pg_out

    - func: WaitV2
      name: waitForPodReady
      args:
        timeout: 5m
        conditions:
          anyOf:
          - condition: '{{ $available := false }}{{ range $condition := $.status.conditions }}{{ if and (eq .type "ContainersReady") (eq .status "True") }}{{ $available = true }}{{ end }}{{ end }}{{ $available }}'
            objectReference:
              apiVersion: "v1"
              name: "{{ .Phases.createBackupPod.Output.name }}"
              namespace: '{{ .StatefulSet.Namespace }}'
              resource: "pods"

    - name: startBackup
      func: KubeExec
      args:
        namespace: '{{ .StatefulSet.Namespace }}'
        pod: "{{ .Phases.createBackupPod.Output.name }}"
        command:
        - bash
        - -o
        - errexit
        - -o
        - pipefail
        - -c
        - |
          ## Send pg_backup_start command to psql session
          echo "SELECT pg_backup_start(label => 'kanister_backup', fast => false);" > /tmp/pg_in
          ## Make sure operation completed
          ## TODO: maybe there's a better way to fail/log here?
          grep -q pg_backup_start <(tail -f /tmp/pg_out)

  backupPosthook:
    phases:
    - name: stopBackup
      func: KubeExec
      args:
        namespace: '{{ .StatefulSet.Namespace }}'
        pod: postgres-backup-session
        command:
        - bash
        - -o
        - errexit
        - -o
        - pipefail
        - -c
        - |
          ## Send pg_backup_stop command to psql session
          echo "SELECT * FROM pg_backup_stop(wait_for_archive => true);" > /tmp/pg_in
          ## Make sure operation completed
          ## TODO: maybe there's a better way to fail/log here?
          grep -q "LABEL: kanister_backup" <(tail -f /tmp/pg_out)

    deferPhase:
      func: KubeOps
      args:
        operation: delete
        objectReference:
          apiVersion: v1
          resource: "pods"
          name: postgres-backup-session
          namespace: '{{ .StatefulSet.Namespace }}'

And then apply the file using:

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

Finally add the following annotation to the PostgreSQL StatefulSets to instruct Veeam Kasten to use the above hooks when performing operations on this PostgreSQL instance.

$ kubectl annotate statefulset postgres-postgresql kanister.kasten.io/blueprint='postgresql-hooks' \
     --namespace=postgresql

Note

The PostgreSQL backup example provided above serves as a blueprint template for achieving application-consistent backups. 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.

Finally, use Veeam Kasten to backup and restore the application.