Backup all databases in pg instance

pg_dump.sh

#!/bin/bash

# Usage
# 1. perform backup:    bash backup.sh -c "postgres://user:password@hostname:5432/dbname?sslmode=require"
# 2. unzip:             gunzip -c dump/full_backup_20241101_081322.sql.gz > restore.sql
# 3. restore:           psql -h hostname -p 5432 -U dedicateddbadmin -d postgres -f restore.sql
# 4. unzip and restore: gunzip -c dump/full_backup_20241101_081322.sql.gz | psql -h hostname -p 5432 -U username -d postgres

# Help function
show_help() {
    echo "Usage: $0 [-c connection_string] [-n]"
    echo "Options:"
    echo "  -c connection_string    PostgreSQL connection string"
    echo "  -n                      Non-superuser mode (skip roles and tablespaces)"
    echo
    echo "Example: $0 -c 'postgres://user:password@hostname:5432/dbname?sslmode=require'"
    exit 1
}

# Parse command line options
CONNECTION_STRING=""
NON_SUPERUSER=false
while getopts "c:nh" opt; do
    case $opt in
        c)
            CONNECTION_STRING=$OPTARG
            ;;
        n)
            NON_SUPERUSER=true
            ;;
        h)
            show_help
            ;;
        \?)
            show_help
            ;;
    esac
done

# Configuration
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
BACKUP_DIR="/home/ubuntu/dump"
LOG_DIR="/home/ubuntu/dump"
BACKUP_FILE="$BACKUP_DIR/full_backup_$TIMESTAMP.sql.gz"
LOG_FILE="$LOG_DIR/backup_$TIMESTAMP.log"

# Function to log messages
log_message() {
    local level=$1
    local message=$2
    local timestamp=$(date '+%Y-%m-%d %H:%M:%S')
    echo "[$timestamp] [$level] $message" | tee -a "$LOG_FILE"
}

get_db_size() {
    local db=$1
    local size=$(psql -tA -c "SELECT pg_size_pretty(pg_database_size('$db'));" postgres)
    echo "$size"
}

# Extract connection parameters
if [ -n "$CONNECTION_STRING" ]; then
    # Extract host, port, dbname, user, and password from connection string
    PGHOST=$(echo "$CONNECTION_STRING" | sed -n 's/.*@\([^:]*\):.*/\1/p')
    PGPORT=$(echo "$CONNECTION_STRING" | sed -n 's/.*:\([0-9]*\)\/.*/\1/p')
    PGDATABASE=$(echo "$CONNECTION_STRING" | sed -n 's/.*\/\([^?]*\).*/\1/p')
    PGUSER=$(echo "$CONNECTION_STRING" | sed -n 's/.*:\/\/\([^:]*\):.*/\1/p')
    PGPASSWORD=$(echo "$CONNECTION_STRING" | sed -n 's/.*:\/\/[^:]*:\([^@]*\)@.*/\1/p')
    export PGHOST PGPORT PGDATABASE PGUSER PGPASSWORD

    # Force SSL mode for RDS connections
    export PGSSLMODE="require"
fi

# Ensure backup and log directories exist
mkdir -p "$BACKUP_DIR" "$LOG_DIR"

# Initialize log file with header
echo "=== PostgreSQL Backup Log - Started at $(date) ===" > "$LOG_FILE"

# Log connection information (masking password)
if [ -n "$CONNECTION_STRING" ]; then
    masked_connection=$(echo "$CONNECTION_STRING" | sed -E 's/(:.*@)/:*****@/')
    log_message "INFO" "Using connection string: $masked_connection"
    log_message "INFO" "SSL Mode: require (forced for RDS)"
else
    log_message "INFO" "Using default PostgreSQL environment variables"
fi

# Log system information
log_message "INFO" "PostgreSQL Version: $(psql --version)"
log_message "INFO" "Backup File: $BACKUP_FILE"
log_message "INFO" "Available Disk Space: $(df -h "$BACKUP_DIR" | tail -1)"

