Bidirektionale Replikation in OCI Database für PostgreSQL mit pglogical

Einführung

Die logische Replikation in OCI PostgreSQL ermöglicht das Replizieren von Datenobjekten und deren Änderungen basierend auf der Replikationsidentität einer Tabelle, in der Regel dem Primärschlüssel. Es verwendet ein Publish-Subscribe-Modell, bei dem ein oder mehrere Abonnenten Veröffentlichungen abonnieren, die auf einem Herausgeberknoten definiert sind. Abonnenten ziehen Daten aus diesen Publikationen und können die Änderungen optional erneut veröffentlichen, um die kaskadierende Replikation oder komplexere Topologien zu ermöglichen.

Logische Replikationsstreams Änderungen auf Zeilenebene – Vorgänge INSERT, UPDATE und DELETE – durch Decodierung des WAL (Write-Ahead-Logs) mit logischer Decodierung. Diese dekodierten Änderungen sind unabhängig vom physischen Speicherformat von OCI PostgreSQL und stellen nur die logischen Datenvorgänge dar und bieten mehr Flexibilität für nachgelagerte Systeme.

In diesem Tutorial wird erläutert, wie Sie die pglogische Erweiterung für die logische Replikation verwenden und wie sie die bidirektionale Replikation zwischen zwei Datenbanksystemen in OCI Database für PostgreSQL ermöglicht.

Bidirektionale Replikation

Bidirektionale Replikation (BDR) mit pglogischer bedeutet, dass zwei oder mehr OCI-PostgreSQL-Datenbanksysteme die Änderungen veröffentlichen und abonnieren. Mit anderen Worten, jedes OCI PostgreSQL-Datenbanksystem kann lesen und schreiben, und alle Änderungen werden in beide Richtungen repliziert. Folglich werden alle Änderungen, die an einem der OCI PostgreSQL-Datenbanksysteme durchgeführt werden, gespiegelt und auf beiden implementiert.

Die bidirektionale Replikation wird in der Regel empfohlen, wenn Sie eine regionale oder globale Datenbankarchitektur benötigen, die Lese- und Schreibzugriff mit geringer Latenz für Benutzer an mehreren Standorten bietet. Durch das gemeinsame Speichern von Lese-/Schreibinstanzen in der Nähe von Benutzern in jeder Region können Änderungen lokal vorgenommen und dann in andere Regionen repliziert werden. So wird sichergestellt, dass die Daten auf allen Knoten synchronisiert werden.

In der Praxis erfordern die meisten Anwendungsfälle, dass nur eine kleine Teilmenge von Tabellen aus mehreren Regionen schreibbar ist, während der Großteil der Datenbank durch die unidirektionale Replikation schreibgeschützt bleiben oder zentral verwaltet werden kann.

Die bidirektionale Replikation führt jedoch zu Komplexität. Die Aufrechterhaltung der Datenkonsistenz und die Verwaltung der Replikationstopologie kann mit dem Systemwachstum zu einer Herausforderung werden.

Wenn die Hauptziele Ihrer Anwendung die Leseskalierbarkeit und Resilienz gegenüber regionalen Ausfällen sind, besteht ein einfacherer und robusterer Ansatz darin, eine hochverfügbare OCI-Datenbank für die PostgreSQL-Instanz mit mehreren Lesereplikaten bereitzustellen, entweder in derselben Region oder regionsübergreifend.

Wie funktioniert bi-direktionale pglogische Arbeit?

In pglogischer Sprache können Sie mehrere Abonnements und Publikationen wie folgt konfigurieren:

• Datenbanksystem 1 veröffentlicht Änderungen → Datenbanksystem 2 abonniert

• Datenbanksystem 2 veröffentlicht Änderungen → Datenbanksystem 1 abonniert

Jeder Knoten:

• Sendet seine Änderungen an den anderen

• Wendet Änderungen von den anderen an

• Behandelt potenzielle Konflikte mit den Konfliktlösungseinstellungen von pglogical

Weitere Informationen zur unidirektionalen pglogischen Replikation über Regionen hinweg in OCI PostgreSQL finden Sie in diesem Blog: Unidirektionale pglogische Replikation über Regionen hinweg.

Konflikte und Konfiguration verwalten

