Turnkey high-availability PostgreSQL setup with Patroni

Why a high-availability PostgreSQL cluster fails when a node goes down

A three-node cluster looks fault-tolerant right up to the first primary node failure. The replication stream breaks, synchronous write acknowledgements stop arriving, and the replica goes into read-only mode: the database is formally alive but cannot accept new data. Two out of three servers are healthy, yet writes are stalled.

Then manual failover according to a runbook begins: promote the replica, make it the primary, rewrite the configuration, move the virtual address, and rebuild the old server as a replica.

In calm conditions this takes tens of minutes; at night without console access it takes longer, and every manual step leaves room for error.

Meanwhile, the application keeps holding connections to the dead node. The pool does not know that there is no server behind the address and keeps issuing new connections to the same place — restarting services or editing connection strings becomes a separate task.

As a result, database downtime stretches into hours, even though the database has already been brought up by then.

A separate cost is the post-incident state. Some transactions did not reach the replica; after rebuilding, it lags and catches up with the primary stream for hours.

If a second node was promoted and the old one was not shut down, there are two primaries, and data divergence has to be reconciled row by row.

The reason is not hardware but the lack of automation: the failure decision is not made by a formal rule, a new primary is not elected, and the address does not move.

Next we break down how this is addressed by the combination of Patroni with a distributed state store and a single entry point for applications.

What a high-availability cluster provides: measurable effect

Manual recovery after a primary node failure means tens of minutes of downtime: the application accumulates connection errors, the queue of unprocessed tasks grows, and the on-call engineer manually promotes the replica and checks data integrity.

A high-availability cluster on Patroni does this itself — database unavailability is measured in seconds, not minutes.

Recovery time. The key metric is how long it takes from the primary node disappearing to accepting new writes.

We configure health check thresholds and the number of acknowledged replicas so that automatic failover fits within units to tens of seconds, but does not falsely trigger on brief network outages. Transactions already acknowledged to the client are not lost.

Automatic failover. The share of failures the cluster handles without a human approaches one hundred percent: a node drops — the primary role moves to the replica, and the application reconnects via the same address.

Engineers are still needed only for planned work: upgrades, topology changes, and migration between sites.

Which failures are eliminated. A high-availability cluster covers an entire class of incidents: split-brain, when writes go to two nodes at once, and reading stale data from a lagging replica.

Under load, predictability appears — reads are distributed across replicas, writes go to the primary, and the loss of one replica goes completely unnoticed by the application.

Options for organizing PostgreSQL: comparison of approaches

The difference between schemes is visible not in calm operation but at the moment of failure: how many transactions are lost and how long the database does not accept writes.

Below are four options we deploy in practice, with limitations worth knowing before implementation, not during an outage.

The choice is determined by two numbers: acceptable downtime and acceptable volume of lost transactions. Where losses are unacceptable, we build a cluster with automatic role failover (Patroni) and a synchronous standby, and leave the hot standby for reads and reporting.

Implementation stages: audit, design, configuration, verification, handover

The work order is structured so that every decision is verified before production: first we record facts about the current cluster, then design the target scheme, and only then change anything on production nodes.

This order removes the main risk — "we tweaked a parameter and saw the consequences during an incident."

1. Configuration audit. We capture Patroni parameters, replication settings, node placement across availability zones, and the behavior of the connection pooler and applications when the connection to the primary is lost. The output is a list of failure points and an estimate of the current cluster downtime.
2. Designing the failover scheme. We determine the number of nodes and quorum, the synchronous replication mode, the new leader election procedure, the arbiter role, and the target failover window. We document what happens to transactions that did not reach the replicas.
3. Building the staging environment. We deploy a copy of the target topology, run load and failure scenarios. Here we also verify compatibility with current application versions and the correctness of connection strings.
4. Configuration on production nodes. We introduce nodes in stages, from replicas to primary, so that a rollback point remains at all times. We configure liveness checks, timeouts, and automatic recovery of a lagging replica.
5. Failure testing. We kill the primary, break the network between zones, slow down the disk, disable a state store node — and observe how the cluster behaves without intervention. We repeat each scenario until the result becomes predictable.
6. Handover to operations. We hand over the planned failover procedure, dashboards and alerts, the procedure for quorum loss, and instructions for reverting the configuration to its previous state. We conduct a walkthrough with the customer's team on a live staging environment.

After handover, the team has not only a working cluster but also an understanding of why it is designed this way — any parameter change can be evaluated before it goes to production.

Configurations, failover schemes, and operations procedures

After delivery, your team has not a set of verbal agreements but a working set of artifacts: configurations, schemes, policies, and procedures.

Each document is tied to a specific on-call action — it can be used to bring up the cluster from scratch, perform a planned failover, or investigate an incident without the person who configured it being online.

