TL;DR
Backups are your last line of defense against data loss. These prompts help you set up automated backups, configure retention policies, and test restoration procedures. A backup you haven't tested is not a backup.
Automated Backup Scripts
Create an automated backup script for PostgreSQL.
Requirements:
- Daily full backups
- Compress backups with gzip
- Upload to S3/R2/cloud storage
- Retention: 7 daily, 4 weekly, 12 monthly
- Notification on failure (email/Slack)
Include:
- Backup script with pg_dump
- Cron job configuration
- Upload to cloud storage
- Cleanup script for old backups
- Verification that backup completed
Environment:
- Database: local/RDS/Supabase/Neon
- Storage: AWS S3/Cloudflare R2/GCS
- Server: Linux/Docker
Handle credentials securely via environment variables.
Managed Database Backups
Configure backups for my managed database.
Platform: Supabase/PlanetScale/Neon/Railway/Render
Help me:
- Understand included backup features
- Configure backup retention period
- Set up point-in-time recovery if available
- Export backups to my own storage
- Document restoration procedure
For Supabase specifically:
- Enable and configure daily backups
- Set up PITR (Point-in-Time Recovery)
- Export to external S3 bucket
For PlanetScale:
- Understand branch-based backup model
- Create production branch protection
- Document restore process
Create a runbook for restoration in emergency.
Test Restoration
Create a backup testing and verification process.
Test procedure should:
- Download latest backup
- Restore to a test database
- Run verification queries
- Compare row counts to production
- Report results
Verification queries:
- Count rows in critical tables
- Check for recent data (last 24h records exist)
- Verify indexes are present
- Test a sample query performance
Schedule:
- Weekly automated restore test
- Monthly manual verification
- Document time-to-restore metrics
Output a report showing:
- Backup size and date
- Restoration time
- Verification results
- Any warnings or issues
Untested backups aren't backups: Many teams discover their backups are corrupted or incomplete only when they need them. Test your restoration process regularly and document the steps.
Disaster Recovery Plan
Create a disaster recovery plan for my database.
Scenarios to plan for:
- Accidental data deletion
- Database corruption
- Provider outage
- Security breach requiring restore
- Complete infrastructure loss
For each scenario, document:
- Detection method
- Immediate response steps
- Restoration procedure
- Verification steps
- Communication template
Define:
- RPO (Recovery Point Objective): max data loss acceptable
- RTO (Recovery Time Objective): max downtime acceptable
Current setup:
- Database: type and provider
- Backup frequency: current
- Backup location: where stored
Create a step-by-step runbook for each scenario.
Pro tip: Store backups in a different region and different cloud provider than your primary database. If AWS us-east-1 goes down, your backups shouldn't be there too.
How often should I backup my database?
Depends on your RPO. Daily is minimum for most apps. High-traffic apps might need continuous WAL archiving or PITR. Consider how much data you can afford to lose.
Should I encrypt my backups?
Yes. Backups contain all your data including sensitive information. Encrypt at rest (storage-level) and in transit. Many cloud storage options encrypt by default.