another script to assist back and restore between local postgres and remote

pg.sh

#!/bin/sh

SELF=${0##*/}

setPgEnvVars() {
  # consult `man test` for conditionals
  if [ -z "$1" ]; then
    usage
    die "Missing aws env argument, like dev or live"
  fi

  # Don't change the PG* names, they're used by pg_dump and pg_restore
  # The export is needed for pg_* to inherit the vars
  case "$1" in
    local)
      export PGHOST="localhost"
      export PGPORT='5432'
      export PGDATABASE="somedb"
      export PGUSER="postgres"
      AWS_ENV=local
    ;;
    dev)
      export PGHOST="dev-app.example.com"
      export PGPORT='5432'
      export PGDATABASE="dev-app-db"
      export PGUSER="dev-app-user"
      AWS_ENV=dev
    ;;
    danger|superuser)
      PGUSER=postgres
      PGHOST=nonlive-db.example.com
    ;;
    *)
      die "Unknown aws env $1"
    ;;
  esac
}

die() {
  log "$@"
  exit 1
}

log() {
  # logs to stderr
  format_statement="$1"
  shift
  printf "🥑 %s %s: ${format_statement}\n" "$SELF" "$(date +%T)" "$@" >&2
}

dieIfNonZeroStatus() {
  estatus=$1 # some shells reserve $status
  if [ $estatus -ne 0 ]; then
    log "$*"
    exit $estatus
  fi
}

tryCommand() {
  command -v $1 >/dev/null 2>&1
}

usage() {
  cat <<EOF
usage: $SELF <command> <arg>

Commands:

create a backup
   $SELF backup dev         Back up the dev env's database
   $SELF backup live        Back up the live env's database

restore a backup
   $SELF restore <path>     Restore a specific backup

EOF
}

backup() {
  # don't print anything, stdout must be the backup bytes
  # --no-password means never prompt for a password, fail
  pg_dump \
      --create \
      --no-password \
      --format=custom \
      --dbname="${PGDATABASE}"
}

backupHandler() {
  setPgEnvVars "$1"
  shift
  BACKUP_FILE="someapp-${AWS_ENV}-$(date +%Y%m%d_%H%M%S).dump"
  printUsefulCommands
  log "Will backup.."
  tryCommand pv
  if [ $? -ne 0 ]; then # check status code of tryCommand
    log "For a progress bar run: brew install pv"
    backup > "${BACKUP_FILE}"
    dieIfNonZeroStatus $? "Backup failed."
  else
    backup | pv > "${BACKUP_FILE}"
    dieIfNonZeroStatus $? "Backup failed."
  fi
  log "Backup complete!"
}


restoreHandler() {

  BACKUP_FILE="${1:?'Must specify a backup file.'}"
  shift
  [ -r "$BACKUP_FILE" ] || die "No file found at backup file path %s\n" $BACKUP_FILE

  awsenv="$1"
  shift

  if [ -z "$awsenv" ]; then
    log "Will restore to local"
    setPgEnvVars "local"
  else
    if [ "$awsenv" = "live" ]; then
      log "Noping out of restoring against live. Edit script to override."
      exit 1
    fi

    log "Will restore to $awsenv"
    setPgEnvVars "$awsenv"
  fi

  shift

  printUsefulCommands

  # log "Will force close connections to database.."
  # force close any existing connections
  # psql --command="select pg_terminate_backend(pid) from pg_stat_activity where datname='${PGDATABASE}' and pid <> pg_backend_pid();";
  # dieIfNonZeroStatus $? "Failed to force close existing connections to database ${PGDATABASE}"

  if [ -n "$awsenv" ]; then

    # capture who should be the owner
    owner=$PGUSER

    # assume superuser credentials
    setPgEnvVars "danger"
    printUsefulCommands
    log "🧨🧨🧨🧨🧨 Assumed super user creds for DROP and CREATE, pausing for a few seconds."
    sleep 1
    log "🧨🧨🧨🧨 This is a destructive operation against a non-live environment!"
    sleep 1
    log "🧨🧨🧨"
    sleep 1
    log "🧨🧨"
    sleep 1
    log "🧨"
    sleep 2
  fi

  log "Will drop.."
  dropdb --no-password --force --if-exists ${PGDATABASE}
  dieIfNonZeroStatus $? "Failed to drop database ${PGDATABASE}"

  log "Will create with owner ${owner:-$PGUSER}.."
  # If the owner var is null, fall back to pguser
  # A one time role grant is necessary: grant $PGDATABASE to $PGSUPERUSER;
  createdb --no-password --owner=${owner:-$PGUSER} ${PGDATABASE}
  # createdb --no-password ${PGDATABASE}
  dieIfNonZeroStatus $? "Failed to create database ${PGDATABASE}"

  if [ -n "$awsenv" ]; then
    # restore regular credentials
    setPgEnvVars "$awsenv"
    log "Resumed regular user creds"
  fi

  log "Will restore \"${BACKUP_FILE}\".."
  pg_restore \
      --verbose \
      --no-password \
      --no-owner \
      --no-acl \
      --dbname="${PGDATABASE}" \
      "${BACKUP_FILE}"
  dieIfNonZeroStatus $? "Restore failed for database ${PGDATABASE} from file \"${BACKUP_FILE}\"."
  log "Restore complete!"
}

printUsefulCommands() {
  text="

host: $PGHOST
port: $PGPORT
  db: $PGDATABASE
user: $PGUSER

~/.pgpass entry: ${PGHOST}:${PGPORT}:${PGDATABASE}:${PGUSER}:CHANGEME_PASSWORD

psql --username="${PGUSER}" --host="${PGHOST}" --port="${PGPORT}"

 List databases: \l
    List tables: \dt
Change database: \c $PGDATABASE
"
  log "$text"
}

if [ $# -eq 0 ]; then
  usage
  exit 1
fi

while [ $# -gt 0 ]; do
  case "$1" in
    backup)
      shift
      backupHandler "$@"
      exit 0
    ;;
    restore)
      shift
      restoreHandler "$@"
      exit 0
    ;;
    help|--help|-h)
      usage
      exit 0
    ;;
    *)
      usage
      die "Unrecognized argument: $1"
    ;;
  esac
done