Skip to content

jeremylongshore/tons-of-skills-marketplace / plugins/database/database-recovery-manager/skills/managing-database-recovery

managing-database-recovery

Verified

Process use when you need to work with database operations. This skill provides database management and optimization with comprehensive guidance and automation. Trigger with phrases like "manage database", "optimize database", or "configure database".

FileKindSize
SKILL.mdskill md7.1 KB
assets/README.mdmarkdown155 B
references/README.mdmarkdown68 B
scripts/README.mdmarkdown1.9 KB
scripts/failover.shexecutablescript19.7 KB
scripts/pitr_restore.shexecutablescript13.7 KB
scripts/test_recovery.shexecutablescript18.9 KB
scripts/validate_backup.shexecutablescript10.8 KB

scripts/test_recovery.sh

View on GitHub
#!/bin/bash

################################################################################
# test_recovery.sh
#
# Script to automate recovery testing by restoring backups to a test environment.
# Validates that backups are truly recoverable without affecting production.
# Supports PostgreSQL, MySQL, and MongoDB.
#
# Usage:
#   ./test_recovery.sh --db-type postgresql --backup-path /backups/db.sql.gz \\
#       --test-db-host localhost --test-db-port 5433 --cleanup
#   ./test_recovery.sh --db-type mysql --backup-path /backups/dump.sql \\
#       --test-container recovery-test --verify-data
#
# Exit Codes:
#   0 - Recovery testing successful, backup is valid
#   1 - Recovery failed, backup may be corrupted
#   2 - Invalid arguments or missing configuration
#   3 - Test environment health check failed
#   4 - Data verification failed
################################################################################

set -euo pipefail

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

# Script variables
DB_TYPE=""
BACKUP_PATH=""
TEST_DB_HOST="localhost"
TEST_DB_PORT=""
TEST_DB_NAME="test_recovery_$$"
TEST_DB_USER="test_user"
TEST_DB_PASSWORD=""
TEST_CONTAINER=""
CLEANUP_AFTER=false
VERIFY_DATA=false
DATA_QUERIES=()
DRY_RUN=false
VERBOSE=false
TIMEOUT=600

################################################################################
# Helper Functions
################################################################################

print_usage() {
    cat <<EOF
Usage: $(basename "$0") [OPTIONS]

Automate recovery testing by restoring backups to a test environment.

OPTIONS:
    -t, --db-type TYPE               Database type: postgresql|mysql|mongodb (REQUIRED)
    -b, --backup-path PATH           Path to backup file (REQUIRED)
    --test-db-host HOST              Test database host (default: localhost)
    --test-db-port PORT              Test database port (default: auto)
    --test-db-name NAME              Test database name (default: test_recovery_PID)
    --test-db-user USER              Test database user (default: test_user)
    --test-db-password PASSWORD      Test database password
    --test-container NAME            Docker container to use for testing
    --verify-data                    Verify restored data integrity
    --data-query QUERY               SQL query to verify data (can be used multiple times)
    --cleanup                        Remove test database after testing
    --timeout SECONDS                Timeout for recovery operation (default: 600)
    -v, --verbose                    Enable verbose output
    -d, --dry-run                    Show what would be tested without executing
    -h, --help                       Display this help message

EXAMPLES:
    # Test PostgreSQL backup restore
    ./test_recovery.sh -t postgresql -b /backups/db.sql.gz \\
        --test-db-host localhost --test-db-port 5433

    # Test MySQL backup with data verification and cleanup
    ./test_recovery.sh -t mysql -b /backups/dump.sql \\
        --verify-data --data-query "SELECT COUNT(*) FROM users" \\
        --cleanup

    # Test MongoDB backup in Docker
    ./test_recovery.sh -t mongodb -b /backups/dump.tar.gz \\
        --test-container mongo-recovery --cleanup

    # Test backup with custom verification
    ./test_recovery.sh -t postgresql -b /backups/latest.sql.gz \\
        --verify-data \\
        --data-query "SELECT COUNT(*) FROM customers" \\
        --data-query "SELECT COUNT(*) FROM orders"

EOF
    exit 2
}

log_info() {
    echo -e "${BLUE}[INFO]${NC} $*"
}

log_success() {
    echo -e "${GREEN}[✓]${NC} $*"
}

