"PostgreSQL cluster" means different things to different people. In PostgreSQL's own documentation a cluster is one server instance and its databases. In most conversations about availability it means a primary with one or more standbys and something that promotes a standby when the primary fails. This guide uses the second meaning and covers the decisions inside it.

The four parts

  • Replication. Standbys receive the primary's write-ahead log by streaming replication. This is built in.
  • Failover management. Something has to detect that the primary is gone, choose a standby, promote it, and stop the old primary from coming back as a second writer. PostgreSQL does not do this itself. Patroni is the common open source choice. repmgr and pg_auto_failover are alternatives.
  • Connection routing. Applications must reach the new primary without a configuration change. Options are a proxy such as HAProxy, a virtual IP, DNS, or client drivers that accept several hosts and pick the writable one.
  • Backups. Replication copies mistakes as faithfully as it copies data. A dropped table is dropped on every standby within seconds. Backups with point-in-time recovery stay necessary.

How much data can a failover lose?

This is the first decision, and it belongs to the business.

  • Asynchronous replication. The primary confirms a commit before the standby has it. Fast, and a failover can lose the last moments of committed work.
  • Synchronous replication. The primary waits for a standby to confirm. No committed transaction is lost on failover. Every write pays the network round trip, and if the only synchronous standby is down, writes wait.

The usual arrangement for systems that cannot lose data is one synchronous standby close by, with at least one more standby so that losing one does not stop writes.

-- On the primary: who is connected, and is anyone synchronous?
SELECT application_name, state, sync_state, replay_lag
FROM   pg_stat_replication;

SHOW synchronous_standby_names;

-- On any node: is this a standby?
SELECT pg_is_in_recovery();

Split brain, and why three nodes

The dangerous failure is not a dead primary. It is two primaries, each accepting writes because each believes the other is gone. Preventing that needs a third party that both can see, which holds the answer to "who is the leader". Patroni uses a small consensus store such as etcd for this. That store needs an odd number of members, usually three, in separate failure zones.

Two database nodes with a script that promotes the standby on a failed ping is the most common home-made design, and it is the one that produces split brain.

What clustering does not give you

  • More write capacity. Standbys take read queries. Writes still go to one primary. PostgreSQL has no built-in multi-writer cluster.
  • Sharding. Spreading data across nodes for scale is a different problem with different tools, such as Citus.
  • Protection from bad changes. A wrong UPDATE replicates. Recovery is from backup.
  • Zero-downtime upgrades by itself. Major version upgrades need their own plan. See the upgrade checklist.

Managed services

Cloud database services provide failover as a setting: a standby in another zone, promoted by the provider. That removes the failover tooling from your side. It does not remove the questions. What is the failover time, what happens to connections, can it lose data, and has anyone triggered a failover on purpose to see?

Test it, on a schedule

  • Trigger a planned switchover in a quiet period and measure the time until the application writes again.
  • Kill the primary without warning in a test environment that matches production.
  • Check what the application did: reconnected, queued, or failed.
  • Confirm the old primary rejoined as a standby and did not accept writes.
  • Restore a backup to a point in time on a separate host, and record how long it took.

A failover that has never been run is a hope. Most surprises are in the application's connection handling, not in PostgreSQL.

What to decide, in order

  • How long can the service be down, and how much data can be lost? Write both down as numbers.
  • Asynchronous or synchronous replication, from the data loss number.
  • Self-managed failover or a managed service.
  • How applications find the primary.
  • Who is paged when a failover happens, and what they check.
  • How often failover and restore are tested.

Where this leads

Datrick plans and carries out this work under a written scope, with a named engineer accountable for the rehearsal, the window and the days after. The service page describes how an engagement runs: PostgreSQL consulting services. Where the need is ongoing, see PostgreSQL DBA services.

Related: PostgreSQL upgrade checklist · backup verification and restore drill · disaster recovery and high availability · emergency DBA support