Der Herausgeber (die Quelldatenbank) definiert eine oder mehrere Publikationen und sendet Änderungsdaten (DML-Vorgänge) an den Abonnenten (die Zieldatenbank). Ein Abonnent kann sich bei mehreren Publishern anmelden und seine Änderungen auf seine lokale Datenbank anwenden.

Die Erweiterung pglogical bietet eine erweiterte Konfliktlösung über den Parameter pglogical.conflict_resolution, der die folgenden Modi unterstützt:

• apply_remote (Standard in OCI Database für PostgreSQL)

• error

• keep_local

• last_update_wins

• first_update_wins

Weitere Details zu conflict_resolution in pglogischer Sprache finden Sie in der offiziellen Dokumentation zu github: 2nd Quadrant Configuration options.

Wenn Sie keep_local, last_update_wins oder first_update_wins verwenden, muss track_commit_timestamps sowohl für den Herausgeber als auch für den Abonnenten aktiviert sein. Beachten Sie, dass die Aktivierung dieser Einstellung je nach Workload einen messbaren Performance-Overhead verursachen kann. Es wird dringend empfohlen, die Auswirkungen auf die Performance in Ihrer Produktionsumgebung vor dem Deployment zu bewerten.

Bidirektionale Replikation in OCI einrichten PostgreSQL

Informationen zum Aktivieren der pglogischen Erweiterung in OCI Database für PostgreSQL finden Sie in diesem Tutorial: Pglogische Erweiterung in OCI PostgreSQL aktivieren.

Aufgabe 1: Voraussetzungen

Nachdem die pglogische Erweiterung in Ihrer OCI-Datenbank für PostgreSQL erstellt wurde, erteilen Sie die folgende Berechtigung für das Datenbanksystem Nr. 1 und das Datenbanksystem Nr. 2.

ALTER ROLE psql WITH REPLICATION;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_replication_origin_session_reset() TO psql; 
GRANT EXECUTE ON FUNCTION pg_catalog.pg_replication_origin_session_setup TO psql; 
GRANT ALL ON FUNCTION pg_catalog.pg_replication_origin_session_setup TO psql;

Hinweis: psql ist ein Beispielreplikationsbenutzer, der speziell zu Demonstrationszwecken in diesem Tutorial erstellt wurde.

Aufgabe 2: Unidirektionale logische Replikation konfigurieren

Auf Datenbanksystem 1:

Nachdem Sie die zuvor beschriebenen Voraussetzungen erfüllt haben, erstellen Sie eine Testtabelle, und fügen Sie einige Datensätze ein, um den pglogischen Workflow zu prüfen.

CREATE TABLE test_table (
    id serial PRIMARY KEY,
    data text
);
 
INSERT INTO test_table
    (id, data)
VALUES
    (generate_series(1, 1000), 'Test');

Erstellen Sie den Providerknoten:

Der nächste Schritt besteht darin, einen Knoten mit der pglogischen Funktion create_node zu erstellen.

SELECT pglogical.create_node(
    node_name := 'provider1',
    dsn := 'host=primary.xxxxxxxx.postgresql.us-phoenix-1.oci.oraclecloud.com
            port=5432
            user=psql
            password=xxxxx
            dbname=postgres'
);

Im Folgenden finden Sie die Parameterdefinitionen für die Funktion create_node:

• node_name: Geben Sie den Namen des Herausgebers an, der im Datenbanksystem Nr. 1 erstellt werden soll.
• host: Geben Sie den vollqualifizierten Domainnamen (FQDN) des Datenbanksystems Nr. 1 ein.
• port_number: Geben Sie den Port an, auf dem das Datenbanksystem Nr. 1 ausgeführt wird.
• database_name: Geben Sie den Namen der Datenbank an, in der die pglogische Replikation konfiguriert wird.

Im nächsten Schritt identifizieren Sie die Tabellen, die in die Replikation aufgenommen werden müssen. pglogical verwendet Replikationsgruppen, um zu verwalten, welche Tabellen und Aktionen (INSERT, UPDATE, DELETE) repliziert werden. Sie können ein benutzerdefiniertes Replikationsset mit der Funktion create_replication_set erstellen oder das vordefinierte Standardset verwenden, das alle Aktionen in den zugewiesenen Tabellen repliziert. Um Tabellen einzuschließen, verwenden Sie replication_set_add_table für einzelne Tabellen oder replication_set_add_all_tables, um alle Tabellen aus einem bestimmten Schema hinzuzufügen.

