basil/scripts/restore-postgres-database.sh

#!/bin/bash
#
# PostgreSQL Database Restore Script
# Restores a single database from backup created by backup-all-postgres-databases.sh
#
# Usage:
#   ./restore-postgres-database.sh <backup_file> [options]
#
# Options:
#   -h HOST       Database host (default: localhost)
#   -p PORT       Database port (default: 5432)
#   -U USER       Database user (default: postgres)
#   -d DBNAME     Target database name (default: extracted from backup filename)
#   -f            Force restore (skip confirmation)
#   -v            Verbose output
#
# Examples:
#   ./restore-postgres-database.sh /var/backups/postgresql/20260120/mydb_20260120_020001.sql.gz
#   ./restore-postgres-database.sh backup.sql -d mydb -f

set -e
set -o pipefail

# Default configuration
DB_HOST="localhost"
DB_PORT="5432"
DB_USER="postgres"
DB_NAME=""
FORCE=false
VERBOSE=false

# Color output
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
BLUE='\033[0;34m'
NC='\033[0m' # No Color

# Logging functions
log_info() {
    echo -e "${GREEN}[INFO]${NC} $(date '+%Y-%m-%d %H:%M:%S') - $1"
}

log_warn() {
    echo -e "${YELLOW}[WARN]${NC} $(date '+%Y-%m-%d %H:%M:%S') - $1"
}

log_error() {
    echo -e "${RED}[ERROR]${NC} $(date '+%Y-%m-%d %H:%M:%S') - $1" >&2
}

log_debug() {
    if [ "$VERBOSE" = true ]; then
        echo -e "${BLUE}[DEBUG]${NC} $(date '+%Y-%m-%d %H:%M:%S') - $1"
    fi
}

# Show usage
show_usage() {
    echo "PostgreSQL Database Restore Script"
    echo ""
    echo "Usage: $0 <backup_file> [options]"
    echo ""
    echo "Options:"
    echo "  -h HOST       Database host (default: localhost)"
    echo "  -p PORT       Database port (default: 5432)"
    echo "  -U USER       Database user (default: postgres)"
    echo "  -d DBNAME     Target database name (default: extracted from filename)"
    echo "  -f            Force restore (skip confirmation)"
    echo "  -v            Verbose output"
    echo "  -H            Show this help"
    echo ""
    echo "Examples:"
    echo "  $0 /var/backups/postgresql/20260120/mydb_20260120_020001.sql.gz"
    echo "  $0 backup.sql -d mydb -f"
    echo ""
}

# Extract database name from backup filename
extract_db_name() {
    local filename=$(basename "$1")
    # Remove extension(s) and timestamp
    # Format: dbname_YYYYMMDD_HHMMSS.sql[.gz]
    echo "$filename" | sed -E 's/_[0-9]{8}_[0-9]{6}\.sql(\.gz)?$//'
}

# Check if file is compressed
is_compressed() {
    [[ "$1" == *.gz ]]
}

# Verify backup file
verify_backup() {
    local backup_file="$1"

    log_debug "Verifying backup file: $backup_file"

    if [ ! -f "$backup_file" ]; then
        log_error "Backup file not found: $backup_file"
        exit 1
    fi

    if [ ! -r "$backup_file" ]; then
        log_error "Backup file is not readable: $backup_file"
        exit 1
    fi

    if [ ! -s "$backup_file" ]; then
        log_error "Backup file is empty: $backup_file"
        exit 1
    fi

    # Verify compressed file integrity
    if is_compressed "$backup_file"; then
        log_debug "Verifying gzip integrity..."
        if ! gzip -t "$backup_file" 2>/dev/null; then
            log_error "Backup file is corrupted (gzip test failed)"
            exit 1
        fi
    fi

    log_debug "Backup file verification passed"
}

# Test database connection
test_connection() {
    log_debug "Testing database connection..."

    if ! psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d postgres -c "SELECT version();" &> /dev/null; then
        log_error "Cannot connect to PostgreSQL server at $DB_HOST:$DB_PORT"
        log_error "Check credentials, network connectivity, and pg_hba.conf settings"
        exit 1
    fi

    log_debug "Database connection successful"
}

# Check if database exists
database_exists() {
    local db_name="$1"
    psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d postgres -t -c \
        "SELECT 1 FROM pg_database WHERE datname='$db_name';" | grep -q 1
}

# Create safety backup
create_safety_backup() {
    local db_name="$1"
    local timestamp=$(date +%Y%m%d_%H%M%S)
    local safety_file="/tmp/${db_name}_pre-restore_${timestamp}.sql.gz"

    log_info "Creating safety backup before restore..."

    if pg_dump -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$db_name" | gzip > "$safety_file"; then
        log_info "Safety backup created: $safety_file"
        echo "$safety_file"
        return 0
    else
        log_error "Failed to create safety backup"
        return 1
    fi
}

# Drop and recreate database
recreate_database() {
    local db_name="$1"

    log_info "Dropping and recreating database: $db_name"

    # Terminate existing connections
    psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d postgres <<EOF
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = '$db_name' AND pid <> pg_backend_pid();
EOF

    # Drop and recreate
    psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d postgres <<EOF
DROP DATABASE IF EXISTS $db_name;
CREATE DATABASE $db_name;
EOF

    log_debug "Database recreated successfully"
}