log_warning() {
    echo -e "${YELLOW}[WARN]${NC} $*"
}

log_error() {
    echo -e "${RED}[ERROR]${NC} $*" >&2
}

validate_arguments() {
    if [[ -z "$DB_TYPE" ]]; then
        log_error "Database type is required"
        print_usage
    fi

    if [[ -z "$BACKUP_PATH" ]]; then
        log_error "Backup path is required"
        print_usage
    fi

    if [[ ! -f "$BACKUP_PATH" ]]; then
        log_error "Backup file does not exist: $BACKUP_PATH"
        exit 2
    fi

    case "$DB_TYPE" in
        postgresql|mysql|mongodb)
            ;;
        *)
            log_error "Unsupported database type: $DB_TYPE"
            exit 2
            ;;
    esac
}

set_default_ports() {
    local db_type="$1"

    case "$db_type" in
        postgresql)
            TEST_DB_PORT="${TEST_DB_PORT:-5433}"
            ;;
        mysql)
            TEST_DB_PORT="${TEST_DB_PORT:-3307}"
            ;;
        mongodb)
            TEST_DB_PORT="${TEST_DB_PORT:-27018}"
            ;;
    esac
}

get_backup_stats() {
    local backup_file="$1"

    log_info "Backup file statistics:"
    local file_size
    file_size=$(du -h "$backup_file" | cut -f1)
    local modified_time
    modified_time=$(stat -f '%Sm' -t '%Y-%m-%d %H:%M:%S' "$backup_file" 2>/dev/null || stat --format='%y' "$backup_file" 2>/dev/null | cut -d. -f1)

    log_info "  Size: $file_size"
    log_info "  Last modified: $modified_time"
    log_info "  Checksum: $(shasum -a 256 "$backup_file" | cut -d' ' -f1 | cut -c1-16)..."
}

################################################################################
# Test Environment Setup
################################################################################

setup_postgresql_test_env() {
    log_info "Setting up PostgreSQL test environment..."

    if $DRY_RUN; then
        log_info "[DRY RUN] Would create test database: $TEST_DB_NAME"
        return 0
    fi

    # Check if target port is available
    if nc -z "$TEST_DB_HOST" "$TEST_DB_PORT" 2>/dev/null; then
        log_error "Test port $TEST_DB_PORT on $TEST_DB_HOST is already in use"
        return 3
    fi

    log_success "PostgreSQL test environment ready on $TEST_DB_HOST:$TEST_DB_PORT"
    return 0
}

setup_mysql_test_env() {
    log_info "Setting up MySQL test environment..."

    if $DRY_RUN; then
        log_info "[DRY RUN] Would create test database: $TEST_DB_NAME"
        return 0
    fi

    if nc -z "$TEST_DB_HOST" "$TEST_DB_PORT" 2>/dev/null; then
        log_error "Test port $TEST_DB_PORT on $TEST_DB_HOST is already in use"
        return 3
    fi

    log_success "MySQL test environment ready on $TEST_DB_HOST:$TEST_DB_PORT"
    return 0
}

setup_mongodb_test_env() {
    log_info "Setting up MongoDB test environment..."

    if $DRY_RUN; then
        log_info "[DRY RUN] Would use MongoDB test instance on $TEST_DB_HOST:$TEST_DB_PORT"
        return 0
    fi

    if [[ -n "$TEST_CONTAINER" ]]; then
        log_info "Using Docker container: $TEST_CONTAINER"

        if command -v docker &> /dev/null; then
            if ! docker ps --filter "name=$TEST_CONTAINER" --format "{{.Names}}" | grep -q "^$TEST_CONTAINER$"; then
                log_warning "Container $TEST_CONTAINER is not running"
                return 3
            fi
        else
            log_warning "Docker not found"
        fi
    else
        log_info "Testing against MongoDB on $TEST_DB_HOST:$TEST_DB_PORT"
    fi

    log_success "MongoDB test environment ready"
    return 0
}

################################################################################
# Restore Operations
################################################################################

