backup-script.sh

#!/bin/bash

# Exit on any error
set -e

# Default values (can be overridden by environment variables)
BACKUP_INTERVAL=${BACKUP_INTERVAL:-86400}  # 24 hours in seconds
RETENTION_DAYS=${RETENTION_DAYS:-7}        # Keep backups for 7 days locally
S3_BUCKET=${S3_BUCKET:-""}                 # S3 bucket name (required)
S3_PREFIX=${S3_PREFIX:-"db-backups"}       # S3 prefix/folder

# Function to log messages with timestamp
log() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1"
}

# Function to create database backup
create_backup() {
    local timestamp=$(date +%Y%m%d_%H%M%S)
    local backup_file="/backups/backup_${timestamp}.sql"

    log "Creating database backup: $backup_file"

    # Create PostgreSQL dump
    if ! PGPASSWORD="$POSTGRES_PASSWORD" pg_dump \
        -h "$POSTGRES_HOST" \
        -U "$POSTGRES_USER" \
        -d "$POSTGRES_DB" \
        > "$backup_file" 2>/dev/null; then
        log "ERROR: pg_dump failed"
        rm -f "$backup_file"
        exit 1
    fi

    # Check if backup file was created and has content
    if [ ! -s "$backup_file" ]; then
        log "ERROR: Backup file is empty or was not created"
        rm -f "$backup_file"
        exit 1
    fi

    # Compress the backup
    gzip "$backup_file"
    backup_file="${backup_file}.gz"

    # Wait a moment for file system to sync
    sleep 1

    log "Backup created successfully: $backup_file"
    echo "$backup_file"
}

# Function to sync backup to S3/R2
sync_to_s3() {
    local backup_file="$1"

    if [ -z "$S3_BUCKET" ]; then
        log "S3_BUCKET not set, skipping S3 sync"
        return 0
    fi

    log "Syncing backup to S3/R2: s3://$S3_BUCKET/$S3_PREFIX/"

    # Execute AWS CLI command with proper argument handling
    if [ -n "$AWS_ENDPOINT_URL" ]; then
        # For R2 or other S3-compatible services
        # The multipart threshold is set to 1GB in AWS config to avoid multipart uploads for most files

        # Change to backup directory and use relative path to avoid path issues
        cd /backups
        local backup_filename=$(basename "$backup_file")

        # Verify backup file exists before upload (using relative path)
        if [ ! -f "$backup_filename" ]; then
            log "ERROR: Backup file $backup_filename does not exist in /backups directory"
            return 1
        fi

        log "Uploading to R2: $backup_filename ($(du -h "$backup_filename" | cut -f1))"

        # Capture AWS CLI output and error separately
        aws_output=$(aws s3 cp "$backup_filename" "s3://$S3_BUCKET/$S3_PREFIX/" \
            --endpoint-url "$AWS_ENDPOINT_URL" 2>&1)
        aws_exit_code=$?

        if [ $aws_exit_code -eq 0 ]; then
            log "Backup synced to R2 successfully"
        else
            log "ERROR: Failed to sync backup to R2"
            log "AWS CLI output: $aws_output"
            return 1
        fi
    else
        # For standard AWS S3
        log "Uploading to S3..."
        if aws s3 cp "$backup_file" "s3://$S3_BUCKET/$S3_PREFIX/" --storage-class STANDARD_IA; then
            log "Backup synced to S3 successfully"
        else
            log "ERROR: Failed to sync backup to S3"
            return 1
        fi
    fi
}

# Function to cleanup old local backups
cleanup_old_backups() {
    log "Cleaning up backups older than $RETENTION_DAYS days"
    find /backups -name 'backup_*.sql.gz' -mtime +$RETENTION_DAYS -delete
    log "Cleanup completed"
}

