Automate PostgreSQL Database Backups with Retention

#!/bin/bash

# --- Configuration ---
DB_NAME="your_database_name"
DB_USER="your_db_user"
BACKUP_DIR="/path/to/your/backup/directory"
RETENTION_DAYS=7 # Keep backups for 7 days

# --- Script Logic ---

# Create backup directory if it doesn't exist
mkdir -p "$BACKUP_DIR"

# Generate timestamp for backup file
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE="$BACKUP_DIR/$DB_NAME-$TIMESTAMP.sql.gz"

echo "[$(date)] Starting backup for database '$DB_NAME'...";

# Perform PostgreSQL database backup
# -h localhost can be omitted if the script runs on the same host as the DB
# You might need to set PGPASSWORD environment variable or use a .pgpass file
# For security, avoid putting password directly in script.
# Example with PGPASSWORD (use with caution or secure method):
# PGPASSWORD="your_db_password" pg_dump -U "$DB_USER" "$DB_NAME" | gzip > "$BACKUP_FILE"

pg_dump -U "$DB_USER" "$DB_NAME" | gzip > "$BACKUP_FILE"

if [ $? -eq 0 ]; then
    echo "[$(date)] Backup successful: $BACKUP_FILE"
else
    echo "[$(date)] Backup failed!"
    exit 1
fi

# Clean up old backups based on retention policy
echo "[$(date)] Cleaning up old backups (older than $RETENTION_DAYS days)..."
find "$BACKUP_DIR" -name "$DB_NAME-*.sql.gz" -type f -mtime +$RETENTION_DAYS -exec rm {} \;

if [ $? -eq 0 ]; then
    echo "[$(date)] Cleanup successful."
else
    echo "[$(date)] Cleanup failed or no old backups found."
fi

echo "[$(date)] Backup and cleanup script finished."