PostgreSQL Replication and High Availability

PostgreSQL’s replication story is one of the most mature in the open-source database world, but it has a way of humbling you. Streaming replication looks simple until you hit your first replication slot that quietly fills your disk overnight. Patroni looks like magic until your etcd cluster loses quorum at 2 AM and the leader lock expires. Logical replication looks like the perfect zero-downtime upgrade path until you discover that ALTER TABLE is not replicated and your schema migration broke the subscriber.

I have managed PostgreSQL HA clusters in production — some handling hundreds of thousands of transactions per second, some backing payment systems where even a few seconds of data loss would be unacceptable — and the consistent lesson is that replication is a system you have to understand at every layer before you trust it. This post covers that system end to end: how WAL-based replication works, how to set it up correctly, when to use synchronous versus asynchronous replication, how logical replication unlocks capabilities physical replication cannot, how Patroni automates failover without introducing split-brain, and how to monitor all of it before a problem finds you.

How PostgreSQL Replication Works: The WAL Foundation

Every change in PostgreSQL — an INSERT, an UPDATE, a DELETE, a checkpoint — is first written to the Write-Ahead Log (WAL) before it touches data pages. This is the atomicity and durability guarantee: if the process crashes before writing data files, the WAL allows recovery on restart. Replication is, at its core, the application of this same mechanism across a network. Standbys replay the same WAL records the primary already generated. They are not executing queries; they are applying a deterministic log of physical changes.

This distinction matters enormously. Because physical replication replays at the block level — byte-for-byte copies of data pages — a standby running physical replication must be binary-identical to the primary. Same architecture, same major version, same tablespace layout. It cannot replicate a subset of tables, it cannot feed a different PostgreSQL version, and it cannot apply data to a table with a different column order. These limitations are not bugs; they are the direct consequence of operating below the SQL layer.

Logical replication, introduced in PostgreSQL 10 and improved in every major release since, operates at the row-change level. It decodes WAL into a stream of INSERT, UPDATE, and DELETE operations for specific tables and sends those operations to a subscriber. The subscriber can be a different PostgreSQL version, a different schema layout, or even a different database entirely. You pay for this flexibility: DDL changes are not replicated, sequences are not replicated, and the overhead of logical decoding is higher than raw WAL shipping.

Physical vs Logical at a Glance

Streaming Replication Setup

The most common deployment pattern is a primary with one or more hot standby servers receiving the WAL stream in real time. Here is what a two-node primary-standby setup looks like:

  Primary (10.0.0.10)                    Standby (10.0.0.11)
 +------------------------+             +------------------------+
 |                        |             |                        |
 |  Client writes         |             |  Read-only queries     |
 |       |                |             |       |                |
 |  PostgreSQL engine     |    WAL      |  PostgreSQL recovery   |
 |  writes WAL to pg_wal  +------------>|  replays WAL records   |
 |                        |  stream     |                        |
 |  WAL sender process    |             |  WAL receiver process  |
 |  (one per standby)     |             |  writes to pg_wal      |
 +------------------------+             +------------------------+
         |                                       |
   pg_stat_replication               standby.signal exists
   shows connection state            primary_conninfo set

Primary Configuration

On the primary, postgresql.conf needs these parameters:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

# postgresql.conf on primary

wal_level = replica          # minimum level required for streaming replication
                             # 'logical' is a superset -- required for logical replication

max_wal_senders = 10         # maximum number of simultaneous WAL sender processes
                             # one per standby + a few spare for pg_basebackup runs

wal_keep_size = 1024         # minimum megabytes of WAL to retain in pg_wal
                             # protects a briefly-lagging standby from being cut off
                             # set to 0 (default) only if you use replication slots

hot_standby = on             # allow read-only queries on standby servers
                             # harmless on primary; required to be set before
                             # pg_basebackup copies the config to the standby

max_slot_wal_keep_size = 10240   # PG 13+: drop a replication slot if its WAL
                                  # retention exceeds this many MB -- safety valve
                                  # against disk fill from an offline standby

The replication connection needs its own entry in pg_hba.conf. This is the authentication file, not a firewall — every replication connection is authenticated through it:

# pg_hba.conf on primary
# TYPE   DATABASE    USER        ADDRESS          METHOD
host     replication replicator  10.0.0.0/24      scram-sha-256

The DATABASE column must be replication — this is a special keyword, not an actual database name. The user named replicator must exist and hold the REPLICATION privilege. Create it:

1

CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'use-a-real-password-here';

Do not grant this role superuser. It only needs REPLICATION and LOGIN. Store the password in your secrets manager; it will need to appear in the standby’s primary_conninfo.

Building the Standby with pg_basebackup

With the primary configured, build the standby. On the standby server, as the postgres OS user:

1
2
3
4
5
6
7
8

pg_basebackup \
  -h 10.0.0.10 \
  -U replicator \
  -D /var/lib/postgresql/17/main \
  --checkpoint=fast \
  -P \
  -Xs \
  -R

Each flag matters:

• -h 10.0.0.10 — connect to the primary
• -U replicator — authenticate as the replication user
• -D /var/lib/postgresql/17/main — destination data directory; must be empty or non-existent
• --checkpoint=fast — trigger an immediate checkpoint on the primary before starting the backup, rather than waiting for the next scheduled one; shortens the backup window
• -P — print progress; useful for large databases
• -Xs — stream WAL during the backup using a separate WAL sender process; prevents WAL rotation from outpacing the base backup on a busy primary
• -R — write standby.signal and populate postgresql.auto.conf with primary_conninfo automatically

The -R flag is the key quality-of-life addition. Without it you would need to write standby.signal manually and add primary_conninfo to postgresql.auto.conf by hand. With it, pg_basebackup writes:

