backup.sh

#!/usr/bin/env bash

# This script should be called inside a docker container 
# to backup the database.
# $ ./backup.sh [hourly|daily|weekly|monthly|yearly]
# The environment variables for pg_dumpall need to be set.

set -e

# First command line arg. 
TYPE=$1

# Check if exactly one command line argument was given
if [ "$#" -ne 1 ]; then
    echo "ERROR: Illegal number of parameters"
    echo "Usage: $0 hourly|daily|weekly|monthly|yearly"
    exit 1
fi

# Check if command line argument was one of
# hourly daily weekly monthly yearly
case "$TYPE" in
  hourly|daily|weekly|monthly|yearly)
    ;;
  *)
    echo "ERROR: Intervall $TYPE not supported!"
    exit 1
    ;;
esac

# save the name for the backup in variable
NOW=$(date +"backup-%Y-%m-%d_%H_%M_%S")
# backup base folder
BASE_DIR="/cron/backup"
# folder for backup type
# example /cron/backup/weekly
DIR="$BASE_DIR/$TYPE"
# Full file name
FILE="$DIR/$NOW.gz"
# Create directory if not exists
mkdir -p $DIR

echo "Starting $TYPE backup"
# Backup postgres, zip it and save it to file
# Needs environment variables for the database:
# see https://www.postgresql.org/docs/12/app-pg-dumpall.html
pg_dumpall | gzip > $FILE
echo "Backup done:"

# Show some stats for the backup
TEXT="$(stat -c "%n  %s" $FILE) Bytes
Total $TYPE $(du -sh $DIR | cut -f -1)
Total $(du -sh $BASE_DIR | cut -f -1)"

echo $TEXT


# Delete older backups when there are too many
if [ "$TYPE" == "hourly" ]; then
    COUNT=$(find $DIR -type f -mtime +2 | wc -l)
    echo "Deleting $COUNT older backups than two days"
    find $DIR -type f -mtime +2 -exec rm -f {} \;
elif [ "$TYPE" == "daily" ]; then
    COUNT=$(find $DIR -type f -mtime +7 | wc -l)
    echo "Deleting $COUNT older backups than one week"
    find $DIR -type f -mtime +7 -exec rm -f {} \;
elif [ "$TYPE" == "weekly" ]; then
    COUNT=$(find $DIR -type f -mtime +93 | wc -l)
    echo "Deleting $COUNT older backups than three months"
    find $DIR -type f -mtime +93 -exec rm -f {} \;
elif [ "$TYPE" == "monthly" ]; then
    COUNT=$(find $DIR -type f -mtime +730 | wc -l)
    echo "Deleting $COUNT older backups than two years"
    find $DIR -type f -mtime +730 -exec rm -f {} \;
elif [ "$TYPE" == "yearly" ]; then
    echo "Keeping all yearly backups"
fi

prod-data-to-staging.sh.sh

#!/usr/bin/env bash

# This script downloads the newest backup from the production server and
# restores it on the staging server.

: "${PROD:=myapp.example.com}"
: "${STAGING:=staging.example.com}"

# ssh into production server
# run docker one shot container with read only access to the backups
# ls all backups
# ignore all but the last line
# save as variable $BACKUP (the name of the latest backup file)
BACKUP=$(ssh $PROD 'docker run --rm -i -v=volume-name:/volume:ro busybox ls /volume/path-to-backups/hourly' | tail -1)

if [ -z "$BACKUP" ]
then
  echo "No backup found!"
  exit 1
else
  echo "Backup: $BACKUP"
fi

echo "Extracting backup from docker"
# ssh into production server
# run docker one shot container with read only access to the backups
# copy last BACKUP to home dir
ssh $PROD "docker run --rm -i -v=volume-name:/volume:ro -v=\$(pwd):/output busybox cp /volume/path-to-backups/hourly/$BACKUP /output"

echo "Copying to staging"
# Copy backup file from the production server
# to the staging server with SCP
scp -3 $PROD:$BACKUP $STAGING:$BACKUP

echo "Finding postgres container"
# ssh into staging server
# find container with the name containing 'postgres'
CONTAINER=$(ssh $STAGING "docker ps --format "{{.Names}}" | grep postgres")
echo "Container: $CONTAINER"


echo "Copy backup to docker container"
# ssh into staging server
# copy backup file to the postgres docker container 
# in the folder /tmp
ssh $STAGING "docker cp $BACKUP $CONTAINER:/tmp"


# Save the following commands inside a variable 
# so its easier to read

# Disble all connections to the database 
# (this can crash your server, docker can just restart it again)
# Reset db by dropping it
# Recreate db
# Load from backup
# remove temp file
CMD=$(echo $(cat << EOM
      echo "Disabling all connections to database" &&
      psql -U postgres -d mydatabase -c "SELECT pid, pg_terminate_backend(pid), datname FROM pg_stat_activity WHERE datname = current_database() AND pid <> pg_backend_pid();" &&
      echo "Recreating database" &&
      dropdb -U postgres mydatabase &&
      createdb -U postgres mydatabase &&
      echo "Restoring database from backup" &&
      gunzip -c /tmp/$BACKUP | psql -U postgres mydatabase &&
      rm /tmp/$BACKUP
EOM
))

echo "Loading database backup"
# ssh into staging
# run above commands inside the postgres docker container
ssh $STAGING "docker exec $CONTAINER sh -c '$CMD'"