readme.md

curl -JO https://gist.githubusercontent.com/sebastian13/c313c055a59342acb9f0a7a3d5d03c09/raw/docker-postgres-dump.sh
curl -JO https://gist.githubusercontent.com/sebastian13/c313c055a59342acb9f0a7a3d5d03c09/raw/docker-postgres-restore.sh
chmod +x docker-postgres-(dump|restore).sh

docker-postgres-dump.sh

#!/bin/bash

set -euo pipefail

# Check package availability
command -v docker compose >/dev/null 2>&1 || { echo "[Error] Please install the Compose plugin"; exit 1; }
command -v gzip >/dev/null 2>&1 || { echo "[Error] Please install gzip"; exit 1; }

# Change to the script's directory & create directory
cd "$(dirname "$(readlink -f "$0")")"
mkdir -p ./pgdumps

# Load database name + username
source .env

# Check if variables were provided
if [ -z ${POSTGRES_DB+x} ]; then echo \$POSTGRES_DB was not provided; exit 1; fi
if [ -z ${POSTGRES_USER+x} ]; then echo \$POSTGRES_USER was not provided; exit 1; fi

# Check if postgres is running
docker compose ps --status running --services | grep -q 'postgres' || { echo "[Error] postgres service not running. Please start manually."; exit 1; }

# Delete old backups
find ./pgdumps/ -name "*.bak.gz" -ctime +7 -exec rm {} +

# Set the filename
dump="$(date +%Y%m%d-%H%M)-$POSTGRES_DB.bak.gz"
tmp_dump="./pgdumps/.${dump}.tmp"

cleanup_tmp() {
    [ -n "${tmp_dump:-}" ] && rm -f "$tmp_dump"
}
trap cleanup_tmp EXIT

gzip_args=()
# BusyBox gzip does not support --rsyncable
if gzip --help 2>&1 | grep -q -- '--rsyncable'; then
    gzip_args+=(--rsyncable)
fi

pg_dump_cmd="$(docker compose exec -T postgres sh -c 'command -v pg_dump' | tr -d '\r')"
[ -n "$pg_dump_cmd" ] || { echo "[Error] pg_dump not found in postgres container"; exit 1; }

if ! docker compose exec -T postgres "$pg_dump_cmd" "$POSTGRES_DB" -U "$POSTGRES_USER" \
      | gzip -c "${gzip_args[@]}" > "$tmp_dump"; then
    echo "[Error] Database dump failed"
    exit 1
fi

gzip -t "$tmp_dump" || { echo "[Error] Backup file is not a valid gzip archive"; exit 1; }

[ -s "$tmp_dump" ] || { echo "[Error] Backup file is empty"; exit 1; }

# Optional safety threshold in bytes; set DUMP_MIN_SIZE_BYTES=0 to disable.
min_size_bytes="${DUMP_MIN_SIZE_BYTES:-256}"
case "$min_size_bytes" in
    ''|*[!0-9]*)
        echo "[Error] DUMP_MIN_SIZE_BYTES must be a non-negative integer"
        exit 1
        ;;
esac

actual_size_bytes="$(wc -c < "$tmp_dump")"
if [ "$actual_size_bytes" -lt "$min_size_bytes" ]; then
    echo "[Error] Backup file is too small (${actual_size_bytes} bytes < ${min_size_bytes} bytes)"
    exit 1
fi

mv "$tmp_dump" "./pgdumps/$dump"

ln -fs "$dump" ./pgdumps/latest.bak.gz

docker-postgres-restore.sh

#!/bin/bash

set -euo pipefail

# Check package availability
command -v docker compose >/dev/null 2>&1 || { echo "[Error] Please install the Compose plugin"; exit 1; }
command -v gzip >/dev/null 2>&1 || { echo "[Error] Please install gzip"; exit 1; }
command -v pv >/dev/null 2>&1 || { echo "[Error] Please install pv"; exit 1; }
command -v awk >/dev/null 2>&1 || { echo "[Error] Please install awk"; exit 1; }

# Change to the script's directory
cd "$(dirname "$(readlink -f "$0")")"

# Load username
source .env

# Check if variables were provided
if [ -z ${POSTGRES_DB+x} ]; then echo \$POSTGRES_DB was not provided; exit 1; fi
if [ -z ${POSTGRES_USER+x} ]; then echo \$POSTGRES_USER was not provided; exit 1; fi

# Start postgres service
docker --log-level=error compose up -d postgres
sleep 5

# Find latest dumps (max 3, unique)
mapfile -t dumps < <(find ./pgdumps -maxdepth 1 -type f -name '*.bak.gz' -print | sort -r | head -n 3)
[ "${#dumps[@]}" -gt 0 ] || { echo "[Error] No dump files found in ./pgdumps"; exit 1; }

# Select dump
echo "Which dump should be used for restoring?"
select result in "${dumps[@]}"
do
	[ -n "${result:-}" ] && break
	echo "Please choose a valid number."
done

# Get the size of the uncompressed sql file
size=$(gzip -l "$result" | awk 'FNR==2{print $2}')

pg_isready_cmd="$(docker compose exec -T postgres sh -c 'command -v pg_isready' | tr -d '\r')"
[ -n "$pg_isready_cmd" ] || { echo "[Error] pg_isready not found in postgres container"; exit 1; }

psql_cmd="$(docker compose exec -T postgres sh -c 'command -v psql' | tr -d '\r')"
[ -n "$psql_cmd" ] || { echo "[Error] psql not found in postgres container"; exit 1; }

# Wait
while ! docker compose exec -T postgres "$pg_isready_cmd" -U "$POSTGRES_USER" -d "$POSTGRES_DB" >/dev/null
do
	sleep 3
	echo "Wait for DB to initialize"
done

# Restore
echo "Starting restore now."
gunzip --stdout "$result" | pv --size "$size" | docker compose exec -T postgres \
		"$psql_cmd" -U "$POSTGRES_USER" "$POSTGRES_DB"