Database Backup and Disaster Recovery for Startups: Automated Snapshots, Point-in-Time Recovery, and Recovery Runbooks

Startups think about backup strategy at one of two moments: when setting up the database initially, or right after losing data. The first conversation produces a checkbox. The second produces a war room. This article is for the first conversation.

The real gap is not that startups skip backups entirely. Most managed services create daily snapshots by default. The gap is that teams have never mapped their backup configuration to their actual recovery requirements, have never tested the restore process, and have no runbook for the 2am call when it matters.

This guide covers the full backup chain for PostgreSQL: snapshot strategies, WAL archiving for point-in-time recovery, cross-region replication, runbook construction and testing, and concrete cost numbers at startup scale.

---

Understanding the Failure Modes First

Before picking a backup strategy, name the failures you are actually defending against. They are different problems with different solutions.

Instance loss: The database host dies. Disk failure, VM termination, hypervisor crash. You need a recent snapshot and a fast way to provision a new instance from it.

Logical corruption: A bad migration ran without a transaction. An application bug overwrote the wrong rows. A bulk delete ran without a WHERE clause. You need to restore to a state from 10 minutes before the error, not from yesterday’s daily snapshot.

Regional failure: An availability zone or entire cloud region has an outage. Single-region backups stored in the same region will not help you. You need cross-region replication or backup replication.

Accidental deletion: Someone dropped a table or truncated production data. Point-in-time recovery and cross-region replication both help here, but only if your retention window covers the time between the deletion and when you discovered it.

Each of these failure modes requires a different part of your backup stack. A daily snapshot alone covers instance loss but not logical corruption that was introduced two days ago and detected today. WAL archiving covers logical corruption but is useless without a working base backup. Cross-region replication covers regional failure but not logical corruption unless you catch it before replication applies the bad writes.

---

Snapshot Strategies

pg_dump: Portable but Slow

pg_dump produces a logical dump: the data as SQL INSERT statements or a PostgreSQL-specific binary format. It is portable across PostgreSQL major versions and useful for migrations and schema inspection.

# Compressed custom format: faster restore than plain SQL, smaller file
pg_dump \
  --format=custom \
  --compress=9 \
  --no-owner \
  --no-acl \
  --dbname="postgresql://user:pass@localhost:5432/dbname" \
  --file="/backups/$(date +%Y%m%d-%H%M%S)-dbname.dump"

# Restore from custom format
pg_restore \
  --dbname="postgresql://user:pass@localhost:5432/dbname_restore" \
  --jobs=4 \
  --no-owner \
  /backups/20260402-020000-dbname.dump

The -j 4 flag on restore uses parallel workers. On a 100 GB database, this cuts restore time from 4+ hours to 60-90 minutes. Still slow. For a 50 GB database, budget 30-45 minutes.

pg_dump is not suitable as your primary recovery mechanism. Use it for cross-version migrations, keeping a portable copy of schema, or restoring individual tables from a large database. The restore time at scale is the problem.

Physical Snapshots with pg_basebackup

pg_basebackup copies the physical data directory. The result is byte-for-byte identical to the source, which makes restore fast: drop it in, start the cluster. No row re-insertion, no index rebuilds.

# Take a base backup with progress reporting
pg_basebackup \
  --host=localhost \
  --port=5432 \
  --username=replication_user \
  --pgdata=/backups/base-$(date +%Y%m%d) \
  --format=tar \
  --gzip \
  --checkpoint=fast \
  --progress

Physical snapshots are the foundation for WAL-based PITR. Without a recent base backup, you cannot replay WAL to a useful recovery point.

Managed Service Snapshots

RDS, Neon, and Supabase take automated snapshots without you configuring anything. The tradeoff: you have less visibility and control, and the defaults may not match your requirements.

RDS: Automated backups enabled by default with a 7-day retention window. Snapshots are stored in S3 within the same region. Change the defaults explicitly:

aws rds modify-db-instance \
  --db-instance-identifier prod-postgres \
  --backup-retention-period 30 \
  --preferred-backup-window "03:00-04:00" \
  --apply-immediately

A 30-day retention window covers the most common detection lag for logical corruption. The 7-day default does not.

Neon: Every branch is a copy-on-write snapshot. You can restore to any second within the history window from the dashboard or API. The history window limit depends on your plan tier. Verify it matches your RPO before relying on it.

Supabase: PITR is available on Pro and above with WAL archiving. Configure it; it is not on by default.

---

WAL Archiving and Point-in-Time Recovery

How WAL Archiving Works

