PostgreSQL Logical Replication (Publication / Subscription)

Logical Replication auf Tabellenebene zwischen zwei PostgreSQL-Clustern: wal_level=logical, Prüfung der Replica Identity, Publication und Subscription, initiale Synchronisation, Monitoring, Behandlung von Konflikten (Conflicts) und manueller Abgleich der Sequences. Enthalten sind auch die Schritte für den finalen Cutover, die bei einer Migration zwischen Versionen (Major Upgrade, Serverwechsel) die Downtime auf Sekunden begrenzen.

Logical Replication ist weder eine HA- noch eine Backup-Methode: Für eine exakte Kopie des gesamten Clusters und Failover verwende Streaming Replication oder Patroni, für Backups pgBackRest. Ein In-Place-Upgrade der Major-Version auf demselben Server (pg_upgrade) beschreibt pg-major-upgrade.

Topologie: 2 Server: Publisher + Subscriber; der Publisher kann ein Einzelserver, eine Streaming-Replication-Umgebung oder ein Patroni-Cluster sein

Szenarien

• Kontinuierliche Replikation (Reporting, Integration, Teilkopie) — Ausgewählte Tabellen fließen kontinuierlich in einen anderen Cluster (Reporting-Server, Befüllung eines Data Warehouse, Integration zwischen Diensten). Der Subscriber bleibt beschreibbar.
• Migration: neuer Server / neue Major-Version + Cutover — Wechsel auf einen neuen Server oder eine neue Major-Version mit Sekunden an Downtime: initiale Kopie und laufender Abgleich, danach ein kurzer Schreibstopp und der Cutover.

Vor dem Start

• Auf dem Publisher läuft PostgreSQL …, und die Datenbank … existiert.
• Der Patroni-Cluster … ist gesund, und … ist derzeit Leader; auf den Replicas gilt hot_standby_feedback = on (Standard in pg-patroni).
• Der Standby … ist über einen physischen Replication Slot mit dem Primary verbunden (primary_slot_name = …, die Slot-Option in pg-streaming). Ohne diesen Slot funktioniert die Slot-Synchronisation nicht.
• Auf dem Subscriber wurde PostgreSQL … mit pg-install installiert und läuft; die …-Versionen der auf dem Publisher genutzten Extensions (PostGIS usw.) sind installiert.
• Der Subscriber erreicht …die HAProxy-/VIP-Adresse des Publishers (…:…)…den Port … des Publishers… (Firewall / Security Group).
• Welchen Wert hat wal_level derzeit auf dem Publisher? Ist er nicht logical, erfordert die Änderung einen Neustart: Plane ein Wartungsfenster ein.
• Die zu veröffentlichenden Tabellen haben einen Primary Key oder einen geeigneten Unique Index. Sonst schlagen UPDATE/DELETE auf dem Publisher fehl: Ergänze bei den aufgeführten Tabellen einen PK oder lege eine Replica Identity fest (Schritt unten).
• Logical Replication überträgt keine DDL (CREATE/ALTER TABLE), keine Sequence-Werte und keine Large Objects. Während die Replikation läuft, werden Schemaänderungen manuell angewendet, zuerst auf dem Subscriber, dann auf dem Publisher; das Team ist informiert.
• Auf dem Publisher gibt es zusätzlichen Plattenplatz für WAL: Stoppt der Subscriber, hält der Slot WAL vor (Grenze: … GB).

Phasen der Anleitung

• Publisher vorbereiten — Prüfen, ob der Publisher …Leader…Primary… ist · Einstellungen für Logical Replication schreiben · Einstellungen für Logical Replication in Patroni schreiben · Publisher neu starten · Patroni-Mitglieder neu starten · Replikationsbenutzer anlegen …
• Replica Identity — Tabellen ohne PK auflisten · Replica Identity für Tabellen ohne PK festlegen
• Publication — Publication anlegen
• Subscriber vorbereiten — Worker-Einstellungen des Subscribers schreiben · Subscriber neu starten · Passwort des Publishers in .pgpass schreiben · Datenbank und Schema anlegen · Rollen und Rechte übertragen
• Subscription und initiale Synchronisation — Subscription anlegen · Auf das Ende der initialen Synchronisation warten · Zeilenanzahlen vergleichen · Test mit Live-Änderung
• Slot beim Failover erhalten — Slot in Patroni als permanent definieren · Prüfen, ob der Slot auf den Replicas sichtbar ist · Primary: synchronisierte Standby-Slots festlegen · Standby: Slot-Synchronisation aktivieren · Subscription nach einem Failover auf den neuen Primary umstellen · Subscription nach einem Failover neu aufbauen
• Monitoring — Status der Subscription (Subscriber) · Slot-Verzögerung (Publisher)
• Konfliktbehandlung — Fehler finden · Kollidierende Zeile auf dem Subscriber korrigieren · Kollidierende Transaktion überspringen
• Sequences abgleichen — Sequence-Werte vom Publisher auf den Subscriber übertragen
• Cutover — Schreibvorgänge auf dem Publisher stoppen · Prüfen, ob die letzten Änderungen übertragen wurden · Sequences ein letztes Mal abgleichen · Subscription löschen · Statistiken sammeln · Anwendungen auf den Subscriber umstellen …
• Wartungsarbeiten — Neue Tabelle hinzufügen · Schemaänderung (ALTER TABLE) · Subscription löschen (wenn der Publisher nicht erreichbar ist)