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_script_generator.py
View on GitHub#!/usr/bin/env python3
"""
Backup Script Generator
Generates production-ready backup scripts for PostgreSQL, MySQL, MongoDB, and SQLite.
Includes compression, encryption, and retention policy support.
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 typing import Optional
from dataclasses import dataclass
@dataclass
class BackupConfig:
"""Backup script configuration."""
db_type: str
database: str
host: str
port: int
user: str
backup_dir: str
compression: str
encryption: Optional[str]
retention_days: int
POSTGRESQL_TEMPLATE = """#!/bin/bash
# PostgreSQL Backup 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}"
RETENTION_DAYS={retention_days}
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE="${{BACKUP_DIR}}/${{DB_NAME}}_${{DATE}}.dump"
LOG_FILE="${{BACKUP_DIR}}/backup.log"
# Ensure backup directory exists
mkdir -p "$BACKUP_DIR"
log() {{
echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" | tee -a "$LOG_FILE"
}}
log "Starting PostgreSQL backup of $DB_NAME"
# Perform backup
if PGPASSWORD="${{PGPASSWORD:-}}" pg_dump -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" \\
--format=custom \\
--compress=9 \\
--verbose \\
--file="$BACKUP_FILE" 2>> "$LOG_FILE"; then
SIZE=$(du -h "$BACKUP_FILE" | cut -f1)
log "Backup completed: $BACKUP_FILE ($SIZE)"
# Verify backup
if pg_restore --list "$BACKUP_FILE" > /dev/null 2>&1; then
log "Backup verification passed"
else
log "ERROR: Backup verification failed"
exit 1
fi
{encryption_block}
else
log "ERROR: Backup failed"
exit 1
fi
# Cleanup old backups
log "Cleaning backups older than $RETENTION_DAYS days"
find "$BACKUP_DIR" -name "*.dump*" -mtime "+$RETENTION_DAYS" -delete
log "Backup process completed"
"""
MYSQL_TEMPLATE = """#!/bin/bash
# MySQL Backup 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}"
RETENTION_DAYS={retention_days}
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE="${{BACKUP_DIR}}/${{DB_NAME}}_${{DATE}}.sql{compression_ext}"
LOG_FILE="${{BACKUP_DIR}}/backup.log"
mkdir -p "$BACKUP_DIR"
log() {{
echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" | tee -a "$LOG_FILE"
}}
log "Starting MySQL backup of $DB_NAME"
# Perform backup
if mysqldump -h "$DB_HOST" -P "$DB_PORT" -u "$DB_USER" -p"${{MYSQL_PWD:-}}" \\
--single-transaction \\
--routines \\
--triggers \\
--events \\
--quick \\
"$DB_NAME" 2>> "$LOG_FILE" {compression_pipe} > "$BACKUP_FILE"; then
SIZE=$(du -h "$BACKUP_FILE" | cut -f1)
log "Backup completed: $BACKUP_FILE ($SIZE)"
# Verify backup
if {verify_command}; then
log "Backup verification passed"
else
log "ERROR: Backup verification failed"
exit 1
fi
{encryption_block}
else
log "ERROR: Backup failed"
rm -f "$BACKUP_FILE"
exit 1
fi
# Cleanup old backups
log "Cleaning backups older than $RETENTION_DAYS days"
find "$BACKUP_DIR" -name "*.sql*" -mtime "+$RETENTION_DAYS" -delete
log "Backup process completed"
"""
MONGODB_TEMPLATE = """#!/bin/bash
# MongoDB Backup Script
# Generated: {timestamp}
# Database: {database}
set -euo pipefail
# Configuration
BACKUP_DIR="{backup_dir}"
MONGO_URI="${{MONGO_URI:-mongodb://{host}:{port}}}"
DB_NAME="{database}"
RETENTION_DAYS={retention_days}
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_PATH="${{BACKUP_DIR}}/${{DB_NAME}}_${{DATE}}"
LOG_FILE="${{BACKUP_DIR}}/backup.log"
mkdir -p "$BACKUP_DIR"
log() {{
echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" | tee -a "$LOG_FILE"
}}
log "Starting MongoDB backup of $DB_NAME"
# Perform backup
if mongodump --uri="$MONGO_URI" \\
--db="$DB_NAME" \\
{compression_flag} \\
--out="$BACKUP_PATH" 2>> "$LOG_FILE"; then
SIZE=$(du -sh "$BACKUP_PATH" | cut -f1)
log "Backup completed: $BACKUP_PATH ($SIZE)"
# Verify backup
BSON_COUNT=$(find "$BACKUP_PATH" -name "*.bson*" | wc -l)
if [ "$BSON_COUNT" -gt 0 ]; then
log "Backup verification passed ($BSON_COUNT collections)"
else
log "WARNING: No BSON files found"
fi
# Create tar archive
log "Creating archive..."
tar -cf "${{BACKUP_PATH}}.tar" -C "$BACKUP_DIR" "${{DB_NAME}}_${{DATE}}"
rm -rf "$BACKUP_PATH"
log "Archive created: ${{BACKUP_PATH}}.tar"
{encryption_block}
else
log "ERROR: Backup failed"
rm -rf "$BACKUP_PATH"
exit 1
fi
# Cleanup old backups
log "Cleaning backups older than $RETENTION_DAYS days"
find "$BACKUP_DIR" -name "*.tar*" -mtime "+$RETENTION_DAYS" -delete
log "Backup process completed"
"""
SQLITE_TEMPLATE = """#!/bin/bash
# SQLite Backup Script
# Generated: {timestamp}
# Database: {database}
set -euo pipefail
# Configuration
DB_PATH="{database}"
BACKUP_DIR="{backup_dir}"
RETENTION_DAYS={retention_days}
DB_NAME=$(basename "$DB_PATH" .db)
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE="${{BACKUP_DIR}}/${{DB_NAME}}_${{DATE}}.db"
LOG_FILE="${{BACKUP_DIR}}/backup.log"
mkdir -p "$BACKUP_DIR"
log() {{
echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" | tee -a "$LOG_FILE"
}}
log "Starting SQLite backup of $DB_PATH"
# Check database exists
if [ ! -f "$DB_PATH" ]; then
log "ERROR: Database not found: $DB_PATH"
exit 1
fi
# Check integrity before backup
if ! sqlite3 "$DB_PATH" "PRAGMA integrity_check;" | grep -q "^ok$"; then
log "WARNING: Database integrity check failed"
fi
# Perform backup using online backup API
if sqlite3 "$DB_PATH" ".backup '$BACKUP_FILE'" 2>> "$LOG_FILE"; then
SIZE=$(du -h "$BACKUP_FILE" | cut -f1)
log "Backup completed: $BACKUP_FILE ($SIZE)"
# Verify backup
if sqlite3 "$BACKUP_FILE" "PRAGMA integrity_check;" | grep -q "^ok$"; then
log "Backup verification passed"
else
log "ERROR: Backup verification failed"
exit 1
fi
# Compress backup
{compression_command}
{encryption_block}
else
log "ERROR: Backup failed"
exit 1
fi
# Cleanup old backups
log "Cleaning backups older than $RETENTION_DAYS days"
find "$BACKUP_DIR" -name "*.db*" -mtime "+$RETENTION_DAYS" -delete
log "Backup process completed"
"""
def get_encryption_block(encryption: Optional[str], backup_var: str = "BACKUP_FILE") -> str:
"""Generate encryption block for backup script."""
if not encryption:
return ""
if encryption == "gpg":
return f"""
# Encrypt backup with GPG
log "Encrypting backup..."
if gpg --symmetric --cipher-algo AES256 --batch --passphrase-file /etc/backup.key "${{{backup_var}}}"; then
rm "${{{backup_var}}}"
log "Encryption completed: ${{{backup_var}}}.gpg"
else
log "ERROR: Encryption failed"
exit 1
fi"""
elif encryption == "openssl":
return f"""
# Encrypt backup with OpenSSL
log "Encrypting backup..."
if openssl enc -aes-256-cbc -salt -pbkdf2 -in "${{{backup_var}}}" -out "${{{backup_var}}}.enc" -pass file:/etc/backup.key; then
rm "${{{backup_var}}}"
log "Encryption completed: ${{{backup_var}}}.enc"
else
log "ERROR: Encryption failed"
exit 1
fi"""
return ""
def generate_postgresql_script(config: BackupConfig) -> str:
"""Generate PostgreSQL backup script."""
return POSTGRESQL_TEMPLATE.format(
timestamp=datetime.now().isoformat(),
database=config.database,
host=config.host,
port=config.port,
user=config.user,
backup_dir=config.backup_dir,
retention_days=config.retention_days,
encryption_block=get_encryption_block(config.encryption),
)
def generate_mysql_script(config: BackupConfig) -> str:
"""Generate MySQL backup script."""
compression_pipe = "| gzip" if config.compression == "gzip" else ""
compression_ext = ".gz" if config.compression == "gzip" else ""
verify_command = (
'gunzip -t "$BACKUP_FILE"' if config.compression == "gzip" else 'head -1 "$BACKUP_FILE" | grep -q "MySQL dump"'
)
return MYSQL_TEMPLATE.format(
timestamp=datetime.now().isoformat(),
database=config.database,
host=config.host,
port=config.port,
user=config.user,
backup_dir=config.backup_dir,
retention_days=config.retention_days,
compression_pipe=compression_pipe,
compression_ext=compression_ext,
verify_command=verify_command,
encryption_block=get_encryption_block(config.encryption),
)
def generate_mongodb_script(config: BackupConfig) -> str:
"""Generate MongoDB backup script."""
compression_flag = "--gzip" if config.compression == "gzip" else ""
return MONGODB_TEMPLATE.format(
timestamp=datetime.now().isoformat(),
database=config.database,
host=config.host,
port=config.port,
backup_dir=config.backup_dir,
retention_days=config.retention_days,
compression_flag=compression_flag,
encryption_block=get_encryption_block(config.encryption, "BACKUP_PATH.tar"),
)
def generate_sqlite_script(config: BackupConfig) -> str:
"""Generate SQLite backup script."""
if config.compression == "gzip":
compression_command = '''gzip "$BACKUP_FILE"
log "Compressed to: ${BACKUP_FILE}.gz"'''
else:
compression_command = "# No compression"
return SQLITE_TEMPLATE.format(
timestamp=datetime.now().isoformat(),
database=config.database,
backup_dir=config.backup_dir,
retention_days=config.retention_days,
compression_command=compression_command,
encryption_block=get_encryption_block(config.encryption),
)
def main():
parser = argparse.ArgumentParser(
description="Generate database backup 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(
"--compression", "-c", choices=["none", "gzip"], default="gzip", help="Compression type (default: gzip)"
)
parser.add_argument(
"--encryption", "-e", choices=["none", "gpg", "openssl"], default="none", help="Encryption type (default: none)"
)
parser.add_argument("--retention", "-r", type=int, default=7, help="Retention days (default: 7)")
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,
}
port = args.port or default_ports[args.db_type]
config = BackupConfig(
db_type=args.db_type,
database=args.database,
host=args.host,
port=port,
user=args.user,
backup_dir=args.backup_dir,
compression=args.compression,
encryption=None if args.encryption == "none" else args.encryption,
retention_days=args.retention,
)
generators = {
"postgresql": generate_postgresql_script,
"mysql": generate_mysql_script,
"mongodb": generate_mongodb_script,
"sqlite": generate_sqlite_script,
}
script = generators[args.db_type](config)
if args.output:
output_path = Path(args.output)
output_path.write_text(script)
output_path.chmod(0o755)
print(f"Generated backup 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 |