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 | |
|---|---|---|
| Copies | The whole cluster, byte for byte, by shipping WAL | Row changes of chosen tables |
| Replica is | Read-only, identical | A normal, writable database |
| Versions | Same major version | Can differ (used for upgrades) |
| Schema changes | Replicated automatically | Not replicated: apply DDL on both sides |
| Typical use | High availability, read scaling | Upgrades, 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
-Rwritesstandby.signaland theprimary_conninfosetting, so the server starts as a standby that followsdb1.-C -S db2creates a replication slot nameddb2on the primary and uses it, so the primary keeps every WAL segment the standby still needs.- Store the password in the
postgresuser’s~/.pgpassondb2so 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_rewindinstead 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
| Task | SQL / command |
|---|---|
| Replication role | CREATE ROLE r WITH REPLICATION LOGIN PASSWORD '...'; |
| Clone a standby | pg_basebackup -h primary -D dir -X stream -R -C -S name |
| Am I a standby? | SELECT pg_is_in_recovery(); |
| Lag on the primary | pg_stat_replication |
| Slots and retained WAL | pg_replication_slots |
| Cap WAL kept by slots | max_slot_wal_keep_size |
| Synchronous commit | synchronous_standby_names = 'ANY 1 (a, b)' |
| Fail over | SELECT pg_promote(); |
| Logical replication | CREATE PUBLICATION / CREATE SUBSCRIPTION |
| Major upgrade | pg_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?
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?
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?
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 watchpg_stat_replicationfor lag. - Replication slots protect standbys but can fill the disk; set
max_slot_wal_keep_sizeand drop unused slots. - Automate failover with Patroni or an operator, and prevent split brain.
- Minor upgrades are a restart; major upgrades use
pg_upgradeor 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.