Build a PostgreSQL 16 hot standby with replication slots, monitor lag and WAL retention, fail over without split-brain, rebuild the old primary with pg_rewind, and route Django reads safely, with a drill checklist to prove it works.
A single PostgreSQL server is one disk failure away from an outage measured in hours: provision a box, restore a backup, replay WAL, hope. Streaming replication keeps a second server a few hundred milliseconds behind, ready to be promoted in under a minute. It only helps if you know what it does not protect against, you watch it, and you have practised the failover before you need it.
Physical streaming replication ships the primary's write-ahead log (WAL) to a standby, which replays it byte for byte. You get a hot copy of the whole cluster (every database, role and index) that can serve read-only queries and can be promoted to a writable primary.
What you do not get:
DROP TABLE orders; or a buggy migration is replicated within milliseconds. Replication protects against losing a server, not against losing data. Keep base backups plus a WAL archive for point-in-time recovery, and test restores separately.Measure the setup against two numbers. The recovery point objective (RPO) is how much committed data you can afford to lose. The recovery time objective (RTO) is how long writes can be down. A nightly dump gives an RPO of up to 24 hours and an RTO of hours. Asynchronous streaming with manual promotion typically gives an RPO below a second and an RTO of five to fifteen minutes, mostly human reaction time. Synchronous replication brings the RPO to zero, and automation brings the RTO to seconds, and each adds cost. For many Django apps, "lose at most a second, back in fifteen minutes" is the right target.
The setup: two Ubuntu 24.04 hosts running PostgreSQL 16 from the distribution packages, on a private network. 10.0.0.10 is the primary (node A) and 10.0.0.11 is the standby (node B). On Ubuntu the configuration lives in /etc/postgresql/16/main/ and the data directory is /var/lib/postgresql/16/main.
The stock postgresql.conf on Ubuntu includes conf.d, so keep replication settings in their own file. Use the same file on both nodes (only listen_addresses differs). Once node B is promoted it becomes the primary and needs the same settings.
# /etc/postgresql/16/main/conf.d/replication.conf (node A)
listen_addresses = 'localhost,10.0.0.10'
wal_level = replica # the default, stated explicitly
max_wal_senders = 10
max_replication_slots = 10
max_slot_wal_keep_size = 50GB # cap WAL retained for a lagging slot
wal_log_hints = on # required later by pg_rewind
hot_standby = on # only takes effect while in recovery
hot_standby_feedback = on # likewise; explained belowwal_log_hints needs a restart and cannot be turned on after the fact for a crash that has already happened. Set it now: it is what makes pg_rewind usable (data checksums also work, but Ubuntu's PostgreSQL 16 clusters are not created with them).
Create a dedicated role that can only replicate. It is not a superuser and cannot log in to any database:
sudo -u postgres psql -c "CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'change-me-long-random';"Allow it only from the standby's address, using SCRAM authentication. The replication keyword in the database column matches physical replication connections. Add the mirror-image line for 10.0.0.10 on node B now, so replication can run in the other direction after a failover.
# /etc/postgresql/16/main/pg_hba.conf (node A)
# TYPE DATABASE USER ADDRESS METHOD
host replication replicator 10.0.0.11/32 scram-sha-256sudo ufw allow from 10.0.0.11 to any port 5432 proto tcp
sudo systemctl restart postgresql@16-mainOn node B, install the same major version, drop the same conf.d/replication.conf in place (with listen_addresses = 'localhost,10.0.0.11'), and store the replication password where the postgres OS user's libpq will find it:
sudo -u postgres bash -c 'echo "10.0.0.10:5432:*:replicator:change-me-long-random" > ~/.pgpass && chmod 600 ~/.pgpass'A hot standby must have max_connections, max_worker_processes, max_wal_senders, max_prepared_transactions and max_locks_per_transaction at least as high as the primary, or it will refuse to start. Keeping the two configurations identical avoids that.
Stop the empty cluster the package created, wipe its data directory and clone the primary:
sudo systemctl stop postgresql@16-main
sudo -u postgres bash -c 'rm -rf /var/lib/postgresql/16/main/*'
sudo -u postgres pg_basebackup \
-h 10.0.0.10 -U replicator \
-D /var/lib/postgresql/16/main \
-X stream -C -S node_b -R -P-X stream streams WAL over a second connection while the copy runs, so the backup is consistent without relying on WAL still being on the primary afterwards.-S node_b with -C creates a physical replication slot on the primary. The primary then keeps WAL until this standby has received it.-R writes an empty standby.signal into the data directory and appends primary_conninfo and primary_slot_name = 'node_b' to postgresql.auto.conf.standby.signal is what makes the server start in recovery mode and stay there. Before the first start, open /var/lib/postgresql/16/main/postgresql.auto.conf and add application_name=node_b inside the primary_conninfo string. Otherwise the standby reports itself under Ubuntu's cluster_name (16/main), which is useless in monitoring and for synchronous replication. Then start it:
sudo systemctl start postgresql@16-main
sudo -u postgres psql -c "SELECT pg_is_in_recovery();" # tOn the primary, pg_stat_replication has one row per connected standby. The four LSN columns tell you where the delay is. sent means the WAL left the primary. write means it reached the standby's OS. flush means it is on the standby's disk. replay means queries on the standby can see it.
SELECT application_name, client_addr, state, sync_state,
pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn) AS unsent_bytes,
pg_wal_lsn_diff(sent_lsn, flush_lsn) AS unflushed_bytes,
pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_lag_bytes,
write_lag, flush_lag, replay_lag
FROM pg_stat_replication;A growing unsent_bytes points at the network or the primary. A growing gap between flush and replay means the standby is receiving WAL but not applying it, usually because a long read query is blocking replay. No row at all means the standby is disconnected, and that is the alert that matters most.
On the standby:
SELECT pg_last_wal_receive_lsn(),
pg_last_wal_replay_lsn(),
now() - pg_last_xact_replay_timestamp() AS since_last_replayed_commit;That interval is the time since the last replayed commit, so it also grows when the primary is simply idle. Alert on it together with the byte lag, or run a heartbeat that updates a one-row table every few seconds.
A heartbeat turns "time since last commit" into real replication delay, because there is always a recent commit to measure. Create the table on the primary (it replicates like everything else) and update it from cron or a systemd timer every five seconds:
-- once, on the primary
CREATE TABLE replication_heartbeat (id int PRIMARY KEY, beat timestamptz NOT NULL);
INSERT INTO replication_heartbeat VALUES (1, now());
-- every few seconds, on the primary
UPDATE replication_heartbeat SET beat = now() WHERE id = 1;Then expose the measured delay from Django so your uptime checker and load balancer can see it. This view reads the heartbeat through the replica alias defined later and returns 503 once the delay passes a threshold. The same endpoint can take the replica out of read routing:
# myproject/health/views.py
from django.db import connections
from django.http import JsonResponse
MAX_REPLICA_DELAY_SECONDS = 10.0
def replica_health(request):
try:
with connections["replica"].cursor() as cur:
cur.execute(
"SELECT pg_is_in_recovery(), "
"EXTRACT(EPOCH FROM now() - beat) "
"FROM replication_heartbeat WHERE id = 1"
)
in_recovery, delay = cur.fetchone()
except Exception as exc: # connection refused, timeout, etc.
return JsonResponse({"ok": False, "error": str(exc)}, status=503)
ok = in_recovery and delay is not None and delay < MAX_REPLICA_DELAY_SECONDS
return JsonResponse(
{"ok": ok, "in_recovery": in_recovery, "delay_seconds": float(delay or 0)},
status=200 if ok else 503,
)
# myproject/urls.py
from django.urls import path
from myproject.health.views import replica_health
urlpatterns = [
# ... your existing routes
path("health/replica/", replica_health),
]Checking pg_is_in_recovery() in the same query matters. After a failover the "replica" host may be the new primary. The endpoint should say so rather than report a healthy replica. Keep clocks on both nodes synchronised (Ubuntu enables systemd-timesyncd by default), because the delay is a wall-clock difference between two machines.
A slot guarantees that WAL is retained until its consumer catches up. If the standby is down for a weekend, the primary keeps every WAL segment written in that time. Without a limit, pg_wal fills the volume and the primary stops. A dead standby takes out a healthy primary. max_slot_wal_keep_size caps the retention. Past the cap the slot is invalidated and the standby must be rebuilt, which is the right trade. Watch it:
SELECT slot_name, active, wal_status,
pg_size_pretty(safe_wal_size) AS headroom,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained
FROM pg_replication_slots;wal_status moves from reserved to extended to unreserved to lost. Alert on anything but reserved and on active = f. Drop slots for standbys you have decommissioned: SELECT pg_drop_replication_slot('node_b');.
By default a commit returns as soon as the WAL is flushed locally, and the standby catches up afterwards. If the primary dies, anything not yet streamed is gone after promotion. Synchronous replication makes commits wait for the standby:
# conf.d/replication.conf on the primary
synchronous_standby_names = 'FIRST 1 (node_b)'
synchronous_commit = onsynchronous_commit controls how far the standby must get before the commit returns:
| Value | Commit waits until | Survives |
|---|---|---|
local | WAL flushed on the primary only | Primary crash, but not primary loss |
remote_write | Standby has written WAL to its OS | Primary loss, unless the standby OS crashes at the same moment |
on | Standby has flushed WAL to disk | Loss of either server |
remote_apply | Standby has replayed it | Loss of either server, and reads on the standby see the write |
The costs are real. Every commit pays a network round trip. With only one standby, if the standby goes down, every write on the primary blocks until it returns or you clear synchronous_standby_names and reload. With two nodes, synchronous mode trades availability for durability. It pays off properly with three nodes and ANY 1 (node_b, node_c). Then either standby can confirm, and losing one does not stop writes.
Latency depends on topology. In one data centre the round trip is a fraction of a millisecond. Across regions it is tens of milliseconds, paid on every commit, and a request with several autocommitted save() calls pays it several times. Also know what synchronous replication does not guarantee. If the standby is disconnected when the primary dies, a client whose commit was waiting may see an error, yet the transaction can still be committed locally on the primary. Clients have to handle an ambiguous commit either way.
A middle ground is to stay asynchronous globally and raise durability only for the transactions that matter:
from django.db import connection, transaction
def capture_payment(order, amount):
with transaction.atomic():
with connection.cursor() as cur:
cur.execute("SET LOCAL synchronous_commit = remote_apply")
order.payments.create(amount=amount)
order.status = "paid"
order.save(update_fields=["status"])This only waits if synchronous_standby_names is set. With it empty, per-transaction settings above local behave like local.
The order matters. Promoting before the old primary is fenced is how you get split-brain: two writable servers, with app workers writing to both, and diverged data that has to be reconciled by hand.
If node A is reachable, stop it and make sure no reboot or package upgrade can bring it back:
# on node A
sudo systemctl stop postgresql@16-main
echo disabled | sudo tee /etc/postgresql/16/main/start.confIf it is unreachable, fence it from outside: power it off through the provider console or out-of-band management, or drop its network access. "It seems down" is not fenced. For a planned switchover, stop node A cleanly first and check that node B's pg_last_wal_replay_lsn() has stopped moving, so nothing is lost.
# on node B, either:
sudo pg_ctlcluster 16 main promote
# or from SQL (waits up to 60 seconds by default):
sudo -u postgres psql -c "SELECT pg_promote();"
sudo -u postgres psql -c "SELECT pg_is_in_recovery(), timeline_id FROM pg_control_checkpoint();"Promotion removes standby.signal, ends recovery and switches to a new timeline. Slots are not replicated, so node B has none. Create one now for the node that will follow it: SELECT pg_create_physical_replication_slot('node_a');.
Never hard-code the database IP in settings. Pick one indirection and practise it:
db-primary.internal) with a short TTL, repointed to 10.0.0.11.DATABASE_HOST) changed on each app server.# settings.py
DATABASES = {
"default": {
"ENGINE": "django.db.backends.postgresql",
"NAME": "myproject",
"USER": "myproject",
"PASSWORD": env("DB_PASSWORD"),
"HOST": "10.0.0.10,10.0.0.11",
"PORT": "5432",
"CONN_MAX_AGE": 60,
"CONN_HEALTH_CHECKS": True,
"OPTIONS": {"target_session_attrs": "read-write", "connect_timeout": 3},
},
}Multi-host needs no change during failover, but it makes fencing mandatory. An old primary that comes back writable will get new connections. Whichever method you use, restart the app processes (sudo systemctl restart gunicorn, along with Celery workers) so no persistent connection stays attached to the old node.
DNS depends on every client honouring TTLs. An environment variable has to change on every app server. Multi-host needs no change, but it puts all the correctness on fencing. With target_session_attrs=read-write, libpq tries hosts in order and skips any that is read-only.
Here is how a real incident runs, to show where the minutes go. At 03:12 the monitoring stops receiving metrics from node A, and replica_health starts climbing because heartbeats have stopped. The on-call engineer is paged at 03:14. Before doing anything irreversible they gather three facts. First, node A does not answer SSH or ping from node B or from an app server, so it is not just a monitoring blip. Second, the provider console shows the host as unresponsive. Third, node B's last replayed LSN and heartbeat are from 03:12:04, which gives the amount of data at risk: whatever node A committed in the last few hundred milliseconds before it died.
At 03:19 they power node A off from the console. That is the fence. Without it, a hypervisor that recovers on its own could bring node A back writable in the middle of the incident. At 03:20 they promote node B and check pg_is_in_recovery(). At 03:21 they change DATABASE_HOST through configuration management, restart gunicorn and the Celery workers, and watch the error rate drop. At 03:25 they create slot node_a on node B and post a status update. Rebuilding node A waits for business hours. For the rest of the night the system runs without a standby. That is an accepted risk, written down in the incident log, not something discovered later.
Total write downtime was about thirteen minutes, and most of it was deliberate verification. Skipping that verification is how a network partition, where node A is alive but unreachable from monitoring, becomes split-brain.
Node A cannot simply follow node B. It may have written WAL that never reached B before the crash, so its history has diverged. There are two options.
Fresh base backup: repeat the standby build on node A pointing at 10.0.0.11 with slot node_a (drop the slot you pre-created, or omit -C). It always works and costs a full copy. Under a few hundred gigabytes on a private network, just do this.
pg_rewind: it copies only the blocks that changed since the timelines forked. It needs wal_log_hints = on (or checksums) on node A before the failure, node A shut down cleanly, and a non-replication connection to node B as a superuser or a role holding the function grants listed in the pg_rewind documentation. That needs its own pg_hba.conf line on B for 10.0.0.10/32.
# on node A, cluster stopped
sudo -u postgres /usr/lib/postgresql/16/bin/pg_rewind \
--target-pgdata=/var/lib/postgresql/16/main \
--source-server='host=10.0.0.11 port=5432 user=postgres dbname=postgres' \
--progresspg_rewind copies postgresql.auto.conf from node B, and that file still holds B's old primary_conninfo pointing at A itself. Rewrite it to host=10.0.0.11 user=replicator application_name=node_a, set primary_slot_name = 'node_a', then touch standby.signal, set start.conf back to auto and start. Node A is now the standby. Fail back later with the same drill if you care which box is primary.
# node A, after pg_rewind, before starting
# /var/lib/postgresql/16/main/postgresql.auto.conf should end with:
primary_conninfo = 'host=10.0.0.11 port=5432 user=replicator application_name=node_a'
primary_slot_name = 'node_a'
sudo -u postgres touch /var/lib/postgresql/16/main/standby.signal
echo auto | sudo tee /etc/postgresql/16/main/start.conf
sudo systemctl start postgresql@16-mainThe password comes from node A's ~/.pgpass, which needs a line for 10.0.0.11 now. On node B, confirm that pg_stat_replication shows node_a streaming. Treat the transactions pg_rewind discarded as a data question: anything node A committed after the fork is gone from the cluster. If the server logs show commits in that window, the application owners need to know.
pg_rewind fails if the new primary no longer has the WAL back to the fork point. After a long outage that is likely, and the base backup is your only route.
Add the standby as a second alias and route reads to it:
# settings.py
DATABASES["replica"] = {
**DATABASES["default"],
"HOST": "10.0.0.11",
"OPTIONS": {"connect_timeout": 3},
"TEST": {"MIRROR": "default"},
}
DATABASE_ROUTERS = ["myproject.routers.PrimaryReplicaRouter"]The router (shown in full below) sends writes and migrations to default and reads to replica, and allows relations between the two because they hold the same data. TEST.MIRROR makes the test runner treat replica as an alias of the test database instead of creating a second one.
Be careful about three things:
.using("default") on post-write reads, or pin a session to the primary for a few seconds after any write (implementation below).transaction.atomic() on default, the router still sends plain reads to the replica, so they are outside your transaction. select_for_update() is routed as a write, but a plain read that decides a write, such as a stock or balance check, must use .using("default") explicitly.canceling statement due to conflict with recovery. hot_standby_feedback = on prevents most of them, at the cost of some bloat on the primary. max_standby_streaming_delay (30s by default) sets how long replay waits before cancelling.The most robust fix for read-your-writes is a short pin. After any unsafe request (POST, PUT, PATCH, DELETE), that client's reads go to the primary for a few seconds, which is longer than normal replication lag. A context variable carries the decision from middleware to router without touching every queryset.
# myproject/routers.py
import time
from contextvars import ContextVar
_use_primary = ContextVar("use_primary", default=False)
PIN_SECONDS = 5
SAFE_METHODS = {"GET", "HEAD", "OPTIONS"}
class PinPrimaryAfterWriteMiddleware:
def __init__(self, get_response):
self.get_response = get_response
def __call__(self, request):
try:
pinned = float(request.COOKIES.get("db_pin", 0)) > time.time()
except ValueError:
pinned = False
token = _use_primary.set(pinned or request.method not in SAFE_METHODS)
try:
response = self.get_response(request)
finally:
_use_primary.reset(token)
if request.method not in SAFE_METHODS:
response.set_cookie(
"db_pin", str(time.time() + PIN_SECONDS),
max_age=PIN_SECONDS, httponly=True, samesite="Lax",
)
return response
class PrimaryReplicaRouter:
def db_for_read(self, model, **hints):
return "default" if _use_primary.get() else "replica"
def db_for_write(self, model, **hints):
return "default"
def allow_relation(self, obj1, obj2, **hints):
return True
def allow_migrate(self, db, app_label, model_name=None, **hints):
return db == "default"Add "myproject.routers.PinPrimaryAfterWriteMiddleware" to MIDDLEWARE after the session and authentication middleware. The cookie is only a routing hint. A forged one just sends that client's reads to the primary, which is harmless. Celery tasks and management commands have no request, so they read from the replica by default. For a task that runs straight after a write, such as "send the confirmation email for order 123", either pass the data it needs in the task arguments or query with .using("default"). Otherwise the task can run before replay and find no order.
Do not do this if the replica would become a single point of failure for reads. When the standby is promoted or rebuilt, the replica alias must fall back to default. For most apps it is simpler to keep the standby purely for failover and route only reports and exports to it explicitly.
Run this on staging quarterly and on production at least twice a year, in a planned window. Time every step.
pg_stat_replication shows node_b streaming, and slot wal_status = reserved.INSERT INTO drill_marker VALUES (now())) and confirm it appears on the standby.start.conf set to disabled).pg_is_in_recovery() = f and that the timeline went up.node_a on B, rebuild A as a standby (pg_rewind or base backup) and confirm it is streaming.Manual failover suits a small team that can accept minutes of downtime and has someone on call. Move to Patroni (with etcd or similar as the consensus store) when you need failover without a human, or when your recovery-time target is below what a paged engineer can deliver. Patroni handles leader election, fencing via leader locks and a watchdog, switchover commands, and rebuilding the old primary. That requires at least three consensus members and a new component to operate and understand. Do not build your own auto-failover script from a health check and pg_promote(). Without consensus, a network partition turns it into a split-brain generator.
DROP TABLE or bad data migration arrives within milliseconds on both nodes.max_slot_wal_keep_size, then losing the primary to a full pg_wal volume.synchronous_standby_names with a single standby and discovering during maintenance that all writes hang.wal_log_hints must already be on. Without it, pg_rewind refuses and you are doing a full base backup under pressure.atomic() blocks that silently go to a lagging server.# primary: standby connected and streaming, lag near zero
sudo -u postgres psql -xc "SELECT application_name, state, sync_state, replay_lag FROM pg_stat_replication;"
# primary: slot healthy
sudo -u postgres psql -c "SELECT slot_name, active, wal_status FROM pg_replication_slots;"
# standby: in recovery and read-only
sudo -u postgres psql -c "SELECT pg_is_in_recovery();"
sudo -u postgres psql -c "CREATE TABLE t (id int);" # ERROR: cannot execute CREATE TABLE in a read-only transaction
# end to end: write on primary, read on standby
sudo -u postgres psql -d myproject -c "CREATE TABLE IF NOT EXISTS drill_marker (at timestamptz); INSERT INTO drill_marker VALUES (now());" # on node A
sudo -u postgres psql -d myproject -c "SELECT max(at) FROM drill_marker;" # on node B
# heartbeat delay as the app sees it
curl -s http://localhost:8000/health/replica/ # {"ok": true, ... "delay_seconds": 0.4}
# Django sees both aliases
python manage.py shell -c "from django.db import connections; [print(a, connections[a].cursor().connection.info.host) for a in ('default', 'replica')]"Replication takes an afternoon to set up. The value comes from the drill: a promotion you have timed, a fence you have tested, and a rebuild you have done before. Until you have failed over on purpose, the standby only tells you there is a second copy, not how long a recovery takes.