Lab 21: Add a streaming standby to a running primary

Objective

Building a streaming standby is a short procedure that has to be done in the right order, and the tooling hides enough of it that people who have done it several times still cannot say what -R writes.

By the end of this lab you will have added a standby to a primary that was already running and already had a standby of its own, verified replication in both directions — data arrives, writes are refused — and promoted it to synchronous while measuring what that costs.

Along the way you will look at the file pg_basebackup -R produced, which on this run contained two primary_conninfo settings, one of them pointing at the wrong host.

Architecture

An existing primary with one standby, plus the new one this lab builds.

flowchart TD
    P["primary: rbpg-sb\n172.26.0.3\nwal_level=replica, 10 senders"] --> S1["existing standby\napplication_name=oldprimary\n172.26.0.2"]
    P --> S2["new standby: rbpg-lab21\n172.26.0.5"]
    SL["slot lab21_slot\ncreated before the backup"] --> S2
    S2 --> R["read-only:\nSELECT works\nINSERT refused"]
    P --> SY["synchronous_standby_names\n= \\\"lab21-standby\\\""]

Requirements

A running PostgreSQL 18 primary with wal_level = replica (the default), spare max_wal_senders, a role carrying the REPLICATION attribute, and a pg_hba.conf rule for the replication pseudo-database.
A second container or host with the same PostgreSQL major version. A standby cannot replicate across major versions.
Network connectivity from the standby to the primary’s port.

Scenario

You have a single production primary and have been asked to add a replica — first asynchronous, so it can serve reads and act as a failover target, and then synchronous once the team has decided the latency cost is acceptable.

Tasks

Task 1 — What the primary needs

LAB="$HOME/rbpg-lab-21"
mkdir -p "$LAB"
PRIM_IP=$(docker inspect -f '{{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}' rbpg-sb)

docker exec -u postgres rbpg-sb psql -X -c "
  SELECT name, setting FROM pg_settings
  WHERE name IN ('wal_level','max_wal_senders','max_replication_slots',
                 'wal_keep_size','hot_standby','synchronous_standby_names')
  ORDER BY name;" | tee "$LAB/primary-side.txt"

docker exec -u postgres rbpg-sb psql -X -c \
  "SELECT rolname, rolreplication, rolcanlogin FROM pg_roles WHERE rolreplication;" \
  | tee -a "$LAB/primary-side.txt"
docker exec -u postgres rbpg-sb psql -X -c "
  SELECT type, database, user_name, address, auth_method
  FROM pg_hba_file_rules WHERE database::text LIKE '%replication%';" | tee -a "$LAB/primary-side.txt"

Read-only / Safeeverything already in place, at defaults— Captured from the lab run of 2026-08-28.

$ query the replication settings, the replication roles, and the HBA rules

           name            | setting 
---------------------------+---------
hot_standby               | on
max_replication_slots     | 10
max_wal_senders           | 10
synchronous_standby_names | 
wal_keep_size             | 0
wal_level                 | replica
(6 rows)

rolname  | rolreplication | rolcanlogin 
----------+----------------+-------------
postgres | t              | t
repl     | t              | t
(2 rows)

type  |   database    | user_name |  address  |  auth_method  
-------+---------------+-----------+-----------+---------------
local | {replication} | {all}     |           | trust
host  | {replication} | {all}     | 127.0.0.1 | trust
host  | {replication} | {all}     | ::1       | trust
host  | {replication} | {repl}    | all       | scram-sha-256
(4 rows)

Four prerequisites, three of which are defaults on PostgreSQL 18:

wal_level = replica — enough. logical is only for logical replication and costs more WAL.
max_wal_senders = 10 — one sender per streaming standby, plus one per concurrent pg_basebackup -Xstream, which uses two connections.
A REPLICATION role. repl here. It needs no privileges on any database; REPLICATION and LOGIN are sufficient, and giving it nothing else is the point.
An HBA rule with database replication. That is not a real database name, and a rule for all does not match it — the fourth line above exists specifically for this.

wal_keep_size = 0 is the interesting one. It means the primary keeps no extra WAL for standbys by default, which is why Task 2 creates a slot.

Task 2 — A slot, before the backup

docker exec -u postgres rbpg-sb psql -X -c \
  "SELECT * FROM pg_create_physical_replication_slot('lab21_slot');"
docker exec -u postgres rbpg-sb psql -X -c \
  "SELECT slot_name, slot_type, active, restart_lsn, wal_status FROM pg_replication_slots;"

