Replica logica e decodifica logica nel server flessibile di Database di Azure per PostgreSQL

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

1. Replica logica

   1. Utilizzo della replica logica nativa di PostgreSQL per replicare oggetti dati. La replica logica offre 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 altro ancora.

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. 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 nel portale.

2. Impostare il parametro wal_level su logical.

3. Se vuoi utilizzare l'estensione pglogical, cerca i parametri shared_preload_libraries e azure.extensions e seleziona pglogical dal menu 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. Verifica che il server flessibile di Database di Azure per PostgreSQL consenta il traffico di rete proveniente dalla risorsa da cui si effettua la connessione.

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

   ALTER ROLE <adminname> WITH REPLICATION;
   

8. Assicurarsi che il ruolo che si sta utilizzando disponga dei privilegi sullo 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 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 usando la decodifica logica.

Replica logica nativa

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

• Il server di pubblicazione è il database del server flessibile di Database di Azure per PostgreSQL che invia i dati.
• Il sottoscrittore è il database del server flessibile di Database di Azure per PostgreSQL che riceve i 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 riceve i 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, passare a un utente membro del azure_pg_admin ruolo e controllare il contenuto della tabella.

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

Usare la replica logica tra database nello stesso server

Per configurare la replica logica tra database diversi nello stesso server flessibile Database di Azure per PostgreSQL, seguire linee guida specifiche per evitare restrizioni di implementazione. Attualmente, è possibile creare una sottoscrizione che si connette allo stesso cluster di database solo se lo slot di replica non viene creato nello stesso comando. In caso contrario, la chiamata CREATE SUBSCRIPTION si blocca in un LibPQWalReceiverReceiveevento di attesa. Questo comportamento è dovuto a una restrizione esistente all'interno del motore Postgres, che potrebbe essere rimossa nelle versioni future.

Per configurare la replica logica tra i database "di origine" e "di destinazione" nello stesso server evitando questa restrizione, seguire questa procedura:

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, separatamente, creare uno slot di replica logica utilizzando la funzione pg_create_logical_replication_slot. Questo approccio aiuta a prevenire il problema di blocco che si verifica in genere quando lo slot viene creato nello stesso comando della sottoscrizione. 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');

Quindi, nel database di destinazione creare una sottoscrizione alla pubblicazione creata in precedenza. Impostare create_slot su false per impedire al server flessibile di Database di Azure per PostgreSQL di creare un nuovo slot e specificare 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, testarla inserendo un nuovo record nella basic tabella 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, viene visualizzato 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 anche di completare le attività dei prerequisiti elencate in precedenza.

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

   \c myDB
   CREATE EXTENSION pglogical;
   

2. Se l'utente di replica non è l'utente di amministrazione del server (l'utente che ha creato il server), concedere all'utente l'appartenenza al azure_pg_admin ruolo e assegnare gli 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. Verificare lo stato della sottoscrizione.

   SELECT subscription_name, status FROM pglogical.show_subscription_status();
   

Decodifica logica

È possibile utilizzare la decodifica logica tramite il protocollo di streaming o l'interfaccia SQL.

Protocollo di flusso

Elaborare le modifiche utilizzando il protocollo di streaming è spesso preferibile. È possibile creare un consumer o un connettore personalizzato oppure usare un servizio di terze parti come Debezium.

Per un esempio che usa il protocollo di streaming con pg_recvlogical, vedere la documentazione di wal2json: un esempio che usa il protocollo di streaming con pg_recvlogical.

Interfaccia SQL

Nell'esempio seguente usare 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. Per 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 è 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. Rilascia lo slot quando hai finito di usarlo.

   SELECT pg_drop_replication_slot('test_slot');
   

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

Monitor

È necessario monitorare la decodifica logica. Eliminare qualsiasi slot di replica inutilizzato. Gli slot conservano i log WAL di PostgreSQL e i relativi cataloghi di sistema finché le modifiche non sono state lette. 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, è necessario utilizzare continuamente gli slot di replica logica. Se uno slot di replica logica non viene più usato, eliminarlo immediatamente.

La colonna active nella vista pg_replication_slots indica se a uno slot è collegato un consumer.

SELECT * FROM pg_replication_slots;

Imposta avvisi per le metriche ID transazione massimi usati e Spazio di archiviazione utilizzato, in modo da ricevere notifiche quando i valori aumentano oltre le soglie normali.

Limitazioni

• Si applicano limitazioni alla replica logica come documentato qui.

• Slot e failover HA - In PostgreSQL 16 e versioni precedenti, quando si utilizzano server con disponibilità elevata (HA) abilitata in Database di Azure per PostgreSQL, gli slot di replica logica non vengono conservati durante gli eventi di failover. Per mantenere gli slot di replica logica e garantire la coerenza dei dati dopo un failover, utilizza l'estensione PG Failover Slots e configura le impostazioni di supporto come hot_standby_feedback = on. Per altre informazioni sull'abilitazione di questa estensione, vedere la documentazione.

Supporto per il failover degli slot di replica logica

Per PostgreSQL 17 e versioni successive, la sincronizzazione degli slot è supportata in modo nativo. Se si abilitano le configurazioni PostgreSQL corrette (sync_replication_slots, hot_standby_feedback), gli slot di replica logica vengono mantenuti automaticamente dopo il failover e non è necessaria alcuna estensione.

Contenuti correlati

• Regole del firewall in Database di Azure per PostgreSQL.
• Panoramica della rete per Database di Azure per PostgreSQL con accesso pubblico.
• Integrazione della rete virtuale in Database di Azure per PostgreSQL.
• Come utilizzare le estensioni.
• Disponibilità elevata in Database di Azure per PostgreSQL.