Database Backup and Disaster Recovery 2026 — Never Lose Data Again

Introduction

Why This Matters

The question is not if your database will have a problem — it is when. Hardware fails, operators make mistakes, and ransomware exists. The difference between a 30-minute incident and a business-ending disaster is having a tested backup and recovery plan before something goes wrong. This guide covers automated backups, point-in-time recovery, read replicas, and the runbooks that make recovery fast.

Define Your RTO and RPO First

Tier	Example	RTO	RPO
Tier 1	E-commerce, payments	<1 hour	<5 minutes
Tier 2	SaaS applications	<4 hours	<1 hour
Tier 3	Blogs, internal tools	<24 hours	<24 hours

RTO (Recovery Time Objective): maximum acceptable downtime. RPO (Recovery Point Objective): maximum acceptable data loss measured in time.

Define these with your business stakeholders before designing your backup strategy. They determine backup frequency, replication topology, and acceptable storage costs.

Automated PostgreSQL Backups to S3

#!/bin/bash
# backup-postgres.sh
set -euo pipefail
 
DB_URL="${DATABASE_URL}"
S3_BUCKET="myapp-backups"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE="postgres_${TIMESTAMP}.sql.gz"
 
log() { echo "[$(date +'%Y-%m-%d %H:%M:%S')] $*"; }
 
log "Starting backup..."
pg_dump "$DB_URL" | gzip > "/tmp/$BACKUP_FILE"
SIZE=$(du -sh "/tmp/$BACKUP_FILE" | cut -f1)
log "Backup created: $BACKUP_FILE ($SIZE)"
 
log "Uploading to S3..."
aws s3 cp "/tmp/$BACKUP_FILE" \
  "s3://$S3_BUCKET/postgres/$BACKUP_FILE" \
  --storage-class STANDARD_IA
 
aws s3 ls "s3://$S3_BUCKET/postgres/$BACKUP_FILE" \
  || { log "Upload verification failed!"; exit 1; }
 
rm "/tmp/$BACKUP_FILE"
log "Backup complete and verified."

Schedule it with GitHub Actions:

# .github/workflows/backup.yml
name: Database Backup
 
on:
  schedule:
    - cron: '0 0 * * *'   # Daily at midnight UTC
  workflow_dispatch:        # Allow manual trigger
 
jobs:
  backup:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - name: Run backup
        env:
          DATABASE_URL: ${{ secrets.DATABASE_URL }}
          AWS_ACCESS_KEY_ID: ${{ secrets.AWS_ACCESS_KEY_ID }}
          AWS_SECRET_ACCESS_KEY: ${{ secrets.AWS_SECRET_ACCESS_KEY }}
        run: bash scripts/backup-postgres.sh
      - name: Alert on failure
        if: failure()
        uses: slackapi/slack-github-action@v1
        with:
          channel-id: '#alerts'
          slack-message: 'DATABASE BACKUP FAILED — check GitHub Actions immediately'
        env:
          SLACK_BOT_TOKEN: ${{ secrets.SLACK_BOT_TOKEN }}

Point-in-Time Recovery (PITR)

# Enable WAL archiving in postgresql.conf
archive_mode = on
archive_command = 'aws s3 cp %p s3://myapp-backups/wal/%f'
archive_timeout = 60   # Force archive every 60 seconds
 
# Recovery procedure
# Step 1: Restore latest base backup
aws s3 sync s3://myapp-backups/base/latest/ /var/lib/postgresql/data/
 
# Step 2: Create recovery configuration
cat > /var/lib/postgresql/data/postgresql.auto.conf <<'CONF'
restore_command = 'aws s3 cp s3://myapp-backups/wal/%f %p'
recovery_target_time = '2026-03-26 14:30:00'
recovery_target_action = 'promote'
CONF
 
# Step 3: Start PostgreSQL — it replays WAL to the target time
pg_ctl start -D /var/lib/postgresql/data

Read Replicas and Routing

import { Pool } from 'pg'
 
const primaryPool = new Pool({
  host: process.env.DB_PRIMARY_HOST,
  max: 10,
  ssl: { rejectUnauthorized: true },
})
 
const replicaPool = new Pool({
  host: process.env.DB_REPLICA_HOST,
  max: 20,
  ssl: { rejectUnauthorized: true },
})
 
export class DatabaseService {
  // Read-only queries go to replica
  async query<T>(sql: string, params: unknown[]): Promise<T[]> {
    const { rows } = await replicaPool.query(sql, params)
    return rows as T[]
  }
 
  // Writes always go to primary
  async write<T>(sql: string, params: unknown[]): Promise<T> {
    const { rows } = await primaryPool.query(sql, params)
    return rows[0] as T
  }
 
