Database Reliability Engineering: Automated Failover, Read Replicas, and Recovery Testing for Production Postgres

Your database is the one thing in your stack where unavailability is never acceptable and data loss is never recoverable. Yet most teams at the startup and growth stage treat Postgres as a managed black box until a primary instance goes down, at which point they discover their understanding of replication lag, failover timing, and connection routing is mostly theoretical.

This is not about the basics of setting up replication. It is about building the operational practices around replication: routing reads correctly, handling failover without cascading application errors, testing that your recovery actually works before you need it, and deciding when self-managing that complexity is worth it versus paying someone else to carry it.

Streaming Replication and Read Replicas

Postgres streaming replication sends WAL records from the primary to one or more standby servers in near-real-time. Each standby replays those records and stays within a few milliseconds of the primary under normal conditions. The key configuration parameters are on the primary:

-- postgresql.conf on primary
wal_level = replica           -- minimum for streaming
max_wal_senders = 5           -- max concurrent replication connections
wal_keep_size = 1GB           -- WAL retained for slow replicas
synchronous_commit = on       -- default; change with care

And on the standby, recovery.conf (Postgres 11 and earlier) or postgresql.conf (Postgres 12+):

-- standby postgresql.conf
primary_conninfo = 'host=primary-db port=5432 user=replicator password=...'
hot_standby = on              -- allow read queries on standby

Hot standby with hot_standby = on is what makes a replica useful for read traffic. Without it, the replica accepts replication connections but rejects application queries.

What Read Replicas Actually Help With

Read replicas are not a write-scaling solution. They help with: reporting queries that would otherwise lock or slow your primary, background jobs that scan large tables, analytics that can tolerate slightly stale data, and connection load distribution when your primary connection pool is saturated.

They do not help with write throughput, and routing writes to a replica crashes the transaction immediately. Your routing layer must enforce the write-to-primary constraint without depending on the application to remember it.

Routing Connections Correctly

The naive approach is to give the application two connection strings: one for writes and one for reads. This works until failover happens and neither string is valid anymore. The better approach separates routing concerns from application code.

A connection router (implemented as a middleware layer or a thin proxy) should:

1. Resolve the current primary by querying pg_is_in_recovery() against known nodes.
2. Route write transactions to the confirmed primary.
3. Route read-only queries to replicas, with fallback to primary if no healthy replica exists.
4. Detect failover by catching connection errors and re-resolving the topology.

Here is a TypeScript implementation of a minimal connection router:

import { Pool, PoolClient } from 'pg';

interface DbNode {
  host: string;
  port: number;
  user: string;
  password: string;
  database: string;
}

interface RouterConfig {
  nodes: DbNode[];
  healthCheckIntervalMs: number;
}

type NodeRole = 'primary' | 'replica' | 'unreachable';

interface NodeStatus {
  node: DbNode;
  role: NodeRole;
  pool: Pool;
  lagMs: number | null;
}

async function resolveNodeRole(pool: Pool): Promise<{ role: NodeRole; lagMs: number | null }> {
  let client: PoolClient | null = null;
  try {
    client = await pool.connect();
    const result = await client.query<{ is_replica: boolean; lag_ms: number | null }>(`
      SELECT pg_is_in_recovery() AS is_replica,
             CASE WHEN pg_is_in_recovery()
               THEN EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp())) * 1000
               ELSE NULL
             END AS lag_ms
    `);
    const row = result.rows[0];
    return {
      role: row.is_replica ? 'replica' : 'primary',
      lagMs: row.lag_ms,
    };
  } catch {
    return { role: 'unreachable', lagMs: null };
  } finally {
    client?.release();
  }
}

class ConnectionRouter {
  private statuses: NodeStatus[] = [];
  private intervalId: NodeJS.Timeout | null = null;

  constructor(private config: RouterConfig) {
    this.statuses = config.nodes.map((node) => ({
      node,
      role: 'unreachable',
      pool: new Pool({ ...node, max: 5 }),
      lagMs: null,
    }));
  }

