10 - PostgreSQL Streaming Replication and Failover

11 min read

PostgreSQL Streaming Replication and Failover

This note is the PostgreSQL operational counterpart to the SQL Server Availability Group setup note. The feature surface is different, but the goal is the same: build a second node, make WAL flow continuously, understand what synchronous versus asynchronous protection actually looks like on the wire, and practice promotion under controlled conditions.

Streaming replication drill topology

The live lab below uses one primary and one physical standby. Automatic failover is intentionally absent; the point is to show the PostgreSQL primitives clearly before layering an orchestrator on top.


flowchart LR
  PRIMARY["Primary<br/>stoxx-postgres<br/>RW"]
  SLOT["Physical slot<br/>note10_slot"]
  STANDBY["Standby<br/>note10-standby<br/>RO while in recovery"]
  PRIMARY -->|"streaming WAL"| SLOT
  SLOT --> STANDBY
  STANDBY -. "manual promotion after fencing primary" .-> NEWPRIMARY["Promoted standby<br/>RW"]

End-To-End Setup

PostgreSQL | replication login and pg_hba.conf | prepare the primary for a standby

Create a dedicated replication role and allow the bridge network in the lab

The primary created a dedicated replication login:

DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'note10_replicator') THEN
    CREATE ROLE note10_replicator WITH LOGIN REPLICATION;
  END IF;
END
$$;
 
SELECT rolname, rolreplication, rolcanlogin
FROM pg_roles
WHERE rolname = 'note10_replicator';
rolnamerolreplicationrolcanlogin
note10_replicatortt

The lab then added a single bridge-network rule to pg_hba.conf and reloaded configuration:

host replication note10_replicator 172.17.0.0/16 trust

Lab-only authentication shortcut

The trust rule above is appropriate only for a disposable local drill. Production replication links should use password or certificate-based authentication and a tightly scoped source address.

PostgreSQL | pg_basebackup -R | seed the standby from the primary

Take the physical base backup and create the slot

The standby container seeded itself directly from the primary:

pg_basebackup -h 172.17.0.3 -p 5432 -U note10_replicator \
  -D /var/lib/postgresql/standby -R -C -S note10_slot \
  -X stream -c fast -l note10_seed -v
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/C000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created replication slot "note10_slot"
pg_basebackup: write-ahead log end point: 0/C000100
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: base backup completed

The seeded standby contained the expected recovery markers:

primary_conninfo = 'user=note10_replicator passfile=''/var/lib/postgresql/.pgpass'' channel_binding=prefer host=172.17.0.3 port=5432 sslmode=prefer sslnegotiation=postgres sslcompression=0 sslcertmode=allow sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres gssdelegation=0 target_session_attrs=any load_balance_hosts=disable'
primary_slot_name = 'note10_slot'
/var/lib/postgresql/standby/standby.signal

PostgreSQL | standby startup | start recovery and verify the first streaming state

Bring the standby online and inspect both sides of the connection

Startup on the standby showed the expected recovery transition:

2026-04-18 23:34:35.393 UTC [78] LOG:  entering standby mode
2026-04-18 23:34:35.393 UTC [78] LOG:  starting backup recovery with redo LSN 0/C000028, checkpoint LSN 0/C000060, on timeline ID 1
2026-04-18 23:34:35.396 UTC [78] LOG:  consistent recovery state reached at 0/C000100
2026-04-18 23:34:35.396 UTC [73] LOG:  database system is ready to accept read-only connections
2026-04-18 23:34:35.402 UTC [79] LOG:  started streaming WAL from primary at 0/D000000 on timeline 1

Primary-side replication view:

SELECT pid, application_name, client_addr, state, sync_state, sent_lsn, write_lsn, flush_lsn, replay_lsn
FROM pg_stat_replication
ORDER BY pid;
pidapplication_nameclient_addrstatesync_statesent_lsnwrite_lsnflush_lsnreplay_lsn
1233note10_standby172.17.0.4streamingasync0/D0000600/D0000600/D0000600/D000060

Standby-side recovery view:

SELECT pg_is_in_recovery() AS in_recovery,
       pg_last_wal_receive_lsn() AS receive_lsn,
       pg_last_wal_replay_lsn() AS replay_lsn,
       pg_last_xact_replay_timestamp() AS replay_ts;
 
SELECT pid, status, receive_start_lsn, written_lsn, flushed_lsn, latest_end_lsn, latest_end_time, slot_name, sender_host, sender_port
FROM pg_stat_wal_receiver;
in_recoveryreceive_lsnreplay_lsnreplay_ts
t0/D0000000/D000060
pidstatusreceive_start_lsnwritten_lsnflushed_lsnlatest_end_lsnlatest_end_timeslot_namesender_hostsender_port
119streaming0/D0000000/D0000600/D0000000/D0000602026-04-18 23:35:14.566691+00note10_slot172.17.0.35432