Configuration changean inactive slot, reserving nothing yet— Captured from the lab run of 2026-08-28.

$ pg_create_physical_replication_slot, then list the slots

 slot_name  | slot_type | active | restart_lsn | wal_status 
------------+-----------+--------+-------------+------------
lab21_slot | physical  | f      |             | reserved
oldprimary | physical  | t      | 0/4B000060  | reserved
(2 rows)

restart_lsn is null because nothing has connected yet. A slot created without immediately_reserve retains no WAL until its consumer first connects — which is correct here, since pg_basebackup is about to connect and pin it.

Task 3 — Build the standby

docker exec rbpg-lab21 bash -c \
  "mkdir -p /var/lib/postgresql/sb && chown postgres:postgres /var/lib/postgresql/sb"

docker exec -u postgres -e PGPASSWORD=replpass rbpg-lab21 \
  pg_basebackup -h "$PRIM_IP" -U repl -D /var/lib/postgresql/sb \
    -Fp -Xstream -c fast -R -S lab21_slot -P
echo "exit status: $?"
docker exec rbpg-lab21 du -sh /var/lib/postgresql/sb

Configuration change125 MB in 363 milliseconds— Captured from the lab run of 2026-08-28.

$ pg_basebackup -R -S lab21_slot against the primary

waiting for checkpoint
110822/110822 kB (100%), 0/1 tablespace
110822/110822 kB (100%), 1/1 tablespace
exit status: 0

125M	/var/lib/postgresql/sb

Each flag earns its place: -R writes the standby configuration, -S uses the slot created in Task 2, -Xstream brings the WAL generated during the backup, and -c fast avoids the wait Lab 18 documented.

Task 4 — Read what -R actually wrote

docker exec rbpg-lab21 ls /var/lib/postgresql/sb/ | grep -E "standby.signal|postgresql.auto"
docker exec rbpg-lab21 cat /var/lib/postgresql/sb/postgresql.auto.conf | tee "$LAB/basebackup.txt"
docker exec rbpg-lab21 bash -c "grep -c primary_conninfo /var/lib/postgresql/sb/postgresql.auto.conf"

Read-only / Safethe primary's own settings, and then the standby's appended after them— Captured from the lab run of 2026-08-28. Note there are two primary_conninfo lines.

$ cat the postgresql.auto.conf that -R produced

postgresql.auto.conf
standby.signal

# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
synchronous_commit = 'on'
synchronous_standby_names = ''
max_slot_wal_keep_size = '-1'
primary_conninfo = 'user=repl password=replpass ... host=''rbpg-prim'' port=5432 ...'
primary_slot_name = 'standby1'
listen_addresses = '*'
hot_standby = 'on'
hot_standby_feedback = 'off'
max_standby_streaming_delay = '300s'
log_statement = 'all'
log_line_prefix = '%m [%p] %q%u@%d/%a '
shared_preload_libraries = 'pg_stat_statements'
primary_conninfo = 'user=repl password=replpass ... host=172.26.0.3 port=5432 ...'
primary_slot_name = 'lab21_slot'

primary_conninfo lines in the file: 2

-R does not create postgresql.auto.conf. The base backup copies the primary’s, and -R appends to the copy.

So this standby inherited every ALTER SYSTEM setting the primary had — including log_statement = 'all', shared_preload_libraries, and a primary_conninfo from when this host was itself a standby of something called rbpg-prim, pointing at a primary_slot_name of standby1.

The standby works, because Lab 3’s rule applies: the later assignment wins, and the appended values are last. But the file is now actively misleading to read.

Read the file -R produced, every time

Two failure modes follow from this.

A stale primary_conninfo earlier in the file looks authoritative. An engineer reading it during an incident, or a configuration-management tool parsing it, sees the wrong host. The running server is correct; the file’s first answer is not.

Every primary-side ALTER SYSTEM comes along. A primary tuned with shared_buffers = 32GB produces a standby that tries the same on a smaller machine, and a primary with log_statement = 'all' produces a standby logging every query on a host nobody is watching.

After pg_basebackup -R, read postgresql.auto.conf and remove what does not belong. It is a thirty-second check that prevents a class of confusing incident.

Note also the password sitting in that file in plain text. postgresql.auto.conf is mode 0600 inside a 0700 data directory, which is the right protection, but it means the file is a credential and should not be copied into a ticket.

standby.signal is the counterpart of Lab 20’s recovery.signal: an empty file whose presence makes the server a standby that streams continuously, rather than a recovery that stops at a target.

Task 5 — Start it and verify both directions

