Production Database Backup and Recovery: Automated Snapshots, Point-in-Time Restore, and Disaster Recovery Runbooks

The worst time to audit your backup strategy is while your database is gone. Most teams discover this the hard way: they SSH into the instance, find the latest backup, and learn it stopped running three weeks ago. Or the backup ran successfully but the restore process has never been tested and the documented steps reference a config that no longer exists. Or the backups exist, the restore works, but the process takes six hours and the SLA promised two.

Backup infrastructure gets set up once, checked off a list, and never revisited until it fails. The gap between “we have backups” and “we can recover from this” is wider than most teams realize.

This guide covers the full stack: backup types, RPO and RTO target selection, automated backup architecture for PostgreSQL with WAL-G and managed services, PITR mechanics, cross-region disaster recovery, and what a runbook needs to actually work under pressure.

Backup Types and What Each One Buys You

Full Snapshots

A full snapshot captures the entire database at a single point in time. pg_basebackup is the standard tool for PostgreSQL; managed services (RDS, Neon, Supabase) take configurable full snapshots automatically. Full snapshots are the foundation: a known-good state you can restore from without replaying transaction logs. The tradeoff is size. A 200 GB database produces a 200 GB snapshot. Taking them infrequently means more WAL replay after restore.

Incremental Backups

Incremental backups capture only blocks that changed since the last full or incremental backup. WAL-G supports this via --delta-from. The benefit is storage efficiency; the risk is chain integrity. A corrupted incremental breaks all subsequent restores from that base. Keep at least two full backups available at all times.

WAL Archiving (Continuous Archiving)

WAL archiving is what enables point-in-time recovery. PostgreSQL writes every transaction to the WAL before applying it. Archive WAL continuously to object storage and you can reconstruct any database state between the last full snapshot and the latest archived WAL segment. WAL archiving extends full snapshots; it does not replace them. Without a recent base, replaying WAL from a week-old snapshot can take hours.

Logical Dumps

pg_dump produces a portable SQL-level dump. It is useful for cross-version migrations and schema inspection, not as a primary recovery mechanism. Restoring a 100 GB logical dump is an order of magnitude slower than restoring a physical backup of the same size because every row must be re-inserted and indexed.

Backup Type Comparison

┌────────────────────┬──────────────────┬─────────────────┬──────────────────────────┐
│ Type               │ Restore Speed    │ Storage Cost    │ Use Case                 │
├────────────────────┼──────────────────┼─────────────────┼──────────────────────────┤
│ Full snapshot      │ Fast             │ High            │ Base for all restores    │
│ Incremental        │ Medium           │ Medium          │ Frequent checkpoint      │
│ WAL archiving      │ Variable         │ Low per segment │ PITR, narrow data loss   │
│ Logical dump       │ Slow             │ Variable        │ Cross-version migration  │
└────────────────────┴──────────────────┴─────────────────┴──────────────────────────┘

Setting RPO and RTO Targets That Are Actually Achievable

RPO (Recovery Point Objective) is the maximum acceptable data loss in time. RTO (Recovery Time Objective) is the maximum acceptable downtime. The common mistake is setting these targets based on what sounds reasonable to stakeholders, then discovering they are not achievable with the actual infrastructure.

RPO: Bounded by WAL archiving frequency. Continuous WAL archiving with WAL-G or a managed service achieves RPO of seconds to five minutes. Daily snapshots with no WAL archiving gives you an RPO of up to 24 hours. Know which one you have.

RTO: The sum of detection time, decision time, instance provisioning, snapshot restore, WAL replay, health checks, and traffic cutover. For a 100 GB database restoring from S3 to a new RDS instance in the same region, budget 30-90 minutes. Cross-region adds 20-40 minutes. Measure it during a drill. Do not estimate it.

Tiered targets: Transactional data (orders, payments) warrants a one-minute RPO. Analytics replicas can tolerate four hours. A single blanket SLA leads to over-engineering low-value data and under-engineering high-value data.

Automated Backup Architecture for PostgreSQL

