Setting Up Streaming Replication

Build a real hot standby from scratch with pg_basebackup -R, start it on port 5433, and confirm it streams

pg_basebackup takes a consistent, byte-for-byte copy of an entire running cluster over the same replication protocol streaming itself uses — no downtime on the primary, no separate export/import step. Passed -R, it goes further: it writes primary_conninfo into the new copy's configuration and drops a standby.signal file into its data directory, so the copy starts up already knowing it is a standby and where to stream from, with no manual recovery configuration at all.\n\nOn a real deployment this copy would live on a second server entirely. This VM only has one machine to give you, so the standby becomes a second, completely independent postgres process on the same box, listening on port 5433 instead of the primary's 5432 — genuinely two separate servers from PostgreSQL's point of view, just sharing one piece of hardware.

One Command Builds the Standby

pg_basebackup -h 127.0.0.1 -p 5432 -U postgres -D /tmp/standby_data -R -X stream -c fast

-R writes primary_conninfo into postgresql.auto.conf and creates standby.signal — everything needed to boot straight into standby mode
-X stream streams the WAL generated during the backup alongside the base copy, so nothing is missed between when the copy started and when it finished
-c fast forces an immediate checkpoint on the primary rather than waiting for a scheduled one, trading a brief I/O spike for a faster backup start

Two Servers, One Box

This lab's standby needs its own port, since the primary already owns 5432:

echo "port = 5433" >> /tmp/standby_data/postgresql.auto.conf
pg_ctl start -D /tmp/standby_data -l /tmp/standby_log.txt -w

From here on, -p 5433 (or -h 127.0.0.1 -p 5433) addresses the standby specifically, exactly as -p 5432 addresses the primary.

Confirming It Actually Streams

-- On the primary:
SELECT client_addr, state, sent_lsn, replay_lsn, sync_state FROM pg_stat_replication;

-- On the standby:
SELECT pg_is_in_recovery();

pg_stat_replication only ever has rows on the primary — it lists every connected standby. pg_is_in_recovery() returns true on a standby and false everywhere else; it is the fastest single-query way to confirm which role a given connection is actually talking to.

pg_basebackup -R

Requests that pg_basebackup write standby configuration automatically: primary_conninfo (pointing back at the server the backup was taken from) into postgresql.auto.conf, and an empty standby.signal file in the data directory's root. Without -R, both would need to be created by hand before the copy could start as a standby at all.

standby.signal

An empty marker file whose mere presence tells PostgreSQL at startup to enter standby mode and begin continuously replaying WAL from primary_conninfo, rather than starting up as a normal read-write server. Removing it (or promoting the standby) is what turns a standby into an independent primary.

📦 Take the Base Backup

Build a complete copy of the primary with pg_basebackup -R, which writes the standby's own configuration for you.

as-postgres pg_basebackup -h 127.0.0.1 -p 5432 -U postgres -D /tmp/standby_data -R -X stream -c fast

student@lab:~$ as-postgres pg_basebackup -h 127.0.0.1 -p 5432 -U postgres -D /tmp/standby_data -R -X stream -c fast 2026-06-26 16:02:39.102 UTC [138] LOG: checkpoint starting: immediate force wait 2026-06-26 16:02:39.128 UTC [138] LOG: checkpoint complete: wrote 0 buffers (0.0%), wrote 3 SLRU buffers; 0 WAL file(s) added, 1 removed, 0 recycled; write=0.006 s, sync=0.002 s, total=0.027 s; sync files=2, longest=0.001 s, average=0.001 s; distance=7162 kB, estimate=7162 kB; lsn=0/8000078, redo lsn=0/8000024

🔎 Inspect What -R Generated

Confirm standby.signal exists and see the primary_conninfo that -R wrote automatically.

as-postgres cat /tmp/standby_data/postgresql.auto.conf

