Lesson 18 advanced

Replication, High Availability and Upgrades

Build a streaming replica of Orbit's database, monitor replication lag, use replication slots safely, fail over with pg_promote or Patroni, replicate selected tables with logical replication, and upgrade between minor and major versions with pg_upgrade or a near-zero-downtime logical switchover.

One server is a single point of failure: if its disk dies or its data centre goes dark, Orbit is down until you restore a backup. Replication keeps one or more copies of the database up to date in real time, so you can fail over in seconds, spread read traffic and upgrade with little downtime. This lesson builds a replica and walks through the upgrade paths.

Physical and logical replication

Physical (streaming)Logical
CopiesThe whole cluster, byte for byte, by shipping WALRow changes of chosen tables
Replica isRead-only, identicalA normal, writable database
VersionsSame major versionCan differ (used for upgrades)
Schema changesReplicated automaticallyNot replicated: apply DDL on both sides
Typical useHigh availability, read scalingUpgrades, migrations, feeding other systems

Set up a streaming replica

The plan: db1 (10.0.0.10) is the primary, db2 (10.0.0.11) becomes a standby. Both run PostgreSQL 18.

On the primary

The defaults already fit: wal_level = replica and max_wal_senders = 10. Create a role for replication and allow it in pg_hba.conf:

CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'repl-secret-change-me';
# pg_hba.conf on db1
hostssl  replication  replicator  10.0.0.11/32  scram-sha-256
SELECT pg_reload_conf();

The replication keyword in the database column is special: it matches replication connections, not a database called “replication”.

On the standby

Stop Postgres on db2, empty its data directory and clone the primary:

