Day 10: Streaming Replication
Goal:
pg2runs as a hot standby ofpg1, you measure the lag, switch to synchronous mode and survive a failover includingpg_rewindand switching back. · Duration: approx. 120 min · Prerequisite: Day 9
Briefing
- The standby receives the primary’s WAL over a replication connection and replays it continuously – a physical copy, block by block.
- A replication slot makes sure the primary does not delete WAL that the standby still needs.
- Asynchronous is the default: fast, but after a failure the last transactions may be missing. In synchronous mode every commit waits for the standby.
- The standby runs as a second cluster
18/standby(port 5433) onpg2.18/mainthere stays untouched – Day 11 needs it as a standalone server.
pg1 .182 18/main (Primary) pg2 .123 18/standby :5433
walsender ---- WAL stream, slot tag10_pg2 ----> walreceiver -> replay
pg_stat_replication pg_stat_wal_receiver, read-only
Drill 1: Replication role and access
On pg1: a role with REPLICATION. For
pg_rewind (Drill 7) it additionally needs execute rights on
four functions – in the database pg_rewind connects to
(postgres):
psql -c "CREATE ROLE tag10_repl LOGIN REPLICATION PASSWORD 'lab-repl-123';"
psql -d postgres <<'SQL'
GRANT EXECUTE ON FUNCTION pg_ls_dir(text, boolean, boolean) TO tag10_repl;
GRANT EXECUTE ON FUNCTION pg_stat_file(text, boolean) TO tag10_repl;
GRANT EXECUTE ON FUNCTION pg_read_binary_file(text) TO tag10_repl;
GRANT EXECUTE ON FUNCTION pg_read_binary_file(text, bigint, bigint, boolean)
TO tag10_repl;
SQL
cat <<'HBA' | sudo tee -a /etc/postgresql/18/main/pg_hba.conf
host replication tag10_repl 172.16.0.123/32 scram-sha-256
host postgres tag10_repl 172.16.0.123/32 scram-sha-256
HBA
psql -Atc 'SELECT pg_reload_conf()'
echo '172.16.0.123:5433:*:tag10_repl:lab-repl-123' \
| sudo -u postgres tee -a /var/lib/postgresql/.pgpass
sudo -u postgres chmod 600 /var/lib/postgresql/.pgpass
psql -Atc 'SHOW wal_level' -c 'SHOW max_wal_senders' -c 'SHOW data_checksums' \
-c 'SHOW wal_log_hints'Expected:
CREATE ROLE
GRANT
GRANT
GRANT
GRANT
host replication tag10_repl 172.16.0.123/32 scram-sha-256
host postgres tag10_repl 172.16.0.123/32 scram-sha-256
t
172.16.0.123:5433:*:tag10_repl:lab-repl-123
replica
10
on
off
Warning: Function privileges apply per database. A
GRANTin the databaseubuntudoes not helppg_rewind– it then fails withpermission denied for function pg_read_binary_file.
The defaults are enough: wal_level = replica, ten WAL
senders. pg_rewind needs wal_log_hints = on
or data checksums – those are the default since 18. The
.pgpass on pg1 is not needed until Drill 7,
when pg1 itself becomes a standby.
One more backup on pg1: in Drill 7,
pg_rewind overwrites pg1’s
postgresql.auto.conf with the one from the new primary. You
restore it from this copy in Drills 7 and 8:
sudo -u postgres mkdir -p /var/lib/postgresql/backup-tag10
sudo -u postgres cp -a /var/lib/postgresql/18/main/postgresql.auto.conf \
/var/lib/postgresql/backup-tag10/
sudo -u postgres cat /var/lib/postgresql/backup-tag10/postgresql.auto.confExpected:
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
listen_addresses = '*'
ssl_cert_file = '/etc/postgresql/18/main/server.crt'
ssl_key_file = '/etc/postgresql/18/main/server.key'
shared_preload_libraries = 'pg_stat_statements'
Save your progress? Sign up for free
Drill 2: pg2 becomes a standby
On pg2: password file, a new cluster with
pg_hba lines for the reverse direction (after the failover
pg1 replicates from here), then replace the data directory
with a base backup. -C -S tag10_pg2 creates the slot right
away, -R writes standby.signal and
primary_conninfo:
echo '172.16.0.182:5432:*:tag10_repl:lab-repl-123' \
| sudo -u postgres tee -a /var/lib/postgresql/.pgpass
sudo -u postgres chmod 600 /var/lib/postgresql/.pgpass
sudo pg_createcluster 18 standby
cat <<'HBA' | sudo tee -a /etc/postgresql/18/standby/pg_hba.conf
host replication tag10_repl 172.16.0.182/32 scram-sha-256
host postgres tag10_repl 172.16.0.182/32 scram-sha-256
HBA
sudo -u postgres rm -rf /var/lib/postgresql/18/standby
sudo -u postgres pg_basebackup -h 172.16.0.182 -U tag10_repl \
-D /var/lib/postgresql/18/standby -X stream -c fast -C -S tag10_pg2 -R -v
sudo -u postgres ls /var/lib/postgresql/18/standby | grep signal
sudo -u postgres cat /var/lib/postgresql/18/standby/postgresql.auto.confExpected (abridged):
pg_basebackup: write-ahead log start point: 0/9D000110 on timeline 1
pg_basebackup: created replication slot "tag10_pg2"
pg_basebackup: base backup completed
standby.signal
listen_addresses = '*'
ssl_cert_file = '/etc/postgresql/18/main/server.crt'
ssl_key_file = '/etc/postgresql/18/main/server.key'
shared_preload_libraries = 'pg_stat_statements'
primary_conninfo = 'user=tag10_repl passfile=''/var/lib/postgresql/.pgpass'' ...
host=172.16.0.182 port=5432 sslmode=prefer ...
primary_slot_name = 'tag10_pg2'
As on Day 9, postgresql.auto.conf brings along
pg1’s certificate paths (if you did Day 3). Remove them,
then start:
sudo -u postgres sed -i '/^ssl_/d' /var/lib/postgresql/18/standby/postgresql.auto.conf
sudo pg_ctlcluster 18 standby start
sudo tail -3 /var/log/postgresql/postgresql-18-standby.logExpected (abridged):
LOG: consistent recovery state reached at 0/9D000208
LOG: database system is ready to accept read-only connections
LOG: started streaming WAL from primary at 0/9E000000 on timeline 1
listen_addresses = '*' also came over from
pg1 – intended here, because after the failover
pg1 connects to port 5433.
Save your progress? Sign up for free
Drill 3: Watch replication
On pg1, the primary’s view, plus a test table:
psql -x -c 'SELECT application_name, client_addr, state, sync_state, sent_lsn,
replay_lsn, write_lag, replay_lag FROM pg_stat_replication'
psql -c 'SELECT slot_name, slot_type, active, restart_lsn, wal_status
FROM pg_replication_slots'
createdb tag10
psql -d tag10 -c 'CREATE TABLE ping (id serial PRIMARY KEY, txt text,
t timestamptz DEFAULT now());'
psql -d tag10 -c "INSERT INTO ping (txt) VALUES ('hallo standby')"Expected:
-[ RECORD 1 ]----+----------------
application_name | 18/standby
client_addr | 172.16.0.123
state | streaming
sync_state | async
sent_lsn | 0/9E000060
replay_lsn | 0/9E000060
write_lag | 00:00:00.119798
replay_lag | 00:00:00.122402
slot_name | slot_type | active | restart_lsn | wal_status
-----------+-----------+--------+-------------+------------
tag10_pg2 | physical | t | 0/9E000060 | reserved
(1 row)
CREATE TABLE
INSERT 0 1
application_name is the standby’s
cluster_name (18/standby) – Debian sets it for
every cluster. On pg2, the standby’s view, reading and
writing:
sudo -u postgres psql -p 5433 -x -c 'SELECT status, sender_host, sender_port,
slot_name, written_lsn FROM pg_stat_wal_receiver'
sudo -u postgres psql -p 5433 -d tag10 -c 'SELECT * FROM ping'
sudo -u postgres psql -p 5433 -Atc \
'SELECT pg_is_in_recovery(), now() - pg_last_xact_replay_timestamp()'
sudo -u postgres psql -p 5433 -d tag10 -c "INSERT INTO ping (txt) VALUES ('von pg2')"Expected:
-[ RECORD 1 ]--+-------------
status | streaming
sender_host | 172.16.0.182
sender_port | 5432
slot_name | tag10_pg2
written_lsn | 0/9E007070
id | txt | t
----+---------------+-------------------------------
1 | hallo standby | 2026-11-19 05:10:46.993862+00
(1 row)
t|00:00:02.173773
ERROR: cannot execute INSERT in a read-only transaction
Hot standby means: read queries yes, writes never.
now() - pg_last_xact_replay_timestamp() measures the age of
the last replayed transaction – without write load the value grows even
though nothing is missing.
Save your progress? Sign up for free
Drill 4: Lag under load
On pg1: pgbench -i writes about 160 MB of
WAL in one go. Meanwhile \watch (Day 1) measures the lag in
bytes every second, four times:
pgbench -i -s 10 -q tag10 &
psql -At <<'SQL'
SELECT application_name, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(),
replay_lsn)), replay_lag FROM pg_stat_replication \watch i=1 c=4
SQL
waitExpected (abridged):
18/standby|0 bytes|00:00:00.114498
18/standby|11 MB|00:00:00.305054
18/standby|7376 kB|00:00:00.131414
18/standby|1829 kB|00:00:00.129456
done in 4.26 s (drop tables 0.00 s, create tables 0.01 s, client-side generate ...
Bytes (pg_wal_lsn_diff) are a more reliable metric than
time: they show how much would be missing if a failure happened right
now. The standby catches up within seconds.
Save your progress? Sign up for free
Drill 5: Replicate synchronously
First one measurement in asynchronous mode, then switch the standby
to synchronous by name (a reload is enough) and measure again. On
pg1:
pgbench -c 4 -T 15 tag10 | grep -E '^tps|latency average'
echo "synchronous_standby_names = '\"18/standby\"'" \
| sudo -u postgres tee /etc/postgresql/18/main/conf.d/tag10-sync.conf
psql -Atc 'SELECT pg_reload_conf()'
psql -c 'SELECT application_name, sync_state FROM pg_stat_replication'
pgbench -c 4 -T 15 tag10 | grep -E '^tps|latency average'
PGOPTIONS='-c synchronous_commit=remote_apply' pgbench -c 4 -T 15 tag10 \
| grep -E '^tps|latency average'
PGOPTIONS='-c synchronous_commit=local' pgbench -c 4 -T 15 tag10 \
| grep -E '^tps|latency average'Expected:
latency average = 5.972 ms
tps = 669.836061 (without initial connection time)
synchronous_standby_names = '"18/standby"'
t
application_name | sync_state
------------------+------------
18/standby | sync
(1 row)
latency average = 9.327 ms
tps = 428.882088 (without initial connection time)
latency average = 9.809 ms
tps = 407.800713 (without initial connection time)
latency average = 6.303 ms
tps = 634.628572 (without initial connection time)
The name is in double quotes because it contains a /.
synchronous_commit = on waits until the standby has the WAL
on disk, remote_apply until it has replayed it (reads on
the standby then see every commit immediately). local only
waits for the local disk – every session may choose that itself for
non-critical data. Here synchronous mode costs about a third of the
throughput.
Now the downside: the standby goes away. On pg2:
sudo pg_ctlcluster 18 standby stopOn pg1, the commit runs in the background:
psql -d tag10 -c "INSERT INTO ping (txt) VALUES ('wartet auf pg2')" &
sleep 4
psql -c "SELECT pid, wait_event_type, wait_event, left(query, 40) AS query
FROM pg_stat_activity WHERE wait_event = 'SyncRep'"Expected (abridged):
pid | wait_event_type | wait_event | query
-------+-----------------+------------+------------------------------------------
24034 | IPC | SyncRep | INSERT INTO ping (txt) VALUES ('wartet a
Every writing commit on pg1 now hangs – including those
of other applications. On pg2, start the standby again,
then on pg1 go back to asynchronous:
sudo pg_ctlcluster 18 standby startsudo rm /etc/postgresql/18/main/conf.d/tag10-sync.conf
psql -Atc 'SELECT pg_reload_conf()'
psql -c 'SELECT application_name, sync_state FROM pg_stat_replication'Expected:
INSERT 0 1
t
application_name | sync_state
------------------+------------
18/standby | async
(1 row)
The hanging INSERT went through as soon as the standby
was back. That’s why the rule is: synchronous only with at least two
standbys (ANY 1 (a, b)), otherwise the standby becomes a
second single point of failure.
Save your progress? Sign up for free
Drill 6: Failover with pg_promote
A real failure often catches WAL that never reached the standby. We
simulate that: stop the standby, write a row on pg1, stop
pg1. On pg2:
sudo pg_ctlcluster 18 standby stopOn pg1:
psql -d tag10 -c "INSERT INTO ping (txt) VALUES ('nur auf pg1')"
sudo systemctl stop postgresql@18-mainOn pg2, start the standby – it finds no primary – and promote it:
sudo pg_ctlcluster 18 standby start
sudo tail -2 /var/log/postgresql/postgresql-18-standby.log
sudo -u postgres psql -p 5433 -Atc 'SELECT pg_promote()'
sudo -u postgres psql -p 5433 -Atc 'SELECT pg_is_in_recovery()'
sudo -u postgres psql -p 5433 -d tag10 -c 'SELECT id, txt FROM ping'
sudo -u postgres psql -p 5433 -d tag10 \
-c "INSERT INTO ping (txt) VALUES ('pg2 ist Primary')"
sudo grep -E 'promote|timeline' /var/log/postgresql/postgresql-18-standby.logExpected (abridged):
FATAL: streaming replication receiver "18/standby" could not connect to the primary
server: connection to server at "172.16.0.182", port 5432 failed: Connection refused
LOG: waiting for WAL to become available at 0/D7002000
t
f
id | txt
----+----------------
1 | hallo standby
2 | wartet auf pg2
(2 rows)
INSERT 0 1
LOG: received promote request
LOG: selected new timeline ID: 2
pg_promote() ends recovery, standby.signal
disappears, and from now on the server writes on timeline 2. The row
nur auf pg1 is missing – that is the data loss of
asynchronous replication. (Without SQL,
pg_ctlcluster 18 standby promote does the same.)
Applications now have to point to pg2:5433; in production a
cluster manager or a proxy takes care of that.
Save your progress? Sign up for free
Drill 7: Bring back the old primary with pg_rewind
pg1 has WAL that never existed on pg2. A
new base backup would copy everything; pg_rewind copies
only the blocks changed since the divergence and rewinds
pg1 to the state of pg2. On pg1,
with the cluster stopped:
sudo -u postgres /usr/lib/postgresql/18/bin/pg_rewind \
-D /var/lib/postgresql/18/main -R -P \
--source-server='host=172.16.0.123 port=5433 user=tag10_repl dbname=postgres'Expected:
pg_rewind: connected to server
pg_rewind: servers diverged at WAL location 0/D7E28FA8 on timeline 1
pg_rewind: error: could not open file "/var/lib/postgresql/18/main/pg_wal/
0000000100000000000000D6": No such file or directory
pg_rewind: error: could not find previous WAL record at 0/D6FFFFF8
pg_rewind reads pg1’s WAL starting at the
last common checkpoint. pg1 already recycled it when
shutting down, and there has been no archive since Day 9. The new
primary still has the timeline 1 segments, though:
sudo -u postgres rsync -a --ignore-existing \
'172.16.0.123:/var/lib/postgresql/18/standby/pg_wal/00000001*' \
/var/lib/postgresql/18/main/pg_wal/
sudo -u postgres /usr/lib/postgresql/18/bin/pg_rewind \
-D /var/lib/postgresql/18/main -R -P \
--source-server='host=172.16.0.123 port=5433 user=tag10_repl dbname=postgres'
sudo cat /var/lib/postgresql/18/main/postgresql.auto.confExpected (abridged):
pg_rewind: servers diverged at WAL location 0/D7E28FA8 on timeline 1
pg_rewind: rewinding from last common checkpoint at 0/AE2AF0E8 on timeline 1
pg_rewind: need to copy 1137 MB (total source directory size is 1393 MB)
pg_rewind: creating backup label and updating control file
pg_rewind: Done!
listen_addresses = '*'
shared_preload_libraries = 'pg_stat_statements'
primary_conninfo = '... host=172.16.0.182 port=5432 ...'
primary_slot_name = 'tag10_pg2'
primary_conninfo = '... host=172.16.0.123 port=5433 ... dbname=postgres'
Warning:
pg_rewindalso copiespostgresql.auto.conffrom the source.pg1’sssl_lines are gone; instead it contains the slot and connection of the old standby. Check and fix it before starting!
The cleanest way: restore the file you backed up in Drill 1 and
append only the new connection. -R has already created
standby.signal:
sudo cp /var/lib/postgresql/backup-tag10/postgresql.auto.conf \
/var/lib/postgresql/18/main/postgresql.auto.conf
echo "primary_conninfo = 'host=172.16.0.123 port=5433 user=tag10_repl'" \
| sudo -u postgres tee -a /var/lib/postgresql/18/main/postgresql.auto.conf
sudo systemctl start postgresql@18-main
psql -Atc 'SELECT pg_is_in_recovery()'
psql -x -c 'SELECT status, sender_host, sender_port, received_tli
FROM pg_stat_wal_receiver'
psql -d tag10 -c 'SELECT id, txt FROM ping'Expected:
primary_conninfo = 'host=172.16.0.123 port=5433 user=tag10_repl'
t
-[ RECORD 1 ]+-------------
status | streaming
sender_host | 172.16.0.123
sender_port | 5433
received_tli | 2
id | txt
----+-----------------
1 | hallo standby
2 | wartet auf pg2
35 | pg2 ist Primary
(3 rows)
pg1 is now a standby on timeline 2. The gap from 2 to
35: sequences are written to the WAL in blocks of 32 values, and after a
promote the rest is lost – sequences are never gapless.
Save your progress? Sign up for free
Drill 8: Switch the roles back
Planned switchover back: stop the old primary cleanly – its shutdown checkpoint reaches the standby – then promote the standby. On pg2:
sudo pg_ctlcluster 18 standby stop
sudo -u postgres /usr/lib/postgresql/18/bin/pg_controldata \
/var/lib/postgresql/18/standby | grep -E 'cluster state|Latest checkpoint location'Expected:
Database cluster state: shut down
Latest checkpoint location: 0/D7E4FA10
On pg1: check that the checkpoint has arrived (replay
LSN past it), promote, restore your own
postgresql.auto.conf, recreate the slot –
pg_rewind does not transfer slots:
psql -Atc 'SELECT pg_last_wal_replay_lsn()'
psql -Atc 'SELECT pg_promote()'
psql -Atc 'SELECT pg_is_in_recovery(), pg_walfile_name(pg_current_wal_lsn())'
sudo cp /var/lib/postgresql/backup-tag10/postgresql.auto.conf \
/var/lib/postgresql/18/main/postgresql.auto.conf
psql -Atc 'SELECT pg_reload_conf()'
psql -Atc "SELECT pg_create_physical_replication_slot('tag10_pg2')"Expected:
0/D7E4FA88
t
f|0000000300000000000000D7
t
(tag10_pg2,)
Timeline 3, visible in the WAL file name. On pg2,
start the cluster as a standby again; its
postgresql.auto.conf still points to pg1 from
Drill 2:
sudo -u postgres touch /var/lib/postgresql/18/standby/standby.signal
sudo pg_ctlcluster 18 standby start
sudo -u postgres psql -p 5433 -x -c 'SELECT status, sender_host, slot_name,
received_tli FROM pg_stat_wal_receiver'Expected:
-[ RECORD 1 ]+-------------
status | streaming
sender_host | 172.16.0.182
slot_name | tag10_pg2
received_tli | 3
A cleanly stopped primary needs no pg_rewind: its WAL
ends exactly where the new timeline branches off.
Save your progress? Sign up for free
Exercise: Speed Drills
- On
pg1, show all standbys with their state and sync mode. Target time: 20 s - On
pg1, show each standby’s lag in bytes. Target time: 30 s - On
pg1, show how much WAL each slot retains. Target time: 40 s - On
pg2, show how old the last replayed transaction is. Target time: 30 s
Solution
psql -c 'SELECT application_name, state, sync_state FROM pg_stat_replication'
psql -Atc 'SELECT application_name, pg_wal_lsn_diff(pg_current_wal_lsn(),
replay_lsn) FROM pg_stat_replication'
psql -c "SELECT slot_name, active, pg_size_pretty(pg_wal_lsn_diff(
pg_current_wal_lsn(), restart_lsn)) AS behalten FROM pg_replication_slots"
sudo -u postgres psql -p 5433 -Atc 'SELECT now() - pg_last_xact_replay_timestamp()'Expected (abridged):
application_name | state | sync_state
------------------+-----------+------------
18/standby | streaming | async
18/standby|5529608
slot_name | active | behalten
-----------+--------+----------
tag10_pg2 | t | 472 kB
00:00:06.43235
An inactive slot holds on to WAL indefinitely.
max_slot_wal_keep_size sets an upper limit; beyond it the
slot becomes invalid (wal_status = lost) instead of filling
the disk.
Save your progress? Sign up for free
Checkpoint
psql -Atc 'SELECT pg_is_in_recovery()'Expected: f – pg1 is
primary again.
psql -c 'SELECT application_name, client_addr, state FROM pg_stat_replication'Expected: one row
18/standby | 172.16.0.123 | streaming.
psql -d tag10 -c "INSERT INTO ping (txt) VALUES ('pg1 wieder Primary')"Expected: INSERT 0 1
On pg2:
sudo -u postgres psql -p 5433 -d tag10 -Atc 'SELECT count(*) FROM ping'Expected: 4 – the row
nur auf pg1 is gone for good.
sudo -u postgres psql -p 5433 -Atc 'SELECT pg_is_in_recovery()'Expected: t
Challenge
synchronous_standby_names names a standby that doesn’t
exist (say, after a typo), and all commits hang. First make a single
session usable again, then the whole server – without starting a
standby.
Hint: synchronous_commit can be set per
session, synchronous_standby_names only via configuration
and reload.
Solution
On pg1, create the problem:
echo "synchronous_standby_names = 'pg2'" \
| sudo -u postgres tee /etc/postgresql/18/main/conf.d/tag10-sync.conf
psql -Atc 'SELECT pg_reload_conf()'
psql -d tag10 -c "INSERT INTO ping (txt) VALUES ('haengt')" &
sleep 3
psql -Atc "SELECT count(*) FROM pg_stat_activity WHERE wait_event = 'SyncRep'"Fix it:
PGOPTIONS='-c synchronous_commit=local' psql -d tag10 \
-c "INSERT INTO ping (txt) VALUES ('lokal')"
sudo rm /etc/postgresql/18/main/conf.d/tag10-sync.conf
psql -Atc 'SELECT pg_reload_conf()'Expected:
1
INSERT 0 1
t
INSERT 0 1
The first line counts the hanging commit; the INSERT
with local goes through immediately. After the reload the
hanging one also reports INSERT 0 1. Cancelling it (Ctrl+C)
would not have rolled anything back: locally the transaction is already
committed, PostgreSQL then only warns that the standby’s confirmation is
missing.
Save your progress? Sign up for free
Cleanup
Day 11 needs pg2 as a standalone server –
18/main there is untouched, the standby cluster goes away.
On pg2:
sudo pg_dropcluster --stop 18 standby
sudo -u postgres rm /var/lib/postgresql/.pgpassOn pg1. DROP OWNED removes the function
privileges, otherwise DROP ROLE refuses to work:
psql -Atc "SELECT pg_drop_replication_slot('tag10_pg2')"
dropdb tag10
psql -d postgres -c 'DROP OWNED BY tag10_repl;'
psql -c 'DROP ROLE tag10_repl;'
sudo sed -i '/^host .* tag10_repl 172.16.0.123\/32/d' \
/etc/postgresql/18/main/pg_hba.conf
psql -Atc 'SELECT pg_reload_conf()'
sudo -u postgres rm /var/lib/postgresql/.pgpass
sudo -u postgres rm -r /var/lib/postgresql/backup-tag10
psql -c 'SELECT count(*) FROM pg_replication_slots'Expected:
DROP OWNED
DROP ROLE
t
count
-------
0
(1 row)
pg1 now runs on timeline 3 – irrelevant for operations,
only the WAL file names start with 00000003.
Interview Note
- “Physical vs. logical replication?” – Physical copies WAL blocks: the whole cluster, same major version, standby read-only. Logical (Day 11) transfers row changes of selected tables, even between versions.
- “What does a replication slot do, and what is its risk?” – It
guarantees that needed WAL is kept; a dead slot fills the primary’s
disk. Counter it with
max_slot_wal_keep_sizeand monitoring onpg_replication_slots. - “What is
pg_rewindfor?” – To turn a diverged old primary back into a standby without a new base backup; it needs checksums orwal_log_hintsand the WAL from the last common checkpoint.
Enterprise Note
- A cluster manager (Patroni with etcd, pg_auto_failover, repmgr) automates failover, including fencing the old primary – never two primaries at the same time.
- Clients find the primary via VIP, DNS, HAProxy or libpq with
multiple hosts and
target_session_attrs=read-write. - Synchronous with quorum (
ANY 1 (s1, s2)) across data centers; lag alerts on bytes and time. - A WAL archive (Day 9) in addition to replication: standbys can catch
up from it,
pg_rewind -cfetches missing WAL viarestore_command. - Practice switchovers regularly – a failover that was never rehearsed rarely works when it counts.