# Function to cleanup old S3/R2 backups (optional)
cleanup_old_s3_backups() {
    if [ -z "$S3_BUCKET" ] || [ -z "$S3_RETENTION_DAYS" ]; then
        return 0
    fi

    log "Cleaning up S3/R2 backups older than $S3_RETENTION_DAYS days"

    # Calculate date threshold
    local threshold_date=$(date -d "$S3_RETENTION_DAYS days ago" +%Y-%m-%d)

    # List and delete old backups with proper command execution
    if [ -n "$AWS_ENDPOINT_URL" ]; then
        # For R2 or other S3-compatible services
        aws s3 ls "s3://$S3_BUCKET/$S3_PREFIX/" --recursive --endpoint-url "$AWS_ENDPOINT_URL" | \
        while read -r line; do
            local file_date=$(echo "$line" | awk '{print $1}')
            local file_path=$(echo "$line" | awk '{print $4}')

            if [[ "$file_date" < "$threshold_date" ]]; then
                log "Deleting old S3/R2 backup: $file_path"
                aws s3 rm "s3://$S3_BUCKET/$file_path" --endpoint-url "$AWS_ENDPOINT_URL"
            fi
        done
    else
        # For standard AWS S3
        aws s3 ls "s3://$S3_BUCKET/$S3_PREFIX/" --recursive | \
        while read -r line; do
            local file_date=$(echo "$line" | awk '{print $1}')
            local file_path=$(echo "$line" | awk '{print $4}')

            if [[ "$file_date" < "$threshold_date" ]]; then
                log "Deleting old S3 backup: $file_path"
                aws s3 rm "s3://$S3_BUCKET/$file_path"
            fi
        done
    fi
}

# Function to run backup cycle
run_backup_cycle() {
    log "Starting backup cycle"
    
    # Create backup
    local backup_file=$(create_backup)
    
    # Sync to S3
    sync_to_s3 "$backup_file"
    
    # Cleanup old backups
    cleanup_old_backups
    cleanup_old_s3_backups
    
    log "Backup cycle completed"
}

# Function to validate required environment variables
validate_env() {
    local required_vars=("POSTGRES_HOST" "POSTGRES_USER" "POSTGRES_PASSWORD" "POSTGRES_DB")

    for var in "${required_vars[@]}"; do
        if [ -z "${!var}" ]; then
            log "ERROR: Required environment variable $var is not set"
            exit 1
        fi
    done

    # Test PostgreSQL connection
    log "Testing PostgreSQL connection to $POSTGRES_HOST..."
    if ! PGPASSWORD="$POSTGRES_PASSWORD" pg_isready -h "$POSTGRES_HOST" -U "$POSTGRES_USER" -d "$POSTGRES_DB" > /dev/null 2>&1; then
        log "WARNING: PostgreSQL is not ready yet, waiting 30 seconds..."
        sleep 30
        if ! PGPASSWORD="$POSTGRES_PASSWORD" pg_isready -h "$POSTGRES_HOST" -U "$POSTGRES_USER" -d "$POSTGRES_DB" > /dev/null 2>&1; then
            log "ERROR: Cannot connect to PostgreSQL at $POSTGRES_HOST"
            exit 1
        fi
    fi
    log "PostgreSQL connection validated successfully"

    # Check AWS/R2 credentials if S3_BUCKET is set
    if [ -n "$S3_BUCKET" ]; then
        if [ -n "$AWS_ENDPOINT_URL" ]; then
            # For R2, test with a simple S3 ls command
            if ! aws s3 ls "s3://$S3_BUCKET" --endpoint-url "$AWS_ENDPOINT_URL" > /dev/null 2>&1; then
                log "ERROR: R2 credentials not configured properly or bucket doesn't exist"
                exit 1
            fi
        else
            # For standard AWS S3, use STS
            if ! aws sts get-caller-identity > /dev/null 2>&1; then
                log "ERROR: AWS credentials not configured properly"
                exit 1
            fi
        fi
        log "AWS/R2 credentials validated successfully"
    fi
}

# Main execution
main() {
    log "Database backup service starting"
    
    # Validate environment
    validate_env
    
    # Run initial backup
    run_backup_cycle
    
    # Continue with scheduled backups
    while true; do
        log "Sleeping for $BACKUP_INTERVAL seconds until next backup"
        sleep "$BACKUP_INTERVAL"
        run_backup_cycle
    done
}

# Handle signals for graceful shutdown
trap 'log "Received shutdown signal, exiting..."; exit 0' SIGTERM SIGINT

# Run main function
main "$@"