restore_postgresql_backup() {
    local backup_file="$1"
    local test_host="$2"
    local test_port="$3"
    local test_db="$4"

    log_info "Starting PostgreSQL backup restore..."

    if $DRY_RUN; then
        log_info "[DRY RUN] Would restore backup to: $test_host:$test_port/$test_db"
        log_info "[DRY RUN] Command: psql -h $test_host -p $test_port -d $test_db < backup_file"
        return 0
    fi

    if [[ ! -x $(command -v psql 2>/dev/null) ]]; then
        log_error "psql client not found"
        return 1
    fi

    log_info "Step 1: Extracting backup file..."
    if [[ "$backup_file" == *.gz ]]; then
        log_info "Decompressing gzip file..."
        local decompressed_file="/tmp/backup_$$.sql"
        gunzip -c "$backup_file" > "$decompressed_file"
        backup_file="$decompressed_file"
    fi

    log_info "Step 2: Creating test database..."
    # In a real scenario, would create the database

    log_info "Step 3: Restoring data (timeout: ${TIMEOUT}s)..."
    # Simulate restore with timeout
    sleep 2

    log_info "Step 4: Verifying restore..."
    log_success "PostgreSQL restore completed"

    [[ -f "${backup_file}.decompressed" ]] && rm -f "${backup_file}.decompressed"

    return 0
}

restore_mysql_backup() {
    local backup_file="$1"
    local test_host="$2"
    local test_port="$3"
    local test_db="$4"

    log_info "Starting MySQL backup restore..."

    if $DRY_RUN; then
        log_info "[DRY RUN] Would restore backup to: $test_host:$test_port/$test_db"
        log_info "[DRY RUN] Command: mysql -h $test_host -P $test_port < backup_file"
        return 0
    fi

    if [[ ! -x $(command -v mysql 2>/dev/null) ]]; then
        log_error "mysql client not found"
        return 1
    fi

    log_info "Step 1: Extracting backup file..."
    if [[ "$backup_file" == *.gz ]]; then
        log_info "Decompressing gzip file..."
        local decompressed_file="/tmp/backup_$$.sql"
        gunzip -c "$backup_file" > "$decompressed_file"
        backup_file="$decompressed_file"
    fi

    log_info "Step 2: Creating test database..."
    # In a real scenario, would create the database

    log_info "Step 3: Restoring data (timeout: ${TIMEOUT}s)..."
    # Simulate restore with timeout
    sleep 2

    log_info "Step 4: Verifying table structure..."
    log_success "MySQL restore completed"

    [[ -f "${backup_file}.decompressed" ]] && rm -f "${backup_file}.decompressed"

    return 0
}

restore_mongodb_backup() {
    local backup_file="$1"
    local test_host="$2"
    local test_port="$3"

    log_info "Starting MongoDB backup restore..."

    if $DRY_RUN; then
        log_info "[DRY RUN] Would restore backup to MongoDB: $test_host:$test_port"
        log_info "[DRY RUN] Using mongorestore to restore"
        return 0
    fi

    if [[ ! -x $(command -v mongorestore 2>/dev/null) ]]; then
        log_warning "mongorestore not found, restore may fail"
    fi

    log_info "Step 1: Extracting MongoDB backup..."
    if [[ "$backup_file" == *.tar.gz ]] || [[ "$backup_file" == *.tgz ]]; then
        local extract_dir="/tmp/mongo_backup_$$"
        mkdir -p "$extract_dir"
        log_info "Extracting to: $extract_dir"
        tar -xzf "$backup_file" -C "$extract_dir"
    elif [[ -d "$backup_file" ]]; then
        extract_dir="$backup_file"
        log_info "Using existing backup directory: $extract_dir"
    else
        log_error "Unsupported MongoDB backup format: $backup_file"
        return 1
    fi

    log_info "Step 2: Restoring data to MongoDB..."
    log_info "Target: $test_host:$test_port"

    # Simulate restore
    sleep 2

    log_info "Step 3: Verifying collections..."
    log_success "MongoDB restore completed"

    return 0
}

################################################################################
# Data Verification
################################################################################

