jeremylongshore/tons-of-skills-marketplace / plugins/database/database-backup-automator/skills/automating-database-backups
automating-database-backups
FlaggedAutomate 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'.
| File | Kind | Size |
|---|---|---|
| SKILL.md | skill md | 5.8 KB |
| assets/README.md | markdown | 64 B |
| config/settings.yaml | config | 1.3 KB |
| references/README.md | markdown | 606 B |
| references/backup_best_practices.md | markdown | 7.1 KB |
| references/cron_syntax.md | markdown | 5.7 KB |
| references/mongodb_backup_restore.md | markdown | 5.5 KB |
| references/mysql_backup_restore.md | markdown | 5.4 KB |
| references/postgresql_backup_restore.md | markdown | 4.7 KB |
| references/sqlite_backup_restore.md | markdown | 6.8 KB |
| scripts/README.md | markdown | 495 B |
| scripts/backup-monitoring.shexecutable | script | 927 B |
| scripts/backup_scheduler.pyexecutable | script | 10.2 KB |
| scripts/backup_script_generator.pyexecutable | script | 12.1 KB |
| scripts/backup_validator.pyexecutable | script | 14.3 KB |
| scripts/restore_script_generator.pyexecutable | script | 15.4 KB |
references/backup_best_practices.md
View on GitHub# Database Backup Best Practices
## The 3-2-1 Backup Rule
- **3** copies of data
- **2** different storage media
- **1** offsite location
```
Primary Database
│
├──► Local Backup (NAS/SAN)
│
├──► Remote Backup (S3/GCS)
│
└──► Archive (Tape/Glacier)
```
## Backup Types
### Full Backup
Complete copy of entire database.
| Pros | Cons |
|------|------|
| Simple to restore | Large storage |
| Self-contained | Long backup time |
| No dependencies | Network bandwidth |
### Incremental Backup
Only changes since last backup (any type).
| Pros | Cons |
|------|------|
| Fast backup | Complex restore (need all incrementals) |
| Small size | Single corrupt file breaks chain |
| Less I/O | More restore time |
### Differential Backup
Changes since last full backup.
| Pros | Cons |
|------|------|
| Simpler than incremental | Grows larger over time |
| Only need full + latest diff | Still requires full backup |
| Faster restore than incremental | More storage than incremental |
## Recommended Strategy
```
Week 1:
Sun: Full backup (retain 4 weeks)
Mon-Sat: Daily incremental
Monthly:
1st: Monthly full (retain 12 months)
Yearly:
Jan 1: Annual archive (retain 7 years)
```
## Security Best Practices
### Encryption
**At Rest:**
```bash
# GPG encryption
pg_dump mydb | gzip | gpg --symmetric --cipher-algo AES256 -o backup.sql.gz.gpg
# OpenSSL encryption
pg_dump mydb | gzip | openssl enc -aes-256-cbc -salt -pbkdf2 -out backup.sql.gz.enc
```
**In Transit:**
```bash
# Use SSL/TLS for remote connections
pg_dump "host=remote.server.com sslmode=require" ...
```
### Key Management
1. **Never** store encryption keys with backups
2. Use separate key storage (HashiCorp Vault, AWS KMS, GCP KMS)
3. Rotate keys periodically
4. Document key recovery procedure
```bash
# Example: Using AWS KMS
aws kms encrypt --key-id alias/backup-key \
--plaintext fileb://backup.key \
--output text --query CiphertextBlob > backup.key.encrypted
```
### Access Control
```sql
-- PostgreSQL: Dedicated backup user
CREATE USER backup_user WITH PASSWORD 'secure_password';
GRANT CONNECT ON DATABASE mydb TO backup_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO backup_user;
GRANT USAGE ON SCHEMA public TO backup_user;
-- MySQL: Backup-only privileges
CREATE USER 'backup'@'localhost' IDENTIFIED BY 'secure_password';
GRANT SELECT, SHOW VIEW, TRIGGER, LOCK TABLES ON *.* TO 'backup'@'localhost';
```
## Testing and Validation
### Regular Restore Testing
```bash
#!/bin/bash
# Monthly restore test
TEST_DB="restore_test_$(date +%Y%m)"
# Create test database
createdb "$TEST_DB"
# Restore latest backup
pg_restore -d "$TEST_DB" /backup/latest.dump
# Run validation queries
psql -d "$TEST_DB" -c "SELECT COUNT(*) FROM users;"
# Compare row counts with production
PROD_COUNT=$(psql -d production -t -c "SELECT COUNT(*) FROM users;")
TEST_COUNT=$(psql -d "$TEST_DB" -t -c "SELECT COUNT(*) FROM users;")
if [ "$PROD_COUNT" -eq "$TEST_COUNT" ]; then
echo "Restore validation PASSED"
else
echo "Restore validation FAILED: $PROD_COUNT vs $TEST_COUNT"
fi
# Cleanup
dropdb "$TEST_DB"
```
### Checksum Verification
```bash
# Create checksum during backup
pg_dump mydb | tee backup.sql | sha256sum > backup.sql.sha256
# Verify before restore
sha256sum -c backup.sql.sha256
```
## Monitoring and Alerting
### Key Metrics
| Metric | Alert Threshold |
|--------|-----------------|
| Backup duration | > 150% of average |
| Backup size | < 50% or > 150% of expected |
| Storage usage | > 80% capacity |
| Time since last backup | > configured interval |
| Restore test age | > 30 days |
### Backup Monitoring Script
```bash
#!/bin/bash
# backup_monitor.sh
BACKUP_DIR="/var/backups/postgresql"
MAX_AGE_HOURS=25 # Alert if backup older than this
MIN_SIZE_MB=100 # Alert if backup smaller than this
# Check latest backup age
LATEST=$(find "$BACKUP_DIR" -name "*.dump" -type f -printf '%T@\n' | sort -n | tail -1)
AGE_HOURS=$(( ($(date +%s) - ${LATEST%.*}) / 3600 ))
if [ "$AGE_HOURS" -gt "$MAX_AGE_HOURS" ]; then
echo "ALERT: Last backup is $AGE_HOURS hours old"
# Send alert (email, Slack, PagerDuty)
fi
# Check backup size
SIZE_MB=$(du -m "$BACKUP_DIR"/*.dump 2>/dev/null | tail -1 | cut -f1)
if [ "$SIZE_MB" -lt "$MIN_SIZE_MB" ]; then
echo "ALERT: Backup size ($SIZE_MB MB) below threshold ($MIN_SIZE_MB MB)"
fi
# Check disk space
USAGE=$(df "$BACKUP_DIR" | tail -1 | awk '{print $5}' | tr -d '%')
if [ "$USAGE" -gt 80 ]; then
echo "ALERT: Backup disk usage at $USAGE%"
fi
```
## Cloud Storage Integration
### AWS S3
```bash
# Upload to S3
aws s3 cp /backup/database.dump s3://my-backups/postgresql/
# With lifecycle policy for automatic transitions
# Configure in S3: Move to Glacier after 30 days, delete after 365
# Sync backup directory
aws s3 sync /backup/ s3://my-backups/postgresql/ --delete
```
### Google Cloud Storage
```bash
# Upload to GCS
gsutil cp /backup/database.dump gs://my-backups/postgresql/
# With retention policy
gsutil retention set 365d gs://my-backups/postgresql/
```
### Azure Blob Storage
```bash
# Upload to Azure
az storage blob upload \
--container-name backups \
--name postgresql/database.dump \
--file /backup/database.dump
```
## Retention Policies
### Example Policy
```yaml
retention:
hourly:
keep: 24
path: /backup/hourly
daily:
keep: 7
path: /backup/daily
weekly:
keep: 4
path: /backup/weekly
monthly:
keep: 12
path: /backup/monthly
yearly:
keep: 7
path: /backup/yearly
```
### Cleanup Script
```bash
#!/bin/bash
# cleanup_backups.sh
# Daily: Keep 7 days
find /backup/daily -name "*.dump" -mtime +7 -delete
# Weekly: Keep 4 weeks
find /backup/weekly -name "*.dump" -mtime +28 -delete
# Monthly: Keep 12 months
find /backup/monthly -name "*.dump" -mtime +365 -delete
# Log deleted files
echo "$(date): Cleanup completed" >> /var/log/backup_cleanup.log
```
## Disaster Recovery Checklist
### RTO/RPO Definition
| Tier | RPO | RTO | Example |
|------|-----|-----|---------|
| Critical | < 1 hour | < 15 min | Payment database |
| Important | < 4 hours | < 1 hour | User data |
| Standard | < 24 hours | < 4 hours | Logs, analytics |
| Archive | < 1 week | < 24 hours | Historical data |
### Recovery Procedure Document
```markdown
# Database Recovery Procedure
## Pre-Recovery Checklist
- [ ] Identify failure scope
- [ ] Notify stakeholders
- [ ] Prepare recovery environment
- [ ] Locate latest verified backup
## Recovery Steps
1. Stop dependent applications
2. Assess current database state
3. Select appropriate backup
4. Execute restore procedure
5. Verify data integrity
6. Test application connectivity
7. Resume operations
## Post-Recovery
- [ ] Document incident
- [ ] Update runbooks
- [ ] Review backup strategy
```
## Common Pitfalls
| Pitfall | Solution |
|---------|----------|
| Never tested restores | Monthly restore drills |
| Backups on same disk | Use separate storage/offsite |
| No encryption | Always encrypt sensitive data |
| No monitoring | Implement backup alerts |
| Incomplete backups | Include schema, procedures, permissions |
| No documentation | Maintain runbooks |
| Stale backups | Monitor backup freshness |
Related skills
| Skill | Stars | Updated | Works with | Status | Install |
|---|---|---|---|---|---|
| deprecation-and-migrationaddyosmani/agent-skillsManages deprecation and migration. Use when removing old systems, APIs, or features. Use when migrating users from one implementation to another. Use when migrating a database schema in production, such as renaming or dropping a column without downtime (expand/contract). Use when deciding whether to maintain or sunset existing code. | Stars: 119k | Updated | Claude CodeCodex | Verified | Install |
| performance-optimizationaddyosmani/agent-skillsOptimizes application performance across frontend, backend, queries, and databases. Use when performance requirements exist, when you suspect performance regressions, when Core Web Vitals or load times need improvement, when N+1 query patterns need fixing, or when profiling reveals bottlenecks. | Stars: 119k | Updated | Claude CodeCodex | Verified | Install |
| agent-cost-reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json. | Stars: 118k | Updated | Claude CodeCodex | Verified | Install |
| agent-cost-reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json. | Stars: 118k | Updated | Claude CodeCodex | Verified | Install |