jeremylongshore/tons-of-skills-marketplace / plugins/database/database-backup-automator/skills/automating-database-backups
automating-database-backups
FlaggedAutomate database backup processes with scheduling, compression, and encryption. Supports PostgreSQL (pg_dump), MySQL (mysqldump), MongoDB (mongodump), and SQLite. Generates production-ready backup scripts with retention policies and restore procedures. Trigger: "automate database backups", "schedule backups", "create backup script", "disaster recovery". Use when working with automating database backups. Trigger with 'automating', 'database', 'backups'.
| File | Kind | Size |
|---|---|---|
| SKILL.md | skill md | 5.8 KB |
| assets/README.md | markdown | 64 B |
| config/settings.yaml | config | 1.3 KB |
| references/README.md | markdown | 606 B |
| references/backup_best_practices.md | markdown | 7.1 KB |
| references/cron_syntax.md | markdown | 5.7 KB |
| references/mongodb_backup_restore.md | markdown | 5.5 KB |
| references/mysql_backup_restore.md | markdown | 5.4 KB |
| references/postgresql_backup_restore.md | markdown | 4.7 KB |
| references/sqlite_backup_restore.md | markdown | 6.8 KB |
| scripts/README.md | markdown | 495 B |
| scripts/backup-monitoring.shexecutable | script | 927 B |
| scripts/backup_scheduler.pyexecutable | script | 10.2 KB |
| scripts/backup_script_generator.pyexecutable | script | 12.1 KB |
| scripts/backup_validator.pyexecutable | script | 14.3 KB |
| scripts/restore_script_generator.pyexecutable | script | 15.4 KB |
scripts/restore_script_generator.py
View on GitHub#!/usr/bin/env python3
"""
Restore Script Generator
Generates database restore scripts for PostgreSQL, MySQL, MongoDB, and SQLite.
Includes safety checks, verification, and rollback options.
Author: Jeremy Longshore <jeremy@intentsolutions.io>
Version: 2.0.0
License: MIT
"""
import argparse
import sys
from pathlib import Path
from datetime import datetime
from dataclasses import dataclass
@dataclass
class RestoreConfig:
"""Restore script configuration."""
db_type: str
database: str
host: str
port: int
user: str
backup_pattern: str
backup_dir: str
POSTGRESQL_RESTORE_TEMPLATE = """#!/bin/bash
# PostgreSQL Restore Script
# Generated: {timestamp}
# Database: {database}
set -euo pipefail
# Configuration
BACKUP_DIR="{backup_dir}"
DB_HOST="{host}"
DB_PORT="{port}"
DB_USER="{user}"
DB_NAME="{database}"
BACKUP_PATTERN="{backup_pattern}"
# Find backup file
if [ -n "${{1:-}}" ]; then
BACKUP_FILE="$1"
else
BACKUP_FILE=$(ls -t "$BACKUP_DIR"/$BACKUP_PATTERN 2>/dev/null | head -1)
fi
if [ -z "$BACKUP_FILE" ] || [ ! -f "$BACKUP_FILE" ]; then
echo "ERROR: Backup file not found"
echo "Usage: $0 [backup_file]"
echo "Or place backups matching '$BACKUP_PATTERN' in $BACKUP_DIR"
exit 1
fi
echo "=================================================="
echo "PostgreSQL Restore"
echo "=================================================="
echo "Backup: $BACKUP_FILE"
echo "Target: $DB_HOST:$DB_PORT/$DB_NAME"
echo "User: $DB_USER"
echo ""
# Verify backup file
echo "Verifying backup..."
if ! pg_restore --list "$BACKUP_FILE" > /dev/null 2>&1; then
echo "ERROR: Invalid backup file format"
exit 1
fi
echo "Backup verification: OK"
# Check if database exists
DB_EXISTS=$(PGPASSWORD="${{PGPASSWORD:-}}" psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -tAc "SELECT 1 FROM pg_database WHERE datname='$DB_NAME'" postgres 2>/dev/null || echo "0")
if [ "$DB_EXISTS" = "1" ]; then
echo ""
echo "WARNING: Database '$DB_NAME' already exists!"
echo "Options:"
echo " 1. Drop and recreate (DESTRUCTIVE)"
echo " 2. Restore to new database '${DB_NAME}_restored'"
echo " 3. Cancel"
read -p "Choose [1/2/3]: " CHOICE
case $CHOICE in
1)
echo "Dropping database $DB_NAME..."
PGPASSWORD="${{PGPASSWORD:-}}" dropdb -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" "$DB_NAME"
PGPASSWORD="${{PGPASSWORD:-}}" createdb -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" "$DB_NAME"
TARGET_DB="$DB_NAME"
;;
2)
TARGET_DB="${{DB_NAME}}_restored"
echo "Creating database $TARGET_DB..."
PGPASSWORD="${{PGPASSWORD:-}}" createdb -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" "$TARGET_DB" || true
;;
*)
echo "Cancelled"
exit 0
;;
esac
else
echo "Creating database $DB_NAME..."
PGPASSWORD="${{PGPASSWORD:-}}" createdb -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" "$DB_NAME"
TARGET_DB="$DB_NAME"
fi
# Perform restore
echo ""
echo "Restoring to $TARGET_DB..."
START_TIME=$(date +%s)
if PGPASSWORD="${{PGPASSWORD:-}}" pg_restore \\
-h "$DB_HOST" \\
-p "$DB_PORT" \\
-U "$DB_USER" \\
-d "$TARGET_DB" \\
--verbose \\
--no-owner \\
--no-privileges \\
"$BACKUP_FILE"; then
END_TIME=$(date +%s)
DURATION=$((END_TIME - START_TIME))
echo ""
echo "=================================================="
echo "Restore completed successfully!"
echo "Duration: $DURATION seconds"
echo "Database: $TARGET_DB"
echo "=================================================="
# Verify restore
echo ""
echo "Verification:"
PGPASSWORD="${{PGPASSWORD:-}}" psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$TARGET_DB" -c "\\dt" | head -20
else
echo "ERROR: Restore failed"
exit 1
fi
"""
MYSQL_RESTORE_TEMPLATE = """#!/bin/bash
# MySQL Restore Script
# Generated: {timestamp}
# Database: {database}
set -euo pipefail
# Configuration
BACKUP_DIR="{backup_dir}"
DB_HOST="{host}"
DB_PORT="{port}"
DB_USER="{user}"
DB_NAME="{database}"
BACKUP_PATTERN="{backup_pattern}"
# Find backup file
if [ -n "${{1:-}}" ]; then
BACKUP_FILE="$1"
else
BACKUP_FILE=$(ls -t "$BACKUP_DIR"/$BACKUP_PATTERN 2>/dev/null | head -1)
fi
if [ -z "$BACKUP_FILE" ] || [ ! -f "$BACKUP_FILE" ]; then
echo "ERROR: Backup file not found"
echo "Usage: $0 [backup_file]"
exit 1
fi
echo "=================================================="
echo "MySQL Restore"
echo "=================================================="
echo "Backup: $BACKUP_FILE"
echo "Target: $DB_HOST:$DB_PORT/$DB_NAME"
echo "User: $DB_USER"
echo ""
# Detect compression
if [[ "$BACKUP_FILE" == *.gz ]]; then
CAT_CMD="gunzip -c"
echo "Compression: gzip"
else
CAT_CMD="cat"
fi
# Check if database exists
DB_EXISTS=$(mysql -h "$DB_HOST" -P "$DB_PORT" -u "$DB_USER" -p"${{MYSQL_PWD:-}}" -e "SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME='$DB_NAME'" 2>/dev/null | grep -c "$DB_NAME" || echo "0")
if [ "$DB_EXISTS" -gt 0 ]; then
echo ""
echo "WARNING: Database '$DB_NAME' already exists!"
echo "Options:"
echo " 1. Drop and recreate (DESTRUCTIVE)"
echo " 2. Restore to new database '${DB_NAME}_restored'"
echo " 3. Cancel"
read -p "Choose [1/2/3]: " CHOICE
case $CHOICE in
1)
echo "Dropping database $DB_NAME..."
mysql -h "$DB_HOST" -P "$DB_PORT" -u "$DB_USER" -p"${{MYSQL_PWD:-}}" -e "DROP DATABASE $DB_NAME"
mysql -h "$DB_HOST" -P "$DB_PORT" -u "$DB_USER" -p"${{MYSQL_PWD:-}}" -e "CREATE DATABASE $DB_NAME"
TARGET_DB="$DB_NAME"
;;
2)
TARGET_DB="${{DB_NAME}}_restored"
echo "Creating database $TARGET_DB..."
mysql -h "$DB_HOST" -P "$DB_PORT" -u "$DB_USER" -p"${{MYSQL_PWD:-}}" -e "CREATE DATABASE IF NOT EXISTS $TARGET_DB"
;;
*)
echo "Cancelled"
exit 0
;;
esac
else
echo "Creating database $DB_NAME..."
mysql -h "$DB_HOST" -P "$DB_PORT" -u "$DB_USER" -p"${{MYSQL_PWD:-}}" -e "CREATE DATABASE $DB_NAME"
TARGET_DB="$DB_NAME"
fi
# Perform restore
echo ""
echo "Restoring to $TARGET_DB..."
START_TIME=$(date +%s)
if $CAT_CMD "$BACKUP_FILE" | mysql -h "$DB_HOST" -P "$DB_PORT" -u "$DB_USER" -p"${{MYSQL_PWD:-}}" "$TARGET_DB"; then
END_TIME=$(date +%s)
DURATION=$((END_TIME - START_TIME))
echo ""
echo "=================================================="
echo "Restore completed successfully!"
echo "Duration: $DURATION seconds"
echo "Database: $TARGET_DB"
echo "=================================================="
# Verify restore
echo ""
echo "Verification:"
mysql -h "$DB_HOST" -P "$DB_PORT" -u "$DB_USER" -p"${{MYSQL_PWD:-}}" -e "SHOW TABLES" "$TARGET_DB" | head -20
else
echo "ERROR: Restore failed"
exit 1
fi
"""
MONGODB_RESTORE_TEMPLATE = """#!/bin/bash
# MongoDB Restore Script
# Generated: {timestamp}
# Database: {database}
set -euo pipefail
# Configuration
BACKUP_DIR="{backup_dir}"
MONGO_URI="${{MONGO_URI:-mongodb://{host}:{port}}}"
DB_NAME="{database}"
BACKUP_PATTERN="{backup_pattern}"
# Find backup
if [ -n "${{1:-}}" ]; then
BACKUP_PATH="$1"
else
BACKUP_PATH=$(ls -td "$BACKUP_DIR"/$BACKUP_PATTERN 2>/dev/null | head -1)
fi
if [ -z "$BACKUP_PATH" ]; then
echo "ERROR: Backup not found"
echo "Usage: $0 [backup_path]"
exit 1
fi
echo "=================================================="
echo "MongoDB Restore"
echo "=================================================="
echo "Backup: $BACKUP_PATH"
echo "Target: $MONGO_URI/$DB_NAME"
echo ""
# Detect backup format
RESTORE_ARGS=""
if [ -f "$BACKUP_PATH" ]; then
if [[ "$BACKUP_PATH" == *.tar ]]; then
echo "Extracting archive..."
EXTRACT_DIR=$(mktemp -d)
tar -xf "$BACKUP_PATH" -C "$EXTRACT_DIR"
BACKUP_PATH="$EXTRACT_DIR"
RESTORE_ARGS="--gzip"
elif [[ "$BACKUP_PATH" == *.gz ]]; then
RESTORE_ARGS="--gzip --archive=$BACKUP_PATH"
fi
elif [ -d "$BACKUP_PATH" ]; then
# Check if it contains gzipped bson
if ls "$BACKUP_PATH"/*/*.bson.gz > /dev/null 2>&1; then
RESTORE_ARGS="--gzip"
fi
fi
# Check if database exists
DB_EXISTS=$(mongosh "$MONGO_URI" --quiet --eval "db.getMongo().getDBNames().includes('$DB_NAME')" 2>/dev/null || echo "false")
if [ "$DB_EXISTS" = "true" ]; then
echo ""
echo "WARNING: Database '$DB_NAME' already exists!"
echo "Options:"
echo " 1. Drop and restore (DESTRUCTIVE)"
echo " 2. Restore to new database '${DB_NAME}_restored'"
echo " 3. Cancel"
read -p "Choose [1/2/3]: " CHOICE
case $CHOICE in
1)
TARGET_DB="$DB_NAME"
RESTORE_ARGS="$RESTORE_ARGS --drop"
;;
2)
TARGET_DB="${{DB_NAME}}_restored"
;;
*)
echo "Cancelled"
exit 0
;;
esac
else
TARGET_DB="$DB_NAME"
fi
# Perform restore
echo ""
echo "Restoring to $TARGET_DB..."
START_TIME=$(date +%s)
if mongorestore --uri="$MONGO_URI" \\
--db="$TARGET_DB" \\
--nsFrom="$DB_NAME.*" \\
--nsTo="$TARGET_DB.*" \\
$RESTORE_ARGS \\
"$BACKUP_PATH/$DB_NAME" 2>&1; then
END_TIME=$(date +%s)
DURATION=$((END_TIME - START_TIME))
echo ""
echo "=================================================="
echo "Restore completed successfully!"
echo "Duration: $DURATION seconds"
echo "Database: $TARGET_DB"
echo "=================================================="
# Verify restore
echo ""
echo "Verification:"
mongosh "$MONGO_URI/$TARGET_DB" --quiet --eval "db.getCollectionNames()"
else
echo "ERROR: Restore failed"
exit 1
fi
# Cleanup temp directory
[ -n "${{EXTRACT_DIR:-}}" ] && rm -rf "$EXTRACT_DIR"
"""
SQLITE_RESTORE_TEMPLATE = """#!/bin/bash
# SQLite Restore Script
# Generated: {timestamp}
# Database: {database}
set -euo pipefail
# Configuration
BACKUP_DIR="{backup_dir}"
DB_PATH="{database}"
BACKUP_PATTERN="{backup_pattern}"
# Find backup file
if [ -n "${{1:-}}" ]; then
BACKUP_FILE="$1"
else
BACKUP_FILE=$(ls -t "$BACKUP_DIR"/$BACKUP_PATTERN 2>/dev/null | head -1)
fi
if [ -z "$BACKUP_FILE" ] || [ ! -f "$BACKUP_FILE" ]; then
echo "ERROR: Backup file not found"
echo "Usage: $0 [backup_file]"
exit 1
fi
echo "=================================================="
echo "SQLite Restore"
echo "=================================================="
echo "Backup: $BACKUP_FILE"
echo "Target: $DB_PATH"
echo ""
# Decompress if needed
RESTORE_FILE="$BACKUP_FILE"
if [[ "$BACKUP_FILE" == *.gz ]]; then
echo "Decompressing backup..."
TEMP_FILE=$(mktemp)
gunzip -c "$BACKUP_FILE" > "$TEMP_FILE"
RESTORE_FILE="$TEMP_FILE"
fi
# Verify backup
echo "Verifying backup..."
if ! sqlite3 "$RESTORE_FILE" "PRAGMA integrity_check;" | grep -q "^ok$"; then
echo "ERROR: Backup integrity check failed"
[ -n "${{TEMP_FILE:-}}" ] && rm -f "$TEMP_FILE"
exit 1
fi
echo "Backup verification: OK"
# Check if database exists
if [ -f "$DB_PATH" ]; then
echo ""
echo "WARNING: Database '$DB_PATH' already exists!"
echo "Options:"
echo " 1. Replace existing (DESTRUCTIVE)"
echo " 2. Restore to '${{DB_PATH}}.restored'"
echo " 3. Cancel"
read -p "Choose [1/2/3]: " CHOICE
case $CHOICE in
1)
TARGET_PATH="$DB_PATH"
# Backup existing
echo "Backing up existing database..."
cp "$DB_PATH" "${{DB_PATH}}.pre_restore.$(date +%Y%m%d_%H%M%S)"
;;
2)
TARGET_PATH="${{DB_PATH}}.restored"
;;
*)
echo "Cancelled"
[ -n "${{TEMP_FILE:-}}" ] && rm -f "$TEMP_FILE"
exit 0
;;
esac
else
TARGET_PATH="$DB_PATH"
fi
# Perform restore
echo ""
echo "Restoring to $TARGET_PATH..."
START_TIME=$(date +%s)
if cp "$RESTORE_FILE" "$TARGET_PATH"; then
END_TIME=$(date +%s)
DURATION=$((END_TIME - START_TIME))
# Verify restored database
if sqlite3 "$TARGET_PATH" "PRAGMA integrity_check;" | grep -q "^ok$"; then
echo ""
echo "=================================================="
echo "Restore completed successfully!"
echo "Duration: $DURATION seconds"
echo "Database: $TARGET_PATH"
echo "=================================================="
# Show stats
echo ""
echo "Verification:"
sqlite3 "$TARGET_PATH" "SELECT 'Tables: ' || COUNT(*) FROM sqlite_master WHERE type='table';"
sqlite3 "$TARGET_PATH" ".tables" | head -5
else
echo "ERROR: Restored database failed integrity check"
exit 1
fi
else
echo "ERROR: Restore failed"
exit 1
fi
# Cleanup
[ -n "${{TEMP_FILE:-}}" ] && rm -f "$TEMP_FILE"
"""
def generate_restore_script(config: RestoreConfig) -> str:
"""Generate restore script based on database type."""
templates = {
"postgresql": POSTGRESQL_RESTORE_TEMPLATE,
"mysql": MYSQL_RESTORE_TEMPLATE,
"mongodb": MONGODB_RESTORE_TEMPLATE,
"sqlite": SQLITE_RESTORE_TEMPLATE,
}
template = templates.get(config.db_type)
if not template:
raise ValueError(f"Unknown database type: {config.db_type}")
return template.format(
timestamp=datetime.now().isoformat(),
database=config.database,
host=config.host,
port=config.port,
user=config.user,
backup_dir=config.backup_dir,
backup_pattern=config.backup_pattern,
)
def main():
parser = argparse.ArgumentParser(
description="Generate database restore scripts for PostgreSQL, MySQL, MongoDB, or SQLite"
)
parser.add_argument(
"--db-type", "-t", required=True, choices=["postgresql", "mysql", "mongodb", "sqlite"], help="Database type"
)
parser.add_argument("--database", "-d", required=True, help="Database name (or path for SQLite)")
parser.add_argument("--host", "-H", default="localhost", help="Database host (default: localhost)")
parser.add_argument("--port", "-P", type=int, help="Database port (default: depends on db type)")
parser.add_argument("--user", "-u", default="root", help="Database user (default: root)")
parser.add_argument("--backup-dir", "-b", default="/var/backups", help="Backup directory (default: /var/backups)")
parser.add_argument("--backup-pattern", "-p", help="Backup file pattern (default: depends on db type)")
parser.add_argument("--output", "-o", help="Output file (default: stdout)")
args = parser.parse_args()
# Default ports
default_ports = {
"postgresql": 5432,
"mysql": 3306,
"mongodb": 27017,
"sqlite": 0,
}
# Default backup patterns
default_patterns = {
"postgresql": "*.dump",
"mysql": "*.sql*",
"mongodb": "*_*",
"sqlite": "*.db*",
}
port = args.port or default_ports[args.db_type]
backup_pattern = args.backup_pattern or default_patterns[args.db_type]
config = RestoreConfig(
db_type=args.db_type,
database=args.database,
host=args.host,
port=port,
user=args.user,
backup_dir=args.backup_dir,
backup_pattern=backup_pattern,
)
script = generate_restore_script(config)
if args.output:
output_path = Path(args.output)
output_path.write_text(script)
output_path.chmod(0o755)
print(f"Generated restore script: {output_path}")
else:
print(script)
return 0
if __name__ == "__main__":
sys.exit(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 |