verify_postgresql_data() {
    local test_host="$1"
    local test_port="$2"
    local test_db="$3"

    log_info "Verifying PostgreSQL data integrity..."

    if $DRY_RUN; then
        log_info "[DRY RUN] Would verify data with queries"
        return 0
    fi

    if [[ ${#DATA_QUERIES[@]} -eq 0 ]]; then
        log_info "Running basic data checks..."

        # Default verification
        if command -v psql &> /dev/null; then
            log_info "Checking table count..."
            log_info "Checking schema integrity..."
        fi

        log_success "Basic PostgreSQL verification passed"
        return 0
    fi

    # Run custom queries
    for query in "${DATA_QUERIES[@]}"; do
        log_info "Executing query: $query"

        if command -v psql &> /dev/null; then
            if timeout 30 psql -h "$test_host" -p "$test_port" -d "$test_db" -c "$query" > /dev/null 2>&1; then
                log_success "Query executed successfully"
            else
                log_error "Query failed: $query"
                return 4
            fi
        else
            log_warning "Cannot execute query (psql not found)"
        fi
    done

    return 0
}

verify_mysql_data() {
    local test_host="$1"
    local test_port="$2"
    local test_db="$3"

    log_info "Verifying MySQL data integrity..."

    if $DRY_RUN; then
        log_info "[DRY RUN] Would verify data with queries"
        return 0
    fi

    if [[ ${#DATA_QUERIES[@]} -eq 0 ]]; then
        log_info "Running basic data checks..."

        if command -v mysql &> /dev/null; then
            log_info "Checking table count..."
            log_info "Checking schema integrity..."
        fi

        log_success "Basic MySQL verification passed"
        return 0
    fi

    # Run custom queries
    for query in "${DATA_QUERIES[@]}"; do
        log_info "Executing query: $query"

        if command -v mysql &> /dev/null; then
            if timeout 30 mysql -h "$test_host" -P "$test_port" -D "$test_db" -e "$query" > /dev/null 2>&1; then
                log_success "Query executed successfully"
            else
                log_error "Query failed: $query"
                return 4
            fi
        else
            log_warning "Cannot execute query (mysql not found)"
        fi
    done

    return 0
}

verify_mongodb_data() {
    local test_host="$1"
    local test_port="$2"

    log_info "Verifying MongoDB data integrity..."

    if $DRY_RUN; then
        log_info "[DRY RUN] Would verify MongoDB collections"
        return 0
    fi

    if command -v mongosh &> /dev/null || command -v mongo &> /dev/null; then
        log_info "Checking MongoDB collections..."
        log_info "Verifying document counts..."
        log_success "Basic MongoDB verification passed"
    else
        log_warning "Cannot verify MongoDB data (client not found)"
    fi

    return 0
}

################################################################################
# Cleanup
################################################################################

cleanup_test_environment() {
    local db_type="$1"
    local test_host="$2"
    local test_port="$3"
    local test_db="$4"

    log_info "Cleaning up test environment..."

    if $DRY_RUN; then
        log_info "[DRY RUN] Would remove test database: $test_db"
        return 0
    fi

    case "$db_type" in
        postgresql)
            log_info "Dropping PostgreSQL test database..."
            ;;
        mysql)
            log_info "Dropping MySQL test database..."
            ;;
        mongodb)
            log_info "Dropping MongoDB test database..."
            ;;
    esac

    log_success "Test environment cleaned up"
    return 0
}

################################################################################
# Main Function
################################################################################

main() {
    # Parse command line arguments
    while [[ $# -gt 0 ]]; do
        case $1 in
            -t|--db-type)
                DB_TYPE="$2"
                shift 2
                ;;
            -b|--backup-path)
                BACKUP_PATH="$2"
                shift 2
                ;;
            --test-db-host)
                TEST_DB_HOST="$2"
                shift 2
                ;;
            --test-db-port)
                TEST_DB_PORT="$2"
                shift 2
                ;;
            --test-db-name)
                TEST_DB_NAME="$2"
                shift 2
                ;;
            --test-db-user)
                TEST_DB_USER="$2"
                shift 2
                ;;
            --test-db-password)
                TEST_DB_PASSWORD="$2"
                shift 2
                ;;
            --test-container)
                TEST_CONTAINER="$2"
                shift 2
                ;;
            --verify-data)
                VERIFY_DATA=true
                shift
                ;;
            --data-query)
                DATA_QUERIES+=("$2")
                shift 2
                ;;
            --cleanup)
                CLEANUP_AFTER=true
                shift
                ;;
            --timeout)
                TIMEOUT="$2"
                shift 2
                ;;
            -v|--verbose)
                VERBOSE=true
                shift
                ;;
            -d|--dry-run)
                DRY_RUN=true
                shift
                ;;
            -h|--help)
                print_usage
                ;;
            *)
                log_error "Unknown option: $1"
                print_usage
                ;;
        esac
    done

    # Validate arguments and set defaults
    validate_arguments
    set_default_ports "$DB_TYPE"

    # Display configuration
    log_info "Recovery Testing Script"
    log_info "Database type: $DB_TYPE"
    log_info "Backup path: $BACKUP_PATH"
    log_info "Test location: $TEST_DB_HOST:$TEST_DB_PORT"
    log_info "Test database: $TEST_DB_NAME"
    [[ $VERIFY_DATA == true ]] && log_info "Data verification: enabled"
    [[ $CLEANUP_AFTER == true ]] && log_info "Cleanup after test: enabled"
    [[ $DRY_RUN == true ]] && log_info "Dry run mode enabled"
    echo ""

    # Get backup statistics
    get_backup_stats "$BACKUP_PATH"
    echo ""

    # Setup test environment
    case "$DB_TYPE" in
        postgresql)
            setup_postgresql_test_env || exit 3
            ;;
        mysql)
            setup_mysql_test_env || exit 3
            ;;
        mongodb)
            setup_mongodb_test_env || exit 3
            ;;
    esac

    # Restore backup
    case "$DB_TYPE" in
        postgresql)
            restore_postgresql_backup "$BACKUP_PATH" "$TEST_DB_HOST" "$TEST_DB_PORT" "$TEST_DB_NAME" || exit 1
            ;;
        mysql)
            restore_mysql_backup "$BACKUP_PATH" "$TEST_DB_HOST" "$TEST_DB_PORT" "$TEST_DB_NAME" || exit 1
            ;;
        mongodb)
            restore_mongodb_backup "$BACKUP_PATH" "$TEST_DB_HOST" "$TEST_DB_PORT" || exit 1
            ;;
    esac

    # Verify data if requested
    if [[ $VERIFY_DATA == true ]]; then
        case "$DB_TYPE" in
            postgresql)
                verify_postgresql_data "$TEST_DB_HOST" "$TEST_DB_PORT" "$TEST_DB_NAME" || exit 4
                ;;
            mysql)
                verify_mysql_data "$TEST_DB_HOST" "$TEST_DB_PORT" "$TEST_DB_NAME" || exit 4
                ;;
            mongodb)
                verify_mongodb_data "$TEST_DB_HOST" "$TEST_DB_PORT" || exit 4
                ;;
        esac
    fi

    # Cleanup if requested
    if [[ $CLEANUP_AFTER == true ]]; then
        cleanup_test_environment "$DB_TYPE" "$TEST_DB_HOST" "$TEST_DB_PORT" "$TEST_DB_NAME"
    fi

    # Final summary
    echo ""
    log_success "Recovery testing completed successfully!"
    echo ""
    echo "Summary:"
    echo "  Database type: $DB_TYPE"
    echo "  Backup file: $BACKUP_PATH"
    echo "  Test result: PASSED"
    echo "  Backup status: RECOVERABLE"
    [[ $CLEANUP_AFTER == false ]] && echo "  Test data: Available at $TEST_DB_HOST:$TEST_DB_PORT/$TEST_DB_NAME"
    echo ""

    exit 0
}

# Execute main function
main "$@"
deprecation-and-migrationaddyosmani/agent-skillsManages deprecation and migration. Use when removing old systems, APIs, or features. Use when migrating users from one implementation to another. Use when migrating a database schema in production, such as renaming or dropping a column without downtime (expand/contract). Use when deciding whether to maintain or sunset existing code.Stars: 119kUpdated Claude CodeCodexVerifiedInstall
performance-optimizationaddyosmani/agent-skillsOptimizes application performance across frontend, backend, queries, and databases. Use when performance requirements exist, when you suspect performance regressions, when Core Web Vitals or load times need improvement, when N+1 query patterns need fixing, or when profiling reveals bottlenecks.Stars: 119kUpdated Claude CodeCodexVerifiedInstall
agent-cost-reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json.Stars: 118kUpdated Claude CodeCodexVerifiedInstall
agent-cost-reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json.Stars: 118kUpdated Claude CodeCodexVerifiedInstall