student@lab:~$ as-postgres cat /tmp/standby_data/postgresql.auto.conf # Do not edit this file manually! # It will be overwritten by the ALTER SYSTEM command. wal_level = 'replica' max_wal_senders = '5' primary_conninfo = 'user=postgres passfile=''/var/lib/postgresql/18/data/.pgpass'' channel_binding=disable host=127.0.0.1 port=5432 client_encoding=UTF8 sslmode=disable sslnegotiation=postgres sslcompression=0 sslcertmode=disable sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=postgres gssdelegation=0 target_session_attrs=any load_balance_hosts=disable'

🔌 Give the Standby Its Own Port

The standby needs a different port from the primary's 5432 before it can start on the same VM.

as-postgres sh -c "echo 'port = 5433' >> /tmp/standby_data/postgresql.auto.conf"

student@lab:~$ as-postgres sh -c "echo 'port = 5433' >> /tmp/standby_data/postgresql.auto.conf"

🚀 Start the Standby

Start the second postgres instance and watch it come up already in recovery mode.

as-postgres pg_ctl start -D /tmp/standby_data -l /tmp/standby_log.txt -w

student@lab:~$ as-postgres pg_ctl start -D /tmp/standby_data -l /tmp/standby_log.txt -w waiting for server to start.... done server started

✅ Confirm Streaming From Both Sides

Check pg_stat_replication on the primary, and pg_is_in_recovery() on the standby.

psql -U postgres -d beer_db -c "SELECT client_addr, state, sent_lsn, replay_lsn, sync_state FROM pg_stat_replication;"

psql -U postgres -h 127.0.0.1 -p 5433 -d beer_db -c "SELECT pg_is_in_recovery();"

student@lab:~$ psql -U postgres -d beer_db -c "SELECT client_addr, state, sent_lsn, replay_lsn, sync_state FROM pg_stat_replication;" SET client_addr | state | sent_lsn | replay_lsn | sync_state -------------+-----------+-----------+------------+------------ 127.0.0.1 | streaming | 0/900C000 | 0/900BA40 | async (1 row) student@lab:~$ psql -U postgres -h 127.0.0.1 -p 5433 -d beer_db -c "SELECT pg_is_in_recovery();" SET pg_is_in_recovery ------------------- t (1 row)

✍️ Write on the Primary, Read on the Standby

Insert 1,000 rows on the primary and confirm they appear on the standby.

psql -U postgres -d beer_db -c "CREATE TABLE IF NOT EXISTS repl_test (id serial PRIMARY KEY, note text);" -c "INSERT INTO repl_test (note) SELECT 'row-'||g FROM generate_series(1,1000) g;"

psql -U postgres -h 127.0.0.1 -p 5433 -d beer_db -c "SELECT count(*) FROM repl_test;"

student@lab:~$ psql -U postgres -d beer_db -c "CREATE TABLE IF NOT EXISTS repl_test (id serial PRIMARY KEY, note text);" -c "INSERT INTO repl_test (note) SELECT 'row-'||g FROM generate_series(1,1000) g;" SET CREATE TABLE INSERT 0 1000 student@lab:~$ psql -U postgres -h 127.0.0.1 -p 5433 -d beer_db -c "SELECT count(*) FROM repl_test;" SET count ------- 1000 (1 row)

📐 Calculate the Lag

Read the replication lag directly, in both bytes and time, from pg_stat_replication.

psql -U postgres -d beer_db -c "SELECT client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn, write_lag, flush_lag, replay_lag, sync_state FROM pg_stat_replication;"

Lab 2.5.2 complete. You built a genuine hot standby from scratch and proved it works from both ends:\n\n\n pg_basebackup -R : ✅ standby.signal + primary_conninfo generated\n Second instance started : ✅ independent postgres process, port 5433\n pg_stat_replication : ✅ streaming, confirmed from the primary\n Write propagation verified : ✅ 1,000 rows, primary to standby\n Lag calculated : ✅ bytes and milliseconds, both read directly\n

Enable JavaScript to run the live terminal and track your progress.