Database Backup and Restore to S3

#!/usr/bin/env bash
set -euo pipefail

# Config
DB_URL="${DATABASE_URL}"
S3_BUCKET="s3://backups/postgres"
DATE=$(date +%Y-%m-%d_%H%M)
BACKUP_DIR="/tmp/backups"
RETENTION_DAYS=30

mkdir -p "$BACKUP_DIR"
log() { echo "[$(date '+%H:%M:%S')] $*"; }

# --- Full backup ---
log "Starting backup..."
BACKUP_FILE="${BACKUP_DIR}/db_${DATE}.sql.gz"

pg_dump "$DB_URL" \
  --format=plain \
  --no-owner \
  --no-privileges \
  --verbose \
  2>/dev/null | gzip > "$BACKUP_FILE"

SIZE=$(du -h "$BACKUP_FILE" | awk '{print $1}')
log "Backup created: ${SIZE}"

# --- Upload to S3 ---
log "Uploading to S3..."
aws s3 cp "$BACKUP_FILE" "${S3_BUCKET}/${DATE}/" \
  --storage-class STANDARD_IA \
  --only-show-errors

# --- Verify upload ---
aws s3 ls "${S3_BUCKET}/${DATE}/" | grep -q "db_${DATE}"
log "Upload verified"

# --- Cleanup old backups ---
log "Cleaning up backups older than ${RETENTION_DAYS} days"
aws s3 ls "$S3_BUCKET/" | while read -r line; do
  dir=$(echo "$line" | awk '{print $2}' | tr -d '/')
  if [[ "$dir" < "$(date -d "${RETENTION_DAYS} days ago" +%Y-%m-%d)" ]]; then
    aws s3 rm "${S3_BUCKET}/${dir}/" --recursive --only-show-errors
    log "  Deleted: ${dir}"
  fi
done

# --- Restore command (for reference) ---
# gunzip -c db_backup.sql.gz | psql "$DB_URL"
# Or from S3:
# aws s3 cp s3://backups/postgres/2024-01-15_0200/db_2024-01-15_0200.sql.gz - | gunzip | psql "$DB_URL"

rm -f "$BACKUP_FILE"
log "Backup complete"