ai_platform/scripts/pg-backup.sh

#!/usr/bin/env bash
# PostgreSQL 논리 백업 (pg_dump -Fc). .env의 DB_* 및 PG_BACKUP_* 사용.
# PG_BACKUP_SCOPE=all 이면 서버의 연결 가능·비템플릿 DB 전체를 각각 .dump 로 저장.
# 운영 서버 cron 예: 0 2 * * * cd /home/xavis/workspace/ai_platform && bash scripts/pg-backup.sh >> /var/log/pg-backup.log 2>&1
set -euo pipefail

REPO_ROOT="$(cd "$(dirname "$0")/.." && pwd)"
# shellcheck source=scripts/lib/load-env.sh
source "$REPO_ROOT/scripts/lib/load-env.sh"

load_project_env "$REPO_ROOT/.env"

: "${DB_HOST:?DB_HOST is required in .env}"
: "${DB_DATABASE:?DB_DATABASE is required in .env}"
: "${DB_USERNAME:?DB_USERNAME is required in .env}"
: "${DB_PASSWORD:?DB_PASSWORD is required in .env}"

DB_PORT="${DB_PORT:-5432}"
PG_BACKUP_DIR="${PG_BACKUP_DIR:-/home/xavis/workspace/backup/ai_platform}"
PG_BACKUP_RETENTION_DAYS="${PG_BACKUP_RETENTION_DAYS:-30}"
PG_BACKUP_SCOPE="${PG_BACKUP_SCOPE:-all}"
PG_BACKUP_GLOBALS="${PG_BACKUP_GLOBALS:-1}"

export PGHOST="$DB_HOST"
export PGPORT="$DB_PORT"

if ! command -v pg_dump >/dev/null 2>&1; then
  echo "pg-backup: pg_dump not found. Install postgresql-client (apt) or postgresql (brew)." >&2
  exit 1
fi
if ! command -v psql >/dev/null 2>&1; then
  echo "pg-backup: psql not found. Install postgresql-client." >&2
  exit 1
fi

log_ts() { date '+%Y-%m-%dT%H:%M:%S%z'; }

set_backup_credentials() {
  if [[ -n "${PG_BACKUP_SUPERUSER_PASSWORD:-}" ]]; then
    export PGUSER="${PG_BACKUP_SUPERUSER:-postgres}"
    export PGPASSWORD="$PG_BACKUP_SUPERUSER_PASSWORD"
  else
    export PGUSER="$DB_USERNAME"
    export PGPASSWORD="$DB_PASSWORD"
  fi
}

list_all_databases() {
  psql -h "$PGHOST" -p "$PGPORT" -U "$PGUSER" -d postgres -v ON_ERROR_STOP=1 -tAc \
    "SELECT datname FROM pg_database WHERE datallowconn AND NOT datistemplate ORDER BY datname;"
}

dump_database() {
  local db_name="$1"
  local dump_file="$2"
  pg_dump \
    -h "$PGHOST" \
    -p "$PGPORT" \
    -U "$PGUSER" \
    -d "$db_name" \
    -Fc \
    --no-password \
    -f "$dump_file"
}

backup_globals() {
  if [[ "$PG_BACKUP_GLOBALS" != "1" ]]; then
    return 0
  fi
  if [[ -z "${PG_BACKUP_SUPERUSER_PASSWORD:-}" ]]; then
    echo "[$(log_ts)] globals skipped: set PG_BACKUP_SUPERUSER_PASSWORD for role/global backup." >&2
    return 0
  fi
  local prev_user="$PGUSER" prev_pass="$PGPASSWORD"
  export PGUSER="${PG_BACKUP_SUPERUSER:-postgres}"
  export PGPASSWORD="$PG_BACKUP_SUPERUSER_PASSWORD"
  pg_dumpall \
    -h "$PGHOST" \
    -p "$PGPORT" \
    -U "$PGUSER" \
    --globals-only \
    --no-password \
    -f "$RUN_DIR/00_globals.sql"
  export PGUSER="$prev_user"
  export PGPASSWORD="$prev_pass"
  echo "[$(log_ts)] globals saved: $RUN_DIR/00_globals.sql"
}

set_backup_credentials