  async start(): Promise<void> {
    await this.refresh();
    this.intervalId = setInterval(() => this.refresh(), this.config.healthCheckIntervalMs);
  }

  private async refresh(): Promise<void> {
    await Promise.all(
      this.statuses.map(async (status) => {
        const { role, lagMs } = await resolveNodeRole(status.pool);
        status.role = role;
        status.lagMs = lagMs;
      })
    );
  }

  getPrimaryPool(): Pool {
    const primary = this.statuses.find((s) => s.role === 'primary');
    if (!primary) throw new Error('No primary available');
    return primary.pool;
  }

  getReplicaPool(maxLagMs = 2000): Pool {
    const replicas = this.statuses.filter(
      (s) => s.role === 'replica' && (s.lagMs === null || s.lagMs < maxLagMs)
    );
    if (replicas.length === 0) {
      // fall back to primary for reads if no healthy replica exists
      return this.getPrimaryPool();
    }
    // round-robin or pick lowest lag
    replicas.sort((a, b) => (a.lagMs ?? Infinity) - (b.lagMs ?? Infinity));
    return replicas[0].pool;
  }

  stop(): void {
    if (this.intervalId) clearInterval(this.intervalId);
  }
}

This router polls every node on a configurable interval. When a failover completes, the next poll will show the promoted replica as primary and traffic reroutes automatically. The maxLagMs parameter on getReplicaPool lets you exclude replicas that are too far behind for a given query category. Reporting queries can tolerate 10 seconds of lag; user-facing reads should stay under 500ms.

Automated Failover

Manual failover for a production database is not a reliability strategy. If your on-call engineer needs to SSH into a server, promote a replica, and update DNS records by hand in the middle of the night, your MTTR is measured in tens of minutes at best. Automated failover brings that to under 30 seconds in most cases.

Patroni

Patroni is the standard self-managed solution for Postgres HA. It wraps Postgres with a watchdog process that coordinates with a distributed consensus store (etcd, Consul, or ZooKeeper) to elect a primary and manage promotions.

Key Patroni concepts to understand before deploying it:

Patroni uses the consensus store as the single source of truth for which node holds the primary lock. A node that cannot reach the consensus store pauses writes rather than promoting itself. This prevents split-brain at the cost of availability during a consensus store outage.

The maximum_lag_on_failover setting controls which replicas are eligible for promotion. Set it too high and you promote a replica that has missed hundreds of transactions. Set it too low and Patroni cannot find an eligible replica and refuses to promote, leaving you with no primary.

# patroni.yml (simplified)
scope: production-cluster
name: node1

restapi:
  listen: 0.0.0.0:8008

etcd:
  hosts: etcd1:2379,etcd2:2379,etcd3:2379

bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576  # 1 MB of WAL lag tolerance

postgresql:
  listen: 0.0.0.0:5432
  connect_address: node1:5432
  data_dir: /var/lib/postgresql/data
  parameters:
    wal_level: replica
    max_wal_senders: 5
    synchronous_commit: "on"

Patroni exposes a REST API on each node. GET /health returns 200 on the primary, 200 on replicas (with body {"role": "replica"}), and 503 on anything unreachable. HAProxy or PgBouncer can use these endpoints for health-check-based routing.

pg_auto_failover

pg_auto_failover is simpler than Patroni: it uses a monitor node (itself a Postgres instance) to track state and coordinate failover. There is no external consensus store dependency. The tradeoff is that the monitor node is a new single point of failure you need to HA separately, which often means you end up with similar complexity anyway.

For teams that are already running etcd for Kubernetes, Patroni slots in naturally. For teams that want a simpler operational model without etcd, pg_auto_failover is worth evaluating. For most startups running on a managed Kubernetes layer, the honest answer is: use a managed Postgres service and skip self-managing failover entirely.

Connection Handling During Failover

Failover does not just break new connections. It actively kills in-flight transactions on the old primary. Your application will see connection errors and transaction rollbacks during the promotion window. Three things need to handle this gracefully:

