Database Replication and Failover Diagram: Primary, Replicas and Split-Brain Protection

Most replication diagrams show a primary, two replicas and arrows between them. That picture is true and almost useless during an incident, because it does not say which node takes writes right now, which replica may serve stale data, or what happens when the primary stops answering.

A useful replication diagram answers four questions. This guide works through them with PostgreSQL, Patroni and etcd, then draws the result. The same questions apply to MySQL, to other failover managers and to managed services.

Four questions every replication diagram must answer

1. Where do writes go? There must be exactly one primary, and clients need a way to find it after a failover.
2. How stale can reads be? Each replica is synchronous or asynchronous, and asynchronous ones lag.
3. Who decides a failover? Something has to detect the failure, pick a replica and promote it.
4. What stops two primaries? A primary that was cut off must stop taking writes before another node is promoted.

Synchronous and asynchronous replication

PostgreSQL streaming replication is asynchronous by default. The primary confirms a commit before the standby has it, so a crash can lose the transactions that had not arrived yet. The PostgreSQL documentation puts it plainly: the amount of data loss is proportional to the replication delay at the time of failover.

Synchronous replication makes commits wait for one or more standbys listed in synchronous_standby_names. How long they wait depends on synchronous_commit:

The price is latency: every commit includes a round trip to the standby, so keep synchronous standbys in a nearby zone, not another region. And if the only synchronous standby disappears, commits can wait forever. Name more than one candidate, or let a failover manager change the setting for you.

Read replicas and read routing

An asynchronous replica is a good place for reports and read-heavy endpoints, as long as the code knows the data can be behind. On the primary, the pg_stat_replication view shows write_lag, flush_lag and replay_lag for each standby; replay_lag approximates how long before a commit becomes visible on the replica.

Decide per query which ones may read stale data. A user who just saved a form and reloads the page should read from the primary. A dashboard can read from the replica.

Failover: who decides

PostgreSQL does not detect a failed primary or promote a standby by itself; its documentation leaves that to external tools. Patroni is a common choice. Each node runs a Patroni agent, and the agents share state through a distributed configuration store such as etcd:

• The primary holds a leader key with a TTL and renews it every loop (loop_wait, 10 seconds by default).
• If it cannot renew the key for ttl seconds (30 by default), the key expires and the eligible replicas start a leader race.
• The best candidate takes the key and promotes itself; the others follow it.

Clients find the new primary through a proxy. Patroni's REST API answers GET /primary with HTTP 200 only on the node that holds the leader key, so HAProxy can send writes to whichever node says yes:

listen primary
    bind *:5000
    option httpchk OPTIONS /primary
    http-check expect status 200
    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
    server pg-a 10.0.1.10:5432 check port 8008
    server pg-b 10.0.2.10:5432 check port 8008
    server pg-c 10.0.3.10:5432 check port 8008

A second listener on port 5001 checks /replica?lag=16MB, which drops a replica that is more than 16 MB of WAL behind. Run HAProxy on more than one machine, or it becomes the single point of failure you were trying to remove.

Split brain: what stops two primaries

Two primaries accepting writes means two diverging histories, and one of them will be thrown away. The PostgreSQL documentation calls the remedy STONITH: the old primary must be told it is no longer the primary. Patroni layers several defenses:

• The lease. A primary that cannot update the leader key stops taking writes: Patroni stops or demotes PostgreSQL. If the store itself is unreachable for longer than retry_timeout, Patroni demotes the primary, unless its DCS failsafe mode is on.
• A watchdog. If the Patroni process hangs, a Linux watchdog resets the machine. Patroni sets it to fire 5 seconds before the TTL expires.
• A quorum. etcd needs a majority of its members, two of three, to accept a write. Three members in three zones survive the loss of one zone, and a partitioned minority cannot elect anyone.
• One process in charge. Only Patroni may start PostgreSQL. A system service that restarts Postgres on its own after a crash can bring back a second primary.

When the old primary returns, it has to rejoin as a replica. pg_rewind resynchronizes its data directory with the new primary without copying unchanged data blocks; Patroni runs it when use_pg_rewind is on.

# Patroni dynamic configuration (patronictl edit-config)
ttl: 30
loop_wait: 10
retry_timeout: 10
synchronous_mode: on        # promote only a node that has every acknowledged commit
postgresql:
  use_pg_rewind: true
  parameters:
    wal_log_hints: "on"