# Get list of databases
log_message "INFO" "Retrieving database list..."
databases=$(psql -tA -c "SELECT datname FROM pg_database WHERE datname NOT IN ('template0', 'template1', 'postgres', 'rdsadmin') ORDER BY datname;")

if [ $? -ne 0 ]; then
    log_message "ERROR" "Failed to retrieve database list"
    exit 1
fi

# Log found databases
log_message "INFO" "Found databases to backup:"
echo "$databases" | while read -r db; do
    if [ -n "$db" ]; then
	db_size=$(get_db_size "$db")
        log_message "INFO" "- $db (Size: $db_size)"
    fi
done

# Start backup
log_message "INFO" "Starting backup process..."

# Create temporary directory for individual database dumps
TEMP_DIR=$(mktemp -d)
trap 'rm -rf "$TEMP_DIR"' EXIT

# Perform backup for each database
echo "" > "$TEMP_DIR/full_backup.sql"

echo "$databases" | while read -r db; do
    if [ -n "$db" ]; then
	db_size=$(get_db_size "$db")
        log_message "INFO" "Backing up database: $db (Size: $db_size)"

        # Add error handling and retry logic for SSL connections
        max_retries=3
        retry_count=0
        success=false

        while [ $retry_count -lt $max_retries ] && [ "$success" = false ]; do
            pg_dump --no-owner --no-acl --clean --if-exists "$db" > "$TEMP_DIR/$db.sql" 2>> "$LOG_FILE"

            if [ $? -eq 0 ]; then
                success=true
                # Add database creation and connection commands
                echo "CREATE DATABASE \"$db\";" >> "$TEMP_DIR/full_backup.sql"
                echo "\\connect \"$db\"" >> "$TEMP_DIR/full_backup.sql"
                cat "$TEMP_DIR/$db.sql" >> "$TEMP_DIR/full_backup.sql"
                echo "" >> "$TEMP_DIR/full_backup.sql"
            else
                retry_count=$((retry_count + 1))
                if [ $retry_count -lt $max_retries ]; then
                    log_message "WARN" "Backup failed for $db, retrying ($retry_count/$max_retries)..."
                    sleep 5
                fi
            fi
        done

        if [ "$success" = false ]; then
            log_message "ERROR" "Failed to backup database: $db after $max_retries attempts"
            exit 1
        fi
    fi
done

# Compress the final backup
cat "$TEMP_DIR/full_backup.sql" | gzip > "$BACKUP_FILE"

if [ $? -eq 0 ] && [ -s "$BACKUP_FILE" ]; then
    backup_size=$(du -h "$BACKUP_FILE" | cut -f1)
    log_message "SUCCESS" "Backup completed successfully"
    log_message "INFO" "Backup size: $backup_size"

    # Verify backup file is not empty
    if [ $(gzip -dc "$BACKUP_FILE" | wc -l) -lt 10 ]; then
        log_message "ERROR" "Backup file appears to be empty or too small"
        rm -f "$BACKUP_FILE"
        exit 1
    fi

    # Check backup file permissions
    chmod 600 "$BACKUP_FILE"
    log_message "INFO" "Backup file permissions set to 600"
else
    log_message "ERROR" "Backup failed! Check log file for details"
    if [ -f "$BACKUP_FILE" ]; then
        rm -f "$BACKUP_FILE"
        log_message "INFO" "Cleaned up failed backup file"
    fi
    exit 1
fi

# Log backup statistics
log_message "INFO" "Backup Statistics:"
log_message "INFO" "- Duration: $SECONDS seconds"
log_message "INFO" "- Final Size: $(du -h "$BACKUP_FILE" | cut -f1)"
log_message "INFO" "- Available Disk Space After Backup: $(df -h "$BACKUP_DIR" | tail -1)"

echo "=== Backup completed at $(date) ===" >> "$LOG_FILE"