Logische Replikation und Decodierung in Azure Database for PostgreSQL – Flexible Server

GILT FÜR: Azure Database for PostgreSQL – Flexible Server

Azure Database for PostgreSQL flexible Server unterstützt die folgenden logischen Datenextraktions- und Replikationsmethoden:

1. Logische Replikation

   1. Verwendung Sie PostgreSQL native logische Replikation, um Datenobjekte zu replizieren. Die logische Replikation ermöglicht eine detaillierte Kontrolle über die Datenreplikation, einschließlich der Datenreplikation auf Tabellenebene.
   2. Verwenden Sie die Erweiterung pglogical, die Ihnen logische Streamingreplikation sowie zusätzliche Funktionen wie das Kopieren des Anfangsschemas der Datenbank, TRUNCATE-Unterstützung und die Möglichkeit, die DDL zu replizieren, bietet.

2. Logische Dekodierung, die durch Dekodierung des Inhalts des Write-Ahead-Logs (WAL) realisiert wird.

Vergleichen der logischen Replikation und der logischen Decodierung

Die logische Replikation und die logische Decodierung weisen Ähnlichkeiten auf. Beide:

• Ermöglichen es Ihnen, Daten aus Postgres zu replizieren.

• Verwenden das Write-Ahead-Protokoll (Write-Ahead Log, WAL) als Änderungsquelle.

• Verwenden logische Replikationsslots, um Daten zu senden. Ein Slot stellt einen Datenstrom von Änderungen dar.

• Bestimmen mithilfe der Eigenschaft „REPLICA IDENTITY“ einer Tabelle, welche Änderungen gesendet werden können.

• Replizieren Sie keine DDL-Änderungen.

Die beiden Technologien unterscheiden sich wie folgt:

Logische Replikation:

• Ermöglicht es Ihnen, eine Tabelle oder Tabellengruppe anzugeben, die repliziert werden soll.

Die logische Decodierung:

• Extrahiert Änderungen aus allen Tabellen in einer Datenbank.

Voraussetzungen für die logische Replikation und die logische Decodierung

1. Navigieren Sie im Portal zur Seite „Serverparameter“.

2. Legen Sie den Serverparameter wal_level auf logical fest.

3. Wenn Sie eine pglogical-Erweiterung verwenden möchten, suchen Sie nach den Parametern shared_preload_libraries und azure.extensions, und wählen Sie in der Auswahlliste die Option pglogical aus.

4. Ändern Sie den Wert des Parameters max_worker_processes mindestens in 16. Andernfalls treten möglicherweise Probleme wie WARNING: out of background worker slots auf.

5. Speichern Sie die Änderungen, und starten Sie den Server neu, um die Änderung zu übernehmen.

6. Vergewissern Sie sich, dass Ihre flexible Azure-Datenbank für PostgreSQL-Serverinstanz Netzwerkdatenverkehr von Ihrer Verbindungsressource zulässt.

7. Erteilen Sie dem Administratorbenutzer Replikationsberechtigungen.

   ALTER ROLE <adminname> WITH REPLICATION;
   

8. Sie sollten sich vergewissern, dass die von Ihnen verwendete Rolle über Berechtigungen für das Schema verfügt, das Sie replizieren. Andernfalls können Fehler wie Permission denied for schema auftreten.

Hinweis

Es ist immer ratsam, den Replikationsbenutzer vom regulären Administratorkonto zu trennen.

Verwenden der logischen Replikation und der logischen Decodierung

Die Verwendung der nativen logischen Replikation ist die einfachste Methode zum Replizieren von Daten aus Der Azure-Datenbank für PostgreSQL flexible Server. Sie können die SQL-Schnittstelle oder das Streamingprotokoll verwenden, um die Änderungen zu verarbeiten. Die SQL-Schnittstelle kann auch verwendet werden, um Änderungen mithilfe der logischen Decodierung zu verarbeiten.

Native logische Replikation

Bei der logischen Replikation wird üblicherweise vom „Verleger“ und vom „Abonnenten“ gesprochen.

• Der Herausgeber ist die Azure-Datenbank für PostgreSQL flexible Serverdatenbank, aus der Sie Daten senden.
• Der Abonnent ist die Azure-Datenbank für PostgreSQL flexible Serverdatenbank, an die Sie Daten senden.

Nachstehend finden Sie einen Beispielcode, mit dem Sie die logische Replikation ausprobieren können.

