`scripts/backup-postgres.sh`

77 lines
#!/bin/bash
# PostgreSQL Backup Script with Retention Policy
# 7 daily, 4 weekly, 12 monthly backups

set -e

# Configuration
BACKUP_DIR="${BACKUP_DIR:-/root/Projects/watches/backups/postgres}"
DB_HOST="${DB_HOST:-localhost}"
DB_PORT="${DB_PORT:-5432}"
DB_NAME="${DB_NAME:-omega_watches}"
DB_USER="${DB_USER:-postgres}"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
DAY_OF_WEEK=$(date +%u)  # 1=Monday, 7=Sunday
DAY_OF_MONTH=$(date +%d)

# Create backup directories
mkdir -p "$BACKUP_DIR/daily"
mkdir -p "$BACKUP_DIR/weekly"
mkdir -p "$BACKUP_DIR/monthly"

echo "=== PostgreSQL Backup - $(date) ==="

# Create backup
BACKUP_FILE="$BACKUP_DIR/daily/omega_watches_${TIMESTAMP}.sql.gz"
echo "Creating backup: $BACKUP_FILE"

PGPASSWORD=$DB_PASSWORD pg_dump \
    -h $DB_HOST \
    -p $DB_PORT \
    -U $DB_USER \
    -d $DB_NAME \
    --format=plain \
    --no-owner \
    --no-privileges \
    | gzip > "$BACKUP_FILE"

# Get backup size
BACKUP_SIZE=$(du -h "$BACKUP_FILE" | cut -f1)
echo "Backup created: $BACKUP_SIZE"

# Weekly backup (Sunday)
if [ "$DAY_OF_WEEK" = "7" ]; then
    echo "Creating weekly backup..."
    cp "$BACKUP_FILE" "$BACKUP_DIR/weekly/omega_watches_weekly_${TIMESTAMP}.sql.gz"
fi

# Monthly backup (1st of month)
if [ "$DAY_OF_MONTH" = "01" ]; then
    echo "Creating monthly backup..."
    cp "$BACKUP_FILE" "$BACKUP_DIR/monthly/omega_watches_monthly_${TIMESTAMP}.sql.gz"
fi

# Retention: Keep 7 daily backups
echo "Cleaning old daily backups (keeping 7)..."
cd "$BACKUP_DIR/daily"
ls -t *.sql.gz 2>/dev/null | tail -n +8 | xargs -r rm -f

# Retention: Keep 4 weekly backups
echo "Cleaning old weekly backups (keeping 4)..."
cd "$BACKUP_DIR/weekly"
ls -t *.sql.gz 2>/dev/null | tail -n +5 | xargs -r rm -f

# Retention: Keep 12 monthly backups
echo "Cleaning old monthly backups (keeping 12)..."
cd "$BACKUP_DIR/monthly"
ls -t *.sql.gz 2>/dev/null | tail -n +13 | xargs -r rm -f

# Summary
echo ""
echo "=== Backup Summary ==="
echo "Daily backups: $(ls -1 $BACKUP_DIR/daily/*.sql.gz 2>/dev/null | wc -l)"
echo "Weekly backups: $(ls -1 $BACKUP_DIR/weekly/*.sql.gz 2>/dev/null | wc -l)"
echo "Monthly backups: $(ls -1 $BACKUP_DIR/monthly/*.sql.gz 2>/dev/null | wc -l)"
echo "Total size: $(du -sh $BACKUP_DIR | cut -f1)"
echo "=== Backup Complete ==="