Logisk replikering och logisk avkodning i Azure Database for PostgreSQL – flexibel server

GÄLLER FÖR: Azure Database for PostgreSQL – flexibel server

Azure Database for PostgreSQL – flexibel server stöder följande logiska dataextraherings- och replikeringsmetoder:

1. Logisk replikering

   1. Använda postgreSQL-intern logisk replikering för att replikera dataobjekt. Med logisk replikering får du mer detaljerad kontroll över datareplikeringen, inklusive datareplikering på tabellnivå.
   2. Använda pglogical-tillägg som ger logisk direktuppspelningsreplikering och fler funktioner, till exempel kopiering av databasens ursprungliga schema, stöd för TRUNCATE, möjlighet att replikera DDL osv.

2. Logisk avkodning som implementeras genom avkodning av innehållet i loggen för framåtskrivning (WAL).

Jämför logisk replikering och logisk avkodning

Logisk replikering och logisk avkodning har flera likheter. De båda:

• Gör att du kan replikera data från Postgres.

• Använd skriv-framåt-loggen (WAL) som källa för ändringar.

• Använd logiska replikeringsfack för att skicka ut data. Ett fack representerar en ström av ändringar.

• Använd en tabells replikidentitetsegenskap för att avgöra vilka ändringar som kan skickas ut.

• Replikera inte DDL-ändringar.

De två teknikerna har sina skillnader:

Logisk replikering:

• Gör att du kan ange en tabell eller uppsättning tabeller som ska replikeras.

Logisk avkodning:

• Extraherar ändringar i alla tabeller i en databas.

Förutsättningar för logisk replikering och logisk avkodning

1. Gå till sidan serverparametrar i portalen.

2. Ange serverparametern wal_level till logical.

3. Om du vill använda ett pglogical-tillägg söker du efter parametrarna shared_preload_librariesoch azure.extensions och väljer pglogical i listrutan.

4. Uppdatera max_worker_processes parametervärdet till minst 16. Annars kan du stöta på problem som WARNING: out of background worker slots.

5. Spara ändringarna och starta om servern för att tillämpa ändringarna.

6. Bekräfta att din flexibla Azure Database for PostgreSQL-serverinstans tillåter nätverkstrafik från din anslutande resurs.

7. Ge administratörsanvändaren replikeringsbehörigheter.

   ALTER ROLE <adminname> WITH REPLICATION;
   

8. Du kanske vill se till att den roll som du använder har behörighet för schemat som du replikerar. Annars kan du stöta på fel som Permission denied for schema.

Kommentar

Det är alltid bra att skilja replikeringsanvändaren från det vanliga administratörskontot.

Använda logisk replikering och logisk avkodning

Att använda intern logisk replikering är det enklaste sättet att replikera data från en flexibel Azure Database for PostgreSQL-server. Du kan använda SQL-gränssnittet eller direktuppspelningsprotokollet för att använda ändringarna. Du kan också använda SQL-gränssnittet för att använda ändringar med hjälp av logisk avkodning.

Intern logisk replikering

Logisk replikering använder termerna "publisher" och "subscriber".

• Utgivaren är den flexibla serverdatabasen Azure Database for PostgreSQL som du skickar data från.
• Prenumeranten är den flexibla serverdatabasen Azure Database for PostgreSQL som du skickar data till.

Här är exempelkod som du kan använda för att testa logisk replikering.

1. Anslut till utgivardatabasen. Skapa en tabell och lägg till data.

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

2. Skapa en publikation för tabellen.

   CREATE PUBLICATION pub FOR TABLE basic;
   

3. Anslut till prenumerantdatabasen. Skapa en tabell med samma schema som i utgivaren.

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

4. Skapa en prenumeration som ansluter till publikationen som du skapade tidigare.

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

5. Nu kan du fråga tabellen i prenumeranten. Du ser att den har tagit emot data från utgivaren.

   SELECT * FROM basic;
   

   Du kan lägga till fler rader i utgivarens tabell och visa ändringarna för prenumeranten.

   Om du inte kan se data aktiverar du inloggningsbehörigheten för azure_pg_admin och kontrollerar tabellinnehållet.

   ALTER ROLE azure_pg_admin login;
   

Gå till PostgreSQL-dokumentationen om du vill veta mer om logisk replikering.

Använda logisk replikering mellan databaser på samma server

När du vill konfigurera logisk replikering mellan olika databaser som finns på samma Azure Database for PostgreSQL-instans för flexibel server är det viktigt att följa specifika riktlinjer för att undvika implementeringsbegränsningar som för närvarande finns. Från och med nu kommer det bara att lyckas att skapa en prenumeration som ansluter till samma databaskluster om replikeringsplatsen inte skapas inom samma kommando. annars CREATE SUBSCRIPTION låser sig samtalet vid en LibPQWalReceiverReceive väntehändelse. Detta inträffar på grund av en befintlig begränsning i Postgres-motorn, som kan tas bort i framtida versioner.

Följ stegen nedan för att effektivt konfigurera logisk replikering mellan dina "käll- och måldatabaser" på samma server samtidigt som du kringgår den här begränsningen:

Skapa först en tabell med namnet "basic" med ett identiskt schema i både käll- och måldatabaserna:

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

I källdatabasen skapar du sedan en publikation för tabellen och skapar separat ett logiskt replikeringsfack med hjälp av pg_create_logical_replication_slot funktionen, vilket hjälper till att avvärja det hängande problemet som vanligtvis uppstår när facket skapas i samma kommando som prenumerationen. Du måste använda plugin-programmet pgoutput :

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

