I was recently looking for a way to backup postgres database that is running on my on-premise Kubernetes cluster. Unfortunately, all the solutions that I have found, required me to create a new git repository, Dockerfile, build pipeline and pulling image to kubernetes cluster. This was too much of a hassle. I wanted something simple and easy that I can run by using official postgres image.
The goal of this post is to run a postgres backup with Kubernetes CronJobs without creating custom docker image.
I am happy to announce that after a few hours of tinkering I have found a solution. Here it is:
Create a secret with postgres admin password. It needs to be in the format that can be read by pg_dump tool. You can find more info about pg_dump password file here: https://www.postgresql.org/docs/9.1/libpq-pgpass.html
If you don’t feel like opening the link above, here is the format of the file:
The default location of the file should be
So for a postgres Depoyment/StatefulSet with database
test_database that is exposed as a service
postgres on port
5432, your file should look something like this:
Kubernetes secrets require data to be base64 encoded, so to create a secret value, invoke the command below in bash (you need to have base64 tool installed as well):
echo "postgres:5432:test_database:postgres_admin:postgres_password" | base64
The output will be:
This is the value you can paste into your kubernetes secret:
apiVersion: v1 kind: Secret metadata: name: pgpass data: pgpass: cG9zdGdyZXM6NTQzMjp0ZXN0X2RhdGFiYXNlOnBvc3RncmVzX2FkbWluOnBvc3RncmVzX3Bhc3N3b3JkCg==
If your postgres password contains special characters, such as exclamation mark or backslash, you need to escape them before you feed them into
base64 command. Best way to test if your password has been encoded correctly is to decode the base64 string and check the password for missing characters.
Now you can inject the password into the cronjob.
apiVersion: batch/v1beta1 kind: CronJob metadata: name: postgres-backup spec: # Backup the database every day at 2AM schedule: "0 2 * * *" jobTemplate: spec: template: spec: containers: - name: postgres-backup image: postgres:12 command: ["/bin/sh"] args: ["-c", 'echo "$PGPASS" > /root/.pgpass && chmod 600 /root/.pgpass && pg_dump -U postgres_admin -h postgres test_database > /var/backups/backup-$(date +"%m-%d-%Y-%H-%M").sql'] env: - name: PGPASS valueFrom: secretKeyRef: name: pgpass key: pgpass volumeMounts: - mountPath: /var/backups name: postgres-storage restartPolicy: Never volumes: - name: postgres-storage hostPath: # Ensure the file directory is created. path: /var/volumes/postgres-backups type: DirectoryOrCreate
Once you apply the cronjob to your cluster (must be in the same namespace as database), it will backup your database every day at 2AM.
The script inside the CronJob might be a bit ugly, but it works! I’ve had a lot of problems when I tried to mount the secret directly as a file. Let me know in case you managed to mount and use succesfully the password file without using the environment variable.