Mit dem folgenden Befehl werden alle Tabellen aus dem öffentlichen Schema zum Standardreplikationsset hinzugefügt:

SELECT pglogical.replication_set_add_all_tables(
    'default',
    ARRAY['public']
);

Weitere Informationen zu Replikationssets finden Sie unter Replikationssets.

Auf Datenbanksystem 2:

Erstellen Sie eine übereinstimmende Tabelle wie im Datenbanksystem #1:

Erstellen Sie die Testtabelle in Datenbanksystem Nr. 2, damit sie der Struktur der Tabelle in Datenbanksystem Nr. 1 entspricht.

CREATE TABLE test_table (
    id serial PRIMARY KEY,
    data text
);

Erstellen Sie den Subscriber-Knoten:

Erstellen Sie als Nächstes einen Knoten in Datenbanksystem Nr. 2 mit der pglogischen Funktion create_node, genau wie im Datenbanksystem Nr. 1:

SELECT pglogical.create_node(
    node_name := 'subscriber1',
    dsn := 'host=primary.xxxxxx.postgresql.us-phoenix-1.oci.oraclecloud.com
            port=5432
            user=psql
            password=xxxxxx
            dbname=postgres'
);

Im Folgenden finden Sie die Parameterdefinitionen für die Funktion create_node:

• node_name: Definieren Sie den Namen des Subscribers im Datenbanksystem #2.
• host: Geben Sie den vollqualifizierten Domainnamen (FQDN) des Datenbanksystems Nr. 2 ein.
• port_number: Geben Sie den Port ein, auf dem das Datenbanksystem #2 ausgeführt wird.
• database_name: Geben Sie den Name der Datenbank an, in der das Abonnement erstellt wird.

pglogisches Abonnement erstellen:

Im nächsten Schritt erstellen Sie das Abonnement, um die Replikation von Daten aus Datenbanksystem Nr. 1 mit der Funktion create_subscription zu starten.

Der Parameter subscription_name kann ein beliebiger Name sein, den Sie für das Abonnement auswählen. provider_dsn ist die Verbindungszeichenfolge für Datenbanksystem Nr. 1. Der Parameter replication_sets gibt an, welche Replikationssets verwendet werden sollen. In diesem Beispiel wird das Standardreplikationsset verwendet.

Das Argument synchronize_data gibt pglogisch an, ob vorhandene Daten aus der Quelle kopiert werden sollen. In unserem Test ist es auf true gesetzt, weil alle Zeilen zusammen mit zukünftigen Änderungen kopiert werden sollen.

Der Parameter forward_origins ist entscheidend für die Aktivierung der bidirektionalen Replikation. Wenn Sie forward_origins := '{}' festlegen, wird sichergestellt, dass nur Änderungen weitergeleitet werden, die vom Knoten selbst stammen, und keine Änderungen, die bereits von anderen Knoten repliziert wurden. Dadurch wird verhindert, dass der Replikationsprozess eine Endlosschleife erreicht, in der Änderungen kontinuierlich vor und zurück repliziert werden.

SELECT pglogical.create_subscription(
    subscription_name := 'subscription1',
    provider_dsn := 'host=primary.xxxxxxx.postgresql.us-phoenix-1.oci.oraclecloud.com
                     port=5432
                     sslmode=require
                     dbname=postgres
                     user=psql
                     password=xxxxx',
    replication_sets := ARRAY['default'],
    synchronize_data := true,
    forward_origins := '{}'
);

Abonnementstatus prüfen:

Prüfen Sie den Abonnementstatus und grundlegende Informationen zum Datenbanksystem Nr. 2 mit dem folgenden Befehl:

select * from pglogical.show_subscription_status();
-[ RECORD 1 ]-----+----------------------------------------------------------------------------------------------------------------------------------------------------------------
subscription_name | subscription1
status            | replicating
provider_node     | provider1
provider_dsn      | host=primary.xxxxxxxxx.postgresql.us-phoenix-1.oci.oraclecloud.com port=5432 sslmode=require dbname=postgres user=psql password=xxxxxx
slot_name         | pgl_postgres_provider1_subscription1
replication_sets  | {default}
forward_origins   |

Aufgabe 3: Replikation von Datenbanksystem 1 zu Datenbanksystem 2 testen

Nachdem die Replikation zwischen Datenbanksystem Nr. 1 (Quelle) und Datenbanksystem Nr. 2 (Ziel) eingerichtet wurde, testen wir, ob Änderungen, die im Datenbanksystem Nr. 1 vorgenommen wurden, automatisch in Datenbanksystem Nr. 2 kopiert werden.

