High availability PostgreSQL patterns

SECTION 1

Introduction

High availability measures a system's ability to remain operational when part of its infrastructure fails. For a PostgreSQL database, it means above all eliminating single points of failure, those components whose failure alone is enough to bring the whole service to a halt.

It assumes continuous monitoring of server health, a reliable automatic failover mechanism and, where possible, geographic distribution of resources. It must be distinguished from load balancing, where several machines work together to serve the same data; both approaches, however, strengthen the resilience of the system and are often combined in production architectures.

SECTION 2

The fundamentals: RTO, RPO and eliminating single points of failure

### Measuring availability: RTO, RPO, MTTR

Before picking a tool, you need numeric targets. RTO (Recovery Time Objective) caps the maximum acceptable downtime after an incident; RPO (Recovery Point Objective) caps the amount of data you accept losing, expressed as time. A billing database with a zero-second RPO doesn't have the same architectural requirements as an analytics warehouse that can tolerate a few minutes of loss. MTTR (Mean Time To Recovery) rounds out the picture by measuring the actually observed average time to get back in service.

### Load balancing: complementary, not a substitute

A load-balanced cluster spreads requests across several nodes to absorb traffic, but guarantees nothing if a node fails and no failover mechanism sits behind it. High availability and horizontal scalability solve different problems: the former protects against downtime, the latter against saturation. A mature architecture combines both, with clearly defined roles between primary nodes, synchronous secondaries and read replicas.

SECTION 3

PostgreSQL replication strategies

### Streaming replication

Streaming replication is the historical pillar of PostgreSQL high availability. A standby server connects to the primary and continuously receives its WAL (Write-Ahead Log) records, which creates an almost identical replica with minimal lag, often on the order of a few milliseconds in synchronous replication. If the primary fails, the standby holds all the data needed to take over quickly, with no lengthy rebuild.

### Logical replication: granularity and flexibility

Logical replication offers more granularity than physical replication. It makes it possible to replicate only selected tables, apply on-the-fly transformations, and optionally allows direct writes on the secondary database. It also enables a single secondary to replicate from multiple sources, an asset for distributed architectures, cold migrations or consolidating several environments into a single analytics warehouse.

SECTION 4

Patroni: declarative cluster orchestration

### The key-value store as the source of truth

Patroni is a cluster management framework that determines the state of a PostgreSQL cluster through integration with a distributed key-value store, etcd, Consul or ZooKeeper depending on the deployment. This store acts as a shared source of truth: every node reads and publishes its state there, which removes any ambiguity about who the primary is at any given moment. Patroni provides continuous monitoring and allows manual or scheduled switchover, useful during maintenance windows.

### Linux watchdog and preventing split-brain

The main risk in a poorly orchestrated cluster is split-brain, where two nodes would both believe they are primary and accept diverging writes. Patroni relies on the Linux watchdog to force a restart of any node that loses contact with the configuration store beyond a critical delay, guaranteeing that a node isolated from the network can never keep writing while still believing it is primary.

SECTION 5

PgPool-II: connection pooling and read distribution

### The Watchdog feature since version 3.2

PgPool-II is a connection pooler placed in front of the PostgreSQL cluster. Since version 3.2, it implements its own Watchdog feature, which lets it run in high availability across several instances and avoids becoming a single point of failure itself, a frequent trap in architectures that neglect the resilience of their proxy layer.

### Read distribution and performance gains

PgPool-II reuses existing connections to reduce the session-setup overhead on PostgreSQL, a cost often underestimated on high-traffic workloads. It also distributes read queries between the primary and the replicas, improving both availability and read performance with no application change.

@cite:postgresql-bonnes-pratiques-de-performance

SECTION 6

PostgreSQL Automatic Failover (PAF) and the Kubernetes ecosystem

### Synchronous replication and data integrity with Pacemaker and Corosync

PAF (PostgreSQL Automatic Failover) addresses a specific need: avoiding any data loss during a failover. To achieve this, it relies on synchronous replication, which guarantees that data is written to the secondary before being committed on the client side. It relies on Pacemaker for cluster resource management and Corosync for the communication layer and failure detection, a combination proven in banking and industrial environments where data integrity outweighs failover latency.

### CloudNativePG and Kubernetes operators in 2026

On Kubernetes platforms, the CloudNativePG operator has become the reference approach for running PostgreSQL cloud-natively: it wraps the same principles (primary election, configurable synchronous or asynchronous replication, automatic failover) as declarative resources, with continuous backups built in toward object storage. It doesn't replace understanding the underlying mechanisms described above, but it automates their operation within a Kubernetes cluster.

SECTION 7

Backup, observability and failover drills

### A backup strategy that complements, not replaces, replication

Replication protects against hardware failure, not against an application bug or an accidental deletion that propagates instantly to every replica. A backup policy with multi-tier retention, regular full backups, continuous WAL archiving, periodic restore tests, remains essential to cover the scenarios that high availability doesn't address.

@cite:sauvegarde-et-restauration-cloud

### Verifying real resilience through controlled failover drills

A failover mechanism never tested under real conditions is a hypothesis, not a guarantee. Mature teams schedule controlled failover drills in pre-production, or even in production during defined windows, to verify that the advertised RTO matches the observed RTO and that client applications reconnect correctly.

@cite:chaos-engineering-bonnes-pratiques

SECTION 8

The Adservio approach

At Adservio, we treat high availability as a design requirement, not as an option added after the fact. The choice between streaming or logical replication, between Patroni, PgPool-II, PAF or a Kubernetes operator such as CloudNativePG, always depends on the acceptable level of data loss, RTO/RPO targets and the real constraints of the infrastructure.

Our conviction: a resilient architecture is planned upstream, around the single points of failure to eliminate, and is proven through regular failover drills rather than theoretical documentation. We support your teams in choosing and setting up these patterns, then transfer them the know-how to sustainably maintain the availability of their PostgreSQL databases.

FAQ

Frequently asked questions