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

#!/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

#!/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" \
  --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:

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:

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:

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:

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

# 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