A simple bash script for creating backups of Postgres running inside a Docker container

This site is hosted on a Digitalocean droplet that runs several docker containers. One of them is Postgres, and this single container is storing all kinds of data I use in my personal projects. If you for instance make a reaction or comment to this post, it’s stored in my Postgres database container.

This data is precious to me, and I therefore wish to create backups of it incase something where to happen.

Here is the complete script file:

#!/bin/bash

CONTAINER_ID="ca79147af925"
DB_USER="axel"
BACKUP_DIR="/home/axel/db_dumps/"
BACKUP_FILE=dump_`date +%Y-%m-%d"_"%H_%M_%S`.sql.gz

echo $(date -u) "Deleting backups older than 30 days inside dir $BACKUP_DIR"
find $BACKUP_DIR -type f -name "*.gz" -mtime +30 -delete
echo $(date -u) "Old backups successfully deleted"
echo $(date -u) "Starting backup of DB"
docker exec -t $CONTAINER_ID pg_dumpall -c -U $DB_USER | gzip > $BACKUP_DIR$BACKUP_FILE
echo $(date -u) "DB backup finished"

ca79147af925 is the container ID of the Postgres container. Replace this with your container id. You can find it by running “docker ps” if the container is up.
axel is the user name of the database user.
All my files are inside /home/axel and /home/axel/db_dumps. Replace these paths with your desired paths.

This script initiates by removing backups in ‘/home/axel/db_dumps’ older than 30 days using the ‘find’ command with ‘-delete’ option. We do this to save disk space. We also only delete files ending with .gz.

It then begins a database backup using Docker. The ‘docker exec’ command runs ‘pg_dumpall’ to dump the database content of all databases into standard output. The output is compressed using ‘gzip’ and saved with a timestamped filename in ‘/home/axel/db_dumps’.

Finally, it prints a timestamped message indicating the completion of the database backup process.

Scheduling the backup script to run every day

I use a cron job for this; simply run:

sudo crontab -e

And add this line to your crontab:

00 04 * * * sh /home/axel/backup.sh >> /home/axel/db-backup-log.txt 2>&1

⚠️ Change the paths to where you placed the script, and redirect the output to your desired log file.

This will run the backup script every morning at 4 am. Check this tool if you wish to run it at another time or multiple times a day. The output of the bash script is redirected to a text file that can be viewed for logging. Any errors are also redirected to the file (2>&1).

Getting the backups off the server

Simply backing up your data is not enough, you need to store the backups somewhere safe. I wont be going through it in this post. Let me know in the comment section if you want ideas on how to do this.