1
2

# postgresql.auto.conf on standby (written by pg_basebackup -R)
primary_conninfo = 'host=10.0.0.10 port=5432 user=replicator password=...'

The standby.signal file is an empty file in the data directory. Its presence tells PostgreSQL on startup to enter recovery mode and attempt to connect to the primary. When you promote the standby (via pg_ctl promote or a Patroni failover), standby.signal is removed and the instance becomes a standalone primary.

Start the standby with your normal init script. Check the logs: you should see LOG: started streaming WAL from primary at ... within a few seconds.

A note on pg_basebackup vs the old approach: before pg_basebackup was mature, DBAs used pg_start_backup() + rsync + pg_stop_backup(). This required manual WAL archiving and careful handling of the backup label. pg_start_backup() was removed in PostgreSQL 17. If you find documentation still referencing this approach, it is outdated. Use pg_basebackup.

Verifying the Connection: pg_stat_replication

On the primary, pg_stat_replication shows one row per connected standby. Here is annotated example output:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

SELECT * FROM pg_stat_replication \gx

-[ RECORD 1 ]----+------------------------------
pid              | 12847           -- PID of the WAL sender process on the primary
usesysid         | 16384           -- OID of the replication user
usename          | replicator      -- name of the replication user
application_name | walreceiver     -- set by application_name in primary_conninfo
client_addr      | 10.0.0.11       -- IP address of the standby
client_hostname  |                 -- reverse DNS lookup (usually blank)
client_port      | 54321           -- ephemeral port on the standby
backend_start    | 2026-05-31 ...  -- when this WAL sender connected
backend_xmin     |                 -- oldest XID the standby has told us it needs
state            | streaming       -- catchup -> streaming once standby is current
sent_lsn         | 1/A1234560      -- last WAL position sent to this standby
write_lsn        | 1/A1234560      -- last WAL position written to standby's disk
flush_lsn        | 1/A1234560      -- last WAL position flushed (fsync'd) on standby
replay_lsn       | 1/A1200000      -- last WAL position applied in standby recovery
write_lag        | 00:00:00.001    -- time between local flush and standby write ack
flush_lag        | 00:00:00.002    -- time between local flush and standby flush ack
replay_lag       | 00:00:00.015    -- time between local flush and standby replay ack
sync_priority    | 0               -- 0 = async; >0 = position in sync standby list
sync_state       | async           -- async / sync / quorum / potential
reply_time       | 2026-05-31 ...  -- time of last standby status message

The progression sent_lsn >= write_lsn >= flush_lsn >= replay_lsn always holds. The gap between sent_lsn and replay_lsn in bytes is your replication lag. The replay_lag timestamp column converts this to wall-clock time, which is more intuitive for alerting — but it measures the lag at the moment the standby last sent a status message, not necessarily right now.

Replication Slots

By default, the primary will recycle WAL files based on checkpoint history and wal_keep_size. If a standby falls behind by more than wal_keep_size megabytes of WAL — because it was restarted, the network was unstable, or a long-running query held back replay — the primary will recycle the WAL the standby still needs, and replication will fail with ERROR: requested WAL segment ... has already been removed.

Replication slots solve this by making the primary track each standby’s WAL consumption position explicitly and guarantee that no WAL the standby still needs is recycled. The primary will hold that WAL indefinitely — even through checkpoints, even across restarts.

Create a physical replication slot on the primary:

1

SELECT pg_create_physical_replication_slot('standby1');

Then reference it in the standby’s postgresql.auto.conf:

1
2

primary_conninfo = 'host=10.0.0.10 port=5432 user=replicator'
primary_slot_name = 'standby1'

Monitor slot health with:

1
2
3
4
5
6
7

SELECT
    slot_name,
    active,
    pg_size_pretty(
        pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)
    ) AS wal_retained
FROM pg_replication_slots;

This query tells you how much WAL the primary is keeping because of this slot. When active is true, the standby is connected and consuming. When active is false, the standby is offline — and WAL is accumulating.

This is the most dangerous aspect of replication slots. An offline standby with an active slot will cause WAL to pile up in pg_wal without limit. Production databases with heavy write loads can fill a 2 TB disk in hours. The primary will crash with FATAL: could not write to file "pg_wal/...": No space left on device — taking down the primary to protect an already-dead standby.

PostgreSQL 13 introduced max_slot_wal_keep_size as a safety valve:

1

max_slot_wal_keep_size = 10240   # drop the slot if WAL retention exceeds 10 GB

When the limit is exceeded, PostgreSQL invalidates the slot. The standby will need to be rebuilt with pg_basebackup. Set this parameter. Do not run replication slots without it in production.

The choice between wal_keep_size and replication slots is a tradeoff between simplicity and guarantee:

• wal_keep_size is a rolling buffer. It keeps a minimum amount of WAL regardless of standby state. It does not prevent WAL loss for a standby that falls behind by more than the buffer, but it also does not accumulate WAL unboundedly if a standby is offline.
• Replication slots guarantee the standby will never lose WAL while connected, but require active monitoring of slot lag and max_slot_wal_keep_size as a backstop.

In practice, use replication slots for standbys you depend on for HA — especially when combined with Patroni — and set max_slot_wal_keep_size aggressively.

Synchronous vs Asynchronous Replication

The default replication mode is asynchronous. The primary writes the WAL locally, sends it to standbys, and immediately confirms the commit to the client. If the primary crashes before the standby receives that WAL, those committed transactions are lost. The data loss window is the amount of WAL that was in-flight between the primary’s local flush and the standby’s disk write — typically sub-second on a LAN, but potentially several seconds on a loaded system or across a WAN link.

