#!/usr/bin/env bash
#
# postgresql-logical-backup-ubuntu.sh
#
# Logical backup for PostgreSQL on Ubuntu using pg_dump / pg_dumpall.
#
#   - one custom-format dump per database (restorable with pg_restore)
#   - one globals-only dump (roles, tablespaces) via pg_dumpall
#   - integrity check of every dump with pg_restore --list
#   - retention by number of days, applied only after a successful run
#   - a single lock so overlapping cron runs cannot corrupt the set
#
# Usage:
#   sudo -u postgres ./postgresql-logical-backup-ubuntu.sh
#   sudo -u postgres BACKUP_ROOT=/backup/pg RETENTION_DAYS=14 ./postgresql-logical-backup-ubuntu.sh
#   sudo -u postgres DATABASES="app billing" ./postgresql-logical-backup-ubuntu.sh
#
# Environment:
#   BACKUP_ROOT     destination directory              (default /var/backups/postgresql)
#   RETENTION_DAYS  delete backup sets older than N days (default 7)
#   DATABASES       space-separated list               (default: all non-template databases)
#   JOBS            parallel dump workers per database  (default 2)
#   COMPRESS_LEVEL  pg_dump compression 0-9             (default 6)
#   PGHOST/PGPORT/PGUSER  standard libpq variables; use ~/.pgpass for passwords
#
set -Eeuo pipefail

BACKUP_ROOT="${BACKUP_ROOT:-/var/backups/postgresql}"
RETENTION_DAYS="${RETENTION_DAYS:-7}"
JOBS="${JOBS:-2}"
COMPRESS_LEVEL="${COMPRESS_LEVEL:-6}"
LOCK_FILE="${LOCK_FILE:-/var/lock/postgresql-logical-backup.lock}"

log() {
  echo "[$(date --iso-8601=seconds)] $*"
}

fail() {
  echo "ERROR: $*" >&2
  exit 1
}

[[ "$RETENTION_DAYS" =~ ^[0-9]+$ ]] || fail "RETENTION_DAYS must be an integer."
[[ "$JOBS" =~ ^[1-9][0-9]*$ ]] || fail "JOBS must be a positive integer."
[[ "$COMPRESS_LEVEL" =~ ^[0-9]$ ]] || fail "COMPRESS_LEVEL must be 0-9."

for command_name in psql pg_dump pg_dumpall pg_restore flock; do
  command -v "$command_name" >/dev/null || fail "Missing command: $command_name"
done

# Serialize runs: a second cron firing while the first is still dumping would
# write into the same set and break the "complete or absent" guarantee below.
exec 9>"$LOCK_FILE" || fail "Cannot open lock file $LOCK_FILE"
flock -n 9 || fail "Another backup run is already in progress."

# Fail fast if the server is unreachable, before creating any directory.
SERVER_VERSION="$(psql -XAtqc 'SHOW server_version' postgres)" ||
  fail "Cannot connect to PostgreSQL. Check PGHOST/PGPORT/PGUSER and ~/.pgpass."

if [[ -z "${DATABASES:-}" ]]; then
  # datallowconn excludes template0, which cannot be dumped.
  mapfile -t DATABASE_LIST < <(psql -XAtqc \
    "SELECT datname FROM pg_database
      WHERE datallowconn AND NOT datistemplate
      ORDER BY datname" postgres)
else
  read -r -a DATABASE_LIST <<< "$DATABASES"
fi

(( ${#DATABASE_LIST[@]} > 0 )) || fail "No databases to back up."

TIMESTAMP="$(date +%Y%m%dT%H%M%S)"
# Dump into a staging directory and rename only on success, so a partial set
# is never mistaken for a valid backup by a restore or by the retention pass.
STAGING_DIR="${BACKUP_ROOT}/.in-progress-${TIMESTAMP}"
FINAL_DIR="${BACKUP_ROOT}/${TIMESTAMP}"

install -d -m 700 "$BACKUP_ROOT"
install -d -m 700 "$STAGING_DIR"

cleanup_staging() {
  local exit_code=$?
  if (( exit_code != 0 )) && [[ -d "$STAGING_DIR" ]]; then
    log "Run failed; removing incomplete set $STAGING_DIR"
    rm -rf -- "$STAGING_DIR"
  fi
  exit "$exit_code"
}
trap cleanup_staging EXIT

log "PostgreSQL $SERVER_VERSION"
log "Databases: ${DATABASE_LIST[*]}"
log "Destination: $FINAL_DIR"

# Roles, tablespace definitions and other cluster-wide objects are NOT inside a
# per-database dump. Without this file, a restore lands with no owners or grants.
log "Dumping globals (roles, tablespaces)"
pg_dumpall --globals-only --no-role-passwords \
  --file="${STAGING_DIR}/globals.sql"

for database in "${DATABASE_LIST[@]}"; do
  target="${STAGING_DIR}/${database}.dump"
  log "Dumping database: $database"
  # --jobs applies to directory format only; custom format is written serially.
  # JOBS is carried into the manifest because pg_restore is where it pays off.
  pg_dump \
    --format=custom \
    --compress="$COMPRESS_LEVEL" \
    --no-password \
    --verbose \
    --file="$target" \
    "$database" 2>"${STAGING_DIR}/${database}.log"

  # A dump that pg_restore cannot read is not a backup. Reading the table of
  # contents is cheap and catches truncation or a broken destination filesystem.
  pg_restore --list "$target" >/dev/null ||
    fail "Integrity check failed for $target"

  log "  $(du -h "$target" | cut -f1) — table of contents verified"
done

(cd "$STAGING_DIR" && sha256sum ./*.dump ./globals.sql > SHA256SUMS)

cat > "${STAGING_DIR}/MANIFEST.txt" <<MANIFEST
backup_type      logical (pg_dump custom format)
started_at       ${TIMESTAMP}
finished_at      $(date --iso-8601=seconds)
server_version   ${SERVER_VERSION}
host             $(hostname -f 2>/dev/null || hostname)
pg_dump_version  $(pg_dump --version)
databases        ${DATABASE_LIST[*]}
restore_globals  psql -f globals.sql postgres
restore_database pg_restore --clean --if-exists --create --jobs=${JOBS} -d postgres <database>.dump
MANIFEST

chmod -R go-rwx "$STAGING_DIR"
mv -- "$STAGING_DIR" "$FINAL_DIR"
trap - EXIT

# Retention runs only after a successful set exists, so a broken night never
# deletes the last good copy. -maxdepth 1 keeps it from descending into sets.
if (( RETENTION_DAYS > 0 )); then
  log "Applying retention: removing sets older than ${RETENTION_DAYS} days"
  find "$BACKUP_ROOT" -mindepth 1 -maxdepth 1 -type d \
    -name '20*T*' -mtime "+${RETENTION_DAYS}" \
    -print -exec rm -rf -- {} +
fi

log "Backup completed: $FINAL_DIR"
log "Total size: $(du -sh "$FINAL_DIR" | cut -f1)"
log "Reminder: a backup is only valid once a restore has been tested."