PostgreSQL writes every committed transaction to the Write-Ahead Log before applying it to data pages. WAL archiving copies completed WAL segments to external storage as they are written. A base backup plus all WAL segments since that backup gives you the ability to reconstruct any database state from backup time to the moment the last WAL segment was archived.

Configure WAL archiving in postgresql.conf:

# Enable WAL archiving
wal_level = replica
archive_mode = on
archive_command = 'wal-g wal-push %p'
archive_timeout = 60

archive_timeout = 60 forces a WAL segment archive every 60 seconds even if the segment is not full. Without this, a low-traffic database could go hours between archived segments. With it, your effective RPO is under 2 minutes.

WAL-G is the production standard for archiving to object storage. Configure it with your S3 bucket:

export WALG_S3_PREFIX=s3://your-backup-bucket/postgres
export AWS_REGION=us-east-1

# Initial full base backup
wal-g backup-push /var/lib/postgresql/data

# WAL segments archive automatically via archive_command

Monitor archiving health. Silent archiving failure is the most common way backup infrastructure drifts without anyone noticing:

SELECT
  archived_count,
  last_archived_wal,
  last_archived_time,
  failed_count,
  last_failed_wal,
  last_failed_time,
  now() - last_archived_time AS archiving_lag
FROM pg_stat_archiver;

Alert when archiving_lag exceeds 5 minutes or when failed_count increases. A backup job that completes successfully while WAL archiving has been silently failing for six hours gives you an effective RPO of six hours, not the two minutes you planned for.

Performing a Point-in-Time Recovery

PITR is the right tool for logical corruption: bad migrations, bulk deletes without a WHERE clause, application bugs that overwrote data. For infrastructure failure (disk corruption, instance termination), a snapshot restore or replica promotion is faster.

# 1. Restore the closest base backup before the corruption timestamp
wal-g backup-fetch /var/lib/postgresql/data LATEST \
  --walg-s3-prefix s3://your-backup-bucket/postgres

# 2. Signal recovery mode and set the target time (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-04-02 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 -l /var/log/postgresql/recovery.log

# 4. Verify the state before promoting or reconnecting application traffic
psql -c "SELECT MAX(created_at) FROM orders;"
psql -c "SELECT COUNT(*) FROM users WHERE created_at > '2026-04-02 14:00:00';"

PostgreSQL replays WAL segments from object storage until it reaches recovery_target_time, then promotes to a writable primary. The replay is sequential: 100 GB of WAL covering a 24-hour window takes 30-90 minutes to replay depending on write throughput.

Two things that trip people up during PITR under pressure:

First, the recovery target time must be before the error, not after it. If the bad migration ran at 14:25, set the target to 14:22. Obvious in planning, easy to get wrong at 3am.

Second, check that the WAL archive has no gaps around the target time. An archive_timeout gap or an archiving failure during the window you are targeting means the replay will stop at the gap. If you run PITR drills quarterly, you will discover gaps during drills rather than during incidents.

---

Cross-Region Replication for Disaster Recovery

A single-region backup strategy fails if the region itself has an outage, if the backup storage bucket is in the same region as the database host, or if a regional compliance requirement forces you to operate from a second region.

Architecture Options at Startup Scale

Option 1: Cross-region backup replication (lowest cost)

Copy WAL archives and snapshots to a second region’s S3 bucket. No second database instance running continuously.

# S3 cross-region replication rule (Terraform HCL)
resource "aws_s3_bucket_replication_configuration" "backup_replication" {
  role   = aws_iam_role.replication.arn
  bucket = aws_s3_bucket.postgres_backups_primary.id

  rule {
    id     = "replicate-to-secondary"
    status = "Enabled"

    destination {
      bucket        = aws_s3_bucket.postgres_backups_secondary.arn
      storage_class = "STANDARD_IA"
    }
  }
}

Recovery from this setup requires provisioning a new database instance in the second region and restoring from the replicated backup. Add 20-40 minutes of provisioning time to your RTO. Cost: cross-region data transfer plus secondary storage (roughly $0.02/GB/month in S3 Standard-IA).

Option 2: Cross-region read replica with manual promotion

An RDS read replica in a second region receives async replication from the primary. Recovery is fast: promote the replica and update DNS. The replica is continuously running, so provisioning time is near-zero.

