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 -e

# 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"

docker compose exec -T postgres /usr/bin/pg_dump $POSTGRES_DB -U $POSTGRES_USER \
      | gzip --rsyncable > ./pgdumps/$dump

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

docker-postgres-restore.sh

#!/bin/bash

set -e

# 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
latest1=$(ls -1t ./pgdumps/*.bak.gz | head -1)
latest2=$(ls -1t ./pgdumps/*.bak.gz | head -n2 | tail -n1)
latest3=$(ls -1t ./pgdumps/*.bak.gz | head -n3 | tail -n1)

# Select dump
echo "Which dump should be used for restoring?"
select result in $latest1 $latest2 $latest3
do
    [ $result ] && break
done

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

# Wait
while ! (docker compose exec postgres /usr/bin/pg_isready -U $POSTGRES_USER -d $POSTGRES_DB)
do
	sleep 3
	echo "Wait for DB to initialize"
done

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