Skip to content
Hands-on IT IT Drills

Day 10: Streaming Replication

Goal: pg2 runs as a hot standby of pg1, you measure the lag, switch to synchronous mode and survive a failover including pg_rewind and 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) on pg2. 18/main there 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 GRANT in the database ubuntu does not help pg_rewind – it then fails with permission 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.conf

Expected:

# 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.conf

Expected (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.log

Expected (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
wait

Expected (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 stop

On 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 start
sudo 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 stop

On pg1:

psql -d tag10 -c "INSERT INTO ping (txt) VALUES ('nur auf pg1')"
sudo systemctl stop postgresql@18-main

On 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.log

Expected (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.conf

Expected (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_rewind also copies postgresql.auto.conf from the source. pg1’s ssl_ 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

  1. On pg1, show all standbys with their state and sync mode. Target time: 20 s
  2. On pg1, show each standby’s lag in bytes. Target time: 30 s
  3. On pg1, show how much WAL each slot retains. Target time: 40 s
  4. 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/.pgpass

On 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_size and monitoring on pg_replication_slots.
  • “What is pg_rewind for?” – To turn a diverged old primary back into a standby without a new base backup; it needs checksums or wal_log_hints and 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 -c fetches missing WAL via restore_command.
  • Practice switchovers regularly – a failover that was never rehearsed rarely works when it counts.