jeremylongshore/tons-of-skills-marketplace / plugins/database/database-recovery-manager/skills/managing-database-recovery
managing-database-recovery
VerifiedProcess 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".
| File | Kind | Size |
|---|---|---|
| SKILL.md | skill md | 7.1 KB |
| assets/README.md | markdown | 155 B |
| references/README.md | markdown | 68 B |
| scripts/README.md | markdown | 1.9 KB |
| scripts/failover.shexecutable | script | 19.7 KB |
| scripts/pitr_restore.shexecutable | script | 13.7 KB |
| scripts/test_recovery.shexecutable | script | 18.9 KB |
| scripts/validate_backup.shexecutable | script | 10.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 "$@"
Related skills
| Skill | Stars | Updated | Works with | Status | Install |
|---|---|---|---|---|---|
| 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: 119k | Updated | Claude CodeCodex | Verified | Install |
| 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: 119k | Updated | Claude CodeCodex | Verified | Install |
| 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: 118k | Updated | Claude CodeCodex | Verified | Install |
| 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: 118k | Updated | Claude CodeCodex | Verified | Install |