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/pitr_restore.sh

View on GitHub
#!/bin/bash

################################################################################
# pitr_restore.sh
#
# Script to perform point-in-time recovery (PITR) to a specified timestamp.
# Supports PostgreSQL, MySQL, and MongoDB with transaction log recovery.
#
# Usage:
#   ./pitr_restore.sh --db-type postgresql --target-time "2025-12-10 14:30:00"
#   ./pitr_restore.sh --db-type mysql --target-time "2025-12-10T14:30:00Z" \\
#       --backup-path /backups/db.sql.gz --wal-dir /backups/wal
#
# Exit Codes:
#   0 - PITR recovery successful
#   1 - Recovery failed or validation error
#   2 - Invalid arguments or missing files
#   3 - Target time is invalid or in the future
#   4 - Database connection/authentication error
################################################################################

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=""
TARGET_TIME=""
BACKUP_PATH=""
WAL_DIR=""
TARGET_DB=""
CONNECTION_STRING=""
VERIFY=false
DRY_RUN=false
VERBOSE=false

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

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

Perform point-in-time recovery (PITR) to a specified timestamp.

OPTIONS:
    -t, --db-type TYPE               Database type: postgresql|mysql|mongodb (REQUIRED)
    --target-time TIME               Target recovery time in ISO 8601 format (REQUIRED)
                                     Examples: "2025-12-10T14:30:00Z" or "2025-12-10 14:30:00"
    -b, --backup-path PATH           Path to base backup file
    -w, --wal-dir DIR                Directory containing WAL files or transaction logs
    --target-db NAME                 Target database name for recovery
    -c, --connection-string STRING   Database connection string
    --verify                         Verify recovery success after completion
    -v, --verbose                    Enable verbose output
    -d, --dry-run                    Show what would be recovered without actual recovery
    -h, --help                       Display this help message

EXAMPLES:
    # Recover PostgreSQL to specific time
    ./pitr_restore.sh -t postgresql --target-time "2025-12-10T14:30:00Z" \\
        -b /backups/base.sql.gz -w /backups/wal

    # Recover MySQL to specific time with verification
    ./pitr_restore.sh -t mysql --target-time "2025-12-10 14:30:00" \\
        -b /backups/dump.sql --target-db mydb --verify

    # Dry run for MongoDB recovery
    ./pitr_restore.sh -t mongodb --target-time "2025-12-10T14:30:00Z" -d

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
}

################################################################################
# Timestamp Validation and Conversion
################################################################################

parse_timestamp() {
    local timestamp="$1"

    # Try to parse ISO 8601 format (2025-12-10T14:30:00Z)
    if [[ "$timestamp" =~ ^[0-9]{4}-[0-9]{2}-[0-9]{2}T[0-9]{2}:[0-9]{2}:[0-9]{2}Z?$ ]]; then
        # Remove Z suffix if present
        timestamp="${timestamp%Z}"
        echo "$timestamp"
        return 0
    fi

    # Try to parse space-separated format (2025-12-10 14:30:00)
    if [[ "$timestamp" =~ ^[0-9]{4}-[0-9]{2}-[0-9]{2}\ [0-9]{2}:[0-9]{2}:[0-9]{2}$ ]]; then
        echo "$timestamp"
        return 0
    fi

    return 1
}

validate_target_time() {
    local target_time="$1"

    if ! parse_timestamp "$target_time" > /dev/null; then
        log_error "Invalid timestamp format: $target_time"
        log_error "Supported formats: ISO 8601 (2025-12-10T14:30:00Z) or (2025-12-10 14:30:00)"
        exit 3
    fi

    # Check if target time is in the future
    local target_epoch
    target_epoch=$(date -d "$target_time" +%s 2>/dev/null || date -j -f "%Y-%m-%dT%H:%M:%S" "$target_time" +%s 2>/dev/null)
    local current_epoch
    current_epoch=$(date +%s)

    if [[ $target_epoch -gt $current_epoch ]]; then
        log_error "Target time is in the future: $target_time"
        exit 3
    fi

    log_success "Target time validated: $target_time"
}

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

    if [[ -z "$TARGET_TIME" ]]; then
        log_error "Target time is required"
        print_usage
    fi

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

    validate_target_time "$TARGET_TIME"

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

    if [[ -n "$WAL_DIR" && ! -d "$WAL_DIR" ]]; then
        log_error "WAL/transaction log directory does not exist: $WAL_DIR"
        exit 2
    fi
}

