backup-database-cluster.sh

#!/bin/bash
set -eo pipefail

# https://stackoverflow.com/a/8574392/79992
contains_element () {
  local e match="$1"
  shift
  for e; do [[ "$e" == "$match" ]] && return 0; done
  return 1
}

db_user=prescribing
timestamp="$(date +%Y_%m_%d)"
backup_path="/mnt/volume-fra1-02/db_backups"

# Credentials are in /home/hello/.pgpass
databases="$(psql -U "$db_user" --no-psqlrc --quiet --tuples-only --no-align <<< \
  'SELECT datname FROM pg_database WHERE datistemplate = false;')"

blacklist=(prescribing_staging warehouse opentrials e2e_prescribing postgres)

for database in $databases; do
  if ! contains_element "$database" "${blacklist[@]}"; then
   pg_dump -U "$db_user" --create --compress=5 --format=plain "$database" > "$backup_path/$database.$timestamp.sql.gz"
  fi
done
  

pg_dumpall -U "$db_user" --globals-only | gzip > "$backup_path/_globals_.$timestamp.sql.gz"

# -n is "no clobber"
gsutil -q cp -n $backup_path/*.$timestamp.sql.gz "gs://ebmdatalab_db_backups/"