For most read-heavy applications with conventional durability requirements, asynchronous replication with a good monitoring setup is the right choice. For payment systems, financial ledgers, or any workload where losing a single committed transaction is unacceptable, you need synchronous replication.

Configuring Synchronous Replication

Set on the primary in postgresql.conf:

1

synchronous_standby_names = '1 (standby1, standby2)'

This instructs the primary to wait for at least one of standby1 or standby2 to acknowledge the WAL before confirming the commit to the client. If both standbys are up, standby1 is preferred (it is first in the list). If standby1 goes down, standby2 takes over as the synchronous standby automatically.

The synchronous_commit parameter on the primary (or per transaction) controls exactly what the standby must acknowledge:

1
2
3
4

synchronous_commit = remote_apply   # strongest: standby has replayed the change
# synchronous_commit = remote_write # standby written to OS buffer (not yet fsync'd)
# synchronous_commit = local        # only local WAL flush; standby is async
# synchronous_commit = off          # async even for local WAL flush (dangerous)

remote_apply is the strongest setting: it ensures that when the commit returns to the client, the standby has not only received and written the WAL but also applied the changes to its data pages. This means reads on the standby immediately reflect the committed data — zero replication lag for reads — but it adds the highest latency to commits.

PostgreSQL 10+ Quorum Commit

PostgreSQL 10 introduced ANY N (...) syntax for quorum-based synchronous commit:

1

synchronous_standby_names = 'ANY 2 (standby1, standby2, standby3)'

This waits for acknowledgment from any 2 of the 3 named standbys. The cluster can sustain one standby failure without blocking writes. This is the recommended pattern for high-availability setups that need zero data loss but cannot afford a single standby outage blocking the entire system.

The Latency Cost

Synchronous replication adds exactly one network round-trip to every commit. There is no way around this — you are waiting for a packet to cross the network to the standby and return. On a LAN with sub-millisecond RTT, this is often imperceptible. Across data centers with 5-20ms RTT, it will show up clearly in commit latency percentiles, and any workload that does many small single-row commits will feel it hard.

The classic mitigation is transaction batching — fewer, larger transactions mean fewer synchronous acknowledgments per second. Another approach is using remote_write instead of remote_apply, which requires the standby to write to the OS buffer but not fsync — this halves the acknowledgment latency at the cost of a narrow data loss window during a simultaneous standby OS crash.

One important operational note: if all synchronous standbys disconnect simultaneously (network partition, maintenance window), the primary will hang on every commit indefinitely waiting for an acknowledgment that will never come. This is the intended behavior — the primary is protecting against data loss — but it will look like a complete outage to your application. Always have a plan: either downgrade synchronous_commit to local temporarily, or ensure your quorum config can tolerate the expected failure scenario.

Logical Replication

Physical streaming replication is a single-primary to N-standby model where all standbys are byte-identical to the primary. Logical replication breaks that constraint at the cost of some capabilities.

  Publisher (PostgreSQL 16)               Subscriber (PostgreSQL 17)
 +--------------------------+            +--------------------------+
 |                          |            |                          |
 |  WAL                     |            |  Apply worker process    |
 |   |                      |            |       |                  |
 |  Logical decoding        |  decoded   |  Applies INSERTs,        |
 |  (pgoutput plugin)       +----------->|  UPDATEs, DELETEs        |
 |   |                      |  row ops   |  to subscriber tables    |
 |  Publication             |            |                          |
 |  (selected tables)       |            |  Subscription            |
 |                          |            |  (connected publication) |
 |  Replication slot        |            |                          |
 |  (tracks subscriber LSN) |            |  Tables can differ in    |
 |                          |            |  columns, indexes, etc.  |
 +--------------------------+            +--------------------------+
         |
   pg_stat_replication
   pg_publication_tables

Publisher Setup

The publisher must have wal_level = logical (a superset of replica). This enables the logical decoding infrastructure, which has a modest performance overhead — roughly 5-15% on write-heavy workloads depending on the workload mix. If you are running wal_level = replica today for streaming replication and want to add logical replication, changing wal_level requires a PostgreSQL restart.

1
2
3
4
5
6
7
8

-- On the publisher
CREATE PUBLICATION app_pub FOR TABLE orders, products, customers;

-- Or publish all tables in the current database
CREATE PUBLICATION full_pub FOR ALL TABLES;

-- Publish only specific operations with column filtering (PG 15+)
CREATE PUBLICATION reports_pub FOR TABLE orders (id, status, total, created_at);

A publication defines what changes are exported. A replication slot is created automatically when a subscription connects; this slot tracks the subscriber’s WAL consumption position so the publisher does not recycle WAL the subscriber still needs. The same slot-overflow risk from physical replication applies here — monitor logical replication slot lag just as carefully.

Subscriber Setup

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

-- On the subscriber PostgreSQL instance
-- Tables must exist and have compatible schema first
CREATE TABLE orders (id bigint PRIMARY KEY, status text, total numeric, created_at timestamptz);
CREATE TABLE products (id bigint PRIMARY KEY, name text, price numeric);
CREATE TABLE customers (id bigint PRIMARY KEY, email text);

-- Create the subscription
CREATE SUBSCRIPTION app_sub
    CONNECTION 'host=publisher.internal port=5432 dbname=appdb user=replicator password=...'
    PUBLICATION app_pub;

On subscription creation, PostgreSQL performs an initial table sync — it copies all existing rows from the publisher tables to the subscriber tables using a temporary replication slot and a COPY operation. This can take hours for large tables. The subscription enters normal streaming mode only after the initial sync completes for all tables in the publication. Check progress:

1
2
3
4
5
6
7
8

-- On subscriber
SELECT subname, relid::regclass, srsyncdone, srsublsn
FROM pg_subscription_rel;
-- srsubstate values:
-- i = initializing
-- d = data copy in progress
-- s = synchronized (initial copy done, streaming)
-- r = ready (all tables synced)

