Recipe to snapshot postgres container

Recipes
1

Here’s a script I cooked up after much trial and error that can be run as a cron script on a docker host machine to snapshot a full database dump out of a standard postgres container.

The bits you’d want to change are double-quoted:

#!/bin/bash -e

# load RESTIC_ environment variables
set -a
HOME=/root
. /root/.resticrc
set +a

cd "/srv/myapp-prod"

docker-compose exec -T db \
	bash -c 'pg_dumpall --clean -U$POSTGRES_USER' \
        | gzip --rsyncable \
        | restic backup \
            --host "myapp-prod-db" \
            --stdin --stdin-filename \
            postgres.sql.gz

/root/.resticrc looks like this:

RESTIC_REPOSITORY=/srv/restic
RESTIC_PASSWORD=abcdef1234567890

The call to pg_dumpall is single-quoted and wrapped with bash so that environment variables defined on the container like $POSTGRES_USER can be used.

1 Like
2

If the DB is large, you could add a pipe through gzip --rsyncable before restic backup (and also then change the filename to postgres.sql.gz).

3

I never knew about --rsyncable and have avoided gzipping for exactly the reason it addresses, thanks for sharing!

Also, I just updated the recipe to pass -T when running docker-compose exec to avoid a crash due to a TTY not being available when run under a cron job

1 Like