Replication & High AvailabilityReplication & High Availability

Part of the PostgreSQL DBA Roadmap knowledge base.

Overview

A single PostgreSQL instance is a single point of failure. The disk can fail, the host can crash, a data center can lose power, and every one of those events takes the database — and every application that depends on it — down with it. Replication is the practice of maintaining one or more additional copies of the database on separate servers, kept continuously in sync with the primary, so that the system as a whole survives the failure of any one node.

Replication earns its keep for three distinct reasons, and it’s worth being precise about which one a given deployment is actually solving for, because the right architecture differs:

• Read scaling. Once data exists on a replica, read-only queries — reporting, analytics, search, dashboards — can run against the replica instead of competing with write traffic on the primary. This offloads CPU, memory, and I/O pressure from the one node that must accept writes.
• High availability (HA). If the primary dies, a replica that already has (nearly) all of the data can be promoted to take over as the new primary in seconds to minutes, instead of the hours a restore-from-backup would take. This is what turns a hardware failure into a brief blip instead of an outage.
• Disaster recovery (DR). A replica placed in a different availability zone, region, or data center survives failures that take out an entire site — a fire, a regional network partition, a cloud provider outage — that no amount of local redundancy protects against.

These three motivations are complementary, not mutually exclusive, and a mature production setup usually gets all three from the same physical topology: replicas serve read traffic day-to-day, stand ready to be promoted if the primary fails, and at least one of them lives far enough away to survive a site-level disaster.

This note covers PostgreSQL’s two replication mechanisms (streaming/physical and logical), how automated failover tooling like Patroni turns a set of independent replicas into a self-healing cluster, and the connection-routing layer (HAProxy, PgBouncer, Keepalived) that makes failover invisible to applications. Backup and point-in-time recovery — the complementary discipline of protecting against data loss rather than downtime — is covered in Backup & Recovery.

Fundamentals

WAL is the foundation of replication

PostgreSQL doesn’t have a separate replication protocol bolted onto the side of the database — it reuses the exact same mechanism the database already uses to guarantee crash safety: the Write-Ahead Log (WAL). As covered in Storage Internals & VACUUM, every change to the database is first recorded as a WAL record before the corresponding data pages are modified on disk. This is what lets PostgreSQL recover cleanly after a crash — replay the WAL since the last checkpoint and the data files are brought back to a consistent state.

Streaming replication simply extends this idea across a network: instead of only writing WAL to local disk and replaying it during local crash recovery, the primary streams its WAL records to one or more replicas as they’re generated, and each replica continuously replays that WAL against its own copy of the data files — permanently, not just after a crash. A replica is, in a very real sense, a server stuck in a permanent, incremental recovery loop. This is why replication in PostgreSQL requires no separate change-tracking mechanism, no triggers, no extra tables: WAL was always a complete, ordered record of every physical change to the cluster, and replication is just shipping that record somewhere else and replaying it there too.

Because it operates on WAL — physical page-level changes — this style of replication is called physical replication, and a replica built this way is a byte-for-byte copy of the primary’s data directory (modulo timing lag). It replicates everything in the cluster: every database, every table, every index, every sequence. There’s no way to replicate only some tables with physical replication — that granularity requires logical replication, covered below.

Setting up streaming replication (conceptual walkthrough)

A minimal streaming replication setup involves a handful of moving pieces:

1. A replication user and permission on the primary — an account with the REPLICATION privilege that replicas authenticate as.
2. pg_hba.conf entries on the primary allowing that user to connect from the replica’s address, using the replication pseudo-database.
3. wal_level = replica (or logical, which is a superset) on the primary, so WAL contains enough information to reconstruct data — the default minimal level omits information replicas need.
4. A base backup of the primary’s data directory, taken with pg_basebackup, used to initialize the replica so it starts from a known-consistent snapshot rather than an empty directory.
5. primary_conninfo on the replica (historically stored in recovery.conf; since PostgreSQL 12 it’s a normal GUC in postgresql.conf, combined with a standby.signal file to tell the server to start in standby mode) pointing at the primary’s connection details.
6. Once started in standby mode, the replica connects to the primary as a walsender/walreceiver pair, requests WAL starting from the position recorded in the base backup, and begins continuously streaming and replaying from there.

# On the replica host: clone the primary's data directory and
# set it up as a streaming standby in one step
pg_basebackup -h primary.db.internal -U replicator -D /var/lib/postgresql/16/main \
  -Fp -Xs -P -R