Monitoring Logical Replication

On the publisher:

1
2
3
4
5

SELECT pubname, schemaname, tablename FROM pg_publication_tables;

-- The same pg_stat_replication view applies here
SELECT client_addr, state, sent_lsn, write_lsn, replay_lsn, sync_state
FROM pg_stat_replication;

On the subscriber:

1
2
3

SELECT subname, pid, received_lsn, latest_end_lsn,
       (now() - last_msg_receipt_time) AS last_heard
FROM pg_stat_subscription;

Limitations You Must Know Before Deploying

Logical replication does not replicate:

• DDL — ALTER TABLE, CREATE INDEX, DROP COLUMN are not sent to the subscriber. You must apply schema changes to the subscriber manually before applying them to the publisher (for additive changes) or after (for destructive changes). Get this order wrong and replication breaks.
• Sequences — nextval() calls on the publisher do not advance the subscriber’s sequences. If you fail over to a subscriber, you must manually advance sequences past the last publisher value or risk primary key conflicts.
• Large objects — pg_largeobject data is not replicated.
• TRUNCATE — supported from PostgreSQL 11, but requires the subscriber table to have no foreign key references from unreplicated tables.

PostgreSQL 16 brought parallel apply for large transactions, allowing subscribers to apply large transactions using multiple worker processes. PostgreSQL 17 added failover slot synchronization: logical replication slots can now be synchronized to standbys via pg_sync_replication_slots() so that on failover the logical replication subscriber can continue without reinitialization. This was a major operational pain point in PG 16 and earlier — a primary failover would break all logical replication subscriptions pointing at the old primary.

Zero-Downtime Major Version Upgrade with Logical Replication

The classic upgrade procedure for zero downtime uses logical replication between the old and new major versions:

1. Stand up a new PostgreSQL instance (e.g., PG 17) alongside the existing PG 16 primary.
2. Apply the same schema to PG 17 via pg_dump --schema-only or your migration tool.
3. Set wal_level = logical on PG 16 if not already set (requires restart).
4. Create a publication on PG 16: CREATE PUBLICATION upgrade_pub FOR ALL TABLES;
5. Create a subscription on PG 17: CREATE SUBSCRIPTION upgrade_sub CONNECTION '...' PUBLICATION upgrade_pub;
6. Wait for initial sync to complete and for replication lag to approach zero.
7. Take a brief maintenance window (typically seconds to under a minute): quiesce application writes, wait for lag to hit zero, verify row counts match on critical tables.
8. Update the application’s connection string to point at PG 17.
9. Drop the subscription on PG 17, decommission PG 16.

This procedure keeps the old primary fully available until the last moment. The only downtime is the connection string switch, which can be as fast as a HAProxy config reload. Compare this to pg_upgrade --link, which requires taking the old primary down entirely and carries risk of needing to roll back at a point where the old data directory has already been modified.

pg_basebackup In Depth

pg_basebackup is the standard tool for creating a consistent binary copy of a running PostgreSQL primary. It uses the replication protocol — it authenticates as a replication user, opens a replication connection, and streams data files while simultaneously streaming WAL changes that occur during the backup.

The full flag reference for production use:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

pg_basebackup \
  -h primary.internal \
  -p 5432 \
  -U replicator \
  -D /var/lib/postgresql/17/main \
  --checkpoint=fast \
  --wal-method=stream \
  --progress \
  --write-recovery-conf \
  --gzip \
  --label="standby_$(date +%Y%m%d)"

For large databases (500 GB+), pg_basebackup can take hours. The backup is safe to run on a live primary — it uses an internal backup mode — but it will consume significant I/O bandwidth and one WAL sender slot. Schedule it during off-peak hours.

To create a compressed tar archive instead of a live data directory (useful for shipping to a backup server or another host):

1
2
3
4
5
6

pg_basebackup \
  -h primary.internal \
  -U replicator \
  -Ft \
  -z \
  -D /backup/pg17/base

This writes base.tar.gz (data directory) and pg_wal.tar.gz (WAL segments) to the destination. On the receiving end, extract and configure normally. For a standby built from a tar backup, you will need to add standby.signal and primary_conninfo manually since --write-recovery-conf cannot be used with tar format and a remote destination.

For compressing on the server side before transfer (PG 15+), which reduces network traffic for remote standbys:

1
2
3
4
5
6
7
8

pg_basebackup \
  -h primary.internal \
  -U replicator \
  -D /var/lib/postgresql/17/main \
  --checkpoint=fast \
  --wal-method=stream \
  --write-recovery-conf \
  --compress=server-gzip:6

Patroni for Automatic Failover

Manual failover — SSHing into a server, promoting a standby, updating connection strings, reconfiguring the old primary as a new standby — has two failure modes. The first is human reaction time: if your on-call engineer is asleep and the primary goes down at 3 AM, your database is down until they wake up. The second is split-brain: if the primary is only partially unavailable (a network partition where the primary can still reach some clients but not the monitoring system), promoting a standby leaves two nodes believing they are the primary. Both start accepting writes. Your data is now irreversibly diverged.

Patroni solves both problems. It is a Python daemon that wraps PostgreSQL and uses a Distributed Configuration Store (DCS) — etcd, Consul, or ZooKeeper — as an external arbiter. The fundamental invariant: there is exactly one leader lock in the DCS. Whoever holds that lock is the primary. PostgreSQL instances without the lock are standbys. The DCS, being a distributed consensus system, guarantees this invariant survives partial failures.