Primary Region (us-east-1)          DR Region (eu-west-1)
┌──────────────────────────┐         ┌────────────────────────────────┐
│  RDS Primary             │         │  RDS Read Replica              │
│  + Automated Backups     │────────>│  (async, ~5-50ms lag at rest)  │
│  + WAL to S3 us-east-1   │         │                                │
└──────────────────────────┘         │  S3 Cross-Region Backup Copy   │
                                     └────────────────────────────────┘

Replication lag is typically 5-50ms under normal write load but grows under write-heavy bursts. Monitor lag via pg_stat_replication:

SELECT
  application_name,
  state,
  sent_lsn,
  replay_lsn,
  pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn)) AS replication_lag_bytes,
  write_lag,
  flush_lag,
  replay_lag
FROM pg_stat_replication;

Alert when replay_lag exceeds your RPO. At the moment of regional failure, the data in-flight but not yet applied to the replica is the data you lose. This number is your actual RPO in a failover scenario. It is not always the same as your theoretical RPO.

Option 3: Active-passive with automated DNS failover

Both regions have running infrastructure. Route 53 health checks monitor the primary region. When the health check fails, Route 53 routes traffic to the secondary automatically. The database replica has been running the whole time; promote it and the application connects.

This is the right setup once you have contractual uptime requirements that a manual promotion process cannot satisfy. The cost is the continuously running secondary compute.

---

Writing Recovery Runbooks That Work Under Pressure

A runbook is a document that someone who has never run this procedure before can execute correctly at 3am. Most runbooks fail this test because they were written by the person who knows the system, contain implicit tribal knowledge, and have never been tested against reality.

Runbook Structure

Every runbook needs:

Trigger criteria. Not every database issue is a DR event. Define what triggers each scenario. Instance unresponsive for 5 minutes triggers a different playbook than data corruption discovered in application logs. Ambiguous trigger criteria guarantee the wrong person executes the wrong runbook.

Prerequisites section. What access the responder needs before starting. AWS IAM roles, SSH keys, database credentials location, Terraform state bucket, Slack channel. Include the verification command for each:

# Verify AWS credentials with backup read access
aws sts get-caller-identity
aws s3 ls s3://your-backup-bucket/postgres/ | tail -5

# Verify PostgreSQL restore user credentials
psql "postgresql://restore_user@restore-host:5432/postgres" -c "SELECT version();"

# Verify WAL-G connectivity
wal-g backup-list --walg-s3-prefix s3://your-backup-bucket/postgres

If any of these commands fail, the responder knows before starting the restore, not 45 minutes in.

Scenario sections. One section per distinct failure type. Each section contains:

• Decision criteria confirming this scenario applies
• Step-by-step commands with expected output at each step
• What to do if a step does not produce the expected output
• The validation query confirming successful recovery before cutting traffic

Communication protocol. Who gets notified at each stage, in which channel, and who to page if the runbook fails to produce expected results. This is not optional. The responder should not be making communication decisions while executing a restore.

Automated Backup Verification