# -R writes standby.signal and primary_conninfo automatically (PG 12+)

-- On the primary: check connected replicas and how far behind each one is
SELECT client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn,
       replay_lag
FROM pg_stat_replication;

Synchronous vs. asynchronous replication

By default, streaming replication is asynchronous: the primary commits a transaction locally, acknowledges it to the client, and then ships the corresponding WAL to replicas whenever it gets around to it (typically milliseconds later, but with no guarantee). This means that if the primary crashes at exactly the wrong instant — after committing but before the WAL for that commit reaches any replica — that transaction is durably recorded on the primary’s disk but has not yet reached any replica, and is lost from the replica’s point of view if the primary’s disk is unrecoverable and the replica is promoted.

Synchronous replication closes that window: the primary waits for one or more replicas to confirm they’ve received (and, depending on configuration, written or flushed) the WAL for a transaction before returning “commit successful” to the client. This is configured with synchronous_standby_names on the primary, naming which replicas count as synchronous, and synchronous_commit controlling how strong the guarantee is (on waits for flush to disk on the standby; remote_write only waits for the OS to have the data, not fsynced; remote_apply — the strongest — waits until the standby has actually replayed the transaction, making it visible to read queries there too).

A common production pattern is to run one synchronous replica in the same or a nearby availability zone (durability without much latency penalty, since network round-trip is small) and one or more asynchronous replicas further away for DR and read scaling, where waiting for a cross-region round-trip on every commit would be too slow to accept.

Key Concepts

Logical replication

Streaming replication replicates the entire cluster as an exact physical copy. Logical replication takes a different approach: it decodes WAL back into a stream of logical changes — “insert this row into this table,” “update this row to have these values,” “delete this row” — and applies those changes on the destination as ordinary SQL-level operations, using a publish/subscribe model.

• A publication, created on the source with CREATE PUBLICATION, defines a set of tables (or all tables, or tables matching a pattern) whose changes should be made available for subscribers.
• A subscription, created on the destination with CREATE SUBSCRIPTION, connects to a publication and begins replicating its changes — first performing an initial data sync (effectively a COPY of existing rows), then applying ongoing changes as they occur.

-- On the source database (e.g. "app_primary")
CREATE PUBLICATION orders_pub FOR TABLE orders, order_items;

-- On the destination database (can be a different major PostgreSQL version,
-- a different server, even a differently-indexed/partitioned schema)
CREATE SUBSCRIPTION orders_sub
  CONNECTION 'host=source.db.internal dbname=app_primary user=replicator password=...'
  PUBLICATION orders_pub;

-- Check subscription status and lag on the destination
SELECT subname, received_lsn, latest_end_lsn, last_msg_receipt_time
FROM pg_stat_subscription;

This model unlocks things physical replication fundamentally cannot do:

• Partial replication. Only specific tables (or even specific columns/rows via WHERE clauses and column lists on newer PostgreSQL versions) can be published, instead of the whole cluster.
• Cross-version replication. Because it operates at the logical row level rather than the physical page format, the source and destination can run different major PostgreSQL versions — this is the standard mechanism for performing a near-zero-downtime major version upgrade: stand up a subscriber on the new version, let it catch up, then cut traffic over.
• Replicating into a different structure. The destination table just needs matching column names and compatible types — it can have different indexes, additional columns (as long as they allow NULL or have a default), different partitioning, or extra triggers, none of which is possible with physical replication where the destination must be a byte-identical copy.
• Multi-source / fan-in and fan-out topologies. A single database can subscribe to publications from several sources (consolidating data from sharded or multi-tenant primaries), or a single publication can feed many subscribers.

The trade-offs: logical replication has more overhead (decoding WAL into logical changes and replaying as SQL is more expensive than physically replaying pages), doesn’t replicate DDL automatically (schema changes on the source must generally be applied to subscribers manually, in the right order), doesn’t replicate sequences by default, and requires a PRIMARY KEY or REPLICA IDENTITY on published tables so that UPDATE/DELETE operations can be matched to the correct row on the subscriber side.

Comparison: streaming (physical) vs. logical replication

Failover: manual and automated

When a primary fails, something has to take over as the new primary — this is failover. The simplest form is manual: an operator notices the primary is down, picks the most caught-up replica, and runs pg_ctl promote (or pg_promote() from SQL) on it. Promotion ends recovery mode, replays any remaining WAL, and makes the replica start accepting writes as a normal primary. Manual failover works, but it’s slow — it depends on a human noticing the outage, deciding which replica is safe to promote, and updating whatever application configuration or DNS pointed at the old primary — typically minutes at best, often much longer at 3 a.m.

