DJP
|
e0128d98b8
|
Add automated PostgreSQL backup and restore system
Implements dual backup strategy with daily SQL dumps and weekly binary
backups, complete with restore capabilities and health monitoring.
Backup System Components:
1. database/backup.sh:
- Daily mode: pg_dump SQL dumps (7-day retention)
- Weekly mode: pg_basebackup binary backup (latest only)
- Automatic cleanup of old backups
- Compression (gzip) for space efficiency
- Email notifications on failures
- Docker-compatible execution
2. database/restore.sh:
- Restore from SQL dump backups
- Safety backup before restore
- Confirmation prompts
- Validation and verification
- List available backups
3. database/check_backups.sh:
- Health check monitoring
- Verifies latest backup age (warns if > 25 hours)
- Displays backup counts and sizes
- Quiet mode for cron automation
- Lists all available backups
Documentation:
- DATABASE_BACKUP_GUIDE.md: Complete backup/restore guide
- Automated cron setup
- Manual backup procedures
- Restore scenarios
- Troubleshooting
- Disk space management
- backups/README.md: Quick reference
- Directory structure
- Common commands
- Retention policy
- Security notes
Configuration:
- Updated .gitignore to exclude backup files
- Backup locations: backups/dumps/, backups/basebackups/
- Logs: logs/backup.log, logs/restore.log
- Retention: 7 daily dumps + 1 weekly basebackup
Cron Schedule (Production):
- Daily: 2:00 AM (pg_dump)
- Weekly: Sundays 3:00 AM (pg_basebackup)
- Health Check: 8:00 AM daily
Features:
✅ Automated daily and weekly backups
✅ Dual strategy (logical + physical)
✅ Space-efficient (7-day retention, ~50 MB total)
✅ Safety backups before restore
✅ Email alerts on failures
✅ Health monitoring
✅ Docker-compatible
✅ Tested locally
Testing Performed:
- Daily backup created successfully (77K compressed)
- Backup file integrity verified (gzip test passed)
- Health check shows "Backup system healthy"
- Restore --list command working
- All scripts executable and functional
Disk Usage Estimate:
- Daily dumps: 7 × ~2 MB = ~14 MB
- Weekly backup: 1 × ~30 MB = ~30 MB
- Total: ~50 MB maximum
🤖 Generated with [Claude Code](https://claude.com/claude-code)
Co-Authored-By: Claude <noreply@anthropic.com>
|
2025-11-11 17:30:10 -05:00 |
|