# Local patroni.yml tags
# pg-a: noloadbalance: true            (failover target, no reads)
# pg-c: nosync: true, nofailover: true (reads only)

With synchronous_mode on, Patroni promotes only a standby it knows has every commit that was reported as successful. The trade-off is availability: if the primary fails while no synchronous standby is attached, Patroni promotes nobody, and writes stay down until someone intervenes.

Replication is not a backup

A DROP TABLE reaches every replica in milliseconds. Keep continuous WAL archiving and regular base backups, which give you point-in-time recovery to any moment after the base backup, and test restores on a schedule.

The diagram

How to read it:

• Writes. The order service connects to HAProxy. Port 5000 goes to pg-b, the only node that answers /primary with 200.
• Reads. Port 5001 goes to pg-c, an asynchronous replica in another zone. pg-a takes no reads.
• Replication. pg-b streams WAL to pg-a synchronously (solid) and to pg-c asynchronously (dashed).
• Failover. pg-b renews the leader key in etcd. If the key expires, pg-a, the synchronous standby, races for it. pg-c is tagged nofailover.
• Recovery. pg-b archives WAL and base backups to object storage.
• Targets. The note at the top left holds RPO, RTO, the lag budget and backup retention.

What to label on a replication diagram

• The mode on every replication arrow, sync or async, and the lag budget for async replicas.
• The role of each replica: failover candidate, read replica, or both.
• Where the leader lock lives and its TTL, or the managed service that plays that role.
• How clients find the primary: a proxy, a DNS name or a virtual IP.
• Failure domains: availability zones as frames, including where the consensus members run.
• RPO and RTO targets, so reviewers can check the design against them.

What managed services do for you

Managed databases answer the same questions, but some answers are fixed. In an Amazon RDS Multi-AZ DB instance, the standby is synchronous, sits in another zone and cannot serve reads. Failover switches the DNS record to the standby and typically takes 60 to 120 seconds. A Multi-AZ DB cluster has two readable standbys, uses semisynchronous replication and typically fails over in under 35 seconds. On the diagram, the endpoint replaces HAProxy and AWS replaces Patroni and etcd. You still label lag, read routing and targets.

Test the failover

Switch the primary on purpose, for example in every maintenance window with patronictl switchover. The PostgreSQL documentation recommends regular switching because it doubles as a test of the failover mechanism. Measure how long writes fail, check that HAProxy follows, and confirm that no acknowledged commit is missing. For failover across regions, see the AWS multi-region architecture diagram.

Build it in Archboard

1. Sign in with Google or GitHub and create a diagram.
2. Add three Core Frame nodes for the availability zones and a Data pack Postgres node in each: pg-a (sync standby), pg-b (primary) and pg-c (async replica).
3. Above the zones, add a C4 Model Container for the order service and a Network pack Load Balancer for HAProxy.
4. Below them, add a C4 Model Container for etcd and a Data pack Object Store for the WAL archive.
5. Label the edges: "writes :5000", "reads :5001", "WAL (sync)" solid, "WAL (async)" dashed, "leader key, TTL 30 s" from pg-b to etcd, and a dashed edge from pg-a to etcd for the leader race.
6. Add a Core Markdown block with RPO, RTO, the lag budget and backup retention.

The example uses 11 diagram nodes, within the Free plan. To keep the diagram current as the cluster changes, see architecture diagrams that don't rot, and for the general method, the system design diagram example.

FAQ

Is a read replica a backup? No. Deletes and bad migrations replicate too. Backups with WAL archiving let you restore to a moment before the mistake.

Does synchronous replication mean zero data loss? For commits a synchronous standby confirmed, yes, as long as failover promotes only that standby. Commits accepted while no synchronous standby was attached exist only on the primary.

Why three etcd members and not two? etcd needs a majority to accept writes. Two members cannot lose either one, while three survive the loss of one and keep a majority on one side of a network split.

Can I use DNS instead of a proxy to find the primary? Yes, managed services do. Keep DNS TTLs short and make sure clients do not cache lookups for long; AWS recommends a JVM DNS cache TTL of no more than 60 seconds.

How often should we test failover? Regularly enough that the team trusts it, for example a planned switchover in every maintenance window, plus an unplanned failure test in staging.

Start free — one seat, no credit card.