Automated failover management removes the human from the critical path: a piece of software continuously monitors the primary’s health, and when it’s confirmed down, automatically selects the best replica and promotes it, with no manual intervention. The dominant tool for this in the PostgreSQL ecosystem today is Patroni.

Patroni architecture

Patroni is not itself a database or a proxy — it’s an agent process that runs alongside each PostgreSQL instance in a cluster (primary and every replica) and manages that instance’s lifecycle: starting it, stopping it, configuring it as a primary or a replica, running periodic health checks, and — critically — deciding who should be the primary at any given moment.

That decision can’t be made safely by each node deciding independently (“I haven’t heard from the primary in a while, I’ll promote myself”) because a network partition can make a perfectly healthy primary look unreachable to a replica, while the primary is still happily serving other clients. If both the original primary and a promoted replica believe they’re the primary and both accept writes, that’s split-brain — two divergent copies of the data, no way to merge them, silent data loss or corruption. Preventing split-brain is the single hardest problem in database HA, and it’s why Patroni doesn’t try to solve leader election with ad hoc heuristics.

Instead, Patroni delegates leader election to a Distributed Consensus Store (DCS) — typically etcd or Consul (ZooKeeper is also supported). These are systems purpose-built around distributed consensus algorithms (Raft, in etcd’s case) that guarantee a single, cluster-wide agreed-upon answer to “who holds the lock right now,” even in the presence of network partitions — because a majority (quorum) of DCS nodes must agree before any state change is accepted. The Patroni agent on the current primary continuously renews a leader lock/lease in the DCS; if it fails to renew it (because the node is down, or partitioned away from the majority of the DCS), the lease expires, and the Patroni agents on the surviving replicas race to acquire it — whichever succeeds becomes the new leader and promotes its PostgreSQL instance. Because the DCS itself only accepts a single winner under quorum, it’s structurally impossible for two nodes to both hold the lease at once, which is exactly the guarantee needed to rule out split-brain.

At a high level, the pieces are:

• Patroni agents, one per PostgreSQL node, each managing its local instance and periodically checking in with the DCS.
• The DCS (etcd/Consul/ZooKeeper), holding the leader lock and cluster configuration/state — running as its own quorum-based cluster (typically 3 or 5 nodes) so it isn’t a single point of failure either.
• Health checks, where Patroni evaluates replication lag, WAL position, and instance health to decide which replica is the best promotion candidate if a failover is needed (it doesn’t just pick arbitrarily — it prefers the replica that’s most caught up).
• A REST API exposed by each Patroni agent, reporting whether that node is currently the leader — this is what connection routing (below) uses to find the current primary.

Patroni alternatives

Patroni is not the only automated failover tool, though it has become the dominant choice in the PostgreSQL ecosystem, with the widest adoption, the most active development, and the deepest integration with cloud-native tooling (Kubernetes operators, Helm charts, etc.).

• repmgr — an older, simpler tool from 2ndQuadrant/EDB that manages replication topology and can perform automatic failover, but historically relies on a simpler witness-node/quorum approach rather than a full DCS like etcd, making its split-brain guarantees weaker than Patroni’s in some partition scenarios. Still used, especially in simpler or legacy setups.
• pg_auto_failover — built by Citus Data/Microsoft, uses a dedicated monitor node (itself just a PostgreSQL database) to track node health and orchestrate failover. Simpler to reason about than a DCS-based setup, but the monitor node’s own availability becomes a design consideration.

Both remain viable in specific situations, but new PostgreSQL HA deployments overwhelmingly default to Patroni today, and it’s the safe default recommendation absent a specific reason to choose otherwise.

Connection routing and pooling

Automated failover solves which node should be primary, but clients still need to find that node — and that node changes over time. This is where a routing layer sits in front of the cluster.

HAProxy is commonly placed in front of a Patroni cluster as a TCP load balancer that routes connections to whichever node is currently primary. It doesn’t need to understand PostgreSQL’s replication protocol at all — it simply polls each node’s Patroni REST API health-check endpoint (e.g., GET /primary returns HTTP 200 only from the current leader, and a different code from replicas), and routes traffic only to the node currently answering as primary. When a failover happens, Patroni’s REST endpoint on the new leader starts responding 200, HAProxy’s health check picks that up within its configured interval, and traffic shifts to the new primary automatically — usually within seconds. Separate listener ports are typically defined for “write” traffic (routed only to the primary) and “read” traffic (routed to replicas, often round-robin, for read-scaling). HAProxy’s general reverse-proxy and health-check mechanics are covered in DevOps — Web Servers & Proxies; the PostgreSQL-specific part is simply what health-check endpoint it polls and how it interprets the response.