Connection pools. PgBouncer or application-level pools need to detect that the primary has changed. PgBouncer’s server_check_query (SELECT 1) will start failing during failover and the pool will drain those connections. Configure server_check_delay low enough (2-5 seconds) that the pool notices quickly.

Retry logic with backoff. Queries that fail during failover should retry with exponential backoff, not immediately. Slamming the new primary with retry storms during promotion is a common way to make a 30-second failover turn into a 3-minute outage.

Transaction semantics. Read-only transactions that were in flight against the old primary are gone. Reads are safe to retry immediately. Write transactions that were committed on the old primary before it failed are durable if replication was synchronous, and may be lost if it was asynchronous. Your application code needs to know which category each operation falls into.

async function withRetry<T>(
  fn: () => Promise<T>,
  options: { maxAttempts: number; baseDelayMs: number }
): Promise<T> {
  for (let attempt = 1; attempt <= options.maxAttempts; attempt++) {
    try {
      return await fn();
    } catch (err) {
      const isConnectionError =
        err instanceof Error &&
        (err.message.includes('ECONNREFUSED') ||
          err.message.includes('terminating connection') ||
          err.message.includes('the database system is starting up'));

      if (!isConnectionError || attempt === options.maxAttempts) throw err;

      const delayMs = options.baseDelayMs * Math.pow(2, attempt - 1);
      await new Promise((resolve) => setTimeout(resolve, delayMs));
    }
  }
  throw new Error('Unreachable');
}

Monitoring Replication Lag

Replication lag is the gap between what the primary has committed and what a replica has replayed. It is the single most important metric to track for a replicated Postgres setup. The query that matters:

-- run on primary to see all connected standbys
SELECT
  client_addr,
  state,
  sent_lsn,
  write_lsn,
  flush_lsn,
  replay_lsn,
  (sent_lsn - replay_lsn) AS total_lag_bytes,
  write_lag,
  flush_lag,
  replay_lag
FROM pg_stat_replication
ORDER BY replay_lag DESC NULLS LAST;

Alert thresholds worth setting:

• Lag above 10 MB: warning. Network or replica I/O is degraded.
• Lag above 100 MB: critical. The replica is falling behind faster than it is catching up, which means a failover to this replica would lose more data than your RPO allows.
• replay_lag column null: the replica stopped sending heartbeats. Treat as unreachable.

Expose these as Prometheus metrics via postgres_exporter or a custom scraper. The pg_replication_slots view is also critical to monitor: a replication slot with a growing pg_wal_lsn_diff and no connected consumer will cause WAL to accumulate on the primary disk until it fills up and crashes.

Recovery Testing: Chaos Engineering for Databases

The only reliable way to know your failover works is to run it. Teams that have never done a controlled failover in staging will make avoidable mistakes under pressure during an actual incident.

A minimal recovery testing program runs the following scenarios on a quarterly schedule:

Primary failure simulation. Kill the primary process with pg_ctl stop -m immediate (unclean shutdown, closest to a real crash). Measure time to promotion, connection error window for the application, and whether reads fell back to the primary pool correctly during the window.

Network partition. Use tc netem or firewall rules to partition the primary from replicas. Verify that Patroni correctly pauses writes when quorum is lost rather than promoting a replica that cannot see the primary.

Replica lag injection. Slow the replica’s disk I/O with stress-ng and confirm that the router’s maxLagMs threshold correctly excludes the lagging replica from the read pool. This tests the lag check is functional, not just configured.

Cascading connection pool failure. Exhaust all connections on the primary and confirm that the application serves errors rather than hanging indefinitely. Set connect_timeout in the connection string, not just in application code.

Document each test in a runbook:

