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 server flessibile supporta le metodologie di estrazione e replica dei dati logiche seguenti:

1. Replica logica

   1. Uso della replica logica nativa di PostgreSQL per replicare gli 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 fornisce 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 decodificando il contenuto del log write-ahead (WAL).

Confrontare la replica logica e la decodifica logica

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

• Consente di replicare i dati da Postgres.

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

• Usare gli slot di replica logica per inviare i dati. Uno slot rappresenta un flusso di modifiche.

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

• Non replicare 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 wal_level del server su logical.

3. Se si vuole usare un'estensione pglogical, cercare i shared_preload_librariesparametri e azure.extensions e selezionare pglogical dalla casella di riepilogo a discesa.

4. Aggiornare il max_worker_processes valore del parametro 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 applicare le modifiche.

6. Verificare che l'istanza del server flessibile 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. È possibile assicurarsi che il ruolo in uso disponga dei privilegi per lo schema che si sta replicando. In caso contrario, è possibile che si verifichino errori come Permission denied for schema.

Nota

È sempre consigliabile separare l'utente di replica dall'account amministratore normale.

Usare la replica logica e la decodifica logica

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

Replica logica nativa

La replica logica usa i termini 'publisher' e 'subscriber'.

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

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

1. Connessione al database del 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. Connessione 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 risiedono nella stessa istanza del server flessibile Database di Azure per PostgreSQL, è essenziale seguire linee guida specifiche per evitare restrizioni di implementazione attualmente presenti. A partire dal momento, 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 CREATE SUBSCRIPTION chiamata si blocca su un evento di LibPQWalReceiverReceive attesa. 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 pg_create_logical_replication_slot funzione , che consente di evitare il problema spordo 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 impostata su false per impedire Database di Azure per PostgreSQL server flessibile di creare un nuovo slot e specificare correttamente il nome dello slot creato nel passaggio precedente. Prima di eseguire il comando, sostituire i segnaposto nel 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 replicato 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 altri dettagli, vedere la documentazione relativa all'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 (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 pglogical.

   GRANT azure_pg_admin to myUser;
   ALTER ROLE myUser REPLICATION LOGIN;
   

3. Nel server di database del 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=myPassword');
   

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

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

8. È quindi possibile verificare lo stato della sottoscrizione.

   SELECT subscription_name, status FROM pglogical.show_subscription_status();
   

Attenzione

Pglogical attualmente non supporta una replica DDL automatica. Lo schema iniziale può essere copiato manualmente usando pg_dump --schema-only. Le istruzioni DDL possono essere eseguite contemporaneamente nel provider e nel sottoscrittore usando la funzione pglogical.replicate_ddl_command. Tenere presente altre limitazioni dell'estensione elencate qui.

Decodifica logica

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

Protocollo di streaming

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

Per un esempio sull'uso del protocollo di streaming con pg_recvlogical, vedere la documentazione di wal2json.

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. Eseguire comandi 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 'active' nella pg_replication_slots vista indica se è presente un consumer connesso a uno slot.

SELECT * FROM pg_replication_slots;

Impostare gli avvisi sugli ID transazione usati massimo e Archiviazione usate Database di Azure per PostgreSQL metriche del server flessibili per notificare quando i valori aumentano le soglie normali.

Limiti

• Le limitazioni della replica logica si applicano come documentato qui.

• Slot e failover a disponibilità elevata: quando si usano server abilitati per la disponibilità elevata con Database di Azure per PostgreSQL server flessibile, 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.

Importante

Se il sottoscrittore corrispondente non esiste più, è necessario eliminare lo slot di replica logica nel server primario. In caso contrario, i file WAL si accumulano nel database primario, riempiendo lo spazio di archiviazione. Si supponga che la soglia di archiviazione superi una determinata soglia e che lo slot di replica logica non sia in uso (a causa di un sottoscrittore non disponibile). In tal caso, l'istanza del server flessibile Database di Azure per PostgreSQL elimina automaticamente lo slot di replica logica inutilizzato. Questa azione rilascia i file WAL accumulati ed evita che il server diventi non disponibile a causa di una situazione di riempimento dello spazio di archiviazione.

Contenuto correlato

• opzioni di rete
• Estensioni
• disponibilità elevata