Self-Managed: pg_basebackup and WAL-G

For teams running PostgreSQL on VMs or Kubernetes, WAL-G is the production standard for managed backup to object storage (S3, GCS, Azure Blob).

Install and configure wal_level = replica and archive_mode = on in postgresql.conf:

# postgresql.conf additions
wal_level = replica
archive_mode = on
archive_command = 'wal-g wal-push %p'
archive_timeout = 60

Take a full base backup:

# Run as the postgres user or with PGPASSWORD set
wal-g backup-push /var/lib/postgresql/data \
  --walg-s3-prefix s3://your-bucket/db-backups/postgres \
  --pghost localhost \
  --pgport 5432

Schedule incremental backups on top of the full base:

# cron: every 4 hours, incremental from latest full
0 */4 * * * wal-g backup-push /var/lib/postgresql/data --delta-from-user-data '{"type":"full"}'

WAL segments archive continuously via archive_command. Monitor archiving health via pg_stat_archiver. A non-zero failed_count with a recent last_failed_time means WAL is not reaching object storage. Alert on it; this is a silent backup failure.

SELECT archived_count, last_archived_time, failed_count, last_failed_wal
FROM pg_stat_archiver;

Managed Services: RDS, Neon, Supabase

Managed services handle the backup mechanics but you still need to configure and verify them.

RDS: Automated backups with a configurable retention period (1-35 days) and continuous WAL archiving. Enable cross-region backup replication explicitly:

# Enable cross-region automated backup replication via AWS CLI
aws rds start-db-instance-automated-backups-replication \
  --source-db-instance-arn arn:aws:rds:us-east-1:123456789:db:prod-db \
  --backup-retention-period 7 \
  --kms-key-id arn:aws:kms:eu-west-1:123456789:key/your-key-id \
  --region eu-west-1

Neon: Branch-based architecture where every branch is a copy-on-write snapshot. PITR is available to any second within the history window via console or API. Verify the history window matches your RPO.

Supabase: PITR with WAL archiving on Pro and above; restore via the dashboard. Test it in a non-production project before you need it.

The common failure mode with managed services is assuming the default configuration is correct. Verify the actual retention period and confirm cross-region replication is active, not just configured.

Point-in-Time Recovery: How It Works and When You Need It

PITR is not primarily for disaster recovery. It is for logical corruption: a bad migration, a bulk delete that ran without a WHERE clause, an application bug that overwrote data. You need to restore to a state 10 minutes before the error, not to a state from yesterday’s snapshot.

The PITR restore process for self-managed PostgreSQL (version 12+):

# 1. Restore the base backup closest to but before the target time
wal-g backup-fetch /var/lib/postgresql/data LATEST \
  --walg-s3-prefix s3://your-bucket/db-backups/postgres

# 2. Configure recovery target (PostgreSQL 12+)
touch /var/lib/postgresql/data/standby.signal
cat >> /var/lib/postgresql/data/postgresql.auto.conf << 'EOF'
restore_command = 'wal-g wal-fetch %f %p'
recovery_target_time = '2026-03-26 14:23:00 UTC'
recovery_target_action = 'promote'
EOF

# 3. Start PostgreSQL. It replays WAL to the target time and promotes.
pg_ctl start -D /var/lib/postgresql/data

PostgreSQL replays WAL segments from object storage until it reaches the target time, then promotes to a primary.

When PITR is the wrong tool: Infrastructure-level failure (disk corruption, instance loss) is faster to recover via replica promotion or snapshot restore. PITR is precision surgery for data state problems: bad migrations, bulk deletes without a WHERE clause, application bugs that overwrote data.

PITR verification: Identify a timestamp from last week, restore to it on a test instance, query a known set of rows, and confirm state. Most teams have never done this.

Cross-Region Replication for Disaster Recovery

A single-region backup strategy fails when the region itself has an availability event. This is rare but not theoretical. The recovery from a regional failure requires backups or a replica in a second region.

DR Architecture: Active + Warm Standby

