Back up / Restore PostgreSQL database in Kubernetes using native tools

Performing backups and restores of PostgreSQL databases is a little different when the database server is running under Kubernetes.

Of course, you can always use cloud-native tools like AWS EBS snapshots, VMWare Tanzu Velero, etc., but this tutorial will cover backup/restore using PostgreSQL's built-in tools - pg_dump and psql. It's especially handy for when you are trying to migrate databases from a non-Kubernetes environment into Kubernetes.

Back up

Use the kubectl exec utility to run pg_dump in your PostgreSQL server pod:

CODE

kubectl exec -it POD_NAME -- bash -c 'PGPASSWORD=POSTGRES_PASSWORD pg_dump -U POSTGRES_USER POSTGRES_DB_NAME' > backup.sql
# replace POD_NAME, POSTGRES_PASSWORD, POSTGRES_USER, POSTGRES_DB_NAME with appropriate values

It will save the backup in basic sql format to backup.sql.

Some online tutorials will suggest adding -Fc flag to the pg_dump command , which will save the backup in PostgreSQL Custom format rather than a simple .sql file. There are some benefits to this format but it will complicate restoration in Kubernetes. This tutorial only works for restoring .sql files, not dumps in the Custom format.

Restore

Use the kubectl exec utility to run psql in your PostgreSQL server pod:

CODE

cat backup.sql | kubectl exec -it POD_NAME -- bash -c 'PGPASSWORD=POSTGRES_PASSWORD psql -U POSTGRES_USER POSTGRES_DB_NAME' 
# replace POD_NAME, POSTGRES_PASSWORD, POSTGRES_USER, POSTGRES_DB_NAME with appropriate values

You will see a bunch of SQL statements output to your terminal, then you can be sure that things are working 🙂

This does not work for the pg_restore utility, which is the only tool that can take PostgreSQL Custom format dumps! I haven't been able to figure out a way to get pg_restore working in Kubernetes - the command always hangs when piping the dump file to kubectl exec 🤷‍♀️