Architecture

     +---------------------------------------------+
     |         etcd Cluster (3 nodes)               |
     |  10.0.1.10:2379  10.0.1.11:2379  10.0.1.12:2379 |
     |  (Raft consensus -- majority vote required)  |
     +--------+-------------------+-----------------+
              |                   |
   +----------+-------+   +-------+----------+   +------------------+
   |  Patroni Node 1  |   |  Patroni Node 2  |   |  Patroni Node 3  |
   |  (Leader)        |   |  (Replica)       |   |  (Replica)       |
   |                  |   |                  |   |                  |
   |  PostgreSQL 17   |   |  PostgreSQL 17   |   |  PostgreSQL 17   |
   |  (primary)       |<--+  (hot standby)   |   |  (hot standby)   |
   |  :5432           |WAL|  streams WAL     |   |  streams WAL     |
   |                  |   |  :5432           |   |  :5432           |
   |  REST API :8008  |   |  REST API :8008  |   |  REST API :8008  |
   +------------------+   +------------------+   +------------------+
            |                      |                      |
   +--------+----------------------+----------------------+-------+
   |                           HAProxy                            |
   |                                                              |
   |  :5000 (writes) -- httpchk GET /primary  -> HTTP 200        |
   |      routes only to the node Patroni marks as leader        |
   |                                                              |
   |  :5001 (reads)  -- httpchk GET /replica  -> HTTP 200        |
   |      routes to standbys (round-robin)                       |
   +--------------------------------------------------------------+
                               |
                   Application clients

The etcd cluster is separate from the PostgreSQL nodes — typically 3 or 5 nodes running only etcd. Patroni connects to etcd to acquire and renew the leader lock. The lock has a TTL (typically 30 seconds). Every loop_wait seconds (typically 10 seconds), the leader’s Patroni daemon renews the lock. If the primary node fails, the lock expires after one TTL, and a standby’s Patroni daemon acquires the lock and promotes its PostgreSQL instance.

As of Patroni 4.1.3 (the current release as of May 2026), etcd 3.5.x is the recommended DCS backend. The etcd3 configuration key in patroni.yml uses etcd’s v3 protocol, which Patroni 4.x declares production-ready. Avoid mixing etcd protocol versions in the same Patroni cluster: keys written with the v2 protocol are invisible to v3 clients and vice versa.

patroni.yml Configuration

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61

name: pg-node1   # unique name for this Patroni member

restapi:
  listen: 0.0.0.0:8008
  connect_address: 10.0.0.10:8008

etcd3:           # use etcd3 for protocol v3 (Patroni 4.x recommended)
  hosts: 10.0.1.10:2379,10.0.1.11:2379,10.0.1.12:2379

bootstrap:
  dcs:
    ttl: 30                    # leader lock TTL in seconds
    loop_wait: 10              # how often Patroni checks DCS and renews lock
    retry_timeout: 10          # how long to retry failed DCS operations
    maximum_lag_on_failover: 1048576   # 1 MB: max replica lag to be eligible for promotion
    postgresql:
      use_pg_rewind: true      # use pg_rewind to rejoin old primary after failover
      use_slots: true          # use replication slots for standbys
      parameters:
        wal_level: replica
        hot_standby: on
        max_wal_senders: 10
        max_replication_slots: 10
        wal_log_hints: on      # required for pg_rewind (see pg_rewind section)
        max_slot_wal_keep_size: 10240

  initdb:
    - encoding: UTF8
    - data-checksums            # enable data checksums; also satisfies pg_rewind requirement

  pg_hba:
    - host replication replicator 10.0.0.0/24 scram-sha-256
    - host all all 10.0.0.0/24 scram-sha-256

postgresql:
  listen: 0.0.0.0:5432
  connect_address: 10.0.0.10:5432
  data_dir: /var/lib/postgresql/17/main
  bin_dir: /usr/lib/postgresql/17/bin
  pgpass: /var/lib/postgresql/.pgpass

  authentication:
    replication:
      username: replicator
      password: repl-password-here
    superuser:
      username: postgres
      password: super-password-here
    rewind:
      username: rewind_user     # used by pg_rewind to connect to new primary
      password: rewind-password-here

  parameters:
    max_connections: 200
    shared_buffers: 4GB
    effective_cache_size: 12GB

watchdog:
  mode: automatic              # automatic fencing via /dev/watchdog
  device: /dev/watchdog
  safety_margin: 5

The watchdog section deserves attention. Fencing is how Patroni prevents split-brain: when a node loses the leader lock (because it failed to renew), Patroni immediately calls pg_ctl stop. But what if PostgreSQL is so hung that pg_ctl stop does not return? The watchdog device — a kernel-level timer that must be periodically reset — provides a hardware-level guarantee: if Patroni stops resetting the watchdog (because Patroni itself is hung), the kernel will reset the machine. This ensures the old primary is truly dead before the new one starts accepting writes.

patronictl Commands

The patronictl command-line tool is your interface to the cluster state. Every DBA managing a Patroni cluster should have these memorized.

Check cluster health:

1
2
3
4
5
6
7
8
9

patronictl -c /etc/patroni/patroni.yml list

# + Cluster: pg-cluster ------+----+-----------+
# | Member   | Host             | Role    | State   | TL | Lag in MB |
# +----------+------------------+---------+---------+----+-----------+
# | pg-node1 | 10.0.0.10:5432   | Leader  | running | 5  |           |
# | pg-node2 | 10.0.0.11:5432   | Replica | running | 5  |         0 |
# | pg-node3 | 10.0.0.12:5432   | Replica | running | 5  |         0 |
# +----------+------------------+---------+---------+----+-----------+

TL is the timeline number — it increments on every failover. All replicas should be on the same timeline as the leader. A replica on a lower timeline has not yet caught up from the last failover.

Planned switchover (zero data loss, for maintenance):

1
2
3
4

