Replica logica e decodifica logica in Database di Azure per PostgreSQL - Server flessibile

SI APPLICA A: Database di Azure per PostgreSQL - Server flessibile

Database di Azure per PostgreSQL - Il server flessibile supporta le metodologie di estrazione e replica dei dati logici seguenti:

1. Replica logica

   1. Uso della replica logica nativa di PostgreSQL per replicare oggetti dati. La replica logica consente un controllo granulare sulla replica dei dati, inclusa la replica dei dati a livello di tabella.
   2. Uso dell'estensione pglogical che offre la replica di streaming logica e altre funzionalità, ad esempio la copia dello schema iniziale del database, il supporto per TRUNCATE, la possibilità di replicare DDL e così via.

2. Decodifica logica implementata tramite la decodifica del contenuto del log write-ahead (WAL).

Confrontare la replica logica e la decodifica logica

La replica logica e la decodifica logica presentano diverse analogie. Entrambe:

• Consentono di replicare i dati da Postgres.

• Usano il log write-ahead (WAL) come origine delle modifiche.

• Usano slot di replica logica per inviare dati. Uno slot rappresenta un flusso di modifiche.

• Usano la proprietà REPLICA IDENTITY di una tabella per determinare quali modifiche possono essere inviate.

• Non replicano le modifiche DDL.

Le due tecnologie presentano differenze:

Replica logica:

• Consente di specificare una tabella o un set di tabelle da replicare.

Decodifica logica:

• Estrae le modifiche in tutte le tabelle di un database.

Prerequisiti per la replica logica e la decodifica logica

1. Passare alla pagina dei parametri del server nel portale.

2. Impostare il parametro del server wal_level su logical.

3. Per usare un'estensione pglogical, cercare i parametri shared_preload_libraries e azure.extensions e selezionare pglogical dalla casella di riepilogo a discesa.

4. Aggiornare il valore del parametro max_worker_processes ad almeno 16. In caso contrario, potrebbero verificarsi problemi come WARNING: out of background worker slots.

5. Salvare le modifiche e riavviare il server per applicarle.

6. Verificare che l'istanza del server flessibile di Database di Azure per PostgreSQL consenta il traffico di rete dalla risorsa di connessione.

7. Concedere le autorizzazioni di replica dell'utente amministratore.

   ALTER ROLE <adminname> WITH REPLICATION;
   

8. Potrebbe essere necessario assicurarsi che il ruolo in uso disponga di privilegi nello schema che si sta replicando. In caso contrario, è possibile che si verifichino errori come Permission denied for schema.

Usare replica logica e decodifica logica

L'uso della replica logica nativa è il modo più semplice per replicare i dati dal server flessibile di Database di Azure per PostgreSQL. È possibile usare l'interfaccia SQL o il protocollo di streaming per utilizzare le modifiche. È anche possibile usare l'interfaccia SQL per utilizzare le modifiche tramite la decodifica logica.

Replica logica nativa

La replica logica usa i termini "server di pubblicazione" e "sottoscrittore".

• Il server di pubblicazione è il server flessibile di Database di Azure per PostgreSQL da cui si inviano dati.
• Il sottoscrittore è il database del server flessibile di Database di Azure per PostgreSQL a cui si inviano dati.

Ecco un codice di esempio che è possibile usare per provare la replica logica.

1. Connettersi al server di pubblicazione. Creare una tabella e aggiungere alcuni dati.

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

2. Creare una pubblicazione per la tabella.

   CREATE PUBLICATION pub FOR TABLE basic;
   

3. Connettersi al database sottoscrittore. Creare una tabella con lo stesso schema del server di pubblicazione.

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

4. Creare una sottoscrizione che si connette alla pubblicazione creata in precedenza.

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

5. È ora possibile eseguire una query sulla tabella nel sottoscrittore. Si noterà che sono stati ricevuti dati dall'editore.

   SELECT * FROM basic;
   

   È possibile aggiungere altre righe alla tabella del server di pubblicazione e visualizzare le modifiche nel sottoscrittore.

   Se non è possibile visualizzare i dati, abilitare il privilegio di accesso per azure_pg_admin e controllare il contenuto della tabella.

   ALTER ROLE azure_pg_admin login;
   

Per altre informazioni sulla replica logica, vedere la documentazione di PostgreSQL.

Usare la replica logica tra database nello stesso server

Quando si intende configurare la replica logica tra database diversi che si trovano nella stessa istanza del server flessibile di Database di Azure per PostgreSQL, è essenziale seguire linee guida specifiche per evitare restrizioni di implementazione attualmente presenti. Attualmente, la creazione di una sottoscrizione che si connette allo stesso cluster di database avrà esito positivo solo se lo slot di replica non viene creato nello stesso comando; in caso contrario, la chiamata CREATE SUBSCRIPTION si blocca su un evento di attesa LibPQWalReceiverReceive. Ciò si verifica a causa di una restrizione esistente all'interno del motore Postgres, che potrebbe essere rimossa nelle versioni future.

Per configurare in modo efficace la replica logica tra i database "di origine" e "di destinazione" nello stesso server e aggirare questa restrizione, attenersi alla procedura descritta di seguito:

Creare prima di tutto una tabella denominata "basic" con uno schema identico nei database di origine e di destinazione:

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