Test: Primary crash failover
Preconditions: staging cluster, 3 nodes, Patroni running, application traffic active
Steps:
  1. Confirm replication healthy: SELECT * FROM pg_stat_replication;
  2. Note current primary node.
  3. Run: pg_ctl stop -m immediate -D /var/lib/postgresql/data
  4. Observe Patroni logs: journalctl -u patroni -f
  5. Confirm new primary elected within 30s.
  6. Confirm application errors stopped within 60s.
  7. Verify new primary: SELECT pg_is_in_recovery();
Expected outcome: promotion within 30s, application recovery within 60s, zero data loss (synchronous_commit = on)
Failure criteria: promotion takes >60s, split-brain observed, data loss on write-ahead transactions

Run these tests against staging every quarter and run the primary failover test in production at least once a year during a planned maintenance window. Teams that only test in staging develop false confidence. Production has different network topology, connection counts, and timing than staging.

Self-Managed vs Managed Postgres

The operational question is not whether to use replication. It is how much of this you want to own yourself.

Dimension	Self-managed (EC2 + Patroni)	RDS / Aurora	Neon	Supabase	Crunchy Bridge
Failover setup	Manual (Patroni config)	Automatic	Automatic	Automatic	Automatic
Failover time	10-30s (tunable)	60-120s (multi-AZ)	Near-instant	60-120s	30-60s
Read replicas	Manual config	Console toggle	Branching model	Console	Console
PITR	WAL-G + S3 (manual)	Built-in, 35 days	Per-branch	Built-in	Built-in
Connection pooling	PgBouncer (manual)	RDS Proxy ($$$)	Built-in	Built-in (pgBouncer)	Built-in (pgBouncer)
Replication lag visibility	Full (pg_stat_replication)	Limited	Limited	Limited	Full
Cost at 100 GB	Low (EC2)	Medium	Pay-per-compute	Medium	Medium-high
Ops burden	High	Low	Low	Low	Low-medium
Postgres version control	Full	Managed upgrades	Managed	Managed	Full

The honest framing: self-managed Patroni gives you the most control and the lowest infrastructure cost at scale, but you are accepting the operational burden of running etcd, managing Patroni upgrades, building your connection routing, and owning your failover runbooks. RDS Multi-AZ removes most of that burden at the cost of slower failover (60-120s) and less visibility into replication internals. Neon’s serverless branching model is compelling for development workflows but its production failover model is opaque. Crunchy Bridge sits closest to the self-managed experience without requiring you to run the infrastructure yourself.

For teams under 20 engineers, the operational cost of self-managed Patroni is rarely worth it. RDS Multi-AZ or Crunchy Bridge gives you automatic failover, PITR, and managed upgrades for a cost that is lower than the engineering time you would spend owning it. Above that threshold, if you have a dedicated platform team and cost efficiency matters, self-managed starts to pay.

Common Failure Scenarios and Runbook Stubs

Three scenarios that come up repeatedly in production and are worth having written runbooks for before you need them:

Replication slot bloat. A consumer stops reading from a replication slot. WAL accumulates. Disk fills. Primary crashes. Detection: alert on pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) > 5GB for any slot. Resolution: drop the slot if the consumer is permanently gone, restart the consumer if recoverable.

Cascading standby failure after primary promotion. After a failover, remaining replicas need to re-point to the new primary. Patroni handles this automatically. Self-configured replicas need primary_conninfo updated and pg_ctl reload run. If replicas fail to reconnect, they fall behind and become ineligible for future failovers.

Synchronous standby loss with synchronous_standby_names set. If you configure synchronous replication (synchronous_standby_names = 'replica1') and that replica becomes unreachable, writes on the primary will hang waiting for acknowledgment. Set synchronous_commit = remote_write instead of on for a more tolerant durability guarantee, or configure ANY 1 (replica1, replica2) to require only one of multiple standbys to acknowledge.

Closing

Database reliability is not a feature you add. It is the set of operational practices you build before the incident. Automated failover, lag monitoring, connection routing, and recovery testing are each individually straightforward. The gap is that most teams implement one or two and assume the rest is covered by the managed service they are using. It usually is not. Know what your provider handles automatically, test what they promise, and own the pieces they leave to you.