patronictl -c /etc/patroni/patroni.yml switchover pg-cluster \
  --master pg-node1 \
  --candidate pg-node2 \
  --scheduled now

Patroni waits for the candidate standby to fully catch up, then atomically demotes the primary and promotes the standby. The operation takes a few seconds and is typically invisible to read queries. Write queries will see a brief pause.

Forced failover (when the primary is completely dead and cannot be recovered):

1
2
3

patronictl -c /etc/patroni/patroni.yml failover pg-cluster \
  --master pg-node1 \
  --candidate pg-node2

Use failover only when the primary is confirmed dead. Unlike switchover, it does not wait for the candidate to be fully caught up — it promotes the standby at its current position. If the standby was lagging, those transactions are lost. For asynchronous setups, this is an accepted tradeoff in exchange for faster recovery.

Reinitialize a broken replica that cannot rejoin via streaming:

1

patronictl -c /etc/patroni/patroni.yml reinit pg-cluster pg-node3

This wipes the standby’s data directory and runs pg_basebackup from the current leader. Use when a replica’s WAL diverged beyond what streaming can repair, or when pg_rewind fails.

Pause automatic failover for maintenance:

1
2
3

patronictl -c /etc/patroni/patroni.yml pause pg-cluster
# ... perform maintenance ...
patronictl -c /etc/patroni/patroni.yml resume pg-cluster

Edit cluster-wide DCS configuration:

1

patronictl -c /etc/patroni/patroni.yml edit-config pg-cluster

This opens the cluster-wide Patroni configuration stored in etcd in your $EDITOR. Changes here propagate to all cluster members without restarting Patroni. Use this to change maximum_lag_on_failover, synchronous_standby_names, or PostgreSQL parameters that Patroni manages.

pg_rewind: Rejoining a Failed Primary

After a failover, the old primary is in an awkward state. It has a WAL history that diverged from the new primary at the moment of failover. It cannot simply be started as a standby pointing at the new primary — its data files reflect a timeline the new primary no longer recognizes.

The naive solution is pg_basebackup: rebuild the old primary from scratch as a new standby. This works, but for a 1 TB database it takes hours and generates significant network traffic during that time.

pg_rewind is the efficient alternative. It identifies the point at which the two timelines diverged, copies only the data blocks that changed on the new primary after divergence, and rewinds the old primary’s data directory to a consistent state from which streaming replication can take over. For a typical failover where the divergence point is seconds or minutes in the past, pg_rewind completes in minutes rather than hours.

Timeline before failover:

  Old Primary  ────────────────────── X (crashed)
                                      |
                                      | divergence point
                                      |
  Standby      ─────────────────────-+
                                      promoted --> new primary, TL 2

  New primary (TL 2)  ──────────────────────────────────────>

  Old primary has data blocks from TL 1 past the divergence point.
  pg_rewind replaces those blocks with the new primary's versions.
  Streaming replication then catches up from the divergence point.

Requirements

pg_rewind requires that one of the following was enabled before the failure on the old primary:

• wal_log_hints = on in postgresql.conf, or
• Data checksums enabled at initdb time (initdb --data-checksums)

Both cause PostgreSQL to write full page images on the first modification of a page after a checkpoint. pg_rewind needs these full page images to identify and copy changed blocks. If neither is enabled, pg_rewind will fail with an error about missing full-page writes.

In PostgreSQL 17, wal_log_hints defaults to off and data checksums default to off (PostgreSQL 18 will default data checksums to on). This means you need to explicitly enable one of them. Patroni’s bootstrap configuration handles this automatically when use_pg_rewind: true is set — it adds wal_log_hints = on to the PostgreSQL parameters. If you are setting up replication manually without Patroni, add wal_log_hints = on now, before you ever need pg_rewind.

PostgreSQL 17 also added a --sync-method option to pg_rewind. The default is fsync, which recursively opens and synchronizes all files in the data directory after rewinding. On Linux, syncfs can be used instead to ask the operating system to synchronize the whole filesystem, which can be faster for large data directories.

You also need a user with permission to read changed blocks from the new primary. Patroni configures this via the rewind authentication block. For manual setups, grant it explicitly in PostgreSQL 14+:

1
2
3
4
5
6
7

-- PG 14+: grant without superuser
CREATE ROLE rewind_user WITH LOGIN PASSWORD '...';
GRANT pg_rewind TO rewind_user;
GRANT EXECUTE ON FUNCTION pg_ls_dir(text, boolean, boolean) TO rewind_user;
GRANT EXECUTE ON FUNCTION pg_stat_file(text, boolean) TO rewind_user;
GRANT EXECUTE ON FUNCTION pg_read_binary_file(text) TO rewind_user;
GRANT EXECUTE ON FUNCTION pg_read_binary_file(text, bigint, bigint, boolean) TO rewind_user;

Running pg_rewind Manually

The old primary must be shut down before running pg_rewind. Starting from PostgreSQL 14, if the old primary did not shut down cleanly (e.g., it was killed or OOM-killed), pg_rewind will start it in single-user mode to complete crash recovery first before rewinding:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

# On the old primary server, as the postgres OS user
# PostgreSQL must be stopped (or pg_rewind will attempt single-user crash recovery)

pg_rewind \
  --target-pgdata=/var/lib/postgresql/17/main \
  --source-server="host=10.0.0.11 port=5432 user=rewind_user dbname=postgres" \
  --progress \
  --restore-target-wal   # PG 13+: fetch missing WAL from restore_command if needed

# After pg_rewind completes, configure as standby:
touch /var/lib/postgresql/17/main/standby.signal
cat >> /var/lib/postgresql/17/main/postgresql.auto.conf << 'EOF'
primary_conninfo = 'host=10.0.0.11 port=5432 user=replicator'
EOF