Synchronous Versus Asynchronous Protection

PostgreSQL | application_name and synchronous_standby_names | make the standby part of the commit path

Promote the standby from async observer to synchronous protection target

The first replication connection came up as a normal asynchronous standby. To make it eligible for synchronous commit, the standby was given an explicit application_name in primary_conninfo:

primary_conninfo = 'host=172.17.0.3 port=5432 user=note10_replicator application_name=note10_standby'

Then the primary set:

ALTER SYSTEM SET synchronous_standby_names = 'FIRST 1 (note10_standby)';
SELECT pg_reload_conf();

PostgreSQL accepted the setting:

SELECT name, setting, source
FROM pg_settings
WHERE name IN ('synchronous_commit', 'synchronous_standby_names')
ORDER BY name;
namesettingsource
synchronous_commitondefault
synchronous_standby_namesFIRST 1 (note10_standby)configuration file

The primary log confirmed the state transition:

parameter "synchronous_standby_names" changed to "FIRST 1 (note10_standby)"
standby "note10_standby" is now a synchronous standby with priority 1

And the replication view showed the standby become part of the commit path:

SELECT pid, application_name, state, sync_state, sync_priority, sent_lsn, write_lsn, flush_lsn, replay_lsn
FROM pg_stat_replication
ORDER BY pid;
pidapplication_namestatesync_statesync_prioritysent_lsnwrite_lsnflush_lsnreplay_lsn
1233note10_standbystreamingsync10/D0000600/D0000600/D0000600/D000060

Name mismatch silently prevents sync protection

Until the standby’s application_name matched the name in synchronous_standby_names, the node remained asynchronous even though the replication link was healthy. This is a common setup mistake and worth checking immediately when sync_state never leaves async.

PostgreSQL | pg_stat_replication / pg_stat_wal_receiver | prove that the standby is caught up

Measure send, write, flush, and replay position directly

After the standby entered synchronous mode, a test table was created on the primary and a row was inserted:

DROP TABLE IF EXISTS demo_stc.note10_replication_demo;
CREATE TABLE demo_stc.note10_replication_demo (
    id integer PRIMARY KEY,
    label text NOT NULL,
    inserted_at timestamptz NOT NULL DEFAULT clock_timestamp()
);
 
INSERT INTO demo_stc.note10_replication_demo (id, label)
VALUES (1, 'synced_from_primary')
RETURNING id, label, inserted_at;
idlabelinserted_at
1synced_from_primary2026-04-18 23:35:59.623255+00

The standby caught up fully:

SELECT pid, application_name, state, sync_state, sent_lsn, write_lsn, flush_lsn, replay_lsn,
       pg_wal_lsn_diff(sent_lsn, replay_lsn) AS send_minus_replay_bytes
FROM pg_stat_replication
ORDER BY pid;
pidapplication_namestatesync_statesent_lsnwrite_lsnflush_lsnreplay_lsnsend_minus_replay_bytes
1233note10_standbystreamingsync0/D0271100/D0271100/D0271100/D0271100
SELECT COUNT(*) AS row_count,
       MIN(label) AS first_label,
       MAX(inserted_at) AS last_seen_insert
FROM demo_stc.note10_replication_demo;
 
SELECT pg_is_in_recovery() AS in_recovery,
       pg_last_wal_receive_lsn() AS receive_lsn,
       pg_last_wal_replay_lsn() AS replay_lsn,
       pg_last_xact_replay_timestamp() AS replay_ts;
row_countfirst_labellast_seen_insert
1synced_from_primary2026-04-18 23:35:59.623255+00
in_recoveryreceive_lsnreplay_lsnreplay_ts
t0/D0271100/D0271102026-04-18 23:35:59.62352+00

PostgreSQL | pg_replication_slots | watch the safety contract and the WAL retention risk

Inspect the physical slot that protects the standby

The primary retained a live physical slot for the standby:

SELECT slot_name, slot_type, active, restart_lsn, wal_status
FROM pg_replication_slots
WHERE slot_name = 'note10_slot';
slot_nameslot_typeactiverestart_lsnwal_status
note10_slotphysicalt0/D027110reserved

The slot is the safety guarantee that the standby will not miss WAL if it disconnects briefly. It is also a risk surface: if the standby stalls for too long, the primary keeps retaining WAL and disk usage grows until the slot is advanced or dropped.

Read Scale Without Built-In Routing