Därefter skapar du en prenumeration på den tidigare skapade publikationen i måldatabasen, vilket säkerställer att create_slot den är inställd false på att förhindra att Azure Database for PostgreSQL– flexibel server skapar ett nytt fack och korrekt anger det facknamn som skapades i föregående steg. Innan du kör kommandot ersätter du platshållarna i anslutningssträng med dina faktiska databasautentiseringsuppgifter:

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

När du har konfigurerat den logiska replikeringen kan du nu testa den genom att infoga en ny post i tabellen "basic" i källdatabasen och sedan verifiera att den replikeras till måldatabasen:

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

-- Run this on the target database
TABLE basic;

Om allt är korrekt konfigurerat bör du bevittna den nya posten från källdatabasen i måldatabasen, vilket bekräftar att den logiska replikeringen har konfigurerats korrekt.

pglogical extension

Här är ett exempel på hur du konfigurerar pglogical på providerdatabasservern och prenumeranten. Mer information finns i dokumentationen för pglogical extension. Kontrollera också att du har utfört nödvändiga uppgifter som anges ovan.

1. Installera pglogical-tillägget i databasen i både providern och prenumerantdatabasservrarna.

   \c myDB
   CREATE EXTENSION pglogical;
   

2. Om replikeringsanvändaren inte är serveradministrationsanvändaren (som skapade servern) kontrollerar du att du beviljar användaren medlemskap i en roll azure_pg_admin och tilldelar replikerings- och INLOGGNINGsattribut till användaren. Mer information finns i pglogical-dokumentationen .

   GRANT azure_pg_admin to myUser;
   ALTER ROLE myUser REPLICATION LOGIN;
   

3. På providerdatabasservern (källa/utgivare) skapar du providernoden.

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

4. Skapa en replikeringsuppsättning.

   select pglogical.create_replication_set('myreplicationset');
   

5. Lägg till alla tabeller i databasen i replikeringsuppsättningen.

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

   Som en alternativ metod kan du också lägga till tabeller från ett specifikt schema (till exempel testUser) till en standardreplikeringsuppsättning.

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

6. Skapa en prenumerantnod på prenumerantdatabasservern .

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

7. Skapa en prenumeration för att starta synkroniseringen och replikeringsprocessen.

   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. Du kan sedan verifiera prenumerationsstatusen.

   SELECT subscription_name, status FROM pglogical.show_subscription_status();
   

Varning

Pglogical stöder för närvarande inte en automatisk DDL-replikering. Det första schemat kan kopieras manuellt med hjälp av pg_dump --schema-only. DDL-instruktioner kan köras på providern och prenumeranten samtidigt med hjälp av funktionen pglogical.replicate_ddl_command. Tänk på andra begränsningar i tillägget som anges här.

Logisk avkodning

Logisk avkodning kan användas via strömningsprotokollet eller SQL-gränssnittet.

Direktuppspelningsprotokoll

Att använda ändringar med strömningsprotokollet är ofta att föredra. Du kan skapa en egen konsument/anslutningsapp eller använda en tjänst från tredje part som Debezium.

Besök wal2json-dokumentationen för ett exempel med hjälp av strömningsprotokollet med pg_recvlogical.

SQL-gränssnitt

I exemplet nedan använder vi SQL-gränssnittet med plugin-programmet wal2json.

1. Skapa ett fack.

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

2. Utfärda SQL-kommandon. Till exempel:

   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. Förbruka ändringarna.

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

   Utdata ser ut så här:

   {
         "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. Släpp facket när du är klar med det.

   SELECT pg_drop_replication_slot('test_slot');
   

Gå till PostgreSQL-dokumentationen om du vill veta mer om logisk avkodning.

Övervaka

Du måste övervaka logisk avkodning. Alla oanvända replikeringsfack måste tas bort. Facken håller fast vid Postgres WAL-loggar och relevanta systemkataloger tills ändringarna har lästs. Om din prenumerant eller konsument misslyckas eller om den är felaktigt konfigurerad staplas de obekräftade loggarna upp och fyller lagringen. Dessutom ökar obekräftade loggar risken för transaktions-ID-omslutning. Båda situationerna kan göra att servern blir otillgänglig. Därför måste logiska replikeringsfack användas kontinuerligt. Om ett logiskt replikeringsfack inte längre används släpper du det omedelbart.

Kolumnen "aktiv" i pg_replication_slots vyn anger om det finns en konsument som är ansluten till ett fack.

SELECT * FROM pg_replication_slots;

Ange aviseringar om maximalt använda transaktions-ID :t och lagringsanvändningen i Azure Database for PostgreSQL – flexibla servermått för att meddela dig när värdena ökar över normala tröskelvärden.

Begränsningar

• Begränsningar för logisk replikering gäller enligt beskrivningen här.

• Fack och HA-redundans – När du använder servrar med hög tillgänglighet (HA) med Azure Database for PostgreSQL – flexibel server bör du vara medveten om att logiska replikeringsplatser inte bevaras under redundansväxlingar. För att upprätthålla logiska replikeringsfack och säkerställa datakonsekvens efter en redundansväxling rekommenderar vi att du använder tillägget PG-redundansfack. Mer information om hur du aktiverar det här tillägget finns i dokumentationen.

Viktigt!

Du måste släppa det logiska replikeringsfacket på den primära servern om motsvarande prenumerant inte längre finns. Annars ackumuleras WAL-filerna i den primära filen och fyller lagringen. Anta att lagringströskelvärdet överskrider ett visst tröskelvärde och att det logiska replikeringsfacket inte används (på grund av en icke-tillgänglig prenumerant). I så fall släpper Azure Database for PostgreSQL flexibel serverinstans automatiskt det oanvända logiska replikeringsfacket. Den åtgärden släpper ackumulerade WAL-filer och undviker att servern blir otillgänglig på grund av att lagringen blir fylld.

Relaterat innehåll

• nätverksalternativ
• Tillägg
• hög tillgänglighet