Postgres Kubernetes backup cron job and restore
Dump your entire postgres db as part of your backup procedure
Mon, 25 Mar 2024
As part of my database maintanence I make a daily backup and once a week delete all backups older then 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 then 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-backupsThe 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').dbIf 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 postgresThe postgres at the end is a fictatious db, the db does not need to exist in order for the restore process to start
