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