PgBouncer solves an orthogonal problem: connection cost. PostgreSQL’s connection model spawns a full OS process per client connection, and each process carries meaningful memory overhead and a real limit (max_connections, typically in the hundreds) — this makes PostgreSQL expensive to connect to directly from, say, a web application with hundreds of short-lived worker processes each holding a handful of connections. PgBouncer sits between clients and PostgreSQL, accepting many lightweight client connections and multiplexing them down onto a much smaller pool of real backend connections.

PgBouncer supports three pooling modes:

Transaction pooling is the common production choice because it captures nearly all of the connection-reduction benefit — most client connections spend the overwhelming majority of their time idle between transactions, not mid-transaction — while being safer than statement pooling. Its cost is real, though: anything relying on session-level state breaks or behaves unpredictably, most notably server-side prepared statements (PREPARE), session-level SET (as opposed to SET LOCAL inside a transaction, which is transaction-scoped and safe), advisory locks meant to be held across transactions, and LISTEN/NOTIFY. Application code and ORMs/drivers need to be aware of this — many drivers default to server-side prepared statements and need explicit configuration to avoid them under transaction pooling (Nginx-style client-side statement caching, or disabling prepared statements entirely, are common workarounds).

Keepalived

Keepalived offers a much simpler HA mechanism based on VRRP (Virtual Router Redundancy Protocol): a virtual IP address is shared among a small group of nodes, and Keepalived instances on each node negotiate which one currently “owns” that IP, moving it to another node within the group if the current owner stops responding to health checks. Applications simply connect to the virtual IP without knowing which physical node currently holds it.

Compared to Patroni + HAProxy, Keepalived is much simpler to set up and reason about, but it’s a blunter instrument — it doesn’t know anything about PostgreSQL replication state, WAL position, or which replica is most caught up; it just moves an IP address based on a health check the operator defines. It’s sometimes used in smaller HA setups (attaching the virtual IP to whichever node a simple health check considers healthy) or occasionally alongside Patroni + HAProxy as a way of providing a floating IP for the HAProxy layer itself, so the load balancer tier is also not a single point of failure. It is not, on its own, a substitute for something that understands PostgreSQL replication topology.

Distributed consensus stores: etcd and Consul

Both etcd and Consul implement the Raft consensus algorithm to maintain a strongly consistent, replicated key-value store across an odd number of nodes (3 or 5 being typical), tolerating the failure of a minority of nodes without losing consistency or availability. Patroni uses whichever one is configured as its DCS purely as a place to store cluster state and, crucially, to perform leader election atomically via a TTL-based lock: the current leader must periodically renew its lease, and if it fails to, the lease expires and becomes available for another node to acquire — with the consensus algorithm guaranteeing only one node can hold it at a time, even during a network partition. etcd tends to be the more common choice in Kubernetes-native environments (it’s already what Kubernetes itself uses for cluster state), while Consul is common in environments already standardized on HashiCorp tooling. The general concepts of quorum, redundancy, and avoiding single points of failure that motivate running the DCS itself as a 3- or 5-node cluster (rather than a single node) mirror the broader HA principles covered in Network Engineer — Redundancy & High Availability.

A simple stateful setup walkthrough

Consider a common production topology: one primary, two streaming replicas, Patroni managing all three, a 3-node etcd cluster as the DCS, and HAProxy in front, all within one data center (with, ideally, one of the replicas or a fourth node located in a separate region for DR).

A client application never connects directly to a PostgreSQL hostname — it connects to HAProxy’s virtual address on the “write” port. HAProxy polls each node’s Patroni REST endpoint; only the current primary answers healthy on the primary-check endpoint, so HAProxy routes the connection there. If the client is doing reads that can tolerate slight staleness, it connects to a separate “read” port that HAProxy load-balances across the replicas (again driven by their Patroni-reported health/role).

