WAL sender and WAL receiver · PostgreSQL

The walsender and walreceiver are the two ends of one TCP connection. Each has a view describing it, and the views are not symmetrical.

The primary’s view

Read-only / Safepg_stat_replication, every column— EXECUTION VALIDATED on 18.6 with one connected standby.

$ SELECT * FROM pg_stat_replication;

-[ RECORD 1 ]----+------------------------------
pid              | 124
usesysid         | 16384
usename          | repl
application_name | walreceiver
client_addr      | 172.26.0.3
client_port      | 46864
backend_start    | 2026-08-27 21:57:00.185689+00
backend_xmin     |
state            | streaming
sent_lsn         | 0/5000060
write_lsn        | 0/5000060
flush_lsn        | 0/5000060
replay_lsn       | 0/5000060
write_lag        | 00:00:00.010948
flush_lag        | 00:00:00.011917
replay_lag       | 00:00:00.011954
sync_priority    | 0
sync_state       | async
reply_time       | 2026-08-27 21:57:04.824462+00

One row per connected standby. No row means no standby is connected, which is the single most important thing this view tells you and the easiest to miss, because an empty result set does not look like an alert.

-- the check that matters, written so that zero standbys is a failure
SELECT count(*) AS connected_standbys FROM pg_stat_replication;

state moves through startup, catchup and streaming. A standby stuck in catchup is reading through a backlog; one in streaming is current. Lesson XIV-04 covers the four LSNs and three lag columns.

The standby’s view

Read-only / Safepg_stat_wal_receiver, every column— EXECUTION VALIDATED on 18.6 on the standby.

$ SELECT * FROM pg_stat_wal_receiver;

-[ RECORD 1 ]---------+---------------------------------------------
pid                   | 32
status                | streaming
receive_start_lsn     | 0/5000000
receive_start_tli     | 1
written_lsn           | 0/5033010
flushed_lsn           | 0/5033010
received_tli          | 1
last_msg_send_time    | 2026-08-27 21:57:20.223009+00
last_msg_receipt_time | 2026-08-27 21:57:20.22313+00
latest_end_lsn        | 0/5033010
latest_end_time       | 2026-08-27 21:57:20.223009+00
slot_name             | standby1
sender_host           | rbpg-prim
sender_port           | 5432
conninfo              | user=repl password=******** dbname=replication
                      host=rbpg-prim port=5432
                      fallback_application_name=walreceiver ...

The columns are written_lsn and flushed_lsn, not received_lsn

Writing received_lsn produces:

Read-only / Safethe error, and the hint that does not help— Real error from 18.6.

$ SELECT received_lsn FROM pg_stat_wal_receiver;

ERROR:  column "received_lsn" does not exist
HINT:  Perhaps you meant to reference the column "pg_stat_wal_receiver.received_tli".

The hint suggests received_tli, which is a timeline, not a position. The columns you want are written_lsn (handed to the OS) and flushed_lsn (fsynced), matching write_lsn and flush_lsn on the primary’s side.

Note also that conninfo masks the password as ********. The password is not masked in postgresql.auto.conf, where pg_basebackup -R writes it in plain text.

last_msg_send_time and last_msg_receipt_time are the pair to watch for a stalled connection: a large and growing gap between the last message the primary sent and now means the connection is not delivering, even when status still says streaming.

Configuring the connection

Three things must line up.

On the primary, a role with the REPLICATION attribute:

CREATE ROLE repl WITH REPLICATION LOGIN PASSWORD '...';

REPLICATION is a distinct privilege from superuser and from any table grant. A replication role needs no SELECT on anything, because it never reads through the SQL layer — which makes it a good example of least privilege that is genuinely achievable.

In pg_hba.conf, a line for the special database name replication:

# TYPE  DATABASE      USER  ADDRESS       METHOD
host    replication   repl  10.0.0.0/24   scram-sha-256

replication here is not a database. It is a keyword meaning “replication connections”, and a host all all line does not cover it. This is the most common reason a standby cannot connect, and the error message says so plainly.