PostgreSQL | direct standby reads | prove the replica is readable but still read-only

Query the standby explicitly

SHOW transaction_read_only;
 
SELECT inet_server_addr() AS server_addr,
       inet_server_port() AS server_port,
       pg_is_in_recovery() AS in_recovery;
 
SELECT COUNT(*) AS eurostoxx50_rows
FROM silver.eurostoxx50_ohlcv;
transaction_read_only
on
server_addrserver_portin_recovery
t
eurostoxx50_rows
67155

The standby is clearly readable, but the routing decision is external. PostgreSQL itself does not expose an ApplicationIntent=ReadOnly equivalent or a built-in read-only routing list.

Failover Operations

PostgreSQL | planned failover | fence first, promote second

Stop the primary cleanly and promote the synchronized standby

Immediately before failover, a second row was inserted on the primary and confirmed on the synchronous standby:

INSERT INTO demo_stc.note10_replication_demo (id, label)
VALUES (2, 'before_failover')
RETURNING id, label, inserted_at;
idlabelinserted_at
2before_failover2026-04-18 23:36:53.800391+00
SELECT id, label, inserted_at
FROM demo_stc.note10_replication_demo
ORDER BY id;
idlabelinserted_at
1synced_from_primary2026-04-18 23:35:59.623255+00
2before_failover2026-04-18 23:36:53.800391+00

The primary-side sync view still showed zero send/replay gap:

application_namesync_statesend_minus_replay_bytes
note10_standbysync0

The old primary was then fenced by stopping the primary container, and the standby accepted promotion:

SELECT pg_promote(wait_seconds => 60) AS promoted;
promoted
t

The first check immediately after pg_promote() still showed in_recovery = t and transaction_read_only = on, which is a useful operational reminder that promotion is asynchronous. A few seconds later the standby was fully writable:

SELECT pg_is_in_recovery() AS in_recovery;
SHOW transaction_read_only;
 
INSERT INTO demo_stc.note10_replication_demo (id, label)
VALUES (3, 'after_promotion')
RETURNING id, label, inserted_at;
 
SELECT id, label, inserted_at
FROM demo_stc.note10_replication_demo
ORDER BY id;
in_recovery
f
transaction_read_only
off
idlabelinserted_at
3after_promotion2026-04-18 23:37:22.82333+00
idlabelinserted_at
1synced_from_primary2026-04-18 23:35:59.623255+00
2before_failover2026-04-18 23:36:53.800391+00
3after_promotion2026-04-18 23:37:22.82333+00

Promotion changes the topology, not just the role

Once the standby is promoted and starts accepting writes, the old primary is stale. In a real environment it must be rewound or re-seeded before it can safely rejoin as a standby.

Backup Offload to the Standby

PostgreSQL | pg_dump on the standby | simple backup offload example

Run a logical export from the replica

pg_dump -U postgres -d stoxx -s -f /tmp/note10_schema_from_standby.sql
ls -lh /tmp/note10_schema_from_standby.sql
head -n 15 /tmp/note10_schema_from_standby.sql
-rw-r--r-- 1 postgres postgres 34K Apr 18 23:36 /tmp/note10_schema_from_standby.sql
--
-- PostgreSQL database dump
--
 
\restrict OLG9iqfGln0oyNfnjDoSYcNNlasg0CedxvkvAvGzdRZHZUF2BoZkuJcq0hjB6T3
 
-- Dumped from database version 16.13 (Debian 16.13-1.pgdg13+1)
-- Dumped by pg_dump version 16.13 (Debian 16.13-1.pgdg13+1)

This is not a substitute for the physical HA chain, but it proves the standby can absorb backup-read workload while the primary stays focused on writes.

Troubleshooting Patterns

PostgreSQL | common issues | diagnose the first things that break

Use the live lab mistakes as the checklist

SymptomWhat the lab showedCorrective action
Standby streams but stays asyncapplication_name initially did not match the sync target namefix primary_conninfo and confirm sync_state changes on the primary
Slot exists but standby is disconnectedslot would remain active = f and keep WAL pinnedrestart or remove the consumer, then drop or advance the slot deliberately
pg_promote() returned true but inserts still failedpromotion had started but the node was still in recovery for a momentwait for pg_is_in_recovery() = f and transaction_read_only = off before treating the node as writable
Primary was not fenced before promotionwould risk split-brainstop or isolate the old primary first, or let an orchestrator handle fencing

Next: 11-postgresql-memory-and-buffer-cache shifts from replication topology back into single-node engine internals: shared buffers, memory accounting, cache observation, and the operational boundaries between memory pressure and query behavior.

Graph View

Backlinks