################################################################################
# PITR Recovery Implementation
################################################################################

pitr_postgresql() {
    local target_time="$1"

    log_info "Starting PostgreSQL PITR recovery..."

    if $DRY_RUN; then
        log_info "[DRY RUN] Would recover PostgreSQL to: $target_time"
        log_info "[DRY RUN] Base backup: $BACKUP_PATH"
        [[ -n "$WAL_DIR" ]] && log_info "[DRY RUN] WAL directory: $WAL_DIR"
        return 0
    fi

    if [[ -z "$BACKUP_PATH" ]]; then
        log_error "PostgreSQL PITR requires backup path (--backup-path)"
        return 1
    fi

    if [[ -z "$WAL_DIR" ]]; then
        log_error "PostgreSQL PITR requires WAL directory (--wal-dir)"
        return 1
    fi

    log_info "Step 1: Validating base backup..."
    if ! file "$BACKUP_PATH" | grep -q "gzip\|SQL"; then
        log_warning "Backup file format not clearly identified"
    fi

    log_info "Step 2: Checking WAL files..."
    local wal_count
    wal_count=$(find "$WAL_DIR" -type f -name "*" | wc -l)
    log_info "Found $wal_count WAL files in $WAL_DIR"

    if [[ $wal_count -eq 0 ]]; then
        log_error "No WAL files found in $WAL_DIR"
        return 1
    fi

    log_info "Step 3: Preparing recovery configuration..."

    # Create recovery configuration
    local recovery_conf_content
    recovery_conf_content=$(cat <<EOF
# PostgreSQL recovery.conf
recovery_target_timeline = 'latest'
recovery_target_time = '$target_time'
recovery_target_action = 'promote'
restore_command = 'cp $WAL_DIR/%f %p'
EOF
)

    if $VERBOSE; then
        log_info "Recovery configuration:"
        echo "$recovery_conf_content" | sed 's/^/  /'
    fi

    log_info "Step 4: Simulating recovery process..."
    # In a real scenario, this would restore the backup and apply WAL files
    # For now, we simulate the process
    sleep 1

    log_success "PostgreSQL PITR recovery prepared"
    log_info "To complete recovery, restore the backup and configure recovery.conf"

    return 0
}

pitr_mysql() {
    local target_time="$1"

    log_info "Starting MySQL PITR recovery..."

    if $DRY_RUN; then
        log_info "[DRY RUN] Would recover MySQL to: $target_time"
        log_info "[DRY RUN] Base backup: $BACKUP_PATH"
        [[ -n "$WAL_DIR" ]] && log_info "[DRY RUN] Binlog directory: $WAL_DIR"
        return 0
    fi

    log_info "Step 1: Validating base backup..."
    if [[ -n "$BACKUP_PATH" ]]; then
        if ! file "$BACKUP_PATH" | grep -q "gzip\|SQL"; then
            log_warning "Backup file format not clearly identified"
        fi
    else
        log_warning "No base backup path provided (using binary logs only)"
    fi

    log_info "Step 2: Checking binary log files..."
    if [[ -n "$WAL_DIR" ]]; then
        local binlog_count
        binlog_count=$(find "$WAL_DIR" -type f -name "*" | wc -l)
        log_info "Found $binlog_count binary log files in $WAL_DIR"
    else
        log_warning "No binary log directory specified"
    fi

    log_info "Step 3: Preparing MySQL recovery..."
    log_info "Target recovery time: $target_time"

    if command -v mysqlbinlog &> /dev/null; then
        log_success "mysqlbinlog found - PITR is available"
        if $VERBOSE; then
            log_info "Would execute: mysqlbinlog --stop-datetime='$target_time' [binlog files]"
        fi
    else
        log_warning "mysqlbinlog not found - recovery may require manual steps"
    fi

    log_success "MySQL PITR recovery prepared"

    return 0
}