docker exec rbpg-lab21 bash -c "chmod 0700 /var/lib/postgresql/sb"
docker exec -u postgres rbpg-lab21 /usr/lib/postgresql/18/bin/pg_ctl \
  -D /var/lib/postgresql/sb -l /tmp/sb.log start
sleep 5
docker exec -u postgres rbpg-lab21 psql -X -c "SELECT pg_is_in_recovery();"
docker exec rbpg-lab21 grep -E "entering standby|started streaming|consistent" /tmp/sb.log

Configuration changestreaming from the primary on timeline 2— Captured from the lab run of 2026-08-28. The primary is on timeline 2 from an earlier promotion.

$ start the standby and read its log

 pg_is_in_recovery 
-------------------
t
(1 row)

2026-08-28 06:11:00.699 UTC [66] LOG:  entering standby mode
2026-08-28 06:11:00.706 UTC [66] LOG:  consistent recovery state reached at 0/4A000120
2026-08-28 06:11:00.706 UTC [60] LOG:  database system is ready to accept read-only connections
2026-08-28 06:11:00.713 UTC [67] LOG:  started streaming WAL from primary at 0/4B000000 on timeline 2

docker exec -u postgres rbpg-sb psql -X -c "
  SELECT application_name, client_addr, state, sync_state, sent_lsn, replay_lsn
  FROM pg_stat_replication ORDER BY application_name;" | tee "$LAB/streaming.txt"

docker exec -i -u postgres rbpg-sb psql -X -c "CREATE DATABASE lab21;"
docker exec -i -u postgres rbpg-sb psql -X -d lab21 -c \
  "CREATE TABLE t(id int primary key, note text);
   INSERT INTO t VALUES (1,'written on the primary');"
sleep 2
docker exec -u postgres rbpg-lab21 psql -X -d lab21 -c "SELECT * FROM t;"
docker exec -u postgres rbpg-lab21 psql -X -d lab21 -c "INSERT INTO t VALUES (2,'attempted on the standby');"

Read-only / Safetwo standbys, data arriving, writes refused— Captured from the lab run of 2026-08-28.

$ pg_stat_replication, then a write on the primary read from the standby, then a write attempt on the standby

 application_name | client_addr |   state   | sync_state |  sent_lsn  | replay_lsn 
------------------+-------------+-----------+------------+------------+------------
oldprimary       | 172.26.0.2  | streaming | async      | 0/4B000060 | 0/4B000060
walreceiver      | 172.26.0.5  | streaming | async      | 0/4B000060 | 0/4B000060
(2 rows)

CREATE DATABASE
CREATE TABLE
INSERT 0 1

id |          note          
----+------------------------
1 | written on the primary
(1 row)

ERROR:  cannot execute INSERT in a read-only transaction

Both directions confirmed in four commands. state = streaming for both, and sent_lsn = replay_lsn means neither is behind.

Task 6 — Give the standby a name

Note the new standby’s application_name: walreceiver. That is the default, and it is useless once there is more than one standby.

docker exec -u postgres rbpg-lab21 psql -X -c "SHOW cluster_name;"
docker exec -u postgres rbpg-lab21 psql -X -c "ALTER SYSTEM SET cluster_name = 'lab21-standby';"
docker exec -u postgres rbpg-lab21 /usr/lib/postgresql/18/bin/pg_ctl \
  -D /var/lib/postgresql/sb restart -m fast -l /tmp/sb.log
sleep 5
docker exec -u postgres rbpg-sb psql -X -c \
  "SELECT application_name, client_addr, state FROM pg_stat_replication ORDER BY application_name;"

Configuration changea name you can write a monitoring rule against— Captured from the lab run of 2026-08-28. cluster_name was empty.

$ set cluster_name on the standby, restart it, then look at the primary

 cluster_name 
--------------

(1 row)

application_name | client_addr |   state   
------------------+-------------+-----------
lab21-standby    | 172.26.0.5  | streaming
oldprimary       | 172.26.0.2  | streaming
(2 rows)

cluster_name is a postmaster parameter, so this needs a restart. The alternative is application_name=... inside primary_conninfo, which is a reload — but cluster_name also prefixes the standby’s own log lines and its process titles, so it is worth the restart.

This matters beyond tidiness: synchronous_standby_names selects standbys by application_name, and it cannot select one called walreceiver when there are two of them.

Task 7 — Make it synchronous

docker exec -u postgres rbpg-sb psql -X -c \
  "ALTER SYSTEM SET synchronous_standby_names = 'lab21-standby';"

