postgres_container_backup

#!/bin/bash

while getopts 'd:h:u:p:n:P:s:i:' flag; do
  case "${flag}" in
    d) DBBACKUPDIR="${OPTARG}" ;;
    h) DBHOST="${OPTARG}" ;;
    u) DBUSER="${OPTARG}" ;;
    p) DBPASSWORD="${OPTARG}" ;;
    n) DBNAME="${OPTARG}" ;;
    P) DBPORT="${OPTARG}" ;;
    s) SSHHOST="${OPTARG}" ;;
    i) SSHKEY="${OPTARG}" ;;
  esac
done

DBPORT="${DBPORT:-5432}"
DBBACKUPDATE=$(date +%Y%m%d-%H%M%S)
SSH_PID=""

if [ -z "$DBBACKUPDIR" ] || [ -z "$DBHOST" ] || [ -z "$DBUSER" ] || [ -z "$DBPASSWORD" ] || [ -z "$DBNAME" ]; then
  echo "postgres_container_backup [OPTIONS...]"
  echo ""
  echo "Backup a PostgreSQL database to a compressed file."
  echo ""
  echo "Flags:"
  echo "  -d    Directory to save backups in"
  echo "  -h    Hostname or IP of the PostgreSQL server"
  echo "  -u    PostgreSQL username"
  echo "  -p    PostgreSQL password"
  echo "  -n    Database name to back up"
  echo "  -P    PostgreSQL port (default: 5432)"
  echo "  -s    SSH host to tunnel through (e.g. user@hostname)"
  echo "  -i    SSH identity file (private key)"
  echo ""
  echo "Example:"
  echo "  postgres_container_backup -d /var/backup/postgres/ -h 10.0.0.5 -u myuser -p mypassword -n mydb"
  echo ""
  echo "Example with SSH tunnel:"
  echo "  postgres_container_backup -d /var/backup/postgres/ -h 127.0.0.1 -u myuser -p mypassword -n mydb -s deploy@10.0.0.1"
  exit 1
fi

if [ ! -d "$DBBACKUPDIR" ] || [ ! -w "$DBBACKUPDIR" ]; then
  echo "$DBBACKUPDIR does not exist or is not writable by $USER"
  exit 2
fi

if ! hash pg_dump 2>/dev/null; then
  echo "pg_dump is required, please install postgresql-client"
  exit 3
fi

if ! hash gzip 2>/dev/null; then
  echo "gzip is required, please install gzip"
  exit 3
fi

if [ -n "$SSHHOST" ] && ! hash ssh 2>/dev/null; then
  echo "ssh is required for tunnelling"
  exit 3
fi

export PGPASSWORD="${DBPASSWORD}"

CONN_HOST="${DBHOST}"
CONN_PORT="${DBPORT}"

if [ -n "$SSHHOST" ]; then
  LOCAL_PORT=$(( RANDOM + 49152 ))
  SSH_KEY_OPT=""
  [ -n "$SSHKEY" ] && SSH_KEY_OPT="-i ${SSHKEY}"

  echo "Opening SSH tunnel to ${SSHHOST} (localhost:${LOCAL_PORT} -> ${DBHOST}:${DBPORT})..."
  ssh -N -o ExitOnForwardFailure=yes -o ConnectTimeout=10 ${SSH_KEY_OPT} \
    -L "${LOCAL_PORT}:${DBHOST}:${DBPORT}" "${SSHHOST}" &
  SSH_PID=$!
  sleep 2

  if ! kill -0 "${SSH_PID}" 2>/dev/null; then
    echo "Failed to establish SSH tunnel to ${SSHHOST}"
    exit 4
  fi

  CONN_HOST="127.0.0.1"
  CONN_PORT="${LOCAL_PORT}"
fi

trap '[ -n "${SSH_PID}" ] && kill "${SSH_PID}" 2>/dev/null' EXIT

cd "${DBBACKUPDIR}"
umask 0027

BACKUP_FILE="${DBNAME}_${DBBACKUPDATE}.dump.gz"

pg_dump -h "${CONN_HOST}" -p "${CONN_PORT}" -U "${DBUSER}" -Fc "${DBNAME}" \
  | gzip -9 > "${BACKUP_FILE}"

if [ ${PIPESTATUS[0]} -eq 0 ] && [ -f "${BACKUP_FILE}" ]; then
  echo "Backup of database ${DBNAME} Ok"
else
  >&2 echo "Backup of database ${DBNAME} Failed"
  rm -f "${BACKUP_FILE}"
  exit 5
fi

md5sum "${BACKUP_FILE}" >> "${DBNAME}_checksums.md5"

exit 0