# Start PostgreSQL -- it enters recovery mode and streams from the new primary
pg_ctlcluster 17 main start

When Patroni is managing the cluster, you rarely need to run pg_rewind manually. Patroni detects the timeline mismatch automatically when the old primary comes back online, runs pg_rewind against the current leader, and reintegrates the node as a standby. This is the main benefit of use_pg_rewind: true in the bootstrap configuration.

When pg_rewind Cannot Help

pg_rewind requires that the WAL of the new primary since the divergence point is still available — either in pg_wal on the new primary or via a WAL archive configured as restore_command. If the new primary has been running for an extended period after the failover and has recycled its WAL, and no WAL archive is configured, pg_rewind will fail. In this case, fall back to pg_basebackup.

This is another compelling reason to configure a WAL archive alongside streaming replication. Whether you use pgBackRest, WAL-G, or a simple archive_command to a remote filesystem, the archive is a permanent record of all WAL that gives pg_rewind a fallback when the new primary’s live pg_wal is gone.

Monitoring Replication Lag

Replication lag has two distinct meanings that get conflated: bytes of WAL the standby has not yet received or applied, and wall-clock time since those bytes were generated. Both matter, but for different reasons. Byte lag tells you how much data would be lost if the primary failed right now. Time lag tells you how stale the standby’s data is for read queries.

Primary-Side Monitoring

The primary has full visibility into all connected standbys via pg_stat_replication:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

-- Bytes of unacknowledged WAL per standby (data loss exposure)
SELECT
    client_addr,
    application_name,
    state,
    pg_size_pretty(
        pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)
    ) AS lag_bytes,
    write_lag,
    flush_lag,
    replay_lag,
    sync_state
FROM pg_stat_replication
ORDER BY pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) DESC NULLS LAST;

Standby-Side Monitoring

From the standby itself (useful when you cannot query the primary, or for monitoring the standby’s own perspective):

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

-- How long ago was the last WAL record applied?
SELECT now() - pg_last_xact_replay_timestamp() AS replication_delay;

-- Is this instance in recovery (i.e., is it a standby)?
SELECT pg_is_in_recovery();

-- Current WAL receive and replay positions
SELECT pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn();

-- If these differ, the standby has received WAL it has not yet applied
-- (apply is the bottleneck, not network)
SELECT pg_wal_lsn_diff(
    pg_last_wal_receive_lsn(),
    pg_last_wal_replay_lsn()
) AS pending_apply_bytes;

Diagnosing the Bottleneck

The three lag columns in pg_stat_replication tell you where in the pipeline the standby is falling behind:

• write_lag consistently greater than zero: the network is the bottleneck. WAL is sitting in the primary’s send buffer waiting for the network path to the standby. Check network bandwidth and latency between primary and standby.
• flush_lag significantly larger than write_lag: the standby’s disk is the bottleneck. WAL is arriving but not being fsync’d fast enough. Check standby I/O wait and disk saturation.
• replay_lag significantly larger than flush_lag: the standby’s CPU or I/O is saturated with applying changes. This happens when the standby has heavy read traffic competing with WAL replay, or when many parallel writers on the primary generate WAL faster than the standby’s single recovery process can replay. Physical replication does not have parallel WAL apply; if this is your sustained bottleneck, consider a more powerful standby or reducing read load.

Replication Slot Monitoring

Monitor slot lag separately from pg_stat_replication — a slot for an offline standby is invisible in pg_stat_replication because there is no active connection, but WAL is silently accumulating:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

SELECT
    slot_name,
    slot_type,
    active,
    active_pid,
    pg_size_pretty(
        pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)
    ) AS wal_retained,
    -- for logical slots, also check confirmed_flush_lsn:
    CASE WHEN slot_type = 'logical' THEN
        pg_size_pretty(
            pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)
        )
    END AS logical_lag
FROM pg_replication_slots
ORDER BY pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) DESC NULLS LAST;

Alert on wal_retained exceeding 5 GB. Alert immediately on active = false for any slot that is supposed to be serving a live standby.

Prometheus and postgres_exporter

The community postgres_exporter exposes all the above as Prometheus metrics out of the box. Key metrics for alerting:

# Replay lag in seconds per standby
pg_stat_replication_replay_lag{application_name="standby1"}

# Whether this instance is in recovery (standby)
pg_recovery_is_in_recovery

# WAL retained by replication slots (bytes)
pg_replication_slots_pg_wal_lsn_diff

# Active status of replication slots (1=active, 0=inactive)
pg_replication_slots_active

Recommended Prometheus alert thresholds:

HAProxy and Connection Routing

A correct HA setup is incomplete without a layer in front of PostgreSQL that routes connections to the current primary. Clients must not have the primary’s IP hardcoded — after a failover, the IP changes, and every application would need reconfiguration.

Patroni’s REST API is designed specifically to integrate with HAProxy. Each Patroni node exposes HTTP endpoints:

• GET /primary — returns 200 OK if this node is the primary, 503 otherwise
• GET /replica — returns 200 OK if this node is a replica (not the primary), 503 otherwise
• GET /health — returns 200 OK if this node is healthy (primary or replica)
• GET /read-only — returns 200 OK if this node is healthy and can serve reads

HAProxy health checks poll these endpoints and route traffic accordingly:

# /etc/haproxy/haproxy.cfg

global
    maxconn 1000

defaults
    log     global
    mode    tcp
    retries 3
    timeout connect 5s
    timeout client  30s
    timeout server  30s
    timeout check   5s

#
# Write port: routes to current primary only
#
frontend pg_write
    bind *:5000
    default_backend pg_primary