Read-only / Safethe value is a name list, not a string— Captured from the lab run of 2026-08-28.

$ ALTER SYSTEM SET synchronous_standby_names = 'lab21-standby'

ERROR:  invalid value for parameter "synchronous_standby_names": "lab21-standby"
DETAIL:  syntax error at or near "-"

synchronous_standby_names parses its contents as a list of SQL identifiers, so a name containing a hyphen must be double-quoted inside the string literal:

docker exec -u postgres rbpg-sb psql -X -c \
  "ALTER SYSTEM SET synchronous_standby_names = '\"lab21-standby\"';"
docker exec -u postgres rbpg-sb psql -X -c "SELECT pg_reload_conf();"
sleep 2
docker exec -u postgres rbpg-sb psql -X -c \
  "SELECT application_name, sync_state, sync_priority FROM pg_stat_replication ORDER BY application_name;" \
  | tee "$LAB/synchronous.txt"

Configuration changeone synchronous standby, one asynchronous— Captured from the lab run of 2026-08-28.

$ the quoted form, a reload, then pg_stat_replication

 synchronous_standby_names 
---------------------------
"lab21-standby"
(1 row)

application_name | sync_state | sync_priority 
------------------+------------+---------------
lab21-standby    | sync       |             1
oldprimary       | async      |             0
(2 rows)

Task 8 — Measure what synchronous costs

docker exec -u postgres rbpg-sb pgbench -i -s 5 -q lab21
docker exec rbpg-sb bash -c "cat > /tmp/bench.sql <<'EOF'
INSERT INTO pgbench_history (tid,bid,aid,delta,mtime) VALUES (1,1,1,1,CURRENT_TIMESTAMP);
EOF
chown postgres /tmp/bench.sql"

for MODE in local remote_write on remote_apply; do
  R=$(docker exec -u postgres -e PGOPTIONS="-c synchronous_commit=$MODE" rbpg-sb \
        pgbench -c 4 -j 2 -T 8 -M prepared --no-vacuum -f /tmp/bench.sql lab21 | grep "^tps")
  printf "synchronous_commit=%-13s %s\n" "$MODE" "$R"
done | tee -a "$LAB/synchronous.txt"

Read-only / Safewaiting for the standby costs roughly a quarter to a third of throughput— Captured from the lab run of 2026-08-28. Single 8-second runs on a shared container host.

$ pgbench at four synchronous_commit levels with a synchronous standby configured

synchronous_commit=local         tps = 824.597449 (without initial connection time)
synchronous_commit=remote_write  tps = 809.972725 (without initial connection time)
synchronous_commit=on            tps = 552.052750 (without initial connection time)
synchronous_commit=remote_apply  tps = 647.385991 (without initial connection time)

What each level actually waits for

synchronous_commit decides how far a commit must travel before the client is told it succeeded:

off — not even the local WAL flush. The commit is acknowledged before it is durable anywhere; a crash can lose recent transactions, though never corrupt the database.
local — the local WAL is flushed to disk. Durable on the primary, nothing waited for on any standby.
remote_write — the standby has written the WAL to its operating system. Survives a standby PostgreSQL crash; does not survive a standby power loss.
on — the standby has flushed the WAL to durable storage. This is the default and the point at which a synchronous standby genuinely protects the transaction.
remote_apply — the standby has applied it, so a read on the standby immediately afterwards will see it. This is the only level that makes read-your-writes work across a primary and its replicas.

It is user context, so it can be set per transaction. That is the most useful property of all: a financial transfer can use remote_apply while an audit-log insert in the same session uses local, and the throughput cost is paid only where it buys something.

Validation

test -s "$LAB/primary-side.txt" && echo "OK primary-side"
test -s "$LAB/basebackup.txt"   && echo "OK basebackup"
test -s "$LAB/streaming.txt"    && echo "OK streaming"
test -s "$LAB/synchronous.txt"  && echo "OK synchronous"

# The end-to-end check, in one command per direction:
docker exec -u postgres rbpg-sb psql -X -d lab21 -c \
  "INSERT INTO t VALUES (99,'validation'); SELECT pg_sleep(2);"
docker exec -u postgres rbpg-lab21 psql -X -d lab21 -c \
  "SELECT note FROM t WHERE id = 99;"

Questions to answer without looking anything up:

A pg_hba.conf has host all all 10.0.0.0/8 scram-sha-256 and no other rules. Can a standby connect?
Why create the replication slot before taking the base backup rather than after?
pg_basebackup -R wrote a postgresql.auto.conf containing two primary_conninfo lines. Which one is in effect, and why is the other there?
Two standbys both report application_name = walreceiver. What can you not do?
Your only synchronous standby loses power. What happens to commits on the primary?