1. Erste Daten überprüfen

   Prüfen Sie auf Datenbanksystem Nr. 2 die Tabelle test_table:

   postgres=> SELECT COUNT(*) FROM test_table;
    count 
   -------
     1000
   (1 row)
   

   Damit wird bestätigt, dass die Tabelle bereits 1000 Zeilen enthält.

2. INSERT testen

   Neue Zeile in Datenbanksystem 1 hinzufügen:

   postgres=> INSERT INTO test_table VALUES (10000, 'initial load');
   

3. In Datenbanksystem Nr. 1 prüfen:

   postgres=> SELECT * FROM test_table WHERE id = 10000;
     id   |     data     
   -------+--------------
   10000 | initial load
   

4. Prüfen Sie jetzt das Datenbanksystem Nr. 2. Dort wird dieselbe Zeile angezeigt, in der die Replikation ausgeführt wird:

   postgres=> SELECT * FROM test_table WHERE id = 10000;
     id   |     data     
   -------+--------------
   10000 | initial load
   

5. LÖSCHEN testen

   Derselbe Datensatz in Datenbanksystem 1 löschen:

   postgres=> DELETE FROM test_table WHERE id = 10000;
   

6. In beiden Systemen prüfen – die Zeile ist von beiden verschwunden.

   (0 rows)
   

7. UPDATE testen

   Vorhandene Zeile in Datenbanksystem Nr. 1 aktualisieren:

   postgres=> UPDATE test_table SET data = 'Initial load' WHERE id = 1;
   

   Datenbanksystem Nr. 1 prüfen:

     id   |     data     
   -------+--------------
     1 | Initial load
   

   Prüfen Sie das Datenbanksystem Nr. 2 – der aktualisierte Wert ist ebenfalls vorhanden.

Mit diesen Tests bestätigen wir, dass INSERT-, UPDATE- und DELETE-Vorgänge aus Datenbanksystem Nr. 1 → Datenbanksystem Nr. 2 korrekt repliziert werden.

Aufgabe 4: Bidirektionale logische Replikation konfigurieren

Die vorherigen Aufgaben richten die unidirektionale Replikation von Datenbanksystem Nr. 1 in Datenbanksystem Nr. 2 ein. Als Nächstes führen wir zusätzliche Befehle aus, um die Replikation auch in umgekehrter Richtung zu ermöglichen.

Auf Datenbanksystem 2:

Richten Sie ein Replikationsset wie in Datenbanksystem Nr. 1 ein:

SELECT pglogical.replication_set_add_all_tables(
    'default',
    ARRAY['public']
);

Auf Datenbanksystem Nr. 1:

Erstellen Sie ein Abonnement in Datenbanksystem Nr. 1, damit die Änderungen aus Datenbanksystem Nr. 2 abgerufen werden:

SELECT pglogical.create_subscription(
    subscription_name := 'subscription2',
    provider_dsn := 'host=primary.xxxxxxxx.postgresql.us-phoenix-1.oci.oraclecloud.com
                     port=5432
                     sslmode=require
                     dbname=postgres
                     user=psql
                     password=xxxxx',
    replication_sets := ARRAY['default'],
    synchronize_data := false,
    forward_origins := '{}'
);

Im vorherigen Befehl wurde das Argument synchronize_data im Gegensatz zu zuvor auf false gesetzt. Dies liegt daran, dass vorhandene Daten nicht aus Datenbanksystem Nr. 2 in Datenbanksystem Nr. 1 kopiert werden müssen, da beide Tabellen bereits identische Daten enthalten.

Mit diesem Befehl ist das bidirektionale Replikationssetup jetzt abgeschlossen, und alle auf einem Server vorgenommenen Änderungen werden auf den anderen repliziert. Prüfen Sie als Nächstes den Abonnementstatus in Datenbanksystem Nr. 1.

Prüfen Sie den Abonnementstatus von Datenbanksystem 1:

Prüfen Sie den Abonnementstatus und grundlegende Informationen zu Datenbanksystem Nr. 1 mit dem folgenden Befehl:

