Tag 11: Logische Replikation
Ziel:
pg2abonniert ausgewählte Tabellen, Zeilen und Spalten vonpg1, du erzeugst einen Konflikt, löst ihn und überwachst Slot und Subscription. · Dauer: ca. 90 min · Voraussetzung: Tag 10
Briefing
- Logische Replikation überträgt Zeilenänderungen (INSERT, UPDATE, DELETE, TRUNCATE) einzelner Tabellen statt WAL-Blöcke. Der Empfänger ist ein normaler, beschreibbarer Server – auch mit anderer Major-Version.
- Der Sender bietet eine Publication an, der Empfänger legt eine Subscription an. Dazwischen hält ein logischer Slot auf dem Sender den Stand.
- Repliziert werden nur Daten: Schema, Sequenzen und DDL bleiben Handarbeit.
- Sender ist
pg1(Datenbanktag11), Empfänger18/mainaufpg2.
pg1 18/main wal_level=logical pg2 18/main
PUBLICATION tag11_de (kunden, gefiltert)
PUBLICATION tag11_best (bestellungen) ----> SUBSCRIPTION tag11_sub
Slot tag11_sub (pgoutput) Apply-Worker, Tabellen beschreibbar
Drill 1: wal_level = logical
Für logische Dekodierung muss das WAL mehr Informationen tragen.
wal_level braucht einen Neustart. Auf pg1:
psql -Atc 'SHOW wal_level'
echo 'wal_level = logical' \
| sudo -u postgres tee /etc/postgresql/18/main/conf.d/tag11-logical.conf
sudo systemctl restart postgresql@18-main
psql -Atc 'SHOW wal_level'Erwartet:
replica
wal_level = logical
logical
Testdaten und eine Rolle für den Abonnenten. Sie braucht
REPLICATION und SELECT für die erste Kopie der
Tabellen. Logische Replikation verbindet sich mit einer echten
Datenbank, deshalb nennt die pg_hba-Zeile
tag11 statt replication:
createdb tag11
psql -d tag11 <<'SQL'
CREATE TABLE kunden (id int PRIMARY KEY, name text, land text, email text);
CREATE TABLE bestellungen (id int PRIMARY KEY, kunde_id int, betrag numeric);
INSERT INTO kunden VALUES (1, 'Anna', 'DE', '[email protected]'),
(2, 'Ben', 'AT', '[email protected]'), (3, 'Cem', 'DE', '[email protected]');
INSERT INTO bestellungen SELECT i, 1 + i % 3, i * 1.5 FROM generate_series(1, 500) i;
CREATE ROLE tag11_repl LOGIN REPLICATION PASSWORD 'lab-logrepl-123';
GRANT SELECT ON kunden, bestellungen TO tag11_repl;
SQL
echo 'host tag11 tag11_repl 172.16.0.123/32 scram-sha-256' \
| sudo tee -a /etc/postgresql/18/main/pg_hba.conf
psql -Atc 'SELECT pg_reload_conf()'Erwartet:
CREATE TABLE
CREATE TABLE
INSERT 0 3
INSERT 0 500
CREATE ROLE
GRANT
host tag11 tag11_repl 172.16.0.123/32 scram-sha-256
t
Fortschritt speichern? Kostenlos registrieren
Drill 2: Publications mit Zeilenfilter und Spaltenliste
tag11_de veröffentlicht nur deutsche Kunden und ohne
E-Mail-Adresse, tag11_best alle Bestellungen:
psql -d tag11 <<'SQL'
CREATE PUBLICATION tag11_de FOR TABLE kunden (id, name, land) WHERE (land = 'DE');
CREATE PUBLICATION tag11_best FOR TABLE bestellungen;
\dRp+ tag11_de
SELECT pubname, tablename, attnames, rowfilter FROM pg_publication_tables;
SQLErwartet (gekürzt):
CREATE PUBLICATION
CREATE PUBLICATION
Publication tag11_de
Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | ...
--------+------------+---------+---------+---------+-----------+-------------------+----
ubuntu | f | t | t | t | t | none | ...
Tables:
"public.kunden" (id, name, land) WHERE (land = 'DE'::text)
pubname | tablename | attnames | rowfilter
------------+--------------+----------------------+---------------------
tag11_de | kunden | {id,name,land} | (land = 'DE'::text)
tag11_best | bestellungen | {id,kunde_id,betrag} |
(2 rows)
Spaltenlisten halten sensible Daten vom Empfänger fern, Zeilenfilter
verteilen etwa Daten pro Mandant oder Region.
FOR ALL TABLES oder
FOR TABLES IN SCHEMA public veröffentlichen alles auf
einmal, auch künftige Tabellen.
Fortschritt speichern? Kostenlos registrieren
Drill 3: Subscription und initialer Sync
Auf pg2. Das Schema kopiert pg_dump -s
von pg1; Owner und Rechte bleiben weg, weil es die Rollen
auf pg2 nicht gibt. Das Passwort kommt in die
.pgpass von postgres statt in den
Verbindungsstring – der stünde sonst lesbar in
pg_subscription:
echo '172.16.0.182:5432:tag11:tag11_repl:lab-logrepl-123' \
| sudo -u postgres tee -a /var/lib/postgresql/.pgpass
sudo -u postgres chmod 600 /var/lib/postgresql/.pgpass
sudo -u postgres createdb tag11
sudo -u postgres pg_dump -h 172.16.0.182 -U tag11_repl -d tag11 -s \
-t kunden -t bestellungen --no-owner --no-privileges \
| sudo -u postgres psql -q -d tag11
sudo -u postgres psql -d tag11 <<'SQL'
CREATE SUBSCRIPTION tag11_sub
CONNECTION 'host=172.16.0.182 dbname=tag11 user=tag11_repl'
PUBLICATION tag11_de, tag11_best;
SQLErwartet:
set_config
------------
(1 row)
NOTICE: created replication slot "tag11_sub" on publisher
CREATE SUBSCRIPTION
sudo -u postgres psql -d tag11 -c \
'SELECT srrelid::regclass, srsubstate FROM pg_subscription_rel'
sudo -u postgres psql -d tag11 -c 'SELECT * FROM kunden ORDER BY id'
sudo -u postgres psql -d tag11 -Atc 'SELECT count(*) FROM bestellungen'
sudo grep 'synchronization worker' /var/log/postgresql/postgresql-18-main.logErwartet (gekürzt):
srrelid | srsubstate
--------------+------------
kunden | r
bestellungen | r
(2 rows)
id | name | land | email
----+------+------+-------
1 | Anna | DE |
3 | Cem | DE |
(2 rows)
500
LOG: logical replication table synchronization worker for subscription "tag11_sub",
table "kunden" has finished
Pro Tabelle kopiert ein Sync-Worker erst den Bestand
(COPY), dann übernimmt der Apply-Worker die laufenden
Änderungen; r heißt „ready“. Ben (AT) fehlt,
email bleibt leer – Filter und Spaltenliste wirken schon
beim Kopieren.
Fortschritt speichern? Kostenlos registrieren
Drill 4: Änderungen live – und die Replica Identity
Auf pg1 Änderungen, darunter zwei, die eine Zeile über
die Filtergrenze schieben:
psql -d tag11 <<'SQL'
INSERT INTO kunden VALUES (4, 'Dora', 'DE', '[email protected]'),
(5, 'Emil', 'CH', 'e@x');
UPDATE kunden SET name = 'Anna M.' WHERE id = 1;
DELETE FROM bestellungen WHERE id > 490;
SQLErwartet:
INSERT 0 2
ERROR: cannot update table "kunden"
DETAIL: Column used in the publication WHERE expression is not part of the replica
identity.
DELETE 10
Für UPDATE und DELETE schickt der Sender die Replica
Identity der alten Zeile mit, standardmäßig den
Primärschlüssel. Ein Zeilenfilter darf nur Spalten daraus prüfen, sonst
weiß PostgreSQL nicht, ob die alte Zeile beim Empfänger war – und
verweigert das UPDATE auf pg1.
REPLICA IDENTITY FULL (alle Spalten) hilft hier nicht:
psql -d tag11 <<'SQL'
ALTER TABLE kunden REPLICA IDENTITY FULL;
UPDATE kunden SET name = 'Anna M.' WHERE id = 1;
ALTER TABLE kunden REPLICA IDENTITY DEFAULT;
SQLErwartet:
ALTER TABLE
ERROR: cannot update table "kunden"
DETAIL: Column list used by the publication does not cover the replica identity.
ALTER TABLE
Die Spaltenliste schließt email aus, FULL verlangt aber
alle Spalten. Die Lösung ist ein eindeutiger Index über id
und land als Replica Identity (Spalten dafür müssen
NOT NULL sein):
psql -d tag11 <<'SQL'
ALTER TABLE kunden ALTER COLUMN land SET NOT NULL;
CREATE UNIQUE INDEX kunden_id_land ON kunden (id, land);
ALTER TABLE kunden REPLICA IDENTITY USING INDEX kunden_id_land;
UPDATE kunden SET name = 'Anna M.' WHERE id = 1;
UPDATE kunden SET land = 'AT' WHERE id = 3;
UPDATE kunden SET land = 'DE' WHERE id = 2;
SQLAuf pg2:
sudo -u postgres psql -d tag11 -c 'SELECT * FROM kunden ORDER BY id'
sudo -u postgres psql -d tag11 -Atc 'SELECT count(*) FROM bestellungen'Erwartet:
id | name | land | email
----+---------+------+-------
1 | Anna M. | DE |
2 | Ben | DE |
4 | Dora | DE |
(3 rows)
490
Cem wechselt nach AT und verlässt den Filter – beim Empfänger wird daraus ein DELETE. Ben kommt nach DE und erscheint als INSERT. Emil (CH) kam nie an.
Fortschritt speichern? Kostenlos registrieren
Drill 5: Konflikt erzeugen und lösen
Der Empfänger ist beschreibbar – und genau das erzeugt Konflikte. Mit
disable_on_error hält die Subscription beim ersten Fehler
an, statt endlos neu zu starten. Auf pg2 eine
Bestellung anlegen, die es gleich auch auf pg1 gibt:
sudo -u postgres psql -d tag11 \
-c 'ALTER SUBSCRIPTION tag11_sub SET (disable_on_error = true)' \
-c 'INSERT INTO bestellungen VALUES (1000, 1, 99.99)'Auf pg1, zwei getrennte Transaktionen:
psql -d tag11 -c 'INSERT INTO bestellungen VALUES (1000, 2, 10.00)' \
-c 'INSERT INTO bestellungen VALUES (1001, 2, 20.00)'Auf pg2:
sudo grep -A3 'conflict detected' /var/log/postgresql/postgresql-18-main.log
sudo -u postgres psql -d tag11 -c 'SELECT subname, subenabled FROM pg_subscription'
sudo -u postgres psql -d tag11 -x -c 'SELECT apply_error_count, confl_insert_exists
FROM pg_stat_subscription_stats'Erwartet (gekürzt):
ERROR: conflict detected on relation "public.bestellungen": conflict=insert_exists
DETAIL: Key already exists in unique index "bestellungen_pkey", modified in
transaction 888.
Key (id)=(1000); existing local row (1000, 1, 99.99); remote row (1000, 2, 10.00).
CONTEXT: processing remote data for replication origin "pg_16763" during message
type "INSERT" for replication target relation "public.bestellungen" in transaction
83049, finished at 1/874CBDF0
LOG: subscription "tag11_sub" has been disabled because of an error
subname | subenabled
-----------+------------
tag11_sub | f
(1 row)
-[ RECORD 1 ]-------+--
apply_error_count | 1
confl_insert_exists | 1
Seit PostgreSQL 18 benennt das Log den Konflikttyp und zeigt lokale
und entfernte Zeile; pg_stat_subscription_stats zählt jeden
Typ. Zwei Auswege: die lokale Zeile löschen (dann kommt die von
pg1), oder die entfernte Transaktion überspringen. Wir
behalten die lokale und überspringen per LSN aus
finished at:
sudo -u postgres psql -d tag11 \
-c "ALTER SUBSCRIPTION tag11_sub SKIP (lsn = '1/874CBDF0')" \
-c 'ALTER SUBSCRIPTION tag11_sub ENABLE'
sudo -u postgres psql -d tag11 -c 'SELECT * FROM bestellungen WHERE id >= 1000'
sudo grep 'skipping transaction' /var/log/postgresql/postgresql-18-main.logErwartet (gekürzt):
ALTER SUBSCRIPTION
ALTER SUBSCRIPTION
id | kunde_id | betrag
------+----------+--------
1000 | 1 | 99.99
1001 | 2 | 20.00
(2 rows)
LOG: logical replication starts skipping transaction at LSN 1/874CBDF0
LOG: logical replication completed skipping transaction at LSN 1/874CBDF0
Übersprungen wird die ganze Transaktion – hier nur
die eine Zeile, weil psql -c jeden Befehl einzeln
committet. 1001 kam durch. Ab jetzt unterscheiden sich
pg1 und pg2 bei Bestellung 1000: SKIP
repariert den Datenstrom, nicht die Daten.
Fortschritt speichern? Kostenlos registrieren
Drill 6: Monitoring – Subscription und Slot
Auf pg2 die Sicht des Empfängers:
sudo -u postgres psql -d tag11 -x -c 'SELECT subname, worker_type,
pid IS NOT NULL AS laeuft, received_lsn, latest_end_time FROM pg_stat_subscription'Erwartet:
-[ RECORD 1 ]---+------------------------------
subname | tag11_sub
worker_type | apply
laeuft | t
received_lsn | 1/877EA9D8
latest_end_time | 2026-11-19 05:21:26.586054+00
Auf pg1 der Slot. Danach die Subscription auf
pg2 anhalten und auf pg1 50 000 Zeilen
schreiben:
psql -x -c "SELECT slot_name, plugin, slot_type, database, active,
confirmed_flush_lsn, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(),
confirmed_flush_lsn)) AS rueckstand FROM pg_replication_slots"Erwartet:
-[ RECORD 1 ]-------+-----------
slot_name | tag11_sub
plugin | pgoutput
slot_type | logical
database | tag11
active | t
confirmed_flush_lsn | 1/877EA9D8
rueckstand | 0 bytes
sudo -u postgres psql -d tag11 -c 'ALTER SUBSCRIPTION tag11_sub DISABLE'psql -d tag11 -c 'INSERT INTO bestellungen SELECT i, 1, 1
FROM generate_series(2000, 52000) i'
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"Erwartet:
INSERT 0 50001
slot_name | active | behalten
-----------+--------+----------
tag11_sub | f | 7770 kB
(1 row)
Der Slot hält jedes WAL fest, solange der Abonnent fehlt. Auf
pg2 wieder einschalten, dann ist der Rückstand auf
pg1 in Sekunden weg:
sudo -u postgres psql -d tag11 -c 'ALTER SUBSCRIPTION tag11_sub ENABLE'
sudo -u postgres psql -d tag11 -Atc 'SELECT count(*) FROM bestellungen'Erwartet:
ALTER SUBSCRIPTION
50493
Achtung: Eine vergessene, abgeschaltete Subscription – oder ein Empfänger, den es nicht mehr gibt – lässt den Slot auf dem Sender WAL sammeln, bis die Platte voll ist. Wird ein Empfänger abgebaut, gehört der Slot mit weg.
Fortschritt speichern? Kostenlos registrieren
Drill 7: Logische Replikation für Upgrades und Migrationen
Weil Sender und Empfänger nur Zeilen austauschen, dürfen sie verschiedene Major-Versionen, Plattformen oder Hosts haben. Das Muster für ein Upgrade fast ohne Ausfallzeit:
- Neuen Server mit neuer Version aufbauen, Schema per
pg_dump -sübernehmen. FOR ALL TABLESpublizieren, abonnieren, initialen Sync abwarten.- Rückstand beobachten (Drill 6), Schreibzugriffe auf den alten Server stoppen.
- Sequenzen nachziehen – sie werden nicht repliziert – etwa mit
setval(). - Anwendung umschalten, Subscription löschen.
Schritt 4 auf pg1: Aus pg_sequences
entstehen fertige setval()-Befehle für den neuen Server. In
tag11 gibt es noch keine Sequenz, also kurz eine
anlegen:
psql -q -d tag11 -c 'CREATE TABLE seqtest (id serial PRIMARY KEY)' \
-c 'INSERT INTO seqtest SELECT FROM generate_series(1, 42)'
psql -d tag11 -Atc "SELECT format('SELECT setval(%L, %s);', schemaname || '.' ||
sequencename, last_value) FROM pg_sequences WHERE last_value IS NOT NULL"
psql -q -d tag11 -c 'DROP TABLE seqtest'Erwartet:
SELECT setval('public.seqtest_id_seq', 42);
Weitere Werkzeuge: pg_createsubscriber (seit 17) macht
aus einem physischen Standby (Tag 10) direkt einen logischen Abonnenten
und spart den initialen Sync großer Datenbanken; pg_upgrade
übernimmt seit 17 logische Slots auf dem Sender, damit Subscriptions ein
Upgrade überleben. Tag 13 zeigt das klassische Upgrade per
pg_upgrade.
Fortschritt speichern? Kostenlos registrieren
Übung: Speed-Drills
- Zeige auf
pg1, welche Tabellen mit welchem Filter veröffentlicht werden. Zeitziel: 20 s - Zeige auf
pg2den Sync-Zustand jeder abonnierten Tabelle. Zeitziel: 20 s - Zeige auf
pg2die Fehler- und Konfliktzähler der Subscription. Zeitziel: 20 s - Zeige auf
pg1den Rückstand des logischen Slots. Zeitziel: 40 s
Lösung
psql -d tag11 -c 'SELECT pubname, tablename, rowfilter FROM pg_publication_tables'
sudo -u postgres psql -d tag11 -c \
'SELECT srrelid::regclass, srsubstate FROM pg_subscription_rel'
sudo -u postgres psql -d tag11 -c \
'SELECT apply_error_count, confl_insert_exists FROM pg_stat_subscription_stats'
psql -Atc "SELECT slot_name, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(),
confirmed_flush_lsn)) FROM pg_replication_slots"Erwartet (gekürzt):
pubname | tablename | rowfilter
------------+--------------+---------------------
tag11_de | kunden | (land = 'DE'::text)
tag11_best | bestellungen |
kunden | r
bestellungen | r
apply_error_count | confl_insert_exists
-------------------+---------------------
1 | 1
tag11_sub|0 bytes
Fortschritt speichern? Kostenlos registrieren
Kontrollpunkt
Auf pg2:
sudo -u postgres psql -d tag11 -Atc 'SELECT subname, subenabled FROM pg_subscription'Erwartet: tag11_sub|t
sudo -u postgres psql -d tag11 -Atc 'SELECT count(*) FROM kunden'Erwartet: 3 – nur Kunden aus DE.
sudo -u postgres psql -d tag11 -Atc 'SELECT betrag FROM bestellungen WHERE id = 1000'Erwartet: 99.99 – die lokale Zeile hat
den Konflikt überlebt.
Auf pg1:
psql -c 'SELECT slot_name, slot_type, active FROM pg_replication_slots'Erwartet: tag11_sub | logical | t
Challenge
Repliziere zusätzlich eine neue Tabelle
notiz (t timestamptz DEFAULT now(), txt text) – ohne
Primärschlüssel – und weise nach, dass ein UPDATE auf pg1
bei pg2 ankommt. Achte auf die Reihenfolge.
Hinweis: Ohne Replica Identity verweigert
pg1 UPDATEs auf einer veröffentlichten Tabelle, und
pg2 muss die Tabelle kennen, bevor ihre Änderungen
eintreffen.
Lösung
Auf pg1 die Tabelle anlegen:
psql -d tag11 -c 'CREATE TABLE notiz (t timestamptz DEFAULT now(), txt text)' \
-c "INSERT INTO notiz (txt) VALUES ('eins')" -c 'GRANT SELECT ON notiz TO tag11_repl'Auf pg2 zuerst die Tabelle:
sudo -u postgres psql -d tag11 \
-c 'CREATE TABLE notiz (t timestamptz DEFAULT now(), txt text)'Auf pg1 Replica Identity setzen und veröffentlichen:
psql -d tag11 -c 'ALTER TABLE notiz REPLICA IDENTITY FULL' \
-c 'ALTER PUBLICATION tag11_best ADD TABLE notiz'Auf pg2 die Subscription auffrischen, dann auf
pg1 ändern und auf pg2 prüfen:
sudo -u postgres psql -d tag11 -c 'ALTER SUBSCRIPTION tag11_sub REFRESH PUBLICATION'psql -d tag11 -c "UPDATE notiz SET txt = 'zwei'"sudo -u postgres psql -d tag11 -Atc 'SELECT txt FROM notiz'Erwartet:
ALTER SUBSCRIPTION
UPDATE 1
zwei
Falsche Reihenfolge kostet: Ohne REPLICA IDENTITY
scheitert das UPDATE auf pg1 mit
cannot update table "notiz" because it does not have a replica identity and publishes updates.
Fehlt die Tabelle auf pg2, stoppt die Subscription mit
logical replication target relation "public.notiz" does not exist,
und REFRESH geht erst nach ENABLE.
FULL vergleicht beim Empfänger ganze Zeilen – bei großen
Tabellen langsam, ein Primärschlüssel ist immer besser.
Fortschritt speichern? Kostenlos registrieren
Aufräumen
Kein späterer Tag braucht logische Replikation. Auf
pg2 zuerst die Subscription – sie löscht den Slot auf
pg1 mit:
sudo -u postgres psql -d tag11 -c 'DROP SUBSCRIPTION tag11_sub'
sudo -u postgres dropdb tag11
sudo -u postgres rm /var/lib/postgresql/.pgpassErwartet:
NOTICE: dropped replication slot "tag11_sub" on publisher
DROP SUBSCRIPTION
Auf pg1, wal_level zurück auf
replica (Neustart):
psql -c 'SELECT slot_name FROM pg_replication_slots'
dropdb tag11
psql -c 'DROP ROLE tag11_repl;'
sudo sed -i '/^host tag11 tag11_repl 172.16.0.123\/32/d' \
/etc/postgresql/18/main/pg_hba.conf
sudo rm /etc/postgresql/18/main/conf.d/tag11-logical.conf
sudo systemctl restart postgresql@18-main
psql -Atc 'SHOW wal_level'Erwartet:
slot_name
-----------
(0 rows)
DROP ROLE
replica
Ist pg1 nicht erreichbar, scheitert
DROP SUBSCRIPTION. Dann vorher
ALTER SUBSCRIPTION tag11_sub DISABLE und
ALTER SUBSCRIPTION tag11_sub SET (slot_name = NONE) – und
den Slot später auf dem Sender von Hand löschen.
Interview-Notiz
- „Was repliziert logische Replikation nicht?“ – DDL, Sequenzen und große Objekte; Schemaänderungen erst beim Empfänger, dann beim Sender einspielen.
- „Was ist die Replica Identity?“ – Die Spalten, mit denen UPDATE und DELETE die alte Zeile beim Empfänger finden: Primärschlüssel, eindeutiger Index oder FULL. Ohne sie verweigert der Sender UPDATE/DELETE auf veröffentlichten Tabellen.
- „Wie löst du einen Konflikt?“ – Ursache im Log, lokale Zeile
bereinigen oder per
ALTER SUBSCRIPTION … SKIP (lsn = …)die Transaktion überspringen;disable_on_errorverhindert Endlosschleifen.
Enterprise-Notiz
- Abonnenten schreibgeschützt halten (Rechte,
default_transaction_read_only), sonst entstehen Konflikte wie in Drill 5. - Slot-Monitoring auf dem Sender mit Alarm auf Rückstand und
active = f;max_slot_wal_keep_sizeals Notbremse. - Typische Einsätze: Zero-Downtime-Upgrade, Umzug in die Cloud oder nach Kubernetes, Teilmengen für Reporting oder Data Warehouse, Change Data Capture (Debezium nutzt denselben Mechanismus).
- Schemamigrationen in der Pipeline zuerst auf den Abonnenten, dann
auf den Sender; neue Tabellen mit
REFRESH PUBLICATION. - Bei Failover des Senders gehen logische Slots ohne
failover = trueundsync_replication_slotsauf dem Standby (seit 17) verloren.