# Restore database
restore_database() {
    local backup_file="$1"
    local db_name="$2"

    log_info "Restoring database from: $backup_file"

    local start_time=$(date +%s)

    # Restore based on compression
    if is_compressed "$backup_file"; then
        if gunzip -c "$backup_file" | psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d postgres -v ON_ERROR_STOP=1; then
            local status="SUCCESS"
        else
            local status="FAILED"
        fi
    else
        if psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d postgres -f "$backup_file" -v ON_ERROR_STOP=1; then
            local status="SUCCESS"
        else
            local status="FAILED"
        fi
    fi

    local end_time=$(date +%s)
    local duration=$((end_time - start_time))

    if [ "$status" = "SUCCESS" ]; then
        log_info "✓ Database restore completed in ${duration}s"
        return 0
    else
        log_error "✗ Database restore failed"
        return 1
    fi
}

# Verify restore
verify_restore() {
    local db_name="$1"

    log_info "Verifying restored database..."

    # Check if database exists
    if ! database_exists "$db_name"; then
        log_error "Database not found after restore: $db_name"
        return 1
    fi

    # Get table count
    local table_count=$(psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$db_name" -t -c \
        "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'public';")
    table_count=$(echo "$table_count" | xargs)

    # Get row count estimate
    local row_count=$(psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$db_name" -t -c \
        "SELECT SUM(n_live_tup) FROM pg_stat_user_tables;")
    row_count=$(echo "$row_count" | xargs)
    row_count=${row_count:-0}

    log_info "Database: $db_name"
    log_info "Tables: $table_count"
    log_info "Approximate rows: $row_count"

    return 0
}

# Parse command line arguments
BACKUP_FILE=""

while [[ $# -gt 0 ]]; do
    case $1 in
        -h)
            DB_HOST="$2"
            shift 2
            ;;
        -p)
            DB_PORT="$2"
            shift 2
            ;;
        -U)
            DB_USER="$2"
            shift 2
            ;;
        -d)
            DB_NAME="$2"
            shift 2
            ;;
        -f)
            FORCE=true
            shift
            ;;
        -v)
            VERBOSE=true
            shift
            ;;
        -H)
            show_usage
            exit 0
            ;;
        -*)
            log_error "Unknown option: $1"
            show_usage
            exit 1
            ;;
        *)
            if [ -z "$BACKUP_FILE" ]; then
                BACKUP_FILE="$1"
            else
                log_error "Multiple backup files specified"
                show_usage
                exit 1
            fi
            shift
            ;;
    esac
done

# Main execution
main() {
    # Check if backup file was provided
    if [ -z "$BACKUP_FILE" ]; then
        log_error "No backup file specified"
        show_usage
        exit 1
    fi

    # Verify backup file
    verify_backup "$BACKUP_FILE"

    # Extract database name if not provided
    if [ -z "$DB_NAME" ]; then
        DB_NAME=$(extract_db_name "$BACKUP_FILE")
        log_debug "Extracted database name: $DB_NAME"
    fi

    log_info "================================================"
    log_info "PostgreSQL Database Restore"
    log_info "================================================"
    log_info "Backup File: $BACKUP_FILE"
    log_info "Target Database: $DB_NAME"
    log_info "Host: $DB_HOST:$DB_PORT"
    log_info "User: $DB_USER"
    log_info "================================================"
    echo ""

    # Test connection
    test_connection

    # Check if database exists
    local db_exists=false
    if database_exists "$DB_NAME"; then
        db_exists=true
        log_warn "Database '$DB_NAME' already exists and will be DROPPED"
    fi

    # Confirmation prompt (unless force flag is set)
    if [ "$FORCE" != true ]; then
        echo ""
        echo -e "${RED}WARNING: This will destroy all current data in database: $DB_NAME${NC}"
        echo ""
        read -p "Are you sure you want to continue? (type 'yes' to confirm): " CONFIRM

        if [ "$CONFIRM" != "yes" ]; then
            log_info "Restore cancelled by user"
            exit 0
        fi
    fi

    # Create safety backup if database exists
    local safety_file=""
    if [ "$db_exists" = true ]; then
        safety_file=$(create_safety_backup "$DB_NAME")
    fi

    # Recreate database
    recreate_database "$DB_NAME"

    # Restore from backup
    if restore_database "$BACKUP_FILE" "$DB_NAME"; then
        verify_restore "$DB_NAME"

        echo ""
        log_info "================================================"
        log_info "Restore completed successfully! ✓"
        log_info "================================================"

        if [ -n "$safety_file" ]; then
            echo ""
            log_info "A safety backup was created before restore:"
            log_info "  $safety_file"
            echo ""
            log_info "To rollback to the previous state, run:"
            log_info "  $0 $safety_file -d $DB_NAME -f"
            echo ""
        fi

        exit 0
    else
        log_error "Restore failed!"

        if [ -n "$safety_file" ]; then
            echo ""
            log_warn "You can restore the previous state using:"
            log_warn "  $0 $safety_file -d $DB_NAME -f"
        fi

        exit 1
    fi
}

# Run main function
main