• Configurations of all nodes — Patroni files, PostgreSQL parameters, DCS settings: in a repository with change history, not scattered across servers. Any edit goes through review and can be rolled back with one revision.
• Topology diagram — which nodes exist, where the synchronous replica is, what failure points exist, and what happens if a node or the state store is lost.
• Access policies — roles and privileges, separation of application and administration, the procedure for granting access to on-call staff, and credential rotation.
• Backups — schedule of full and incremental copies, retention depth, continuous transaction log archiving, and the point to which we are guaranteed to recover.
• Operations procedure — planned failover, maintenance windows, updating replicas, actions on node loss, and escalation criteria.
• On-call instructions — step-by-step scenarios with commands and checks: what the on-call engineer does independently and at which step to call an engineer.
• Cluster documentation — recovery diagram after failure, results of training runs, and a change log with the owner of each change.

We hand over the set together with on-call shift training: two or three walkthroughs on a test environment, where your engineers perform failover and we only observe.

Case study: how manual failover was replaced with automatic

A three-node cluster ran on streaming replication, but role failover remained manual: the on-call engineer used a two-page instruction to promote the replica and switch traffic.

The application was down — from the monitoring alert to write recovery took about forty minutes, and five to six such incidents accumulated per quarter, half at night and on weekends.

We moved the cluster to Patroni: it keeps state in a distributed store, monitors node health, and elects a new leader itself if the current one stops responding. The application connects to a virtual address, so the connection string does not need to be changed after failover.

Metrics before and after

• Emergency failovers: 5-6 per quarter → 2-3, none at night.
• Write recovery time: about forty minutes → less than a minute.
• False on-call alerts: reduced by about half.

What changed for the on-call shift

Work shifted from "fix it now" to "check that it fixed itself": the on-call engineer receives a notification after the role move and investigates the cause during business hours.

The two-page procedure was replaced with a short status check and a list of actions for when automation fails.

One caveat: leader election requires quorum, so the state store nodes were spread across availability zones — otherwise failure of one segment disables all the logic.

How does automatic leader election differ from manual failover?

The difference is who makes the decision to change the role. With automatic leader election, this is done by the node quorum: they constantly confirm each other's presence, and as soon as the primary stops responding, the replica promotes itself.

With manual failover, the decision is made by an engineer — based on an alert, a procedure, or a phone call.

Hence the main difference in the impact of downtime. Automation fits within seconds: a node loses connection, the quorum detects it, and the role is transferred.

The manual scenario includes alert firing time, the on-call shift, access, and the command itself — tens of minutes; all this time writes to the database are stalled and the connection queue grows.

Network partition is the parameter most often missed in manual schemes. Automation is designed so that a node without quorum does not become leader: a short write outage is preferable to two independent leaders and diverging data that later has to be reconciled manually.

Application behavior directly depends on how quickly the database resumes accepting writes. With automatic failover, clients reconnect by timeout and retry the operation on the new leader.

With manual failover, the application must survive a long failure window; otherwise lost orders and duplicates appear.

Technical concerns: replication, failover, maintenance

Concerns at this stage come down to four questions: are the most recent transactions lost during an incident, how long does the application stop, how is recovery tested, and what to do with an already running cluster.

Will the application stop during failover?

Leader failover takes a few seconds. Application reconnection takes longer: without retries in the connection pool, downtime stretches until manual intervention. That is why, alongside the cluster, we configure a single connection endpoint and retry parameters.

How do you test backups?

A copy that has never been restored does not count as a backup. We configure continuous transaction log archiving, point-in-time recovery, and regular testing: deploy the cluster entirely from a copy and measure actual recovery time. We also monitor separately to ensure archiving does not lag silently.

We already have a cluster deployed — do we need to rebuild everything from scratch?

No. We start with an audit: nodes and quorum, replica lag, synchronization mode, and backup state. Then we adjust parameters, add a witness node if needed, and test behavior with a controlled failure. A cluster assembled manually with scripts is brought under management without stopping the service.

Let's discuss the task: we will return with an estimate and work plan

To base the work estimate on facts rather than assumptions, a few initial data points are enough to start: node placement scheme, database sizes and workload profile, recovery time requirements, and acceptable data loss.

If some information is missing, engineers will collect it themselves during an introductory walkthrough — that is one call, and it does not slow down the start.

In response, we send a package you can use to make a decision:

• Analysis of the current scheme: failure points, failover behavior, and disk and replication bottlenecks.
• Phased implementation plan — with work order, dependencies, and stage approval points.
• Composition of deliverables: configurations, cluster diagram, failover and recovery procedures.
• Environment requirements: nodes, disk subsystem, network, access, and service accounts.
• Verification plan: failure scenarios we run before handover, and acceptance criteria for each.
• Work estimate broken down by stage, so it can be launched in parts.

Describe the task and attach whatever you have on infrastructure — we will return with an estimate and work plan, and discuss contentious points on a call.