1. Stellen Sie eine Verbindung mit der Verlegerdatenbank her. Erstellen Sie eine Tabelle, und fügen Sie Daten hinzu.

   CREATE TABLE basic (id INTEGER NOT NULL PRIMARY KEY, a TEXT);
   INSERT INTO basic VALUES (1, 'apple');
   INSERT INTO basic VALUES (2, 'banana');
   

2. Erstellen Sie eine Veröffentlichung für die Tabelle.

   CREATE PUBLICATION pub FOR TABLE basic;
   

3. Stellen Sie eine Verbindung mit der Abonnentendatenbank her. Erstellen Sie eine Tabelle mit demselben Schema wie für den Verleger.

   CREATE TABLE basic (id INTEGER NOT NULL PRIMARY KEY, a TEXT);
   

4. Erstellen Sie ein Abonnement, das eine Verbindung mit der zuvor erstellten Veröffentlichung herstellt.

   CREATE SUBSCRIPTION sub CONNECTION 'host=<server>.postgres.database.azure.com user=<rep_user> dbname=<dbname> password=<password>' PUBLICATION pub;
   

5. Sie können die Tabelle nun auf dem Abonnenten abfragen. Sie sehen, dass Daten vom Herausgeber empfangen wurden.

   SELECT * FROM basic;
   

   Sie können der Tabelle des Verlegers weitere Zeilen hinzufügen und die Änderungen auf dem Abonnenten anzeigen.

   Sollten die Daten nicht angezeigt werden, aktivieren Sie die Anmeldeberechtigung für azure_pg_admin, und überprüfen Sie den Tabelleninhalt.

   ALTER ROLE azure_pg_admin login;
   

Weitere Informationen über die logische Replikation finden Sie in der PostgreSQL-Dokumentation.

Verwenden der logischen Replikation zwischen Datenbanken auf dem gleichen Server

Wenn Sie die logische Replikation zwischen verschiedenen Datenbanken einrichten möchten, die sich in derselben Azure-Datenbank für eine flexible Serverinstanz von PostgreSQL befinden, ist es wichtig, bestimmte Richtlinien zu befolgen, um derzeit vorhandene Implementierungseinschränkungen zu vermeiden. Ab sofort ist das Erstellen eines Abonnements, das eine Verbindung mit dem gleichen Datenbankcluster herstellt, nur erfolgreich, wenn der Replikationsslot nicht innerhalb des gleichen Befehls erstellt wurde. Andernfalls bleibt der CREATE SUBSCRIPTION-Aufruf bei einem LibPQWalReceiverReceive-Warteereignis hängen. Dies geschieht aufgrund einer bestehenden Einschränkung innerhalb der Postgres-Engine, die in zukünftigen Releases möglicherweise entfernt wird.

Führen Sie die folgenden Schritte aus, um die logische Replikation zwischen Ihren Quell- und Zieldatenbanken auf dem gleichen Server unter Umgehung dieser Einschränkung effektiv einzurichten:

Erstellen Sie zunächst eine Tabelle mit dem Namen „basic“ mit einem identischen Schema sowohl in den Quell- als auch in den Zieldatenbanken:

-- Run this on both source and target databases
CREATE TABLE basic (id INTEGER NOT NULL PRIMARY KEY, a TEXT);

Erstellen Sie als Nächstes in der Quelldatenbank eine Veröffentlichung für die Tabelle und separat mithilfe der pg_create_logical_replication_slot-Funktion einen logischen Replikationsslot. Dadurch können Sie das Problem mit dem Aufhängen abwenden, das normalerweise auftritt, wenn der Slot mit demselben Befehl wie das Abonnement erstellt wird. Sie müssen das Plug-In pgoutput verwenden:

-- Run this on the source database
CREATE PUBLICATION pub FOR TABLE basic;
SELECT pg_create_logical_replication_slot('myslot', 'pgoutput');

Erstellen Sie anschließend in Ihrer Zieldatenbank ein Abonnement für die zuvor erstellte Publikation, um sicherzustellen, dass create_slotfalse die Azure-Datenbank für PostgreSQL flexible Server am Erstellen eines neuen Steckplatzes hindert und den Im vorherigen Schritt erstellten Slotnamen richtig angibt. Ersetzen Sie vor dem Ausführen des Befehls die Platzhalter in der Verbindungszeichenfolge durch Ihre tatsächlichen Datenbankanmeldeinformationen:

-- Run this on the target database
CREATE SUBSCRIPTION sub
   CONNECTION 'dbname=<source dbname> host=<server>.postgres.database.azure.com port=5432 user=<rep_user> password=<password>'
   PUBLICATION pub
   WITH (create_slot = false, slot_name='myslot');

