Skip to content
Tutorials Center
My learning

Topics

Lesson 73 of 90 ยท Partitioning and Scaling

High Availability and Upgrades

Plan PostgreSQL high availability: automatic failover with Patroni, managed services, major upgrades with pg_upgrade or logical replication, and minor releases.

Backups bring your data back. High availability (HA) is about not needing them for the common failures: a dead server, a lost disk, a rebooting host. With HA, a standby takes over automatically within seconds to a minute, and the application reconnects.

The second half of this lesson covers a failure of a different kind: planned change. PostgreSQL releases a major version every year and minor versions (bug and security fixes) several times a year. Staying current is part of operating a database, and doing it without long downtime takes a plan. HA and upgrades need multiple servers, so the PostgreSQL commands here are read-only. The runnable examples do the arithmetic and the decision logic with SQL.

What high availability needs

Standby A replica that is always close behind the primary.
Detection Something that notices the primary is gone.
One leader Exactly one writable primary at any time.
Routing Clients find the new primary without manual edits.
1 / 2

How much availability do you need?

Availability is usually quoted in nines. The numbers are small, which is why each extra nine is expensive:

SQL Downtime allowed per availability target

Press Run (or Ctrl + Enter) to see the result here.

At 99.99% you have about 53 minutes a year for everything: failovers, upgrades and mistakes. A human who must log in, diagnose and promote a standby rarely finishes within that budget, which is the case for automation. Be honest about requirements, though. A tool used during business hours may be perfectly happy with 99.9%.

Failover: promote a standby

A streaming standby can be promoted to a primary by running pg_ctl promote or SELECT pg_promote();. That is the easy part. The hard parts are everything around it:

  • Detecting that the primary is really gone, rather than just slow or unreachable from one network.
  • Avoiding split brain: two servers both accepting writes, which produces two diverging histories that cannot be merged. The old primary must be fenced off (shut down, or made unable to write) before or as another node takes over.
  • Choosing the best standby: normally the one with the most replayed WAL, to lose as little as possible (see Streaming and Logical Replication).
  • Redirecting clients and rebuilding or rewinding the old primary as a standby.

PostgreSQL itself ships no automatic failover manager. That is the job of external tools.

The election logic is easy to model: among healthy standbys, pick the one that is furthest ahead.

SQL Choosing a standby to promote

Press Run (or Ctrl + Enter) to see the result here.

db3 is the freshest healthy standby, so it wins, and the failover loses roughly 100 bytes of WAL because the replica was asynchronous. With a synchronous standby that has acknowledged every commit, the loss would be zero. Real managers also consider priorities, locations and whether a node is allowed to become primary.

Patroni and friends

Patroni is the most widely used open-source HA manager for PostgreSQL. It runs next to each PostgreSQL server and uses a distributed configuration store (etcd, Consul, ZooKeeper or the Kubernetes API) as the single source of truth. The node that holds the leader key, which it must keep renewing within a time limit, is the primary. If the leader stops renewing, the key expires, the remaining nodes hold an election and the winner promotes itself. A node that loses contact with the store demotes itself, which prevents split brain.

YAML
# patroni.yml (shortened)
scope: shop-cluster
name: db2
etcd3:
  hosts: etcd1:2379,etcd2:2379,etcd3:2379
postgresql:
  data_dir: /var/lib/postgresql/17/main
  use_pg_rewind: true
bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    maximum_lag_on_failover: 1048576

Notice use_pg_rewind: true. After a failover, the old primary may contain WAL the new primary never saw. pg_rewind rolls it back to the point where histories diverged, so it can follow the new primary without a full re-copy. Clients reach the primary through a proxy such as HAProxy or a virtual IP that asks Patroni's REST API who the leader is, or by using a driver's multi-host connection string with target_session_attrs=read-write. Related tools include repmgr, and for Kubernetes several operators (CloudNativePG, Zalando's operator, Crunchy's operator) that bundle HA, backups and pooling.

Managed services

