Database Backup Automation

Generate production-ready backup scripts for PostgreSQL, MySQL, MongoDB, and SQLite with compression, encryption, scheduling, and retention policies.

Quick Start

PostgreSQL Backup

#!/bin/bash
set -euo pipefail
BACKUP_DIR="/var/backups/postgresql"
DB_NAME="mydb"
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE="${BACKUP_DIR}/${DB_NAME}_${DATE}.sql.gz"

pg_dump -h localhost -U postgres -d "$DB_NAME" \
  --format=custom \
  --compress=9 \
  --file="$BACKUP_FILE"

# Encrypt with GPG (optional)
gpg --symmetric --cipher-algo AES256 --batch --passphrase-file /etc/backup.key "$BACKUP_FILE"
rm "$BACKUP_FILE"

MySQL Backup

#!/bin/bash
BACKUP_DIR="/var/backups/mysql"
DB_NAME="mydb"
DATE=$(date +%Y%m%d_%H%M%S)

mysqldump -h localhost -u root -p"${MYSQL_PASSWORD}" \
  --single-transaction \
  --routines \
  --triggers \
  "$DB_NAME" | gzip > "${BACKUP_DIR}/${DB_NAME}_${DATE}.sql.gz"

MongoDB Backup

#!/bin/bash
mongodump --uri="mongodb://localhost:27017" \  # 27017: MongoDB port
  --db=mydb \
  --out=/var/backups/mongodb/$(date +%Y%m%d_%H%M%S) \
  --gzip

Instructions

Step 1: Gather Requirements

Ask the user for:

• Database type (PostgreSQL, MySQL, MongoDB, SQLite)
• Database connection details (host, port, database name)
• Backup schedule (cron expression or frequency)
• Retention policy (days to keep)
• Encryption requirement (yes/no)
• Backup destination (local path, S3, GCS)

Step 2: Generate Backup Script

Use scripts/backupscriptgenerator.py to create a customized backup script:

python3 ${CLAUDE_SKILL_DIR}/scripts/backup_script_generator.py \
  --db-type postgresql \
  --database mydb \
  --output /opt/backup-scripts/mydb-backup.sh \
  --compression gzip \
  --encryption gpg

Step 3: Schedule with Cron

Use scripts/backup_scheduler.py to create cron entries:

python3 ${CLAUDE_SKILL_DIR}/scripts/backup_scheduler.py \
  --script /opt/backup-scripts/mydb-backup.sh \
  --schedule "0 2 * * *" \
  --user postgres

Step 4: Validate Backup

After backup completes, validate integrity:

python3 ${CLAUDE_SKILL_DIR}/scripts/backup_validator.py \
  --backup-file /var/backups/postgresql/mydb_20250115.sql.gz \
  --db-type postgresql

Step 5: Generate Restore Procedure

Create matching restore script:

python3 ${CLAUDE_SKILL_DIR}/scripts/restore_script_generator.py \
  --db-type postgresql \
  --database mydb \
  --outp