A way to get Daily PostgreSQL Backups from Docker Swarm
May 15, 2021
How to make backups from PostgreSQL running in Docker Swarm?
Running Docker container always has a unique container id. Docker Swarm (actually Docker) runs services with dynamic IDs.
There is a Docker service name that has a fixed name. We can use this name to get a service ID.
You can list all services with the next command
docker service ls
I have the service name swarm_postgres. Actually we need to get a task (container) ID running PostgreSQL service. We can do this with the next command:
docker service ps -f "name=swarm_postgres" swarm_postgres -q --no-trunc | head -n1
This command would return you Docker Swarm task (container) ID from postgres service. For example: m4q72hmem43talp8ndxn6a37r
So we know container ID and we can make PostgreSQL backup using native pg_dump tool
docker exec -t swarm_postgres.1.m4q72hmem43talp8ndxn6a37r pg_dump -U dbusername dbname > dump_dbname.sql
As a result of this command you would get dump_dbname.sql file which contains SQL dump
All these actions can be done with one bash script. Let's call it postgres-backup.sh
#!/bin/bash #configuration settings POSTGRES_USER=dbusername POSTGRES_DB=dbname CURRENT_MONTH=$(date +%Y-%m) CURRENT_DATE=$(date +%Y-%m-%d) CURRENT_DATETIME=$(date +%d-%b-%Y_%H_%M_%Z) BACKUPS_PATH=/backups DOCKER_SWARM_SERVICE_NAME=swarm_postgres #################################### #backup PostgreSQL database BACKUP_FOLDER=$BACKUPS_PATH/$CURRENT_MONTH/$CURRENT_DATE if [ ! -d "$BACKUP_FOLDER" ]; then mkdir -p "$BACKUP_FOLDER" fi echo 'Creating PostgreSQL backups...' cd "$BACKUP_FOLDER" if [ -f 'dump_'"$POSTGRES_DB"'.sql' ]; then rm 'dump_'"$POSTGRES_DB"'.sql' fi db_backup_filename=$POSTGRES_DB'_'$CURRENT_DATETIME'.tar.gz' postgres_container_id=$(docker service ps -f "name=$DOCKER_SWARM_SERVICE_NAME" $DOCKER_SWARM_SERVICE_NAME -q --no-trunc | head -n1) docker exec -t symfony-blog_postgres.1."$postgres_container_id" pg_dump -U $POSTGRES_USER $POSTGRES_DB > 'dump_'"$POSTGRES_DB"'.sql' tar -cf - 'dump_'"$POSTGRES_DB"'.sql' | gzip -9 > "$db_backup_filename" rm 'dump_'"$POSTGRES_DB"'.sql' cd "$BACKUP_FOLDER" md5sum * > MD5SUMS echo 'Done.'
There are some extra actions. Scripts creates tar.gz archive, creates MD5SUMS file which contain md5 sum from archive.
You can add this command to cron and run it each day. Just add a line to your /etc/crontab
0 8 * * * root /root/postgres-backup.sh
In this example I run this script each day at 8 a.m.
Removing outdated PostgreSQL backups
After some period of time you will have a lot of archives with PostgreSQL database dumps. We don't need all of them. I suggest storing backups for the last 14 days.
There is a way to check file creation date and remove outdated files. We can do this with bash script. Let's call it postgres-backups-cleaner.sh
#!/bin/bash EXPIRE_DAYS=14 SCAN_DIR=/backups FILES=$(find $SCAN_DIR -type f) for file in $FILES; do timestamp=$(date -r $file +%Y%m%d); echo "Processing $file file.."; date1yrs=$(date -d "$timestamp" +%Y); date1days=$(date -d "$timestamp" +%j); date2yrs=$(date +%Y); date2days=$(date +%j); diffYear=$(expr "$date2yrs" - "$date1yrs"); diffYear2days=$(expr "$diffYear" \* 365); diffDays=$(expr "$date2days" - "$date1days"); DAYS=$(expr "$diffYear2days" + "$diffDays"); if [ "$DAYS" -ge $EXPIRE_DAYS ] then echo "Deleting $file file..."; rm "$file"; fi done
We need to run this script on a daily basis. The same way like we did for daily backups. Cron is a good option to do this. Edit your /etc/crontab file and add an extra line there
0 9 * * * root /root/postgres-backups-cleaner.sh
In this example I run this script each day at 9 a.m.
Coping backups to safe place
Created Backups stored on host machine running Docker Swarm. It would be bad if something happened to the host machine.
I decided to copy backups to my home PC. I have a PC running all time. So that's the cheapest way to store backups in my case. I added cron task to copy backups from host machine to my PC with rsync command
30 21 * * * luxurydab /usr/bin/rsync --progress -zoguhvr --compress-level=9 firstname.lastname@example.org:/backups/ /media/storage/Backups/
You can use any cloud storage like Amazon S3, Google Cloud Storage, DigitalOcean Spaces, etc. Just add extra copy actions to your backup script.