select * from pglogical.show_subscription_status();
-[ RECORD 1 ]-----+----------------------------------------------------------------------------------------------------------------------------------------------------------------
subscription_name | subscription2
status            | replicating
provider_node     | subscriber1
provider_dsn      | host=primary.xxxxxxxx.postgresql.us-phoenix-1.oci.oraclecloud.com port=5432 sslmode=require dbname=postgres user=psql password=xxxxx
slot_name         | pgl_postgres_subscriber1_subscription2
replication_sets  | {default}
forward_origins   |

Aufgabe 5: Bidirektionale Replikation testen

Beginnen wir mit dem Hinzufügen einer Zeile zu beiden Datenbanksystemen, um zu prüfen, ob die bidirektionale Replikation ordnungsgemäß funktioniert.

1. Zeile in Datenbanksystem 1 einfügen:

   Führen Sie den folgenden Befehl in Datenbanksystem 1 aus:

   postgres=# INSERT INTO test_table VALUES (1001, 'Initial_load');
   INSERT 0 1
   

2. Prüfen Sie die eingefügte Zeile in Datenbanksystem Nr. 1:

   postgres=# SELECT * FROM test_table WHERE id = 1001;
     id  |     data     
   ------+--------------
   1001 | Initial_load
   (1 row)
   

3. Replikation auf Datenbanksystem Nr. 2 prüfen:

   Prüfen Sie nun das Datenbanksystem Nr. 2, um zu bestätigen, dass die Zeile repliziert wurde:

   postgres=> SELECT * FROM test_table WHERE id = 1001;
     id  |     data     
   ------+--------------
   1001 | Initial_load
   (1 row)
   

   Dies zeigt, dass die Einfügung in Datenbanksystem Nr. 1 in Datenbanksystem Nr. 2 repliziert wurde.

4. Zeile in Datenbanksystem 2 einfügen:

   Führen Sie den folgenden Befehl in Datenbanksystem 2 aus:

   postgres=> INSERT INTO test_table VALUES (1002, 'Initial_load');
   INSERT 0 1
   

5. Prüfen Sie die eingefügte Zeile in Datenbanksystem Nr. 2:

   postgres=> SELECT * FROM test_table WHERE id = 1002;
     id  |     data     
   ------+--------------
   1002 | Initial_load
   (1 row)
   

6. Replikation auf Datenbanksystem Nr. 1 prüfen:

   Prüfen Sie nun das Datenbanksystem Nr. 1, um zu bestätigen, dass die Zeile zurück repliziert wurde:

   postgres=# SELECT * FROM test_table WHERE id = 1002;
     id  |     data     
   ------+--------------
   1002 | Initial_load
   (1 row)
   

   Dieser Test bestätigt, dass Datenänderungen zwischen Datenbanksystem Nr. 1 und Datenbanksystem Nr. 2 erfolgreich in beide Richtungen repliziert werden.

Logische Replikationsverzögerungen überwachen

Nachdem das pglogische Setup abgeschlossen ist, sollten Sie das Replikationslog überwachen, um Replikations-Slots, Verzögerungen und andere relevante Metriken zu verfolgen.

select * from pg_stat_replication;

Dieser Befehl gibt den aktuellen Status der Replikation in OCI PostgreSQL zurück.

Schlussfolgerung

Dieses Tutorial führt Sie durch die Konfiguration der bidirektionalen PostgreSQL-Replikation mit pglogical. Die bidirektionale Replikation erhöht die Komplexität Ihrer Datenbankinfrastruktur und erfordert zusätzlichen Aufwand. Daher ist es wichtig zu bestätigen, dass sie zu Ihrem Anwendungsfall passt. Wenn Ihre Anwendung mehrere Autoren in verschiedenen Regionen erfordert und das Sharding der Datenbank nicht möglich ist, ist die bidirektionale Replikation die ideale Lösung.

Bestätigungen

• Autor - Kaviya Selvaraj (Senior Member Technical Staff)

Weitere Lernressourcen

Sehen Sie sich weitere Übungen zu docs.oracle.com/learn an, oder greifen Sie auf weitere kostenlose Lerninhalte im Oracle Learning YouTube-Kanal zu. Besuchen Sie außerdem education.oracle.com/learning-explorer, um ein Oracle Learning Explorer zu werden.

Die Produktdokumentation finden Sie im Oracle Help Center.

---

Titel und Copyright-Informationen

Bi-directional replication in OCI Database for PostgreSQL using pglogical

G43208-01

Copyright ©2025,

Oracle und/oder verbundene Unternehmen.