Cloud providers run HA for you: Amazon RDS and Aurora, Google Cloud SQL and AlloyDB, Azure Database for PostgreSQL and others. Typically you tick a "multi-zone" or "multi-AZ" option, and the provider keeps a standby in another zone and fails over automatically, along with automated backups, PITR and minor-version patching. The trade-offs are less control (no OS access, extension allowlists, restricted superuser), provider-specific failover behavior and cost. Whatever you choose, test a failover while you are watching, and make sure your application reconnects rather than holding dead connections.

Minor releases

Since version 10, a PostgreSQL version number has two parts: the major version (17, 18) and the minor number (17.5, 17.6). Minor releases fix bugs and security problems and never change the on-disk format, so upgrading is: install the new packages and restart. Standbys can be updated first, then the primary, which gives you one short restart (or a planned switchover first). The PostgreSQL project supports each major version for five years, and you should apply minor releases promptly. Read the release notes, because occasionally a minor release asks for an extra step such as reindexing a specific index type.

Major upgrades

A major upgrade changes the internal format, so you cannot just start new binaries on the old data directory. There are three routes.

1. Dump and restore. Simple and portable, but downtime grows with database size. Fine for small databases (see Backup, Restore and Point-in-Time Recovery).

2. pg_upgrade. It migrates the catalogs and either copies the data files, or with --link hard-links them for a near-instant upgrade:

Terminal
# Check first: does not change anything
pg_upgrade --check \
  -b /usr/lib/postgresql/16/bin -B /usr/lib/postgresql/17/bin \
  -d /var/lib/postgresql/16/main -D /var/lib/postgresql/17/main

# Real upgrade with hard links, 4 parallel jobs (stop the old server first)
pg_upgrade --link --jobs 4 \
  -b /usr/lib/postgresql/16/bin -B /usr/lib/postgresql/17/bin \
  -d /var/lib/postgresql/16/main -D /var/lib/postgresql/17/main

Downtime is minutes, but take a backup first, because link mode makes the old cluster unusable once the new one has started. Update extensions afterwards (ALTER EXTENSION ... UPDATE). Planner statistics were not carried over in older releases, so run vacuumdb --all --analyze-in-stages or the equivalent before sending traffic; PostgreSQL 18 can keep them. Replicas are rebuilt or upgraded with the documented rsync procedure.

3. Logical replication. Build a new cluster on the new version, copy the data with a logical subscription, let it catch up, then switch writers over in a short window. Total downtime can be seconds, and you can roll back by keeping the old cluster. The extras to handle are DDL, sequences and tables without primary keys (see Streaming and Logical Replication). Recent versions add helpers such as pg_createsubscriber, which converts a physical standby into a logical replica.

SQL Comparing upgrade routes by downtime

Press Run (or Ctrl + Enter) to see the result here.

The numbers are illustrative, not benchmarks, but the shape holds: dumps and copies scale with size, while link mode and logical replication do not. Whatever you pick, rehearse the whole procedure on a copy of production and keep the old cluster until the new one has proven itself.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is split brain?

    If the old primary keeps accepting writes after a standby has been promoted, the data diverges and cannot be merged automatically. HA tools fence or demote the old primary to prevent it.
  2. 2. What does pg_rewind do after a failover?

    pg_rewind undoes the old primary's changes that the new primary never received, which avoids a full re-copy of the data directory.
  3. 3. Which statement about minor releases such as 17.5 to 17.6 is correct?

    Minor releases are bug and security fixes with the same on-disk format. Installing the new packages and restarting is enough.
  4. 4. Which major upgrade route usually gives the shortest downtime on a very large database?

    Link mode avoids copying data files, and logical replication lets a new cluster catch up while the old one serves traffic, so the switchover window is short in both cases.

Summary

  • HA needs a standby, failure detection, a single leader and client routing; PostgreSQL leaves automation to tools such as Patroni or to managed services.
  • Prevent split brain, choose the freshest standby, and use pg_rewind to reuse the old primary.
  • Be honest about the nines you need: 99.99% is under an hour of downtime per year.
  • Minor releases are a binary swap and restart; apply them promptly.
  • Major upgrades use dump and restore, pg_upgrade (preferably --link) or logical replication; rehearse and keep a rollback.

Next: Roles, Privileges and GRANT.