# cron 일 1회 기준: YYYYMMDD 폴더에 당일 백업 저장
STAMP="$(date +%Y%m%d)"
RUN_DIR="$PG_BACKUP_DIR/$STAMP"
mkdir -p "$RUN_DIR"

echo "[$(log_ts)] pg-backup start → $RUN_DIR (scope=${PG_BACKUP_SCOPE}, retention ${PG_BACKUP_RETENTION_DAYS} days)"

if [[ -z "${PG_BACKUP_SUPERUSER_PASSWORD:-}" && "$PG_BACKUP_SCOPE" == "all" ]]; then
  echo "[$(log_ts)] note: PG_BACKUP_SUPERUSER_PASSWORD not set; backing up only databases visible to ${DB_USERNAME}." >&2
fi

backup_globals

declare -a TARGET_DBS=()
if [[ "$PG_BACKUP_SCOPE" == "single" ]]; then
  TARGET_DBS=("$DB_DATABASE")
else
  while IFS= read -r db; do
    [[ -n "$db" ]] && TARGET_DBS+=("$db")
  done < <(list_all_databases)
  if [[ ${#TARGET_DBS[@]} -eq 0 ]]; then
    echo "pg-backup: no databases found to backup." >&2
    exit 1
  fi
fi

MANIFEST="$RUN_DIR/00_manifest.txt"
{
  echo "# pg-backup manifest $(log_ts)"
  echo "scope=${PG_BACKUP_SCOPE}"
  echo "host=${PGHOST}:${PGPORT}"
  echo "user=${PGUSER}"
} > "$MANIFEST"

dump_ok=0
dump_fail=0
for db_name in "${TARGET_DBS[@]}"; do
  dump_file="$RUN_DIR/${db_name}.dump"
  echo "[$(log_ts)] dumping database: $db_name"
  if dump_database "$db_name" "$dump_file"; then
    bytes="$(wc -c < "$dump_file" | tr -d ' ')"
    echo "[$(log_ts)] dump saved: $dump_file (${bytes} bytes)"
    echo "${db_name}.dump ${bytes}" >> "$MANIFEST"
    dump_ok=$((dump_ok + 1))
  else
    echo "[$(log_ts)] dump failed: $db_name" >&2
    dump_fail=$((dump_fail + 1))
  fi
done

if [[ "$dump_ok" -eq 0 ]]; then
  echo "pg-backup: all database dumps failed." >&2
  exit 1
fi
if [[ "$dump_fail" -gt 0 ]]; then
  echo "[$(log_ts)] warning: ${dump_fail} database dump(s) failed, ${dump_ok} succeeded." >&2
fi

ln -sfn "$RUN_DIR" "$PG_BACKUP_DIR/latest"

prune_old_backups() {
  local cutoff removed=0 base day entry
  if date -d "1 day ago" +%Y%m%d >/dev/null 2>&1; then
    cutoff=$(date -d "${PG_BACKUP_RETENTION_DAYS} days ago" +%Y%m%d)
  else
    cutoff=$(date -v-"${PG_BACKUP_RETENTION_DAYS}"d +%Y%m%d)
  fi

  shopt -s nullglob
  for entry in "$PG_BACKUP_DIR"/*; do
    [[ -d "$entry" ]] || continue
    base=$(basename "$entry")
    [[ "$base" == "latest" ]] && continue
    if [[ "$base" =~ ^([0-9]{8}) ]]; then
      day="${BASH_REMATCH[1]}"
      if [[ "$day" < "$cutoff" ]]; then
        rm -rf "$entry"
        removed=$((removed + 1))
        echo "[$(log_ts)] retention: removed $entry (backup date $day < cutoff $cutoff)"
      fi
    fi
  done
  shopt -u nullglob
  echo "[$(log_ts)] retention: pruned ${removed} dir(s); keeping backups from ${cutoff} onward (${PG_BACKUP_RETENTION_DAYS} days)"
}

if [[ "$PG_BACKUP_RETENTION_DAYS" =~ ^[0-9]+$ ]] && [[ "$PG_BACKUP_RETENTION_DAYS" -gt 0 ]]; then
  prune_old_backups
fi

echo "[$(log_ts)] pg-backup done: ${dump_ok} database(s), latest → $PG_BACKUP_DIR/latest"