Zum Inhalt springen
Trainingslager IT-Trainingslager

Tag 10: Streaming-Replikation

Ziel: pg2 läuft als Hot Standby von pg1, du misst den Rückstand, schaltest synchron und überstehst einen Failover samt pg_rewind und Rücktausch. · Dauer: ca. 120 min · Voraussetzung: Tag 9

Briefing

  • Der Standby empfängt das WAL des Primary über eine Replikationsverbindung und spielt es fortlaufend ein – eine physische Kopie, Block für Block.
  • Ein Replikations-Slot sorgt dafür, dass der Primary kein WAL löscht, das der Standby noch braucht.
  • Asynchron ist Standard: schnell, aber beim Ausfall können die letzten Transaktionen fehlen. Synchron wartet jeder Commit auf den Standby.
  • Der Standby läuft als zweiter Cluster 18/standby (Port 5433) auf pg2. 18/main dort bleibt unberührt – Tag 11 braucht ihn als eigenständigen 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, nur lesend

Drill 1: Replikationsrolle und Zugang

Auf pg1: Rolle mit REPLICATION. Für pg_rewind (Drill 7) braucht sie zusätzlich Leserechte auf vier Funktionen – und zwar in der Datenbank, mit der sich pg_rewind verbindet (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'

Erwartet:

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

Achtung: Funktionsrechte gelten pro Datenbank. Ein GRANT in der Datenbank ubuntu hilft pg_rewind nicht – es scheitert dann mit permission denied for function pg_read_binary_file.

Die Standardwerte reichen: wal_level = replica, zehn WAL-Sender. pg_rewind braucht wal_log_hints = on oder Datenprüfsummen – die sind seit 18 Standard. Die .pgpass auf pg1 braucht erst Drill 7, wenn pg1 selbst Standby wird.

Noch auf pg1 eine Sicherung: pg_rewind überschreibt in Drill 7 die postgresql.auto.conf von pg1 mit der des neuen Primary. Mit dieser Kopie stellst du sie in Drill 7 und 8 wieder her:

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

Erwartet:

# 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'

Fortschritt speichern? Kostenlos registrieren

Drill 2: pg2 wird Standby

Auf pg2: Passwortdatei, neuer Cluster mit pg_hba-Zeilen für die Gegenrichtung (nach dem Failover repliziert pg1 von hier), dann das Datenverzeichnis durch ein Basis-Backup ersetzen. -C -S tag10_pg2 legt den Slot gleich mit an, -R schreibt standby.signal und 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

Erwartet (gekürzt):

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'

Wie in Tag 9 bringt postgresql.auto.conf die Zertifikatspfade von pg1 mit (falls du Tag 3 gemacht hast). Raus damit, dann starten:

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

Erwartet (gekürzt):

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 = '*' kam ebenfalls von pg1 mit – hier gewollt, denn nach dem Failover verbindet sich pg1 zu Port 5433.

Fortschritt speichern? Kostenlos registrieren

Drill 3: Replikation beobachten

Auf pg1 die Sicht des Primary, dazu eine Testtabelle:

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')"

Erwartet:

-[ 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 ist der cluster_name des Standby (18/standby) – Debian setzt ihn für jeden Cluster. Auf pg2 die Sicht des Standby, lesen und schreiben:

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')"

Erwartet:

-[ 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 heißt: lesende Abfragen ja, Schreiben nie. now() - pg_last_xact_replay_timestamp() misst das Alter der zuletzt eingespielten Transaktion – ohne Schreiblast wächst der Wert, obwohl nichts fehlt.

Fortschritt speichern? Kostenlos registrieren

Drill 4: Rückstand unter Last

Auf pg1: pgbench -i schreibt rund 160 MB WAL am Stück. Währenddessen misst \watch (Tag 1) jede Sekunde den Rückstand in Bytes, viermal:

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

Erwartet (gekürzt):

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) sind die verlässlichere Metrik als Zeit: Sie zeigen, wie viel bei einem Ausfall gerade fehlen würde. Der Standby holt in Sekunden auf.

Fortschritt speichern? Kostenlos registrieren

Drill 5: Synchron replizieren

Erst eine Messung asynchron, dann den Standby per Name synchron schalten (Reload genügt) und erneut messen. Auf 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'

Erwartet:

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)

Der Name steht in doppelten Anführungszeichen, weil er einen / enthält. synchronous_commit = on wartet, bis der Standby das WAL auf Platte hat, remote_apply, bis er es eingespielt hat (Lesen auf dem Standby sieht dann sofort jeden Commit). local wartet nur auf die eigene Platte – das darf jede Sitzung für unkritische Daten selbst wählen. Synchron kostet hier rund ein Drittel Durchsatz.

Jetzt die Kehrseite: Standby weg. Auf pg2:

sudo pg_ctlcluster 18 standby stop

Auf pg1, der Commit läuft im Hintergrund:

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'"

Erwartet (gekürzt):

  pid  | wait_event_type | wait_event |                  query
-------+-----------------+------------+------------------------------------------
 24034 | IPC             | SyncRep    | INSERT INTO ping (txt) VALUES ('wartet a

Jeder schreibende Commit auf pg1 hängt jetzt – auch die anderer Anwendungen. Auf pg2 den Standby wieder starten, dann auf pg1 zurück auf asynchron:

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'

Erwartet:

INSERT 0 1
t
 application_name | sync_state
------------------+------------
 18/standby       | async
(1 row)

Das hängende INSERT kam durch, sobald der Standby wieder da war. Deshalb gilt: synchron nur mit mindestens zwei Standbys (ANY 1 (a, b)), sonst ist der Standby ein zweiter Single Point of Failure.

Fortschritt speichern? Kostenlos registrieren

Drill 6: Failover mit pg_promote

Ein echter Ausfall erwischt oft WAL, das den Standby nicht mehr erreicht hat. Das simulieren wir: Standby anhalten, auf pg1 eine Zeile schreiben, pg1 stoppen. Auf pg2:

sudo pg_ctlcluster 18 standby stop

Auf pg1:

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

Auf pg2 den Standby starten – er findet keinen Primary – und befördern:

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

Erwartet (gekürzt):

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() beendet die Recovery, standby.signal verschwindet, der Server schreibt ab jetzt auf Timeline 2. Die Zeile nur auf pg1 fehlt – das ist der Datenverlust der asynchronen Replikation. (Ohne SQL geht dasselbe mit pg_ctlcluster 18 standby promote.) Anwendungen müssen nun auf pg2:5433 zeigen; das übernimmt in Produktion ein Cluster-Manager oder ein Proxy.

Fortschritt speichern? Kostenlos registrieren

Drill 7: Alter Primary zurück per pg_rewind

pg1 hat WAL, das es auf pg2 nie gab. Ein neues Basis-Backup würde alles kopieren, pg_rewind kopiert nur die seit der Abzweigung geänderten Blöcke und spult pg1 auf den Stand von pg2 zurück. Auf pg1, Cluster gestoppt:

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'

Erwartet:

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 liest das WAL von pg1 ab dem letzten gemeinsamen Checkpoint. Den hat pg1 beim Herunterfahren schon recycelt, und ein Archiv gibt es seit Tag 9 nicht mehr. Der neue Primary hat die Segmente der Timeline 1 aber noch:

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

Erwartet (gekürzt):

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'

Achtung: pg_rewind kopiert auch postgresql.auto.conf vom Source. Die ssl_-Zeilen von pg1 sind weg, dafür stehen dort Slot und Verbindung des alten Standby. Vor dem Start prüfen und korrigieren!

Am saubersten: die in Drill 1 gesicherte Datei zurück und nur die neue Verbindung anhängen. standby.signal hat -R schon angelegt:

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'

Erwartet:

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 ist jetzt Standby auf Timeline 2. Die Lücke von 2 auf 35: Sequenzen werden in Blöcken von 32 Werten ins WAL geschrieben, nach einem Promote geht der Rest verloren – Sequenzen sind nie lückenlos.

Fortschritt speichern? Kostenlos registrieren

Drill 8: Rollen zurücktauschen

Geplanter Switchover zurück: alten Primary sauber stoppen – sein Shutdown-Checkpoint erreicht den Standby –, dann den Standby befördern. Auf 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'

Erwartet:

Database cluster state:               shut down
Latest checkpoint location:           0/D7E4FA10

Auf pg1: prüfen, dass der Checkpoint angekommen ist (Replay-LSN dahinter), befördern, die eigene postgresql.auto.conf wiederherstellen, Slot neu anlegen – pg_rewind überträgt keine 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')"

Erwartet:

0/D7E4FA88
t
f|0000000300000000000000D7
t
(tag10_pg2,)

Timeline 3, sichtbar im WAL-Dateinamen. Auf pg2 den Cluster wieder als Standby starten; seine postgresql.auto.conf zeigt noch aus Drill 2 auf pg1:

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'

Erwartet:

-[ RECORD 1 ]+-------------
status       | streaming
sender_host  | 172.16.0.182
slot_name    | tag10_pg2
received_tli | 3

Ein sauber gestoppter Primary braucht kein pg_rewind: Sein WAL endet genau dort, wo die neue Timeline abzweigt.

Fortschritt speichern? Kostenlos registrieren

Übung: Speed-Drills

  1. Zeige auf pg1 alle Standbys mit Zustand und Sync-Modus. Zeitziel: 20 s
  2. Zeige auf pg1 den Rückstand jedes Standby in Bytes. Zeitziel: 30 s
  3. Zeige auf pg1, wie viel WAL jeder Slot zurückhält. Zeitziel: 40 s
  4. Zeige auf pg2, wie alt die zuletzt eingespielte Transaktion ist. Zeitziel: 30 s

Lösung

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()'

Erwartet (gekürzt):

 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

Ein inaktiver Slot hält WAL unbegrenzt fest. max_slot_wal_keep_size setzt eine Obergrenze, danach wird der Slot ungültig (wal_status = lost) statt die Platte zu füllen.

Fortschritt speichern? Kostenlos registrieren

Kontrollpunkt

psql -Atc 'SELECT pg_is_in_recovery()'

Erwartet: f – pg1 ist wieder Primary.

psql -c 'SELECT application_name, client_addr, state FROM pg_stat_replication'

Erwartet: eine Zeile 18/standby | 172.16.0.123 | streaming.

psql -d tag10 -c "INSERT INTO ping (txt) VALUES ('pg1 wieder Primary')"

Erwartet: INSERT 0 1

Auf pg2:

sudo -u postgres psql -p 5433 -d tag10 -Atc 'SELECT count(*) FROM ping'

Erwartet: 4 – die Zeile nur auf pg1 fehlt für immer.

sudo -u postgres psql -p 5433 -Atc 'SELECT pg_is_in_recovery()'

Erwartet: t

Challenge

synchronous_standby_names nennt einen Standby, den es nicht gibt (etwa nach einem Tippfehler), und alle Commits hängen. Mach erst eine einzelne Sitzung wieder arbeitsfähig, dann den ganzen Server – ohne einen Standby zu starten.

Hinweis: synchronous_commit lässt sich pro Sitzung setzen, synchronous_standby_names nur per Konfiguration und Reload.

Lösung

Auf pg1, das Problem herstellen:

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'"

Lösen:

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()'

Erwartet:

1
INSERT 0 1
t
INSERT 0 1

Die erste Zeile zählt den hängenden Commit, das INSERT mit local kommt sofort durch. Nach dem Reload meldet auch das hängende INSERT 0 1. Ein Abbruch (Strg+C) hätte nichts zurückgerollt: Lokal ist die Transaktion schon committet, PostgreSQL warnt dann nur, dass die Bestätigung des Standby fehlt.

Fortschritt speichern? Kostenlos registrieren

Aufräumen

Tag 11 braucht pg2 als eigenständigen Server – 18/main dort ist unberührt, der Standby-Cluster verschwindet. Auf pg2:

sudo pg_dropcluster --stop 18 standby
sudo -u postgres rm /var/lib/postgresql/.pgpass

Auf pg1. DROP OWNED entfernt die Funktionsrechte, sonst verweigert DROP ROLE den Dienst:

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'

Erwartet:


DROP OWNED
DROP ROLE
t
 count
-------
     0
(1 row)

pg1 läuft jetzt auf Timeline 3 – für den Betrieb ohne Bedeutung, nur die WAL-Dateinamen beginnen mit 00000003.

Interview-Notiz

  • „Physische vs. logische Replikation?“ – Physisch kopiert WAL-Blöcke: ganzer Cluster, gleiche Major-Version, Standby nur lesend. Logisch (Tag 11) überträgt Zeilen- änderungen ausgewählter Tabellen, auch zwischen Versionen.
  • „Was macht ein Replikations-Slot, und was ist sein Risiko?“ – Er garantiert, dass benötigtes WAL bleibt; ein toter Slot füllt die Platte des Primary. Dagegen max_slot_wal_keep_size und Monitoring auf pg_replication_slots.
  • „Wozu pg_rewind?“ – Um einen abgezweigten alten Primary ohne neues Basis-Backup wieder zum Standby zu machen; braucht Prüfsummen oder wal_log_hints und das WAL ab dem letzten gemeinsamen Checkpoint.

Enterprise-Notiz

  • Failover automatisiert ein Cluster-Manager (Patroni mit etcd, pg_auto_failover, repmgr) inklusive Fencing des alten Primary – nie zwei Primaries gleichzeitig.
  • Clients finden den Primary über VIP, DNS, HAProxy oder libpq mit mehreren Hosts und target_session_attrs=read-write.
  • Synchron mit Quorum (ANY 1 (s1, s2)) über Rechenzentren; Lag-Alarme auf Bytes und Zeit.
  • WAL-Archiv (Tag 9) zusätzlich zur Replikation: Standbys können daraus aufholen, pg_rewind -c holt fehlendes WAL per restore_command.
  • Switchover regelmäßig üben – ein Failover, der nie geprobt wurde, klappt im Ernstfall selten.