PostgreSQL now runs order systems, payment platforms, hospital records and SaaS products. When it stops, the business stops with it. PostgreSQL high availability is about keeping the database serving requests through hardware failures, maintenance and human error, with an agreed limit on how much data you can lose.
PostgreSQL has excellent building blocks for this. What it does not have is automatic failover out of the box. Below we go through each building block, how they fit together, and the mistakes that still cause outages in setups that were meant to be highly available.
Start With RPO and RTO, Not Tools
Two numbers should drive every design decision:
- RPO (Recovery Point Objective): how much data you can afford to lose, measured in time.
- RTO (Recovery Time Objective): how long the database can be unavailable.
Agree on these with the business first. They decide how much complexity and cost you need.
| Business need | Typical RPO and RTO | Common PostgreSQL setup |
| Internal tools and reporting | RPO: hours. RTO: hours | Nightly base backup plus WAL archiving, restore on demand |
| Customer-facing applications | RPO: seconds to minutes. RTO: minutes | Primary plus asynchronous standby with automatic failover |
| Payments, healthcare, core transactions | RPO: zero. RTO: about a minute | Synchronous replication to at least one standby, quorum-based failover across zones |
| Regional disaster | RPO: minutes. RTO: under an hour | Replica or backups in a second region |
Building Block 1: Streaming Replication
PostgreSQL writes every change to its write-ahead log (WAL). With streaming replication, one or more standby servers receive that WAL from the primary and replay it continuously. A standby running in hot standby mode can also serve read-only queries.
Asynchronous or Synchronous?
- Asynchronous (the default): the primary confirms a commit without waiting for the standby. It is fast, but the last few transactions can be lost if the primary fails.
- Synchronous: the primary waits until a standby confirms it has the change. You get zero data loss at the cost of extra commit latency.
Configure synchronous replication with synchronous_standby_names. A quorum setting such as ANY 1 (standby1, standby2) means a commit succeeds when either standby confirms. Always have at least two synchronous candidates: with only one, writes stop whenever that standby is down.
Watch Replication Slots and Lag
Replication slots stop the primary from deleting WAL that a standby still needs. They also have a well-known failure mode: an abandoned slot keeps WAL forever and can fill the disk. On PostgreSQL 13 and later, set max_slot_wal_keep_size as a safety limit, and alert on slot growth. Monitor replication lag through the pg_stat_replication view (write_lag, flush_lag, replay_lag).
Where Logical Replication Fits
Logical replication (PostgreSQL 10 and later) copies selected tables rather than the whole cluster. It is very useful for near-zero-downtime major version upgrades and migrations. It is not a substitute for a physical HA standby, because schema changes (DDL) are not replicated.
Building Block 2: Automatic Failover
PostgreSQL will not promote a standby on its own. You need a cluster manager that detects failure, chooses a new primary and promotes it. The most widely used options are:
- Patroni : the most popular choice. It uses a distributed configuration store (etcd, Consul, ZooKeeper or Kubernetes) to elect a leader safely.
- repmgr: a simpler tool for managing replication and failover.
- pg_auto_failover: uses a separate monitor node to coordinate failover.
Prevent Split-Brain
The worst HA failure is split-brain: two nodes both believe they are the primary and both accept writes. Prevent it with consensus, typically a three-node etcd cluster, so a node only acts as primary while it holds the leader lock. Never base failover on a single health check from a single location.
After a failover, the old primary must be resynchronised with pg_rewind or rebuilt before it can rejoin as a standby.
Building Block 3: Routing Connections to the Right Node
Failover is only useful if applications can find the new primary quickly. Common approaches:
- HAProxy in front of the cluster, using Patroni’s REST API as a health check to route writes to the current primary and reads to standbys.
- A virtual IP that moves with the primary.
- Multi-host connection strings in libpq-based drivers, with target_session_attrs=read-write so the client connects only to the writable node.
- PgBouncer for connection pooling, which also reduces the cost of reconnecting after failover.
Whatever you choose, applications need retry logic. Even a fast failover drops in-flight connections.
Building Block 4: Backups and Point-in-Time Recovery
Replication is not a backup. If someone runs DROP TABLE on the primary, every standby drops it a moment later.
Protect against that with base backups plus continuous WAL archiving. Together they allow point-in-time recovery (PITR): restoring the database to any moment, such as one second before the mistake. The PostgreSQL documentation on continuous archiving explains the mechanism in detail.
- Use an established tool such as pgBackRest or Barman rather than hand-written scripts.
- Keep at least one copy in another region or account, and encrypt it.
- Test restores on a schedule and measure how long they take. If a restore takes longer than your RTO, your plan does not work yet.
A Reference Architecture That Works for Most Businesses
- Three PostgreSQL nodes in three availability zones or racks: one primary and two standbys.
- Patroni on each node, with a three-node etcd cluster for leader election.
- Quorum synchronous replication for low RPO, or asynchronous replication if write latency matters more.
- HAProxy and PgBouncer for routing and pooling, with a separate read-only endpoint for replicas.
- pgBackRest sending backups and WAL to object storage, with a copy in a second region.
- Monitoring and alerts for replication lag, slot WAL retention, disk space, failover events and backup age.
- A failover drill and a restore test every quarter.
What About Managed Cloud PostgreSQL?
Amazon RDS for PostgreSQL (Multi-AZ), Amazon Aurora PostgreSQL, Azure Database for PostgreSQL Flexible Server (zone-redundant HA) and Google Cloud SQL (HA configuration) all handle replication and failover for you. That removes a lot of work. You still own:
- Application retry logic and connection handling.
- Backup retention, restore testing and cross-region disaster recovery.
- Parameter tuning, query performance and cost control.
Common PostgreSQL HA Mistakes
- Treating a replica as a backup.
- Running a single synchronous standby, so one standby outage stops all writes.
- No consensus or fencing, which invites split-brain.
- Forgotten replication slots filling the primary’s disk.
- Never testing failover or restores until a real incident.
- Applications that crash instead of retrying after a failover.
For the bigger picture, read our guides on business continuity, disaster recovery and high availability and how the cloud changes disaster recovery.
How TechyBex Helps With PostgreSQL High Availability
TechyBex designs, builds and runs PostgreSQL clusters on-premises and on AWS, Azure and Google Cloud. As an AWS Select Tier Partner, we also manage RDS and Aurora PostgreSQL. Our team monitors replication, backups and failover 24×7 and runs regular failover drills so you know your setup works before you need it.
See our Database Managed Services and Cloud Managed Services.
Frequently Asked Questions
1. Does PostgreSQL have built-in high availability?
PostgreSQL has built-in streaming replication, synchronous replication and hot standby. It does not include automatic failover. You add that with a cluster manager such as Patroni, repmgr or pg_auto_failover, or by using a managed cloud service.
2. What is the difference between synchronous and asynchronous replication?
With asynchronous replication, the primary does not wait for standbys, so a failure can lose the most recent transactions. With synchronous replication, a commit waits until a standby confirms it, giving zero data loss with slightly higher commit latency.
3. How many nodes do I need for PostgreSQL high availability?
Three is the practical minimum for automatic failover with safe leader election: either three PostgreSQL nodes, or two PostgreSQL nodes plus a separate three-node consensus store. Three PostgreSQL nodes also let you keep a standby available during maintenance.
4. Is Patroni better than repmgr?
Patroni is generally preferred for production because it uses a consensus store to prevent split-brain and integrates well with Kubernetes and HAProxy. repmgr is simpler and can suit smaller setups that accept more manual control.
Putting It Together
PostgreSQL high availability is a combination of parts: streaming replication for copies, a cluster manager for failover, smart connection routing, and backups with point-in-time recovery for everything replication cannot fix. Set your RPO and RTO first, build the simplest design that meets them, and test it regularly.