automating-database-backups

# 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 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" \ --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 {baseDir}/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 {baseDir}/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 {baseDir}/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 {baseDir}/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 # 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 - `{baseDir}/references/postgresql_backup_restore.md` - PostgreSQL backup guide - `{baseDir}/references/mysql_backup_restore.md` - MySQL backup guide - `{baseDir}/references/mongodb_backup_restore.md` - MongoDB backup guide - `{baseDir}/references/sqlite_backup_restore.md` - SQLite backup guide - `{baseDir}/references/backup_best_practices.md` - Security and storage best practices - `{baseDir}/references/cron_syntax.md` - Cron scheduling reference