On the standby, primary_conninfo, normally written by pg_basebackup -R.

pg_basebackup -R writes the password in plain text

primary_conninfo = 'user=repl password=replpass host=''rbpg-prim'' ...'

That is postgresql.auto.conf, mode 0600 and owned by the postgres user, which is some protection and not much. It ends up in backups, in configuration management, and in any copy of the data directory.

Use a .pgpass file (mode 0600, in the postgres user’s home), a client certificate as in lesson V-04, or an environment-injected secret instead, and remove the password from primary_conninfo once the standby is built.

When a standby will not connect

The failures are distinguishable from their messages, and each names its own fix.

Message	Cause
`no pg_hba.conf entry for replication connection`	Missing `replication` line
`password authentication failed for user "repl"`	Wrong password, or `.pgpass` not read
`must be superuser or replication role to start walsender`	Role lacks `REPLICATION`
`number of requested standby connections exceeds max_wal_senders`	Too few sender slots
`database system identifier differs between the primary and standby`	The standby is not a copy of this primary
`can no longer access replication slot "..."`	Slot invalidated (lesson XIV-06)

The identifier mismatch deserves its own note, because it was produced by accident while preparing this part:

Service impact possiblea standby built the wrong way— Real error from 18.6, after PGDATA was replaced while the standby's own postmaster was still running.

$ tail /tmp/sb.log

FATAL:  database system identifier differs between the primary and standby
DETAIL:  The primary's identifier is 7678833575722270763, the standby's identifier is 7678833643757821995.

The data directory had been wiped and re-created by pg_basebackup while the old postmaster was still alive; on shutdown it rewrote pg_control with its own identifier, overwriting the one the backup had installed. The procedural lesson is small and absolute: stop the target cluster before replacing its data directory, and confirm it stopped.

What the replication protocol carries, in both directions

A replication connection is a normal PostgreSQL connection that issued START_REPLICATION instead of running queries, after which the protocol becomes a bidirectional stream of CopyData messages.

Primary to standby, two message types matter. XLogData carries a chunk of WAL with the LSN it starts at. Primary keepalive carries the primary’s current end-of-WAL and a flag asking for an immediate reply, which is how the connection is proven alive during quiet periods and how the standby learns of WAL it has not been sent yet.

Standby to primary, one message type carries everything the primary knows about the standby: Standby status update, containing the write, flush and replay LSNs and — when hot_standby_feedback is on — the standby’s oldest xmin. Every value in pg_stat_replication beyond sent_lsn arrives in that message. The primary is not observing the standby; it is being told.

Three practical consequences follow.

The lag columns are as fresh as the last status update. The interval is governed by wal_receiver_status_interval, default 10 seconds. A lag figure can be up to that stale, which matters when you are watching it during an incident.

A standby that stops sending status updates looks frozen rather than gone. wal_sender_timeout, default 60 seconds, is what eventually terminates such a walsender. Until it fires, pg_stat_replication shows a row with stale LSNs, which reads as a lagging standby rather than a dead one. The reply_time column is what distinguishes them.

Synchronous replication is implemented entirely in this exchange. A committing backend on the primary waits until a status update reports the required LSN. Lesson XIV-05 shows what that costs and what happens when the update never arrives.

What to take from this

pg_stat_replication has one row per connected standby. Zero rows is the alert.
pg_stat_wal_receiver uses written_lsn and flushed_lsn; there is no received_lsn.
A replication role needs the REPLICATION attribute and no table grants.
pg_hba.conf needs a line for the replication keyword; host all all does not cover it.
pg_basebackup -R writes the password in plain text. Replace it.
Stop a cluster before replacing its data directory, or you get an identifier mismatch.

Cross-course references

Linux for Production Sysadmins — Part VI (Processes) covers seeing the sender and receiver as operating-system processes, and Part XLII (Network Performance) covers measuring the link they depend on.
Observability for Production Sysadmins — Part LX (Network observability) covers the bandwidth and latency series that explain most lag before any database setting does.