Nachdem Sie die logische Replikation eingerichtet haben, können Sie sie jetzt testen, indem Sie einen neuen Datensatz in die Tabelle „basic“ in Ihrer Quelldatenbank einfügen und dann überprüfen, ob sie in Ihre Zieldatenbank repliziert wird:

-- Run this on the source database
INSERT INTO basic SELECT 3, 'mango';

-- Run this on the target database
TABLE basic;

Wenn alles ordnungsgemäß konfiguriert ist, sollte der neue Datensatz aus der Quelldatenbank in Ihrer Zieldatenbank angezeigt werden, wodurch die erfolgreiche Einrichtung der logischen Replikation bestätigt wird.

pglogische Erweiterung

Im Folgenden finden Sie ein Beispiel für die Konfiguration von pglogical auf dem Anbieter-Datenbankserver und dem Abonnenten. Weitere Details finden Sie in der Dokumentation zur pglogical-Erweiterung. Stellen Sie außerdem sicher, dass Sie die oben genannten Aufgaben durchgeführt haben.

1. Installieren Sie die pglogical-Erweiterung sowohl auf den Anbieter- als auch auf den Abonnentendatenbankservern in der Datenbank.

   \c myDB
   CREATE EXTENSION pglogical;
   

2. Wenn der Replikationsbenutzer sich vom Administratorbenutzer des Servers (der den Server erstellt hat) unterscheidet, stellen Sie sicher, dass Sie dem Benutzer die Mitgliedschaft in einer Rolle azure_pg_admin gewähren und dem Benutzer die Attribute REPLICATION und LOGIN zuweisen. Weitere Informationen finden Sie in der Dokumentation zu pglogical.

   GRANT azure_pg_admin to myUser;
   ALTER ROLE myUser REPLICATION LOGIN;
   

3. Erstellen Sie auf dem Datenbankservers des Anbieters (Quelle/Verleger) den Anbieterknoten.

   select pglogical.create_node( node_name := 'provider1',
   dsn := ' host=myProviderServer.postgres.database.azure.com port=5432 dbname=myDB user=myUser password=myPassword');
   

4. Erstellen Sie eine Replikatgruppe.

   select pglogical.create_replication_set('myreplicationset');
   

5. Fügen Sie der Replikatgruppe alle Tabellen in der Datenbank hinzu.

   SELECT pglogical.replication_set_add_all_tables('myreplicationset', '{public}'::text[]);
   

   Alternativ können Sie auch Tabellen aus einem bestimmten Schema (z. B. testUser) einer Standardreplikatgruppe hinzufügen.

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

6. Erstellen Sie auf dem Datenbankserver des Abonnenten einen Abonnentenknoten.

   select pglogical.create_node( node_name := 'subscriber1',
   dsn := ' host=mySubscriberServer.postgres.database.azure.com port=5432 dbname=myDB user=myUser password=myPasword' );
   

7. Erstellen Sie ein Abonnement, um den Synchronisierungs- und Replikationsprozess zu starten.

   select pglogical.create_subscription (
   subscription_name := 'subscription1',
   replication_sets := array['myreplicationset'],
   provider_dsn := 'host=myProviderServer.postgres.database.azure.com port=5432 dbname=myDB user=myUser password=myPassword');
   

8. Anschließend können Sie den Abonnementstatus überprüfen.

   SELECT subscription_name, status FROM pglogical.show_subscription_status();
   

Achtung

Pglogical unterstützt derzeit keine automatische DDL-Replikation. Das anfängliche Schema kann manuell mit pg_dump --schema-only kopiert werden. DDL-Anweisungen können mit der Funktion „pglogical.replicate_ddl_command“ gleichzeitig für den Anbieter und den Abonnenten ausgeführt werden. Beachten Sie weitere Einschränkungen der hier aufgeführten Erweiterung.

Logische Decodierung

Die logische Decodierung kann über das Streamingprotokoll oder die SQL-Schnittstelle genutzt werden.

Streamingprotokoll

Häufig ist das Verarbeiten von Änderungen mithilfe des Streamingprotokolls vorzuziehen. Sie können einen eigenen Consumer/Connector erstellen oder einen Drittanbieterdienst wie Debezium verwenden.

In der wal2json-Dokumentation finden Sie ein Beispiel für die Verwendung des Streamingprotokolls mit pg_recvlogical.

SQL-Schnittstelle

Im folgenden Beispiel wird die SQL-Schnittstelle mit dem wal2json-Plug-In verwendet.