$ sudo systemctl stop postgresql
$ sudo -u postgres rm -rf /var/lib/postgresql/18/main/*
$ sudo -u postgres pg_basebackup -h 10.0.0.10 -U replicator \
    -D /var/lib/postgresql/18/main -X stream -R -C -S db2 -P
$ sudo systemctl start postgresql
  • -R writes standby.signal and the primary_conninfo setting, so the server starts as a standby that follows db1.
  • -C -S db2 creates a replication slot named db2 on the primary and uses it, so the primary keeps every WAL segment the standby still needs.
  • Store the password in the postgres user’s ~/.pgpass on db2 so the standby can reconnect on its own.

Check that it works

On the standby:

SELECT pg_is_in_recovery();
 pg_is_in_recovery
-------------------
 t
(1 row)

t means it is a standby, replaying WAL. It accepts read-only queries; writes fail with cannot execute INSERT in a read-only transaction. On the primary, pg_stat_replication shows each connected standby:

SELECT application_name, client_addr, state, sync_state,
       write_lag, flush_lag, replay_lag,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS behind
FROM pg_stat_replication;

state should be streaming. The *_lag columns measure time and behind measures bytes of WAL not yet replayed. Alert when lag grows beyond what your application tolerates.

Replication slots: useful and dangerous

A slot guarantees that the primary keeps WAL until the standby has received it, so a standby that was offline for an hour can catch up. But if a standby disappears for good, its slot keeps WAL forever and the primary’s disk fills up. Protect yourself:

SELECT slot_name, active,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained
FROM pg_replication_slots;

ALTER SYSTEM SET max_slot_wal_keep_size = '50GB';

Drop slots you no longer need with SELECT pg_drop_replication_slot('db2');. PostgreSQL 18 also offers idle_replication_slot_timeout to invalidate slots that stay unused too long.

Synchronous replication

By default replication is asynchronous: the primary confirms a commit before the standby has it, so a crash of the primary can lose the last fraction of a second of commits. To lose nothing, make commits wait for a standby:

ALTER SYSTEM SET synchronous_standby_names = 'ANY 1 (db2, db3)';
SELECT pg_reload_conf();

Each commit now waits until at least one of the listed standbys has it on disk. That adds network latency to every commit, and with only one synchronous standby, writes stop if it goes down. That is why ANY 1 over two standbys is common.

Failover

When the primary dies, promote the standby:

SELECT pg_promote();

It stops replaying, starts a new timeline and accepts writes. Then point the application at it (a DNS name, a virtual IP or a proxy). Two things matter:

  • Make sure the old primary cannot come back and accept writes, or you get two primaries with diverging data (“split brain”).
  • To turn the old primary into a standby of the new one, use pg_rewind instead of cloning it again.

Doing all this by hand at 3 a.m. is error-prone. Patroni (with etcd, Consul or Kubernetes for consensus) automates health checks, leader election, promotion and fencing, and is the most common way to run highly available Postgres on your own servers. On Kubernetes, operators such as CloudNativePG do the same. Managed services include automatic failover.

Logical replication

Logical replication sends row changes for selected tables to another database, which stays fully writable. Orbit’s analytics team wants a live copy of users and notes in their own server. On the primary, set wal_level = logical (restart needed) and create a publication:

CREATE PUBLICATION orbit_analytics FOR TABLE users, notes;

On the analytics server, create the same tables (logical replication does not copy schema; pg_dump -s -t users -t notes helps), then subscribe:

CREATE SUBSCRIPTION orbit_analytics_sub
  CONNECTION 'host=10.0.0.10 dbname=orbit user=replicator password=repl-secret-change-me'
  PUBLICATION orbit_analytics;

Postgres copies the existing rows first, then streams every insert, update and delete. Publications can filter rows (FOR TABLE notes WHERE (pinned)) and columns. Watch subscriptions with pg_stat_subscription, and remember: DDL changes must be applied to the subscriber yourself, before the publisher starts sending rows with new columns.

Upgrading PostgreSQL

Minor versions

Minor releases (18.5 to 18.6) only fix bugs and security issues. Install the new packages and restart; on a replicated setup, upgrade the standbys first, then fail over or restart the primary:

$ sudo apt update && sudo apt install --only-upgrade postgresql-18
$ sudo systemctl restart postgresql

Major versions with pg_upgrade

pg_upgrade moves a cluster to a new major version by copying (or hard-linking) the data files and recreating the system catalogs. Install the new version next to the old one, create an empty new cluster with the same settings (including data checksums: on by default since 18), then run a check:

$ sudo systemctl stop postgresql@17-main
$ sudo -u postgres /usr/lib/postgresql/18/bin/pg_upgrade \
    --old-bindir=/usr/lib/postgresql/17/bin --new-bindir=/usr/lib/postgresql/18/bin \
    --old-datadir=/var/lib/postgresql/17/main --new-datadir=/var/lib/postgresql/18/main \
    --old-options='-c config_file=/etc/postgresql/17/main/postgresql.conf' \
    --new-options='-c config_file=/etc/postgresql/18/main/postgresql.conf' \
    --check

When the check passes, run the same command without --check and with --link (hard links instead of copies, done in minutes even for terabytes) or, new in 18, --swap. Since PostgreSQL 18, pg_upgrade keeps the planner statistics, so the upgraded server performs well right away; run vacuumdb --all --analyze-in-stages --missing-stats-only to fill any gaps. On Debian and Ubuntu, pg_upgradecluster 17 main wraps all of these steps.

Warning: Take a backup first. With --link, once the new cluster has started, the old one must not be started again.

Streaming standbys cannot replay WAL across major versions: rebuild them from the upgraded primary, or upgrade them with the rsync procedure described in the pg_upgrade documentation.

Near-zero downtime with logical replication

For upgrades with only seconds of downtime, set up a new server on the new version, copy the schema, subscribe it to a publication of all tables on the old server, wait until it has caught up, then stop writes, move the application to the new server and drop the subscription. pg_createsubscriber (PostgreSQL 17) can turn a physical standby into a logical subscriber without copying the data again, which makes this much faster for big databases.

Quick reference

TaskSQL / command
Replication roleCREATE ROLE r WITH REPLICATION LOGIN PASSWORD '...';
Clone a standbypg_basebackup -h primary -D dir -X stream -R -C -S name
Am I a standby?SELECT pg_is_in_recovery();
Lag on the primarypg_stat_replication
Slots and retained WALpg_replication_slots
Cap WAL kept by slotsmax_slot_wal_keep_size
Synchronous commitsynchronous_standby_names = 'ANY 1 (a, b)'
Fail overSELECT pg_promote();
Logical replicationCREATE PUBLICATION / CREATE SUBSCRIPTION
Major upgradepg_upgrade --check, then --link or --swap

Exercise

A standby was decommissioned months ago, and the primary’s disk is now nearly full of WAL files. What is the likely cause?

  1. archive_command is too slow
  2. The old standby’s replication slot still exists and makes the primary keep all WAL since then
  3. Synchronous replication is enabled
  4. The standby is still replaying in the background
Show answer

Answer: B. An inactive slot retains WAL indefinitely. Drop it with pg_drop_replication_slot and cap future retention with max_slot_wal_keep_size.

Exercise

You need to move Orbit from PostgreSQL 17 to 18 with only a few seconds of downtime. Which approach fits?

  1. pg_dump on 17 and pg_restore on 18 during a maintenance window
  2. Adding an 18 server as a streaming standby of the 17 primary
  3. Installing the 18 packages and restarting the 17 cluster
  4. Logical replication from the 17 server to a new 18 server, then switching the application over
Show answer

Answer: D. Logical replication works across major versions, so the new server can catch up while the old one keeps serving traffic. Streaming replication requires the same major version.

Exercise

After a failover, why must you make sure the old primary does not come back online on its own?

  1. It would delete the new primary’s data
  2. Postgres refuses to start two servers on the same network
  3. Clients could write to both servers, creating two diverging versions of the data
  4. It would immediately become a standby and slow down replication
Show answer

Answer: C. Two writable primaries cause split brain, which is very hard to repair. Fence the old primary, then rejoin it as a standby with pg_rewind.

Key takeaways

  • Streaming replication keeps exact, read-only copies for failover and read scaling; logical replication copies selected tables to writable databases.
  • Build standbys with pg_basebackup -R -C -S, and watch pg_stat_replication for lag.
  • Replication slots protect standbys but can fill the disk; set max_slot_wal_keep_size and drop unused slots.
  • Automate failover with Patroni or an operator, and prevent split brain.
  • Minor upgrades are a restart; major upgrades use pg_upgrade or logical replication for minimal downtime.

FAQ

Can I send read queries to the standby?

Yes, it is a hot standby by default. Remember that it lags slightly behind, so read-your-own-writes flows (save a note, then show it) should read from the primary.

Why was my long query on the standby cancelled?

Replaying WAL sometimes needs to remove rows the query is still reading. Postgres waits up to max_standby_streaming_delay (30 seconds by default) and then cancels the query. hot_standby_feedback = on avoids most cancellations at the cost of some bloat on the primary.

Is a replica a backup?

No. Every mistake, such as a DROP TABLE, is replicated almost instantly. Keep backups with point-in-time recovery as well (lesson 16).

How many standbys do I need?

One standby gives you failover. Two let you lose one and still have a copy, and allow synchronous replication with ANY 1 without blocking writes when one is down.

Can I skip major versions when upgrading, such as 15 straight to 18?

Yes. pg_upgrade and dump/restore both support jumping several versions at once. Read the release notes of every version in between for incompatibilities.

↑ ↓ navigate Enter open Esc close