Database Backup Script with Rotation

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

# Configuration
DB_HOST="${DB_HOST:-localhost}"
DB_NAME="${DB_NAME:-appdb}"
DB_USER="${DB_USER:-appuser}"
BACKUP_DIR="/backups/postgres"
RETENTION_DAYS=30
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE="${BACKUP_DIR}/${DB_NAME}_${TIMESTAMP}.sql.gz"

# Ensure backup directory exists
mkdir -p "$BACKUP_DIR"

echo "Starting backup of ${DB_NAME} at $(date)"

# Create compressed backup
pg_dump -h "$DB_HOST" -U "$DB_USER" -Fc "$DB_NAME" | gzip > "$BACKUP_FILE"

# Verify backup
if [[ -s "$BACKUP_FILE" ]]; then
  SIZE=$(du -h "$BACKUP_FILE" | cut -f1)
  echo "Backup created: $BACKUP_FILE ($SIZE)"
else
  echo "Error: Backup file is empty" >&2
  rm -f "$BACKUP_FILE"
  exit 1
fi

# Remove old backups
find "$BACKUP_DIR" -name "${DB_NAME}_*.sql.gz" -mtime +${RETENTION_DAYS} -delete
REMAINING=$(find "$BACKUP_DIR" -name "${DB_NAME}_*.sql.gz" | wc -l)
echo "Cleaned up old backups. ${REMAINING} backups remaining."

# Optional: Upload to S3
# aws s3 cp "$BACKUP_FILE" "s3://my-backups/postgres/" --storage-class STANDARD_IA

echo "Backup completed at $(date)"