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/backup_validator.py
View on GitHub#!/usr/bin/env python3
"""
Backup Validator
Validates the integrity of database backup files.
Supports PostgreSQL, MySQL, MongoDB, and SQLite backup formats.
Author: Jeremy Longshore <jeremy@intentsolutions.io>
Version: 2.0.0
License: MIT
"""
import argparse
import gzip
import hashlib
import subprocess
import sys
from pathlib import Path
from dataclasses import dataclass
from typing import Optional, Tuple
@dataclass
class ValidationResult:
"""Result of backup validation."""
valid: bool
file_path: str
file_size: int
db_type: str
compressed: bool
encrypted: bool
checksum: str
errors: list
warnings: list
details: dict
def detect_file_type(file_path: Path) -> Tuple[str, bool, bool]:
"""Detect database type, compression, and encryption from file."""
name = file_path.name.lower()
suffixes = file_path.suffixes
encrypted = ".gpg" in suffixes or ".enc" in suffixes
compressed = ".gz" in suffixes or ".tar" in suffixes
# Remove encryption/compression suffixes for base detection
base_name = name
for ext in [".gpg", ".enc", ".gz", ".tar"]:
base_name = base_name.replace(ext, "")
if base_name.endswith(".dump") or "postgresql" in base_name or "pg_" in base_name:
return "postgresql", compressed, encrypted
elif base_name.endswith(".sql") or "mysql" in base_name:
return "mysql", compressed, encrypted
elif base_name.endswith(".bson") or "mongodb" in base_name or "mongo" in base_name:
return "mongodb", compressed, encrypted
elif base_name.endswith(".db") or "sqlite" in base_name:
return "sqlite", compressed, encrypted
return "unknown", compressed, encrypted
def calculate_checksum(file_path: Path) -> str:
"""Calculate SHA256 checksum of file."""
sha256 = hashlib.sha256()
with open(file_path, "rb") as f:
for chunk in iter(lambda: f.read(8192), b""):
sha256.update(chunk)
return sha256.hexdigest()
def validate_postgresql(file_path: Path, compressed: bool) -> ValidationResult:
"""Validate PostgreSQL backup file."""
errors = []
warnings = []
details = {}
# For custom format (.dump), use pg_restore --list
try:
result = subprocess.run(["pg_restore", "--list", str(file_path)], capture_output=True, text=True, timeout=30)
if result.returncode == 0:
lines = result.stdout.strip().split("\n")
details["objects"] = len([l for l in lines if l.strip() and not l.startswith(";")])
details["format"] = "custom"
else:
# Try reading as SQL
if compressed:
with gzip.open(file_path, "rt", errors="ignore") as f:
header = f.read(1000)
else:
with open(file_path, "r", errors="ignore") as f:
header = f.read(1000)
if "PostgreSQL database dump" in header or "pg_dump" in header:
details["format"] = "plain_sql"
else:
errors.append("Could not verify PostgreSQL backup format")
except subprocess.TimeoutExpired:
warnings.append("Validation timed out - file may be very large")
except FileNotFoundError:
warnings.append("pg_restore not found - limited validation")
# Fallback: check file headers
if compressed:
try:
with gzip.open(file_path, "rt", errors="ignore") as f:
header = f.read(500)
if "PostgreSQL" in header or "pg_dump" in header:
details["format"] = "plain_sql_gzip"
except Exception:
errors.append("Could not read gzip file")
return ValidationResult(
valid=len(errors) == 0,
file_path=str(file_path),
file_size=file_path.stat().st_size,
db_type="postgresql",
compressed=compressed,
encrypted=False,
checksum=calculate_checksum(file_path),
errors=errors,
warnings=warnings,
details=details,
)
def validate_mysql(file_path: Path, compressed: bool) -> ValidationResult:
"""Validate MySQL backup file."""
errors = []
warnings = []
details = {}
try:
if compressed:
with gzip.open(file_path, "rt", errors="ignore") as f:
header = f.read(2000)
else:
with open(file_path, "r", errors="ignore") as f:
header = f.read(2000)
# Check for MySQL dump signature
if "MySQL dump" in header or "mysqldump" in header.lower():
details["format"] = "mysqldump"
# Try to extract version
for line in header.split("\n"):
if "Server version" in line:
details["server_version"] = line.split(":")[-1].strip()
break
# Count tables
if "CREATE TABLE" in header:
details["has_schema"] = True
if "INSERT INTO" in header:
details["has_data"] = True
else:
errors.append("File does not appear to be a MySQL dump")
except gzip.BadGzipFile:
errors.append("Invalid gzip format")
except Exception as e:
errors.append(f"Error reading file: {e}")
return ValidationResult(
valid=len(errors) == 0,
file_path=str(file_path),
file_size=file_path.stat().st_size,
db_type="mysql",
compressed=compressed,
encrypted=False,
checksum=calculate_checksum(file_path),
errors=errors,
warnings=warnings,
details=details,
)
def validate_mongodb(file_path: Path, compressed: bool) -> ValidationResult:
"""Validate MongoDB backup (directory or archive)."""
errors = []
warnings = []
details = {}
if file_path.is_dir():
# Directory format
bson_files = list(file_path.rglob("*.bson*"))
metadata_files = list(file_path.rglob("*.metadata.json*"))
if bson_files:
details["bson_files"] = len(bson_files)
details["collections"] = len(metadata_files)
details["format"] = "directory"
else:
errors.append("No BSON files found in backup directory")
elif file_path.suffix == ".tar":
# Tar archive
try:
result = subprocess.run(["tar", "-tf", str(file_path)], capture_output=True, text=True, timeout=30)
if result.returncode == 0:
files = result.stdout.strip().split("\n")
bson_count = len([f for f in files if ".bson" in f])
if bson_count > 0:
details["bson_files"] = bson_count
details["format"] = "tar_archive"
else:
errors.append("No BSON files in archive")
else:
errors.append("Could not read tar archive")
except Exception as e:
errors.append(f"Error reading archive: {e}")
else:
# Single BSON file
if file_path.stat().st_size > 0:
details["format"] = "single_bson"
else:
errors.append("Empty backup file")
return ValidationResult(
valid=len(errors) == 0,
file_path=str(file_path),
file_size=file_path.stat().st_size
if file_path.is_file()
else sum(f.stat().st_size for f in file_path.rglob("*") if f.is_file()),
db_type="mongodb",
compressed=compressed,
encrypted=False,
checksum=calculate_checksum(file_path) if file_path.is_file() else "N/A (directory)",
errors=errors,
warnings=warnings,
details=details,
)
def validate_sqlite(file_path: Path, compressed: bool) -> ValidationResult:
"""Validate SQLite backup file."""
errors = []
warnings = []
details = {}
# Decompress if needed
if compressed and file_path.suffix == ".gz":
# Read header from gzip
try:
with gzip.open(file_path, "rb") as f:
header = f.read(16)
except Exception as e:
errors.append(f"Could not read gzip file: {e}")
header = b""
else:
with open(file_path, "rb") as f:
header = f.read(16)
# SQLite magic bytes
if header.startswith(b"SQLite format 3"):
details["format"] = "sqlite3"
# If not compressed, run integrity check
if not compressed:
try:
result = subprocess.run(
["sqlite3", str(file_path), "PRAGMA integrity_check;"], capture_output=True, text=True, timeout=60
)
if result.returncode == 0 and "ok" in result.stdout.lower():
details["integrity_check"] = "passed"
else:
warnings.append(f"Integrity check: {result.stdout.strip()}")
details["integrity_check"] = "failed"
# Get table count
result = subprocess.run(
["sqlite3", str(file_path), "SELECT COUNT(*) FROM sqlite_master WHERE type='table';"],
capture_output=True,
text=True,
timeout=10,
)
if result.returncode == 0:
details["table_count"] = int(result.stdout.strip())
except FileNotFoundError:
warnings.append("sqlite3 not found - limited validation")
except subprocess.TimeoutExpired:
warnings.append("Validation timed out")
elif compressed:
warnings.append("Cannot fully validate compressed SQLite backup")
details["format"] = "sqlite3_compressed"
else:
errors.append("File does not appear to be SQLite format")
return ValidationResult(
valid=len(errors) == 0,
file_path=str(file_path),
file_size=file_path.stat().st_size,
db_type="sqlite",
compressed=compressed,
encrypted=False,
checksum=calculate_checksum(file_path),
errors=errors,
warnings=warnings,
details=details,
)
def validate_backup(file_path: Path, db_type: Optional[str] = None) -> ValidationResult:
"""Validate a backup file."""
if not file_path.exists():
return ValidationResult(
valid=False,
file_path=str(file_path),
file_size=0,
db_type="unknown",
compressed=False,
encrypted=False,
checksum="",
errors=[f"File not found: {file_path}"],
warnings=[],
details={},
)
# Detect type if not specified
detected_type, compressed, encrypted = detect_file_type(file_path)
if db_type:
detected_type = db_type
if encrypted:
return ValidationResult(
valid=True,
file_path=str(file_path),
file_size=file_path.stat().st_size,
db_type=detected_type,
compressed=compressed,
encrypted=True,
checksum=calculate_checksum(file_path),
errors=[],
warnings=["Encrypted backup - content validation skipped"],
details={"encrypted": True},
)
validators = {
"postgresql": validate_postgresql,
"mysql": validate_mysql,
"mongodb": validate_mongodb,
"sqlite": validate_sqlite,
}
if detected_type in validators:
result = validators[detected_type](file_path, compressed)
result.encrypted = encrypted
return result
return ValidationResult(
valid=False,
file_path=str(file_path),
file_size=file_path.stat().st_size,
db_type=detected_type,
compressed=compressed,
encrypted=encrypted,
checksum=calculate_checksum(file_path),
errors=[f"Unknown database type: {detected_type}"],
warnings=[],
details={},
)
def print_result(result: ValidationResult, verbose: bool = False) -> None:
"""Print validation result."""
status = "VALID" if result.valid else "INVALID"
print(f"\nBackup Validation: {status}")
print("=" * 50)
print(f"File: {result.file_path}")
print(f"Size: {result.file_size:,} bytes ({result.file_size / 1024 / 1024:.2f} MB)")
print(f"Type: {result.db_type}")
print(f"Compressed: {result.compressed}")
print(f"Encrypted: {result.encrypted}")
print(f"Checksum (SHA256): {result.checksum[:16]}...")
if result.details and verbose:
print("\nDetails:")
for key, value in result.details.items():
print(f" {key}: {value}")
if result.errors:
print("\nErrors:")
for error in result.errors:
print(f" - {error}")
if result.warnings:
print("\nWarnings:")
for warning in result.warnings:
print(f" - {warning}")
def main():
parser = argparse.ArgumentParser(description="Validate database backup file integrity")
parser.add_argument("backup_file", help="Backup file to validate")
parser.add_argument(
"--db-type",
"-t",
choices=["postgresql", "mysql", "mongodb", "sqlite"],
help="Database type (auto-detected if not specified)",
)
parser.add_argument("--verbose", "-v", action="store_true", help="Verbose output")
parser.add_argument("--checksum-file", "-c", help="Write checksum to file")
parser.add_argument("--json", action="store_true", help="Output as JSON")
args = parser.parse_args()
file_path = Path(args.backup_file)
result = validate_backup(file_path, args.db_type)
if args.json:
import json
print(
json.dumps(
{
"valid": result.valid,
"file_path": result.file_path,
"file_size": result.file_size,
"db_type": result.db_type,
"compressed": result.compressed,
"encrypted": result.encrypted,
"checksum": result.checksum,
"errors": result.errors,
"warnings": result.warnings,
"details": result.details,
},
indent=2,
)
)
else:
print_result(result, args.verbose)
if args.checksum_file:
Path(args.checksum_file).write_text(f"{result.checksum} {result.file_path}\n")
print(f"\nChecksum written to: {args.checksum_file}")
return 0 if result.valid else 1
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 |