Postgres Kubernetes backup cron job and restore

As part of my database maintenance I make a daily backup and once a week delete all backups older than 7 days old. I’m using a pvc as storage, that being said you could just as easily mount a nfs share to the pod. This tool will use pgdump_all which will dump absolutely everything.

---
apiVersion: batch/v1
kind: CronJob
metadata:
  name: postgres-backup
  namespace: databases
spec:
  schedule: "@daily"
  concurrencyPolicy: Forbid
  successfulJobsHistoryLimit: 0
  failedJobsHistoryLimit: 1
  jobTemplate:
    spec:
      backoffLimit: 0
      ttlSecondsAfterFinished: 600
      template:
        spec:
          restartPolicy: Never
          containers:
            - name: postgres-backup
              image: postgres:alpine@sha256:884718395ee967fc70374563f3b4d72f0c61f48f81868decb9fa452915fe070e
              command: [/backups/backup_postgres.sh]
              env:
                - name: PGPASSWORD
                  value: PG_HOST_PASSWORD
                - name: POSTGRES_HOST_AUTH_METHOD
                  value: trust # this will allow us to make a connection to another instance
              volumeMounts:
                - name: nfs-backup-vol
                  mountPath: /backups
          volumes: # Using a PVC here, can easily make this a nfs share or storage of choice
            - name: nfs-backup-vol
              persistentVolumeClaim:
                claimName: pg-backups
---
# Deletes all files in the backup folder that are older than 7 days
apiVersion: batch/v1
kind: CronJob
metadata:
  name: postgres-dump-cleanup
  namespace: databases
spec:
  schedule: "@weekly"
  concurrencyPolicy: Forbid
  successfulJobsHistoryLimit: 0
  failedJobsHistoryLimit: 1
  jobTemplate:
    spec:
      backoffLimit: 0
      ttlSecondsAfterFinished: 600
      template:
        spec:
          restartPolicy: Never
          containers:
            - name: postgres-dump-cleanup
              image: busybox@sha256:ba76950ac9eaa407512c9d859cea48114eeff8a6f12ebaa5d32ce79d4a017dd8
              command: ["find", "/backups/sqldumps", "-type", "f", "-mtime", "+7", "-exec", "rm", "-f", "{}", "+"]
              volumeMounts:
                - name: nfs-backup-vol
                  mountPath: /backups
          volumes:
            - name: nfs-backup-vol
              persistentVolumeClaim:
                claimName: pg-backups

The contents of backup_postgres.sh are

#! /bin/sh
pg_dumpall -h pg_host -U pg_user -p 5432 -f /backups/sqldumps/tds-cluster-$(date +'%m-%d-%Y').db

If you needed to restore your db you can reverse the process a little. Instead of running backup_postgres.sh we can run restore_postgres.sh. You can also make this a job that you start manually, run it in a pod interactively or use pgadmin.

psql "postgresql://USER:PASSWORD@HOST:5432/*" -f ./tds-cluster-12-31-2023.db postgres

The postgres at the end is a fictatious db, the db does not need to exist in order for the restore process to start