Bash script for daily backups from PostgreSQL Docker containers

I use Docker with Docker Compose for my own apps. I've got a few apps that use PostgreSQL, and a couple of years ago I was looking for a way to backup the databases from those containers in an easy and automated way.

I found a few solutions, but all were way too complicated or inefficient or required more work to restore, or were reliant on the host machine having PostgreSQL installed (which I didn't want because my apps are running different PostgreSQL versions), so I wrote a script myself!

I've been using this bash script to backup the databases from the containers. It's a simple script that runs every day at a specific time (via a simple cron job), and backs up the databases to a specific directory (via pg_dump), without any passwords in the script. It keeps 14 days of backups, compresses them, and cleans up old backups. Oh, and it supports multiple containers!

Here it is:

backup.sh

#!/bin/bash

#
# Backup Postgresql databases into daily files for multiple containers.
#

# Base backup settings
BASE_BACKUP_DIR=/home/www/backups
DAYS_TO_KEEP=14
FILE_SUFFIX=_backup.sql

# App container names
declare -A CONTAINERS=(
    ["amazing-app"]="/home/www/apps/amazing-app"
    ["another-app"]="/home/www/apps/another-app"
)

# Common PostgreSQL settings
POSTGRES_USER=postgres
POSTGRES_CONTAINER_USER=postgres

# Function to backup a single container
backup_container() {
    local container_name=$1
    local container_dir=$2
    local backup_dir="${BASE_BACKUP_DIR}/${container_name}"
    local file=$(date +"%Y%m%d%H%M")${FILE_SUFFIX}
    local output_file="${backup_dir}/${file}"
    local postgres_container="${container_name}-postgresql-1"
    local postgres_db="${container_name}"

    # Create backup directory if it doesn't exist
    mkdir -p "${backup_dir}"

    # Do the database backup (dump)
    cd "${container_dir}" && docker exec -u ${POSTGRES_CONTAINER_USER} ${postgres_container} pg_dump --dbname="${postgres_db}" --username="${POSTGRES_USER}" > "${output_file}"

    # gzip the database dump file
    gzip "${output_file}"

    # Show the result
    echo "${output_file}.gz was created:"
    ls -l "${output_file}.gz"

    # Prune old backups for this container
    find "${backup_dir}" -maxdepth 1 -mtime +${DAYS_TO_KEEP} -name "*${FILE_SUFFIX}.gz" -exec rm -f '{}' ';'
}

for container_name in "${!CONTAINERS[@]}"; do
    echo "Backing up ${container_name}..."
    backup_container "${container_name}" "${CONTAINERS[$container_name]}"
done

echo "Done backing up databases."

## Restore:
# cd ${container_dir} && docker cp /home/www/backups/${postgres_container}/*_backup.sql.gz ${postgres_container}:/tmp/backup.sql.gz
# docker exec -u ${POSTGRES_CONTAINER_USER} -i ${postgres_container} pg_restore -C --dbname="${postgres_db}" --username="${POSTGRES_USER}" /tmp/backup.sql.gz

Obviously there are some things you can improve, like adding support for multiple databases per container, multiple postgresql container names (per container), and multiple postgresql users. I don't need that, so it'd be unnecessary complexity for me.

I run this with a simple cronjob:

# Every day at 3:07am
7 3 * * * /home/www/scripts/backup.sh > /home/www/logs/backup.log 2>&1

Which keeps a nice log of the last daily backup in /home/www/logs/backup.log.

I hope this helps you.

Thank you so much for being here. I really appreciate you!