1. Erstellen Sie einen Slot.

   SELECT * FROM pg_create_logical_replication_slot('test_slot', 'wal2json');
   

2. Geben Sie SQL-Befehle aus. Beispiel:

   CREATE TABLE a_table (
      id varchar(40) NOT NULL,
      item varchar(40),
      PRIMARY KEY (id)
   );
   
   INSERT INTO a_table (id, item) VALUES ('id1', 'item1');
   DELETE FROM a_table WHERE id='id1';
   

3. Verarbeiten Sie die Änderungen.

   SELECT data FROM pg_logical_slot_get_changes('test_slot', NULL, NULL, 'pretty-print', '1');
   

   Die Ausgabe sieht wie folgt aus:

   {
         "change": [
         ]
   }
   {
         "change": [
                  {
                           "kind": "insert",
                           "schema": "public",
                           "table": "a_table",
                           "columnnames": ["id", "item"],
                           "columntypes": ["character varying(40)", "character varying(40)"],
                           "columnvalues": ["id1", "item1"]
                  }
         ]
   }
   {
         "change": [
                  {
                           "kind": "delete",
                           "schema": "public",
                           "table": "a_table",
                           "oldkeys": {
                                 "keynames": ["id"],
                                 "keytypes": ["character varying(40)"],
                                 "keyvalues": ["id1"]
                           }
                  }
         ]
   }
   

4. Löschen Sie den Slot, sobald Sie ihn nicht mehr benötigen.

   SELECT pg_drop_replication_slot('test_slot');
   

Weitere Informationen über die logische Decodierung finden Sie in der PostgreSQL-Dokumentation.

Monitor

Sie müssen die logische Decodierung überwachen. Alle nicht verwendeten Replikationsslots müssen gelöscht werden. Slots halten Verbindungen zu den Postgres-WAL-Protokollen und den relevanten Systemkatalogen aufrecht, bis Änderungen gelesen wurden. Wenn Ihr Abonnent oder Consumer ausfällt oder nicht ordnungsgemäß konfiguriert ist, werden die nicht genutzten Protokolle aufbewahrt und Ihren Speicher füllen. Außerdem erhöhen nicht verarbeitete Protokolle das Risiko von Transaktions-ID-Umläufen. Beide Situationen können dazu führen, dass der Server nicht mehr verfügbar ist. Daher müssen logische Replikationsslots kontinuierlich verarbeitet werden. Wenn ein logischer Replikationsslot nicht mehr verwendet wird, löschen Sie ihn sofort.

Die Spalte „active“ der Ansicht pg_replication_slots gibt an, ob ein Consumer mit einem Slot verbunden ist.

SELECT * FROM pg_replication_slots;

Legen Sie Warnungen für die maximal verwendeten Transaktions-IDs und die verwendete Azure-Datenbank für PostgreSQL flexible Servermetriken fest, um Sie zu benachrichtigen, wenn die Werte über normale Schwellenwerte steigen.

Begrenzungen

• Es gelten logische Replikationseinschränkungen, wie hier dokumentiert.

• Slots und HA-Failover – Beachten Sie, dass logische Replikationsplätze bei Failoverereignissen nicht beibehalten werden, wenn Sie serverfähige Server mit hoher Verfügbarkeit (HA) mit Azure Database für PostgreSQL flexible Server verwenden. Um logische Replikationsslots beizubehalten und die Datenkonsistenz nach einem Failover zu gewährleisten, empfiehlt es sich, die Erweiterung für PG-Failoverslots zu verwenden. Weitere Informationen zum Aktivieren dieser Erweiterung finden Sie in der Dokumentation.

Wichtig

Sie müssen den logischen Replikationsslot auf dem primären Server löschen, wenn der entsprechende Abonnent nicht mehr vorhanden ist. Andernfalls sammeln sich die WAL-Dateien im primären Speicher an und füllen den Speicher. Angenommen, beim Speicher wird ein bestimmter Schwellenwert überschritten, und der logische Replikationsslot wird nicht verwendet (aufgrund eines nicht verfügbaren Abonnenten). In diesem Fall legt die flexible Serverinstanz der Azure-Datenbank für PostgreSQL automatisch diesen nicht verwendeten logischen Replikationsplatz ab. Diese Aktion gibt akkumulierte WAL-Dateien frei und verhindert, dass Ihr Server aufgrund der Speichersituation nicht mehr verfügbar ist.

Zugehöriger Inhalt

• Netzwerkoptionen
• extensions
• Hochverfügbarkeit