backend pg_primary
    option httpchk GET /primary
    http-check expect status 200
    default-server inter 3s fastinter 1s fall 3 rise 2 on-marked-down shutdown-sessions
    server pg-node1 10.0.0.10:5432 check port 8008
    server pg-node2 10.0.0.11:5432 check port 8008
    server pg-node3 10.0.0.12:5432 check port 8008

#
# Read port: routes to replicas (excludes primary)
#
frontend pg_read
    bind *:5001
    default_backend pg_replicas

backend pg_replicas
    balance roundrobin
    option httpchk GET /replica
    http-check expect status 200
    default-server inter 3s fastinter 1s fall 3 rise 2
    server pg-node1 10.0.0.10:5432 check port 8008
    server pg-node2 10.0.0.11:5432 check port 8008
    server pg-node3 10.0.0.12:5432 check port 8008

With this configuration, your application uses port 5000 for writes and port 5001 for reads. HAProxy polls the Patroni REST API every 3 seconds. After a failover, HAProxy detects the old primary’s /primary endpoint returning 503 and the new primary’s returning 200, and reroutes write traffic within one check interval — typically under 10 seconds from the failover event.

Virtual IP Alternative

For simpler deployments with a single write endpoint and no read distribution, a Virtual IP (VIP) is an attractive alternative to HAProxy. Patroni supports on_role_change callbacks:

1
2
3

postgresql:
  callbacks:
    on_role_change: /etc/patroni/promote-vip.sh

The promote-vip.sh script adds the VIP to the network interface when this node becomes primary and removes it when it becomes a replica:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

#!/bin/bash
ROLE=$1
VIP=10.0.0.100/24
IFACE=eth0

if [ "$ROLE" = "master" ]; then
    ip addr add $VIP dev $IFACE
    arping -c 3 -I $IFACE ${VIP%/*}   # gratuitous ARP to update switch/router tables
elif [ "$ROLE" = "replica" ]; then
    ip addr del $VIP dev $IFACE 2>/dev/null || true
fi

This is simpler than HAProxy but provides no read distribution and no connection multiplexing. Keepalived with VRRP is a more production-hardened VIP management approach for environments where ARP announcements need to be handled more robustly.

DNS-Based Routing

For environments where HAProxy and VIPs are not options (some cloud environments, Kubernetes with external PostgreSQL), DNS-based routing can work: a short-TTL DNS record (pgwrite.internal pointing to the current primary IP) is updated by Patroni’s callback scripts on failover. The disadvantage is TTL propagation delay — 30-60 seconds of misrouting during failover — and the fact that many PostgreSQL drivers and connection pools resolve DNS once at connection open and do not re-resolve until the connection is closed. Use with caution and short TTLs (10-30 seconds maximum).

Production Operational Notes

A few hard-learned lessons that do not fit cleanly into any other section:

Test your failover before you need it. Run patronictl switchover quarterly. A failover mechanism you have never exercised in production is not a failover mechanism; it is a hope. Patroni switchovers are non-destructive and take less than 30 seconds in a healthy cluster. Do them regularly. Know what your application does during the brief write pause, and verify that clients reconnect automatically.

Replication lag during maintenance. If you are applying OS patches, taking a standby offline temporarily, or running a large operation on the primary, replication lag will spike. A VACUUM FULL on a large table generates enormous WAL; prefer pg_repack for production table bloat reclamation. Have a runbook that defines acceptable lag thresholds and what to do when they are breached.

Connection pooling. HAProxy provides connection routing, but it is not a connection pool. A heavily connected application should sit behind PgBouncer in transaction mode on each PostgreSQL node, with HAProxy routing to PgBouncer instances rather than directly to PostgreSQL. PgBouncer is ignorant of primary/replica state; HAProxy handles that routing; PgBouncer handles the N:M connection multiplexing. The two tools compose cleanly.

etcd sizing. The etcd cluster is the brain of your PostgreSQL HA setup. Run it on separate nodes from PostgreSQL — never colocate etcd with your database nodes, because a disk-full event or OOM on a PostgreSQL node should not take etcd with it. etcd is lightweight in CPU and memory, but it is latency-sensitive: run it on fast SSDs and keep network latency between etcd nodes under 10ms. Use a 3-node etcd cluster for single-DC deployments; use 5 nodes for multi-AZ deployments where you want to tolerate 2 simultaneous node failures.

Backup is not replication. Physical standbys protect against instance failure. They do not protect against logical errors: an accidental DROP TABLE, a bulk DELETE without a WHERE clause, or an application bug that corrupts rows. Those changes replicate instantly to all standbys. You need a separate backup solution — pgBackRest or WAL-G — that provides point-in-time recovery. Replication and backup solve different problems. Both are required for a complete data protection strategy.

The pg_stat_replication view lies by omission. It only shows currently-connected standbys. An offline standby with a replication slot does not appear here — but its slot is still accumulating WAL. Always query pg_replication_slots separately. The combination of an inactive slot and no max_slot_wal_keep_size limit has caused primary crashes in production more than once. Monitor both views, alert on both.

PostgreSQL’s replication infrastructure has reached a level of maturity where building a production HA cluster is genuinely tractable with open-source tools: pg_basebackup for initial standby creation, streaming replication for continuous WAL delivery, Patroni 4.x with etcd 3.5.x for automatic failover, and pg_rewind for efficient reintegration of a failed primary. The complexity is real — etcd adds operational overhead, replication slots add a failure mode, synchronous replication adds latency — but the building blocks are well-documented and the operational patterns are well-established.

What separates a solid HA deployment from a shaky one is not the initial setup but the ongoing operations: monitoring that alerts early, runbooks that have been actually exercised, backup infrastructure that is tested regularly, and a team that has done a failover drill and knows how long each step takes. Build the automation, then prove it works before 3 AM makes you prove it under pressure.