Backups & Recovery
RTO, RPO, automated database backups to S3, file backups, and how to actually test your recovery plan
Skills taught: deployment.backups, deployment.rto-rpo, deployment.postgres-backup, deployment.s3-backup, deployment.disaster-recovery
RTO and RPO — the two metrics that matter
- Recovery Time Objective (RTO)
- How long can your app be down after a failure before it causes unacceptable business impact? An RTO of 4 hours means you have 4 hours to restore service. This drives your disaster recovery architecture.
- Recovery Point Objective (RPO)
- How much data can you afford to lose? An RPO of 1 hour means your backups must be at most 1 hour old. If your database backup runs every 6 hours and the server dies, you've lost up to 6 hours of data.
PERSONAL PROJECT / PORTFOLIO:
RTO: 24 hours (few users, can tolerate downtime)
RPO: 24 hours (losing 1 day of data is acceptable)
Strategy: Daily backups, restore from backup manually
SMALL SAAS / STARTUP:
RTO: 4 hours
RPO: 1 hour
Strategy: Hourly backups to S3, standby plan, documented runbook
E-COMMERCE:
RTO: 30 minutes
RPO: 15 minutes
Strategy: Managed PostgreSQL with continuous backups (AWS RDS, PlanetScale),
read replicas, documented recovery procedure
FINANCIAL / HEALTHCARE:
RTO: 5 minutes
RPO: 0 (no data loss)
Strategy: Multi-region with synchronous replication, automatic failover
KEY INSIGHT:
The more aggressive your RTO/RPO, the higher the cost.
A $7/month VPS with daily backups might be fine for your use case.
A $1000/month multi-region RDS setup is overkill for a personal project.
Automated PostgreSQL backups to S3
#!/bin/bash
set -euo pipefail
# Configuration (set as environment variables on the server)
DB_HOST=${DB_HOST:-localhost}
DB_PORT=${DB_PORT:-5432}
DB_NAME=${DB_NAME:-myapp}
DB_USER=${DB_USER:-postgres}
S3_BUCKET=${S3_BUCKET:-myapp-backups}
RETENTION_DAYS=${RETENTION_DAYS:-30}
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE="/tmp/${DB_NAME}_${TIMESTAMP}.sql.gz"
echo "Starting backup of ${DB_NAME} at ${TIMESTAMP}"
# Dump database and compress
pg_dump \
--host=$DB_HOST \
--port=$DB_PORT \
--username=$DB_USER \
--no-password \
--format=custom \
$DB_NAME | gzip > $BACKUP_FILE
# Upload to S3
aws s3 cp $BACKUP_FILE \
s3://${S3_BUCKET}/postgres/${DB_NAME}_${TIMESTAMP}.sql.gz \
--storage-class STANDARD_IA # Infrequent Access = cheaper for backups
# Remove local temp file
rm $BACKUP_FILE
# Delete old backups (S3 lifecycle policy is better, but this works too)
aws s3 ls s3://${S3_BUCKET}/postgres/ | \
awk '{print $4}' | \
while read file; do
file_date=$(echo $file | grep -oP '\d{8}')
cutoff=$(date -d "-${RETENTION_DAYS} days" +%Y%m%d)
if [[ "$file_date" < "$cutoff" ]]; then
aws s3 rm s3://${S3_BUCKET}/postgres/$file
echo "Deleted old backup: $file"
fi
done
echo "Backup complete: s3://${S3_BUCKET}/postgres/${DB_NAME}_${TIMESTAMP}.sql.gz"
# Add to crontab: crontab -e
# Run backup every hour at minute 0
0 * * * * PGPASSWORD=$DB_PASSWORD /home/deploy/scripts/backup.sh >> /home/deploy/logs/backup.log 2>&1
# Or via systemd timer (more robust — retry on failure):
# Create /etc/systemd/system/db-backup.service
# Create /etc/systemd/system/db-backup.timer
# Restore from backup:
TIMESTAMP="20261001_120000" # replace with actual backup timestamp
aws s3 cp \
s3://myapp-backups/postgres/myapp_${TIMESTAMP}.sql.gz \
/tmp/restore.sql.gz
gunzip /tmp/restore.sql.gz
# Restore to database
pg_restore \
--host=$DB_HOST \
--username=$DB_USER \
--dbname=$DB_NAME \
--verbose \
--clean \
--if-exists \
/tmp/restore.sql
rm /tmp/restore.sql
echo "Restore complete"
Managed database backups (easier)
AWS RDS:
Automated backups: daily snapshots + continuous transaction logs
Point-in-time recovery: restore to any second in the last 35 days
Manual snapshots: keep indefinitely
Multi-AZ: synchronous standby, automatic failover in < 60s
Cost: small markup on instance price; snapshots free for backup window
NEON (managed PostgreSQL):
Branch-based: every branch has instant snapshots
Point-in-time restore: 7 days on free tier, 30 days on paid
Branching is instant — great for testing migrations on a copy of prod
SUPABASE:
Daily backups on free tier (7-day retention)
Point-in-time recovery on Pro plan
RAILWAY / RENDER POSTGRESQL:
Daily backups included
Manual backup via CLI
FOR VPS + SELF-HOSTED POSTGRESQL:
Use the backup.sh script above
Consider pgBackRest or Barman for enterprise-grade backup management
Warning: Untested backups are not backups. Many teams have been devastated to discover that their 'backups' were corrupted, pointed at the wrong database, or required steps no one knew how to perform. Schedule a disaster recovery drill quarterly: actually restore from your backup into a test environment and verify data integrity. If you can't restore within your RTO, your backup strategy needs fixing. The worst time to discover a broken backup is during an actual emergency.
Frequently Asked Questions
How much does S3 backup storage cost?
With S3 Infrequent Access (--storage-class STANDARD_IA): $0.0125/GB/month. A typical PostgreSQL database backup compresses to 10-50% of original size. A 1GB database = ~200MB compressed backup. 30 daily backups + 12 monthly backups = ~7GB stored = ~$0.09/month. Even for 100GB databases, backup storage is inexpensive. The real cost is your time designing and maintaining the backup system — which is why managed database services with built-in backup are often worth the premium.