PostgreSQL Replication and High Availability: Streaming Replication, Patroni, and Automatic Failover
This article is a comprehensive reference for building a highly available PostgreSQL cluster – from native streaming replication, through Patroni orchestration with etcd and the Raft algorithm, to Split-Brain prevention with a Watchdog module and dynamic traffic management with HAProxy. It also covers the new logical slot synchronization in PostgreSQL 17 and deployment differences between Linux and FreeBSD.
Introduction to database clustering
Modern mission-critical systems must operate without interruption. They demand minimal downtime (RTO) and tight control over potential data loss in the event of a failure (RPO). PostgreSQL is a powerful and reliable relational engine, but in its default form it has one important limitation: it cannot independently and automatically redirect traffic to a standby server when the primary fails.
This means that while built-in streaming replication effectively copies data to standby servers, the process of replacing a failed primary with a standby requires manual administrator intervention. For production systems expected to run 24/7, this is an unacceptable single point of failure. Every minute waiting for the operations team to respond translates to measurable business losses.
To fully automate this process, the database must be wrapped in an integrated management system. The established standard is to combine PostgreSQL's native streaming replication with Patroni (the orchestrator), etcd (the consensus store), and HAProxy (the traffic director). This architecture can detect a failure in seconds, hold a new leader election, and smoothly redirect application traffic – entirely without human intervention.
Anatomy of streaming replication
The foundation of every PostgreSQL high availability cluster is streaming replication, which works by continuously shipping Write-Ahead Log (WAL) files. Before any change is permanently written to the main table files on disk, it is first written to the WAL. By streaming these logs to standby servers in real time, standbys are kept in near-perfect synchronization with the primary.
Asynchronous vs synchronous replication
In its default Patroni configuration, streaming replication runs in asynchronous mode. When an application writes data, the primary records it to its own disk and immediately confirms success to the client. A fraction of a second later, in the background, it sends the same data to the replicas. The advantage is maximum throughput – the primary does not wait for slow network paths or a busy replica disk. The trade-off is a small risk: if the primary suffers a sudden power failure immediately after confirming a write but before sending the WAL over the network, that specific transaction is lost. To guard against promoting a replica that is too far behind, Patroni uses the maximum_lag_on_failover parameter, which prevents a lagging standby from being promoted to leader.
If company policy – for example in the financial sector – requires absolutely zero data loss, the cluster must be switched to synchronous replication. In this mode the primary will not confirm a transaction to the application until a replica reports that it has safely written the received WAL to its own disk.
Pure synchronous replication carries the risk of blocking the database: if the only available replica suffers a network outage, the primary will stall all writes, waiting indefinitely for acknowledgement. For this reason, production environments use quorum synchronous mode by setting synchronous_mode to quorum. The primary then considers a write safe once, say, two of three replicas have confirmed receipt. The failure or lag of a single machine no longer paralyzes the entire database.
Replication lag analysis (LSN)
For the cluster to run reliably, administrators must continuously monitor whether replicas are keeping pace with the primary. The tool for this is the LSN (Log Sequence Number) – a unique identifier pointing to a specific position in the continuous WAL stream. Querying the pg_stat_replication system view lets you precisely track how data flows from the primary to a replica's disk.
LSN values enable instant diagnosis of problems:
sent_lsn: How much data has actually been sent from the primary into the network.write_lsn: Data has reached the replica and been placed in its RAM. If the gap between sent and received grows steadily, look for a network bandwidth problem (packet loss).flush_lsn: Confirms that data from the replica's RAM has been durably written to its disk. A large gap here means the replica's storage subsystem is too slow to keep up with the write rate.replay_lsn: The final step – data has been decoded and physically applied to the replica's tables. Lag at this stage typically indicates insufficient CPU resources on the replica or table locks held by read queries from analysts.
In newer PostgreSQL versions this information is also available as simple time-based lag values in the write_lag, flush_lag, and replay_lag columns. Patroni continuously analyzes these metrics before deciding which machine to promote as the new leader.
New in PostgreSQL 17: Logical slot synchronization
Alongside physical replication (where the server is copied byte for byte), modern architectures commonly use logical replication – the foundation of Change Data Capture (CDC) systems. It allows selected changes to be streamed from the database directly to systems such as Apache Kafka or data warehouses. The key mechanism is replication slots, which guarantee that the primary will not discard old WAL files until all connected external systems have fully consumed their data.
For years, logical slots were a serious problem in high availability clusters. Their state was maintained only on the primary and was never synchronized to physical standbys. When the primary was lost and Patroni promoted a standby to leader, the new server started up with no slot state. External analytics systems lost their position in the stream, resulting in duplicate messages or permanently lost transactions.
PostgreSQL 17 delivered a fundamental fix: native logical replication slot synchronization. Slot state is now continuously updated on physical standbys, creating a fully failover-safe environment.
Configuring the slot sync mechanism
Synchronization is handled by a new, isolated background worker running on standby servers, called the slot sync worker. It connects to the primary and continuously copies slot metadata into its own storage.
Integrating this with a Patroni cluster requires configuring several key parameters:
- When creating a subscription, add the
failover = trueflag. This tells the database engine that the state of this particular slot must be protected and propagated to standbys. - On standby nodes, set
sync_replication_slots = true, which activates the background worker. - On the primary, define the list of physical standbys in the
standby_slot_namesparameter. This ensures that logical replication data will not be processed faster than it has been safely copied to the physical standbys.
When a failure occurs and Patroni promotes a standby, the new leader safely halts the slot sync process, and external systems, upon reconnecting, resume consuming data from exactly the last correctly written position.
Patroni and etcd: Centralized cluster management
Patroni is an open-source Python daemon that runs alongside every PostgreSQL instance, managing configuration, the database startup process, and replication health.
A fundamental principle of distributed systems design is that a single server cannot objectively assess its own state. When network connectivity is lost, a machine cannot determine on its own whether the rest of the infrastructure has failed or whether the problem is limited to its own network interface. If an isolated server could declare itself leader, it would cause a data consistency violation. Patroni therefore delegates the leader election decision to an external, distributed key-value store – most commonly etcd.
The Raft algorithm and etcd mechanics
The etcd store is the reliability foundation of the cluster, operating on the Raft consensus algorithm. Under Raft's rules, no configuration change – such as updating which node is leader – is committed until a majority of the etcd cluster's nodes confirm it.
To avoid ties in votes, the etcd cluster must consist of an odd number of servers – typically three or five. The minimum number of nodes required for a binding decision, the quorum, is calculated as:
Quorum = floor(N / 2) + 1
(where floor is the mathematical floor function rounding down to the nearest integer, and N is the total number of etcd nodes).
For a three-node cluster, quorum is 2. This means the failure of one etcd server does not affect the cluster's ability to operate.
Heartbeat and leader election
Patroni's leadership management is built around the concept of a key lease in etcd. The current primary regularly sends a heartbeat to etcd (for example every 10 seconds), renewing the lock on the leader key. Other cluster nodes read this state and correctly operate as replicas.
The lease key has a fixed expiration time defined by the TTL (Time To Live) parameter. If the primary fails and its heartbeats stop being received, the key simply expires. This signals Patroni processes on the standby machines to start a new election. The software identifies which surviving replica has the most current data (the highest LSN), assigns it the key in etcd, and promotes the associated PostgreSQL instance to full leader.
The scenario where the old leader eventually regains network connectivity is also handled. It immediately recognizes that its key is gone, after which Patroni automatically demotes it to replica. Using built-in tooling (such as pg_rewind), it resynchronizes with the new leader, restoring full cluster harmony without conflicts.
Risks of shared infrastructure
To cut costs, some teams try installing etcd on the same virtual machines as PostgreSQL. This is a high-risk practice.
During heavy transactional activity – particularly during checkpoint operations, when PostgreSQL flushes dirty buffers to disk – PostgreSQL can completely saturate available I/O bandwidth. With etcd sharing the same disk, it cannot write the data required by the Raft protocol quickly enough. Delays accumulate, Patroni concludes it has lost stable connectivity to etcd, and triggers a false failover, demoting a fully healthy database. For full reliability, etcd must run on separate, isolated machines.
Preventing Split-Brain (Watchdog)
The most serious threat in distributed systems is Split-Brain. It occurs when a network infrastructure failure partitions the cluster into isolated fragments. If the old leader erroneously retains its role while a cut-off replica holds its own election, two primary servers end up accepting writes simultaneously. When the network is restored, the databases contain mutually exclusive transactions that cannot be merged without data loss.
Three layers of defense
To eliminate this risk entirely, Patroni enforces rigorous protection mechanisms. If any node loses connectivity to etcd and cannot renew its key, it must undergo immediate and automated shutdown (fencing).
- The etcd foundation: Raft consensus technically prevents two leader keys from being issued simultaneously. Until the old key expires according to its
TTL, no new instance can be promoted. - Agent-level protection (self-fencing): When Patroni detects loss of its etcd key, it automatically shuts down the PostgreSQL process it supervises, dropping all active client connections.
- Hardware-level protection (Watchdog): What if the Patroni process itself fails – for example, killed by the system OOM killer – or the virtual machine is briefly frozen by a hypervisor? A software shutdown of the database will not execute. This is where the last line of defense comes in: a hardware or kernel watchdog timer, implemented in Linux by the
softdogmodule.
The module expects a regular keepalive signal from the Patroni process. If the signal does not arrive within the required time (because the server stopped responding), softdog does not perform a graceful shutdown. Instead, it immediately forces a hard reset (reboot) of the entire machine, providing absolute certainty that the cut-off database server will accept no further transactions.
Correct configuration requires activating the device (modprobe softdog), granting it the appropriate permissions for the database system user, and then precisely calculating the timing values in the Patroni configuration. The point at which the machine will be reset is given by a simple formula:
Watchdog_Timeout = TTL - safety_margin - loop_wait
This ensures the server resets before its key expires in the etcd registry, eliminating any chance of two simultaneous leaders.
HAProxy: Dynamic traffic management
Even when the election process completes cleanly, a critical question remains: how do you redirect the application to the newly elected leader? The standard and highly efficient solution is HAProxy as a load balancer. The application always connects to a single, stable IP address exposed by HAProxy, which decides which physical database to forward each query to.
Patroni greatly simplifies this by running a lightweight HTTP server with a REST API on every node, continuously reporting database status. HAProxy periodically checks this status:
- A check against
/primaryreturns HTTP 200 only for the current legitimate leader. HAProxy uses this to direct all transactional (write) traffic to a dedicated port (for example 5000). - A check against
/replicaidentifies fully healthy standby servers. HAProxy can use a separate port (for example 5001) to distribute pure read queries (such as report generation), effectively offloading the primary.
Health check optimization (agent-check)
Checking the /primary endpoint directly using HAProxy's standard HTTP mechanism has one drawback in a monitoring context. Replica nodes naturally return a 503 error code (since they are not the leader). HAProxy interprets this as a failure and begins generating "Server is DOWN" alerts to monitoring systems (such as Datadog or Zabbix), producing false alarms for fully healthy standby servers.
The solution is to use HAProxy's agent-check feature. It allows polling an independent diagnostic port on the database servers. This port returns a simple text status (for example "up" or "down"). HAProxy knows from this that the server is physically healthy and does not send false outage notifications, while still correctly not routing write traffic to standby nodes.
Safe teardown of stale TCP sessions
The final challenge is how HAProxy handles active TCP sessions at the moment of a leader switchover. Standard load balancer behavior allows already-established connections to finish their work gracefully, even after the target server loses its ready status.
For a relational database, keeping such a session open is a significant risk. If the old leader regains connectivity and is demoted to replica, an application with a still-open TCP socket to it may attempt to finalize a write operation against a server now running in read-only mode. This causes an immediate application error.
To prevent these anomalies, add the on-marked-down shutdown-sessions directive to the HAProxy configuration. This forces the load balancer to immediately tear down (hard reset) all active TCP connections to a node the moment it loses leader status. The application receives a connection-reset notification and, following its built-in retry logic, repeats the query – this time being routed by HAProxy directly to the newly elected, correct leader.
Runtime environment: FreeBSD versus Linux
Although Linux is the default and most common operating system for PostgreSQL clusters, experienced engineers often consider FreeBSD, which is known for its excellent native integration with ZFS. This choice affects how the database itself is configured and tuned.
The most immediate difference concerns kernel parameter modification. On Linux, ZFS memory limits (such as the ARC cache size) and disk-related settings are adjusted by writing to virtual files under /sys/. On FreeBSD the same settings are controlled via the built-in sysctl command or dedicated configuration files (such as /etc/sysctl.conf).
A significant difference also appears in extended permission handling (ACLs). Linux ZFS documentation places heavy emphasis on forcing POSIX attributes to be stored inside inode structures via the xattr=sa parameter to avoid performance penalties. On FreeBSD this problem is absent – the system uses the more advanced NFSv4 ACL standard (acltype=nfsv4), which manages its metadata structures optimally. A detailed treatment of this topic can be found in our article Advanced ZFS Optimization for PostgreSQL and MySQL.
Database environments running on FreeBSD often also apply one specific tuning: the vfs.zfs.prefetch.disable=1 parameter. This disables hardware prefetching and read-ahead buffering by the filesystem, which – given the random I/O pattern of database queries – saves significant CPU resources and improves overall machine responsiveness.
The choice between FreeBSD and Linux remains open. Depending on the workload, FreeBSD can offer excellent stability and raw I/O throughput. On the other hand, Linux benefits from a vast support ecosystem and modern asynchronous I/O mechanisms (such as io_uring), making it the more common choice for cloud-based, highly available production environments.
Conclusion and architecture selection
Building a highly available database cluster is a process of systematically eliminating single points of failure (SPOFs). Properly integrating PostgreSQL streaming replication, the Patroni orchestrator, etcd for consistency, and HAProxy for traffic management creates an environment resilient to the overwhelming majority of infrastructure failures.
These solutions, supported by hardware-level watchdog protection and modern features such as the automatic logical slot synchronization introduced in PostgreSQL 17, maintain application continuity and seamless operation of analytics data pipelines even during a serious primary node failure.
The right level of architectural complexity depends on budget and downtime tolerance. The full Patroni and etcd stack is uncompromising and offers the highest level of safety, but requires managing additional servers for the consensus service. For smaller projects where operational simplicity matters most, lighter alternatives (such as pg_auto_failover) represent a sound engineering trade-off, significantly reducing infrastructure cost at the expense of strict redundancy.
At WebOptimo we specialize in PostgreSQL cluster administration, high availability architecture design, and database performance optimization. If you are planning to implement streaming replication or Patroni, or need an audit of your existing cluster, get in touch. You can also explore our server administration, PostgreSQL administration, Linux administration, and FreeBSD administration services. See also our other articles: ZFS Optimization for Databases, LEMP Stack Architecture, and PostgreSQL Performance Tuning.
FAQ – PostgreSQL HA
In asynchronous mode the primary confirms a write immediately and sends data to replicas in the background. In synchronous mode the primary waits for replica confirmation, guaranteeing zero data loss at the cost of higher latency. Production environments typically use quorum synchronous mode, where acknowledgement from a majority of replicas is sufficient.
PostgreSQL has built-in streaming replication but cannot perform automatic failover on its own. Patroni is an orchestrator that monitors cluster health, detects failures, and automatically promotes a replica to leader, using the distributed etcd store for safe leader election.
Split-Brain occurs when, after a network partition, two nodes simultaneously believe they are leader and accept writes. Patroni prevents this through three mechanisms: Raft consensus in etcd makes it technically impossible to issue two leader keys simultaneously; the Patroni agent shuts down PostgreSQL on key loss (self-fencing); and the softdog Watchdog module hard-resets the machine if Patroni itself stops responding.
This is a risky practice. During heavy checkpoint activity, PostgreSQL can saturate disk I/O bandwidth, preventing etcd from writing Raft protocol data on time. This triggers false failovers. The etcd service should run on separate, isolated machines.
Patroni runs a lightweight HTTP server with a REST API on every node. HAProxy polls the /primary endpoint, which returns HTTP 200 only for the current leader. Write traffic goes to a dedicated port; read queries go to a separate port serving replicas via the /replica endpoint.
PostgreSQL 17 introduces native synchronization of logical replication slots to physical standbys. Previously, logical slots existed only on the primary and were lost after failover, causing external systems (such as Kafka) to lose their position in the stream. The new slot sync worker automatically copies slot metadata to standbys, ensuring data pipeline continuity after an emergency switchover.
An etcd cluster must consist of an odd number of nodes – minimum three. Quorum is floor(N/2) + 1, so for three nodes quorum is 2. The failure of one etcd server does not affect the cluster's ability to operate.
