--- name: automating-database-backups description: | Automate 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'. allowed-tools: Read, Write, Edit, Grep, Glob, Bash(pg_dump:*), Bash(mysqldump:*), Bash(mongodump:*), Bash(cron:*), Bash(gpg:*) version: 2.0.0 author: Jeremy Longshore license: MIT compatible-with: claude-code, codex, openclaw tags: [database, postgresql, mysql, mongodb] --- # 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 ```bash #!/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 ```bash #!/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 ```bash #!/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/backup_script_generator.py` to create a customized backup script: ```bash 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: ```bash 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: ```bash 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: ```bash python3 ${CLAUDE_SKILL_DIR}/scripts/restore_script_generator.py \ --db-type postgresql \ --database mydb \ --output /opt/backup-scripts/mydb-restore.sh ``` ## Cron Schedule Reference | Schedule | Cron Expression | Description | |----------|-----------------|-------------| | Daily 2 AM | `0 2 * * *` | Low-traffic window | | Every 6 hours | `0 */6 * * *` | Frequent backups | | Weekly Sunday | `0 2 * * 0` | Weekly full backup | | Monthly 1st | `0 2 1 * *` | Monthly archive | ## Retention Policy Example ```bash # Keep daily backups for 7 days # Keep weekly backups for 4 weeks # Keep monthly backups for 12 months find /var/backups -name "*.gz" -mtime +7 -delete # Daily cleanup find /var/backups/weekly -mtime +28 -delete # Weekly cleanup find /var/backups/monthly -mtime +365 -delete # 365: Monthly cleanup ``` ## Output - **Backup Scripts**: Database-specific shell scripts with compression and encryption - **Cron Entries**: Ready-to-install crontab configurations - **Restore Scripts**: Matching restore procedures for each backup type - **Validation Reports**: Integrity check results for backup files ## Error Handling | Error | Cause | Solution | |-------|-------|----------| | Connection refused | DB not running | Check service status: `systemctl status postgresql` | | Permission denied | Wrong credentials | Verify user has backup privileges | | Disk full | No space | Check space: `df -h`, clean old backups | | Lock timeout | Active transactions | Use `--single-transaction` for MySQL | ## Resources - `${CLAUDE_SKILL_DIR}/references/postgresql_backup_restore.md` - PostgreSQL backup guide - `${CLAUDE_SKILL_DIR}/references/mysql_backup_restore.md` - MySQL backup guide - `${CLAUDE_SKILL_DIR}/references/mongodb_backup_restore.md` - MongoDB backup guide - `${CLAUDE_SKILL_DIR}/references/sqlite_backup_restore.md` - SQLite backup guide - `${CLAUDE_SKILL_DIR}/references/backup_best_practices.md` - Security and storage best practices - `${CLAUDE_SKILL_DIR}/references/cron_syntax.md` - Cron scheduling reference ## Overview Automate database backup processes with scheduling, compression, and encryption. ## Prerequisites - Access to the PostgreSQL environment or API - Required CLI tools installed and authenticated - Familiarity with PostgreSQL concepts and terminology ## Examples **Basic usage**: Apply automating database backups to a standard project setup with default configuration options. **Advanced scenario**: Customize automating database backups for production environments with multiple constraints and team-specific requirements. --- Source attribution: Jeremy Longshore Original project: https://github.com/jeremylongshore/claude-code-plugins-plus-skills License: MIT License information: https://opensource.org/license/mit Original upstream instructions; downloaded from Dave AI. ## Upstream license notice MIT License Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions: The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software. THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE. # Dave AI resource metadata Creator / publisher: Jeremy Longshore Original project: https://github.com/jeremylongshore/claude-code-plugins-plus-skills Attribution basis: Original repository namespace preserved by package mirror Fields of work: Development & Engineering, Operations Roles: Backend Developer, Data Engineer, Database Administrator, DevOps / SRE, Operations Manager Specialties: Database Tools: MongoDB, MySQL, PostgreSQL, SQLite Country applicability: Country not specified Classification basis: Source catalog labels Instruction SHA-256: 238b1b8a91c567c59308675257edb300f7fefba5708e11fd545897e3161268d8 Metadata reviewed: 2026-10-27 This metadata supersedes earlier Dave AI catalog attribution. Original instruction and supporting files are preserved.