  // Transactions always on primary
  async transaction<T>(fn: (client: any) => Promise<T>): Promise<T> {
    const client = await primaryPool.connect()
    try {
      await client.query('BEGIN')
      const result = await fn(client)
      await client.query('COMMIT')
      return result
    } catch (err) {
      await client.query('ROLLBACK')
      throw err
    } finally {
      client.release()
    }
  }
}

Weekly Backup Verification

#!/bin/bash
# verify-backup.sh — Test restore every week in CI
set -euo pipefail
 
BACKUP_URL="s3://myapp-backups/postgres/latest.sql.gz"
TEST_DB="backup_test_$(date +%Y%m%d)"
 
echo "Testing backup restore..."
 
aws s3 cp "$BACKUP_URL" /tmp/backup.sql.gz
createdb "$TEST_DB"
gunzip -c /tmp/backup.sql.gz | psql "$TEST_DB"
 
TABLES=$(psql "$TEST_DB" -t -c \
  "SELECT count(*) FROM information_schema.tables WHERE table_schema='public'")
USERS=$(psql  "$TEST_DB" -t -c "SELECT count(*) FROM users")
echo "Tables: $TABLES, Users: $USERS"
 
dropdb "$TEST_DB"
rm /tmp/backup.sql.gz
echo "Backup verification passed."

S3 Backup Lifecycle Policy

{
  "Rules": [{
    "Status": "Enabled",
    "Transitions": [
      { "Days": 30,  "StorageClass": "STANDARD_IA" },
      { "Days": 90,  "StorageClass": "GLACIER_IR" },
      { "Days": 365, "StorageClass": "DEEP_ARCHIVE" }
    ],
    "Expiration": { "Days": 2555 }
  }]
}

Disaster Recovery Runbook

## Database DR Runbook
 
### Scenario: Primary database unreachable
 
Step 1 — Assess (5 minutes)
- Check RDS status in AWS Console
- Check application logs for connection errors
- Confirm replica is healthy and caught up
 
Step 2 — Failover to replica (10 minutes)
- Promote replica:
  aws rds promote-read-replica \
    --db-instance-identifier myapp-replica
- Update DNS CNAME: db.myapp.com → replica endpoint
- Verify application reconnects
- Notify team on Slack
 
Step 3 — Restore from backup (if no replica)
- Identify latest S3 backup
- Provision new RDS instance
- Restore from snapshot
- Update DATABASE_URL in Secrets Manager
- Restart application
 
Step 4 — Post-incident
- Write blameless postmortem
- Create new replica for the promoted primary
- Review alert thresholds
- Update this runbook

Common Mistakes

Never testing restores — a backup that has never been restored is untested hope, not a backup strategy
Single backup destination — store backups in at least two AWS regions in case one region has an outage
No backup encryption — always encrypt backups at rest; S3 SSE-S3 is free and automatic
Overlooking WAL archiving — daily backups give daily RPO; WAL archiving gives sub-minute RPO for critical databases
No monitoring on backup jobs — silent failures are common; always alert on non-zero exit codes from backup scripts

Best Practices

Run verify-backup.sh weekly in a CI job that restores to a throwaway database and checks table counts
Use S3 Versioning on backup buckets to protect against accidental deletion of backup files
Test your DR runbook quarterly with a real failover drill — the drill reveals gaps that theory misses
Use RDS Multi-AZ for production databases — automatic failover in under 60 seconds without manual intervention
Tag backup S3 objects with the application name, environment, and date for easier lifecycle management

Key Takeaways

Define RTO and RPO with business stakeholders before designing backup infrastructure — they determine everything else
pg_dump | gzip | aws s3 cp is the simplest backup pipeline; run it daily and alert on failures
WAL archiving enables point-in-time recovery to any second, not just the last daily backup
Read replicas serve dual purpose: offload read traffic in normal operation, failover target in disaster recovery
S3 lifecycle policies automatically tier backups from Standard to Standard-IA to Glacier as they age, cutting storage costs by 80-90%
A backup that has never been restored is not verified; restore tests should be automated and run weekly
RDS Multi-AZ uses synchronous replication — the standby is always current, and failover takes 60 seconds automatically
The DR runbook should be runnable by any engineer on the team at 3 AM without consulting documentation

Introduction#

Why This Matters#

Define Your RTO and RPO First#

Automated PostgreSQL Backups to S3#

Point-in-Time Recovery (PITR)#

Read Replicas and Routing#

Weekly Backup Verification#

S3 Backup Lifecycle Policy#

Disaster Recovery Runbook#

Common Mistakes#

Best Practices#

Key Takeaways#

Related reading