Expected Outcome

You have added a standby to a live primary, verified it in both directions, named it, and made it synchronous while measuring the cost.

The procedure:

# On the primary:
psql -c "SELECT pg_create_physical_replication_slot('sb2_slot');"

# On the standby host:
pg_basebackup -h primary -U repl -D $PGDATA -Fp -Xstream -c fast -R -S sb2_slot -P
# READ what -R wrote, and remove the primary's settings that came with it.
echo "cluster_name = 'sb2'" >> $PGDATA/postgresql.auto.conf
pg_ctl -D $PGDATA start

# Verify from the primary, not from hope:
psql -c "SELECT application_name, state, sent_lsn, replay_lsn FROM pg_stat_replication;"

Lab 22 measures how far behind that standby is, and Lab 23 covers what the slot you just created can do to the primary if the standby goes away.

Troubleshooting

pg_basebackup fails with FATAL: no pg_hba.conf entry for replication connection. Replication needs its own rule; a rule for all databases does not cover it. Add a line with replication in the database column and reload.

FATAL: must be superuser or replication role. The role lacks the REPLICATION attribute. ALTER ROLE repl REPLICATION;.

The standby starts as a normal read-write server. standby.signal is missing from the data directory. pg_basebackup -R writes it along with primary_conninfo; without -R you write both yourself. Confirm with SELECT pg_is_in_recovery(); — it must be t.

-R wrote settings you did not want. It copies the primary’s postgresql.auto.conf, which can carry primary-specific settings into the standby. Task 4 exists to make you read the file rather than trust it — cluster_name in particular should be the standby’s own.

The standby connects and immediately disconnects. Read the standby log. The commonest causes are a WAL segment already removed on the primary — which the slot in Task 2 prevents — and a primary_conninfo password the standby cannot supply.

pg_stat_replication is empty on the primary. The standby is not connected. Check from the standby with SELECT * FROM pg_stat_wal_receiver;, which is the view that still works when the primary-side one shows nothing.

Commits hang after Task 7. That is synchronous replication doing exactly what it promises. If the standby is gone, every commit waits at IPC / SyncRep with reads still working and nothing in the log. Clear it by restoring the standby or by removing it from synchronous_standby_names and reloading.

Cleanup

docker exec -u postgres rbpg-sb psql -X -c "ALTER SYSTEM RESET synchronous_standby_names;"
docker exec -u postgres rbpg-sb psql -X -c "SELECT pg_reload_conf();"
docker exec -u postgres rbpg-lab21 /usr/lib/postgresql/18/bin/pg_ctl \
  -D /var/lib/postgresql/sb stop -m fast
docker exec -u postgres rbpg-sb psql -X -c \
  "SELECT pg_drop_replication_slot('lab21_slot');"
docker exec -u postgres rbpg-sb psql -X -c "DROP DATABASE IF EXISTS lab21;"
docker rm -f rbpg-lab21

Dropping the slot matters. A slot whose standby is gone retains WAL forever, which is Lab 23.

Production notes

Create the slot before the base backup, and take the backup with the slot. Otherwise the primary can recycle a segment the standby still needs, in the window between the backup finishing and the standby connecting.
Read what -R wrote. It is a convenience, not a configuration decision, and it carries the primary’s postgresql.auto.conf with it.
Give every standby a cluster_name. It becomes application_name in pg_stat_replication, and without it the view shows walreceiver for every node, which is useless during an incident.
A single synchronous standby is a single point of failure for writes. ANY 1 (sb1, sb2) survives losing one; FIRST 1 (sb1) does not. Choose knowing that.
Verify replication in both directions: the primary’s pg_stat_replication and the standby’s own view, plus a row written on one and read on the other. Only the last one proves data is moving.

What You Learned

Replication needs its own pg_hba.conf rule and a role with the REPLICATION attribute.
A slot taken before the backup closes the window where the primary could recycle a segment the standby still needs.
standby.signal is what makes a data directory a standby, not a setting — recovery.conf and standby_mode were removed in PostgreSQL 12.
-R writes primary_conninfo and standby.signal and brings the primary’s auto.conf with it, which you should read.
cluster_name becomes application_name, which is what makes pg_stat_replication and synchronous_standby_names legible.
Synchronous replication is a durability promise paid for in commit latency, and losing the standby stops writes with no error at all.

Objectives

Prerequisites