Now suppose the primary’s host loses power. Patroni’s agent on that node stops renewing its leader lease in etcd. Within the configured TTL (commonly a few seconds), the lease expires. The Patroni agents on the two replicas each notice the lease is free and attempt to acquire it; etcd’s Raft consensus ensures only one succeeds. That agent checks that its own replica is sufficiently caught up (or, if multiple candidates are viable, Patroni’s election logic prefers whichever has the least replication lag / most advanced WAL position), then promotes its PostgreSQL instance to primary — replaying any remaining WAL and opening for writes. Its Patroni REST endpoint immediately starts reporting itself as the leader. HAProxy’s next health-check poll (within its configured interval, typically a few seconds) notices this and starts routing new write connections to the newly promoted node. Existing connections to the dead primary are dropped and reconnect (application-level retry logic and connection pooling matter a lot here); once the old primary’s host comes back, Patroni notices a different node is now the leader and reconfigures the recovered node to rejoin as a replica of the new primary, rather than fighting over leadership — which is exactly the split-brain scenario the whole DCS-based design exists to prevent. End-to-end, this whole sequence typically completes in well under a minute, versus the many minutes (or longer) a manual, human-driven failover would take.

Best Practices

Sizing and topology

• Run the DCS (etcd/Consul) as its own quorum cluster of 3 or 5 nodes, ideally on hardware or VMs separate from the PostgreSQL nodes themselves, so a PostgreSQL host failure can’t also take down the thing responsible for detecting it.
• Keep at least one replica in a genuinely separate failure domain (different rack, AZ, region) from the primary — a same-rack replica protects against disk/software failure but not against the failure mode (power, network, natural disaster) that actually causes the worst outages.
• Don’t rely on a single replica for both HA and DR simultaneously if avoidable — a nearby synchronous replica for fast failover and a farther asynchronous one for disaster recovery serve different purposes and shouldn’t be conflated into one node wearing both hats.

Synchronous replication tuning

• Use synchronous_standby_names with a quorum form (ANY n (...)) rather than naming a single fixed replica, so the loss of one designated synchronous replica doesn’t stall writes on the primary entirely.
• Understand that synchronous_commit = on guarantees the transaction survives primary failure, not that it’s instantly visible everywhere — use remote_apply if replicas must be guaranteed to reflect a just-committed transaction the moment the client gets its acknowledgment (e.g., read-after-write-your-own-write consistency requirements).
• Monitor pg_stat_replication.replay_lag and alert on it — a synchronous replica that’s slow (not down, just lagging) will slow down every commit on the primary, and this is a subtler failure mode than an outright down replica.

Patroni operational hygiene

• Never manage postgresql.conf or the standby/promotion state by hand once Patroni is managing a node — Patroni owns the instance’s configuration and lifecycle, and manual changes get silently overwritten or fight with Patroni’s reconciliation loop.
• Test failover regularly in a non-production environment (or during planned maintenance windows in production) — an HA setup that has never actually failed over is an HA setup with unknown, untested assumptions.
• Watch replication lag on every replica, not just the one currently designated synchronous — a badly lagging replica is a bad failover candidate, and Patroni’s own election logic depends on accurate, timely lag information.

PgBouncer and pooling

• Default to transaction pooling in production unless there’s a specific, understood reason not to; audit application code and ORM configuration for reliance on session-level state (prepared statements, session SET, advisory locks across statements) before enabling it.
• Run PgBouncer close to the application (often colocated on the same host or in the same AZ) to avoid adding cross-network latency to every query, and run a separate PgBouncer/pool per logical workload where isolation matters (so one noisy application doesn’t starve another’s connection pool).
• Size pool_size based on actual concurrency needs and PostgreSQL’s max_connections, not guesswork — oversized pools defeat the purpose of pooling, undersized ones cause client-side queueing and timeouts.

Logical replication caveats

• Remember that DDL is not replicated — schema migrations must be applied to both publisher and subscriber, in a compatible order (usually additive changes on the subscriber first).
• Ensure published tables have a PRIMARY KEY or explicit REPLICA IDENTITY FULL/REPLICA IDENTITY USING INDEX, or UPDATE/DELETE replication will fail or behave incorrectly.
• Don’t use logical replication as a failover mechanism on its own — it’s not designed to preserve sequence values, DDL, or full cluster state the way physical replication does; use it for its actual strengths (partial replication, cross-version migration, data distribution).

References

• PostgreSQL Documentation — High Availability, Load Balancing, and Replication
• PostgreSQL Documentation — Log-Shipping Standby Servers (Streaming Replication)
• PostgreSQL Documentation — Logical Replication
• PostgreSQL Documentation — pg_basebackup
• Patroni Documentation
• PgBouncer Documentation — Pooling Modes
• HAProxy Documentation
• etcd Documentation — Raft Consensus