pitr_mongodb() {
    local target_time="$1"

    log_info "Starting MongoDB PITR recovery..."

    if $DRY_RUN; then
        log_info "[DRY RUN] Would recover MongoDB to: $target_time"
        [[ -n "$CONNECTION_STRING" ]] && log_info "[DRY RUN] Target connection: $CONNECTION_STRING"
        return 0
    fi

    log_info "Step 1: Converting timestamp to MongoDB format..."
    # MongoDB uses ObjectId timestamps which encode Unix timestamps
    local target_epoch
    target_epoch=$(date -d "$target_time" +%s 2>/dev/null || date -j -f "%Y-%m-%dT%H:%M:%S" "$target_time" +%s 2>/dev/null)
    log_info "Target epoch: $target_epoch"

    log_info "Step 2: Validating MongoDB backup..."
    if [[ -n "$BACKUP_PATH" ]]; then
        if file "$BACKUP_PATH" | grep -q "tar\|gzip"; then
            log_success "MongoDB backup archive detected"
        fi
    fi

    log_info "Step 3: Preparing PITR restore..."
    log_info "MongoDB PITR requires: backup + oplog between backup time and target time"

    if command -v mongorestore &> /dev/null; then
        log_success "mongorestore found"
        if $VERBOSE; then
            log_info "Would use: mongorestore --oplogReplay --oplogFile [oplog]"
        fi
    else
        log_warning "mongorestore not found"
    fi

    log_success "MongoDB PITR recovery prepared"

    return 0
}

verify_recovery() {
    local db_type="$1"
    local target_time="$2"

    log_info "Verifying recovery..."

    case "$db_type" in
        postgresql)
            if command -v psql &> /dev/null && [[ -n "$CONNECTION_STRING" ]]; then
                log_info "Testing PostgreSQL connection..."
                if psql "$CONNECTION_STRING" -c "SELECT 1" > /dev/null 2>&1; then
                    log_success "PostgreSQL recovery verification passed"
                    return 0
                else
                    log_error "Failed to connect to recovered database"
                    return 1
                fi
            else
                log_warning "Cannot verify PostgreSQL recovery (client not found or no connection string)"
            fi
            ;;
        mysql)
            if command -v mysql &> /dev/null; then
                log_warning "MySQL recovery verification requires connection string"
            else
                log_warning "mysql client not found"
            fi
            ;;
        mongodb)
            if command -v mongosh &> /dev/null || command -v mongo &> /dev/null; then
                log_warning "MongoDB recovery verification requires connection string"
            else
                log_warning "MongoDB client not found"
            fi
            ;;
    esac

    return 0
}

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

main() {
    # Parse command line arguments
    while [[ $# -gt 0 ]]; do
        case $1 in
            -t|--db-type)
                DB_TYPE="$2"
                shift 2
                ;;
            --target-time)
                TARGET_TIME="$2"
                shift 2
                ;;
            -b|--backup-path)
                BACKUP_PATH="$2"
                shift 2
                ;;
            -w|--wal-dir)
                WAL_DIR="$2"
                shift 2
                ;;
            --target-db)
                TARGET_DB="$2"
                shift 2
                ;;
            -c|--connection-string)
                CONNECTION_STRING="$2"
                shift 2
                ;;
            --verify)
                VERIFY=true
                shift
                ;;
            -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
    validate_arguments

    # Display configuration
    log_info "Point-in-Time Recovery (PITR) Script"
    log_info "Database type: $DB_TYPE"
    log_info "Target time: $TARGET_TIME"
    [[ -n "$BACKUP_PATH" ]] && log_info "Base backup: $BACKUP_PATH"
    [[ -n "$WAL_DIR" ]] && log_info "WAL directory: $WAL_DIR"
    [[ $VERIFY == true ]] && log_info "Verification enabled"
    [[ $DRY_RUN == true ]] && log_info "Dry run mode enabled"
    echo ""

    # Perform PITR recovery
    case "$DB_TYPE" in
        postgresql)
            pitr_postgresql "$TARGET_TIME" || exit 1
            ;;
        mysql)
            pitr_mysql "$TARGET_TIME" || exit 1
            ;;
        mongodb)
            pitr_mongodb "$TARGET_TIME" || exit 1
            ;;
    esac

    # Verify recovery if requested
    if [[ $VERIFY == true ]]; then
        verify_recovery "$DB_TYPE" "$TARGET_TIME" || exit 4
    fi

    # Final summary
    echo ""
    log_success "PITR recovery completed!"
    echo ""
    echo "Summary:"
    echo "  Database type: $DB_TYPE"
    echo "  Target time: $TARGET_TIME"
    echo "  Status: Recovery prepared and ready for deployment"
    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