Successivamente, nel database di origine creare una pubblicazione per la tabella e creare separatamente uno slot di replica logica usando la funzione pg_create_logical_replication_slot, che consente di evitare il problema di blocco che si verifica in genere quando lo slot viene creato nello stesso comando della sottoscrizione. È necessario usare il plug-in pgoutput:

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

Successivamente, nel database di destinazione creare una sottoscrizione alla pubblicazione creata in precedenza, assicurandosi che create_slot sia impostato su false per impedire al server flessibile di Database di Azure per PostgreSQL di creare un nuovo slot e specificare correttamente il nome dello slot creato nel passaggio precedente. Prima di eseguire il comando, sostituire i segnaposto nella stringa di connessione con le credenziali effettive del database:

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

Dopo aver configurato la replica logica, è ora possibile testarla inserendo un nuovo record nella tabella "basic" nel database di origine e quindi verificando che venga replicata nel database di destinazione:

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

-- Run this on the target database
TABLE basic;

Se tutti gli elementi sono configurati correttamente, è necessario verificare il nuovo record dal database di origine nel database di destinazione, confermando la corretta configurazione della replica logica.

estensione pglogical

Di seguito è riportato un esempio di configurazione pglogical nel server di database del provider e nel sottoscrittore. Per altre informazioni, vedere la Documentazione dell'estensione pglogical. Assicurarsi inoltre di aver eseguito le attività dei prerequisiti elencate in precedenza.

1. Installare l'estensione pglogical nel database sia nel provider che nei server di database del sottoscrittore.

   \c myDB
   CREATE EXTENSION pglogical;
   

2. Se l'utente di replica è diverso dall'utente di amministrazione del server (colui che ha creato il server), assicurarsi di concedere l'appartenenza a un ruolo azure_pg_admin all'utente e assegnare attributi REPLICATION e LOGIN all'utente. Per informazioni dettagliate, vedere la documentazione di pglogical.

   GRANT azure_pg_admin to myUser;
   ALTER ROLE myUser REPLICATION LOGIN;
   

3. Nel server di database provider (origine/server di pubblicazione) creare il nodo del provider.

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

4. Creare un set di replica.

   select pglogical.create_replication_set('myreplicationset');
   

5. Aggiungere tutte le tabelle nel database al set di replica.

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

   Come metodo alternativo, è anche possibile aggiungere tabelle da uno schema specifico, ad esempio testUser, a un set di replica predefinito.

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

6. Nel server di database sottoscrittore creare un nodo sottoscrittore.

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

7. Creare una sottoscrizione per avviare la sincronizzazione e il processo di replica.

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

8. È quindi possibile verificare lo stato della sottoscrizione.

   SELECT subscription_name, status FROM pglogical.show_subscription_status();
   

Decodifica logica

La decodifica logica può essere utilizzata tramite il protocollo di streaming o l'interfaccia SQL.

Protocollo di flusso

L'utilizzo delle modifiche tramite il protocollo di streaming è spesso preferibile. È possibile creare un consumer o un connettore personalizzato oppure usare un servizio di terze parti come Debezium.

Consultare la documentazione di wal2json per un esempio relativo all'uso del protocollo di streaming con pg_recvlogical.

Interfaccia SQL

Nell'esempio seguente viene usata l'interfaccia SQL con il plug-in wal2json.

1. Creare uno slot.

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

2. Immettere comandi T-SQL. Ad esempio:

   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. Utilizzare le modifiche.

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

   L'output sarà simile al seguente:

   {
         "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. Eliminare lo slot dopo averlo usato.

   SELECT pg_drop_replication_slot('test_slot');
   

Per altre informazioni sulla decodifica logica, vedere la documentazione di PostgreSQL.

Monitoraggio

È necessario monitorare la decodifica logica. Qualsiasi slot di replica inutilizzato deve essere eliminato. Gli slot sono in attesa nei log WAL Postgres e nei cataloghi di sistema pertinenti fino a quando non vengono lette le modifiche. Se il sottoscrittore o il consumer non riesce o se è configurato in modo non corretto, i log non utilizzati si accumulano e riempiono lo spazio di archiviazione. Inoltre, i log non utilizzati aumentano il rischio di wrapping dell'ID transazione. Entrambe le situazioni possono causare la mancata disponibilità del server. Pertanto, gli slot di replica logica devono essere utilizzati in modo continuo. Se uno slot di replica logica non viene più usato, eliminarlo immediatamente.

La colonna "attiva" nella visualizzazione pg_replication_slots indica se è presente un consumer connesso a uno slot.

SELECT * FROM pg_replication_slots;

Impostare avvisi sulle metriche ID transazioni usati massimi e Archiviazione usata del server flessibile di Database di Azure per PostgreSQL per ricevere avvisi quando i valori aumentano oltre le soglie normali.

Limiti

• Si applicano limitazioni della replica logica come documentato qui.

• Slot e failover a disponibilità elevata: quando si usano server abilitati per la disponibilità elevata con il server flessibile di Database di Azure per PostgreSQL, tenere presente che gli slot di replica logica non vengono mantenuti durante gli eventi di failover. Per mantenere gli slot di replica logici e garantire la coerenza dei dati dopo un failover, è consigliabile usare l'estensione Slot di failover PG. Per altre informazioni sull'abilitazione di questa estensione, vedere la documentazione.

Contenuto correlato

• opzioni di rete
• estensioni
• disponibilità elevata