Region: us-east-1 (Primary)          Region: eu-west-1 (DR)
┌─────────────────────────────┐       ┌───────────────────────────────┐
│  RDS Primary                │       │  RDS Read Replica             │
│  + Automated Backups        │──────▶│  (async replication ~5ms lag) │
│  + WAL to S3 us-east-1      │       │                               │
└─────────────────────────────┘       │  S3 Backup Replication        │
                                      │  (cross-region copy of WAL)   │
                                      └───────────────────────────────┘

Replication lag: Async replication to a cross-region replica typically runs 5-50ms behind primary, but can fall further behind under write-heavy load. Monitor via pg_stat_replication and alert when lag exceeds your RPO. Promoting a replica is fast; the cost is the data that was in-flight and not yet applied. Know that number before you need it.

Cross-region transfer cost: For a 500 GB database with high write throughput, cross-region replication can reach $500-2,000/month depending on provider. Price this before committing.

The Disaster Recovery Runbook

A runbook that only exists in a Google Doc is not a runbook. It is a document. A runbook is a tested procedure that someone who has never run it before can execute under stress and produce the correct outcome.

What to Include

Runbook header: Incident classification criteria. Not every database issue is a DR event. Define what triggers each scenario (data corruption vs. instance failure vs. regional failure) and which runbook section applies.

Prerequisites section: What access the responder needs before they start. AWS IAM roles, SSH keys, database credentials in the secret manager, Terraform state access. List the verification commands for each.

Scenario sections: One H2 per distinct failure type.

• Full instance loss (restore from snapshot)
• Data corruption (PITR to known-good state)
• Regional failure (promote cross-region replica)
• Cascading dependency failure (application-level, not database)

Each scenario: decision criteria, step-by-step commands, expected output at each step, what to do if a step fails, and the validation query to confirm success.

Communication and escalation: Who gets notified at each stage, what channel, and who to page if the runbook steps do not produce the expected output.

Automated Backup Verification

Backup verification should run automatically after every backup completes. The pattern: restore to an ephemeral instance, run validation queries, emit a structured result, and alert on failure.

import { execSync } from "child_process";
import { Client } from "pg";

interface BackupVerificationResult {
  backupId: string;
  verifiedAt: Date;
  success: boolean;
  rowCounts: Record<string, number>;
  latestRecordAgeHours: number | null;
  errorMessage?: string;
}

async function verifyBackup(
  backupId: string,
  dataDir: string,
  pgConfig: { host: string; port: number; database: string; user: string; password: string }
): Promise<BackupVerificationResult> {
  const result: BackupVerificationResult = {
    backupId,
    verifiedAt: new Date(),
    success: false,
    rowCounts: {},
    latestRecordAgeHours: null,
  };

  try {
    execSync(
      `wal-g backup-fetch ${dataDir} ${backupId} --walg-s3-prefix ${process.env.WALG_S3_PREFIX}`,
      { stdio: "pipe", timeout: 300_000 }
    );
    execSync(`pg_ctl start -D ${dataDir} -w -t 60`, { stdio: "pipe" });

    const client = new Client(pgConfig);
    await client.connect();

    for (const table of ["users", "orders", "subscriptions"]) {
      const { rows } = await client.query(`SELECT COUNT(*) AS n FROM ${table}`);
      result.rowCounts[table] = parseInt(rows[0].n, 10);
      if (result.rowCounts[table] === 0) {
        throw new Error(`Table ${table} is empty. Backup may be corrupt.`);
      }
    }

    const { rows } = await client.query("SELECT MAX(created_at) AS latest FROM orders");
    const ageHours = (Date.now() - new Date(rows[0].latest).getTime()) / 3_600_000;
    result.latestRecordAgeHours = ageHours;

    if (ageHours > 25) {
      throw new Error(`Latest order is ${ageHours.toFixed(1)}h old. Expected < 25h.`);
    }

    await client.end();
    result.success = true;
  } catch (err) {
    result.errorMessage = err instanceof Error ? err.message : String(err);
  } finally {
    try { execSync(`pg_ctl stop -D ${dataDir} -m fast`, { stdio: "pipe" }); } catch {}
  }

  return result;
}