Backup verification should run automatically after every backup completes. A backup that was never verified is not a backup; it is a file that might be a backup.

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

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

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

  try {
    // Restore backup to ephemeral directory
    execSync(
      `wal-g backup-fetch ${restoreDataDir} ${backupId}`,
      { env: { ...process.env }, timeout: 600_000 }
    );
    execSync(`pg_ctl start -D ${restoreDataDir} -w -t 60`);

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

    // Verify critical tables are non-empty
    const criticalTables = ["users", "orders", "subscriptions"];
    for (const table of criticalTables) {
      const { rows } = await client.query(
        `SELECT COUNT(*) AS n FROM ${table}`
      );
      const count = parseInt(rows[0].n, 10);
      result.rowCounts[table] = count;
      if (count === 0) {
        throw new Error(`Table ${table} is empty: backup may be corrupt`);
      }
    }

    // Verify most recent data is within expected age
    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 under 25h.`
      );
    }

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

  return result;
}

Run this in a GitHub Actions workflow or Kubernetes Job after each backup. Emit the result as a structured log event to your observability system. Page on success: false. The run takes 10-20 minutes for a typical startup database; schedule it at low-traffic hours.

Running Recovery Drills

A drill is not a tabletop exercise. It is an actual restore into a staging environment, executed by someone following only the runbook, with a stopwatch running.

What to measure: time from drill start to “application accepting queries against restored database.” Compare it against your documented RTO. If it takes longer, update the runbook or the target. Do not adjust the stopwatch.

Run drills quarterly at minimum. Monthly is better for teams that have paying customers. Include at least one drill where the executor has never done it before. The runbook is broken if it requires tribal knowledge.

Common failures found in drills rather than incidents:

• IAM credentials for backup bucket reads have been rotated without updating the runbook or the restore automation
• The WAL archive has a 4-hour gap from a silently failed archive_command during a high-write period
• The restore instance type listed in the runbook was deprecated; a newer instance type is required
• A required environment variable references a secret that was renamed during a secrets rotation
• The documented RTO was estimated, not measured; reality is 3x longer

Every one of these failures discovered in a drill costs one engineer an hour. Discovered during a real incident with customer data at risk, they cost much more.

---

Cost Analysis at Startup Scale

The right backup strategy depends on your data size, write throughput, and recovery targets. Here is a concrete cost breakdown for three common startup database sizes.

Strategy	50 GB Database	200 GB Database	500 GB Database	Notes
Managed daily snapshots only	~$0 (included)	~$0 (included)	~$0 (included)	Default on RDS, Neon, Supabase. No PITR.
WAL archiving to S3 (same region)	~$5/mo	~$20/mo	~$50/mo	S3 Standard, 30-day retention. Provides PITR.
WAL archiving + cross-region S3 copy	~$8/mo	~$30/mo	~$75/mo	Adds cross-region transfer + secondary storage.
Cross-region read replica (RDS)	~$60/mo	~$180/mo	~$450/mo	db.t3.medium replica + data transfer. Fast failover.
Cross-region replica + backup replication	~$68/mo	~$210/mo	~$525/mo	Full DR stack: fast failover + independent backup path.

These numbers assume AWS us-east-1 to eu-west-1 replication, S3 Standard-IA for replicated backups, and RDS db.t3.medium class for replicas. They will differ on GCP or Azure, and vary with actual write throughput affecting replication transfer costs.

The cost of WAL archiving is small relative to the instance cost for any database that justifies a managed service. For a team running RDS at $200/month for the primary instance, adding WAL archiving to S3 costs less than 10% on top. Not doing it and suffering a data loss event that a 10-minute PITR would have recovered from is not a rational tradeoff.

Where costs escalate is the cross-region replica. A warm replica in a second region costs 60-100% of the primary instance cost on top of your primary cost. This is the right investment when you have contractual uptime commitments or regulatory requirements mandating a geographic separation. It is overhead before you reach that point.

The practical starting configuration for most startups: managed snapshots with a 30-day retention window plus WAL archiving to S3 with archive_timeout = 60. This gives you snapshot recovery for instance loss and PITR for logical corruption at a cost of under $30/month for a 200 GB database. Add cross-region backup replication if your RTO for a regional outage is under 2 hours. Add a cross-region replica when you have explicit uptime contracts to defend.

---

Production Considerations

Encrypt everything. WAL-G supports AWS KMS server-side encryption. For managed services, use a customer-managed key. A provider-managed key means the provider controls rotation and access auditing; a customer-managed key gives you control of both. Include a decryption key access check in the prerequisites section of every runbook. An encrypted backup without an accessible decryption key is indistinguishable from no backup.

Set retention to 30 days, not 7. The RDS default is 7 days. Logical corruption often goes undetected for more than 7 days: a bad migration that introduced subtle data inconsistencies, an application bug that was silently overwriting rows. A 7-day window does not cover that detection lag. Move backups older than 7 days to S3 Glacier Instant Retrieval to keep the cost manageable; the price difference between Standard and Glacier Instant Retrieval is roughly 68% per GB-month.

Monitor archiving lag explicitly. A backup job that completes successfully while WAL archiving silently fails for six hours gives you an effective RPO of six hours. The only way to know this is to alert on pg_stat_archiver.last_archived_time. Alert threshold: 10 minutes behind wall clock. Alert owner: on-call engineer, not just a monitoring dashboard.

Stage your recovery environment in advance. Provisioning a new RDS instance from a snapshot takes 15-30 minutes. If you provision it for the first time during an incident, you are also learning the console UI under stress. Pre-create the recovery VPC, subnet, and security group configurations in Terraform. Keep them applied. The incremental cost of running the network plumbing with no instances attached is near zero.

Include a rollback path in every runbook. If step 7 produces unexpected output, what do you do? Most runbooks do not answer this. The answer is not always “abort and escalate.” Sometimes it is a specific diagnostic command. Capture the decision tree during runbook writing, when you are not under pressure, not during the incident.

The gap between having backups and being able to recover is mostly a process and testing gap, not a technology gap. The tools exist and most managed services provision them by default. The work is verifying that the backups are valid, documenting how to use them under pressure, and drilling that documentation until the procedure is boring.

Boring recovery procedures are the goal.