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:
hostname:port:database:username:password
The default location of the file should be $HOME/.pgpass.
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:
postgres:5432:test_database:postgres_admin:postgres_password
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:
cG9zdGdyZXM6NTQzMjp0ZXN0X2RhdGFiYXNlOnBvc3RncmVzX2FkbWluOnBvc3RncmVzX3Bhc3N3b3JkCg==
This is the value you can paste into your kubernetes secret:
apiVersion: v1
kind: Secret
metadata:
name: pgpass
data:
pgpass: cG9zdGdyZXM6NTQzMjp0ZXN0X2RhdGFiYXNlOnBvc3RncmVzX2FkbWluOnBvc3RncmVzX3Bhc3N3b3JkCg==
TIP
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.