Run this as a Kubernetes job or GitHub Actions workflow after each backup completes. Emit result as a structured log event and page if success is false.

Recovery Drills: How Often and What to Test

A recovery drill is a scheduled, intentional restoration exercise. An actual restore into a real environment with real data. Not a tabletop. Not a Confluence review.

Frequency: Once per quarter minimum for each failure scenario. Monthly is better. Some teams run weekly smoke tests in CI using the previous day’s backup.

Each drill must: locate the backup using only the runbook (no tribal knowledge), execute the restore end-to-end in staging, measure wall-clock time from start to “application accepting traffic,” and compare against the documented RTO. If the time exceeds the target, update either the procedure or the target. Run the validation script and log the result with timestamps and the specific backup ID.

What tends to fail during drills: IAM roles for backup reads expired or rotated without updating the runbook. The restore instance type is no longer available. A required environment variable references a secret that was moved. WAL archiving had a silent gap and the PITR target time lands inside it.

Each of these failures found in a drill costs an hour. Found during an incident, they cost much more.

Managed vs. Self-Managed Backup Strategies

Tradeoffs: Managed vs. Self-Managed Backups

┌─────────────────────────┬─────────────────────────────────┬─────────────────────────────────┐
│ Dimension               │ Managed (RDS, Neon, Supabase)   │ Self-Managed (WAL-G on EC2/K8s) │
├─────────────────────────┼─────────────────────────────────┼─────────────────────────────────┤
│ Setup time              │ Minutes                         │ Hours to days                   │
│ Operational overhead    │ Low                             │ High                            │
│ PITR granularity        │ 1-5 minutes (varies by service) │ Seconds (WAL archiving)         │
│ Cross-region support    │ Built-in (RDS), limited others  │ Requires manual S3 replication  │
│ Cost                    │ Included in instance cost       │ Storage + compute for restores  │
│ Restore flexibility     │ Console or API, limited hooks   │ Full control, scriptable        │
│ Encryption              │ At rest + in transit, managed   │ You configure KMS or GPG        │
│ Backup verification     │ Not automated by default        │ You build and run verification  │
│ Portability             │ Locked to provider format       │ Portable to any PostgreSQL      │
│ Compliance visibility   │ Audit logs via CloudTrail       │ Full access to all logs         │
└─────────────────────────┴─────────────────────────────────┴─────────────────────────────────┘

Most production setups combine both: a managed service for the primary (RDS, Neon) with built-in automated backups, plus a WAL-G pipeline exporting WAL to a second region for an additional recovery path and cross-provider portability.

Self-managed is the right call when you need sub-minute PITR, explicit backup custody for regulatory compliance, or are running on infrastructure without a managed option. Managed is sufficient if you are a small team shipping product: the overhead of operating WAL-G correctly (monitoring archive failures, verifying restores, managing IAM, controlling storage costs) is real work that competes with product work.

Production Considerations

Encrypt all backups. WAL-G supports AWS KMS server-side encryption out of the box. For managed services, use a customer-managed key (CMK), not the provider-managed default. A CMK gives you control over rotation and access auditing.

Monitor archiving lag, not just backup success. A backup job that completes successfully while WAL archiving is 6 hours behind produces an effective RPO of 6 hours. Track pg_stat_archiver.last_archived_time and alert when it falls more than 10 minutes behind wall clock.

Retention period is a policy decision, not a default. RDS defaults to 7 days. A corruption that goes undetected for 8 days cannot be recovered. Set retention to 30 days for transactional databases; move backups older than 7 days to cold storage (S3 Glacier Instant Retrieval) to keep costs manageable.

Test your encryption key access. An encrypted backup without accessible decryption keys is indistinguishable from no backup. Include a key access check as the first step in your runbook prerequisites.

Backups that have never been restored are not backups. They are files. The only thing that transforms a file into a backup is a successful, verified restore. Build that verification into the automation, run the drills, and update the runbook when reality diverges from the document. The incident where you need it will not wait for you to catch up.