Utilizzo di pglogical per sincronizzare i dati tra le istanze

Tutte le versioni di Aurora PostgreSQL attualmente disponibili supportano l'estensione pglogical, che precede la funzionalità di replica logica funzionalmente simile introdotta nella versione 10 di PostgreSQL. Per ulteriori informazioni, consulta Utilizzo della replica logica di PostgreSQL con Aurora.

L'estensione pglogical supporta la replica logica tra due o più cluster database Aurora PostgreSQL. Supporta anche la replica tra diverse versioni di PostgreSQL e tra database in esecuzione in istanze database RDS per PostgreSQL e cluster database Aurora PostgreSQL. L'estensione pglogical utilizza un modello publish-subscribe per replicare le modifiche apportate alle tabelle e ad altri oggetti, come le sequenze, da un publisher in un subscriber. Si basa su uno slot di replica per garantire la sincronizzazione delle modifiche da un nodo publisher a un nodo subscriber, definiti come indicato di seguito.

• Il nodo publisher è il cluster database Aurora PostgreSQL che costituisce l'origine dei dati da replicare in altri nodi. Il nodo publisher definisce le tabelle da replicare in un set di pubblicazione.

• Il nodo subscriber è il cluster database Aurora PostgreSQL che riceve gli aggiornamenti WAL dal publisher. Il subscriber crea una sottoscrizione per connettersi al publisher e ottenere i dati WAL decodificati e contemporaneamente nel nodo publisher viene creato lo slot di replica.

Di seguito sono riportati gli argomenti sull'impostazione dell'estensione pglogical.

Requisiti e limitazioni dell'estensione pglogical

Tutte le versioni attualmente disponibili di Aurora PostgreSQL supportano l'estensione pglogical.

Sia il nodo publisher che il nodo subscriber devono essere impostati per la replica logica.

Le tabelle che devono essere replicate dal subscriber nel publisher devono avere gli stessi nomi e lo stesso schema. Inoltre devono contenere le stesse colonne e le colonne devono utilizzare gli stessi tipi di dati. Le tabelle del publisher e del subscriber devono avere le stesse chiavi primarie. Si consiglia di utilizzare solo la CHIAVE PRIMARIA come vincolo univoco.

Le tabelle del nodo subscriber possono avere vincoli più permissivi rispetto ai vincoli CHECK e NOT NULL delle tabelle del nodo publisher.

L'estensione pglogical fornisce funzionalità, come la replica bidirezionale, che non sono supportate dalla funzionalità di replica logica integrata in PostgreSQL 10 e versioni successive. Per ulteriori informazioni, consulta PostgreSQL bi-directional replication using pglogical (Replica bidirezionale di PostgreSQL utilizzando pglogical).

Impostazione dell'estensione pglogical

Per impostare l'estensione pglogical per il cluster database Aurora PostgreSQL, aggiungi pglogical alle librerie condivise nel gruppo di parametri del·cluster database personalizzato per il cluster database Aurora PostgreSQL. È inoltre necessario impostare il valore del parametro rds.logical_replication su 1 per attivare la decodifica logica. Infine, crei l'estensione nel database. Per queste attività puoi utilizzare la AWS Management Console o AWS CLI.

Per eseguire queste attività sono richieste le autorizzazioni del ruolo rds_superuser.

Le fasi seguenti si basano sull'ipotesi che il cluster database Aurora PostgreSQL sia associato a un gruppo di parametri cluster di database personalizzato. Per informazioni sulla creazione di un gruppo di parametri del·cluster database, consulta Utilizzo di gruppi di parametri.

Console

Per impostare l'estensione pglogical

1. Accedi alla AWS Management Console e apri la console Amazon RDS all'indirizzo https://console.aws.amazon.com/rds/.

2. Nel riquadro di navigazione, scegli l'istanza di scrittura del cluster database Aurora PostgreSQL .

3. Apri la scheda Configurazione per l'istanza di scrittura del cluster database Aurora PostgreSQL. Tra i dettagli dell'istanza, individua il collegamento Parameter group (Gruppo di parametri).

4. Scegli il collegamento per aprire i parametri personalizzati associati al cluster database Aurora PostgreSQL.

5. Nel campo di ricerca Parametri, digita shared_pre per trovare il parametro shared_preload_libraries.

6. Scegli Edit parameters (Modifica parametri) per accedere ai valori delle proprietà.

7. Aggiungi pglogical all'elenco nel campo Values (Valori). Utilizza una virgola per separare gli elementi nell'elenco di valori.

   

8. Individua il parametro rds.logical_replication e impostalo su 1 per attivare la replica logica.

9. Riavvia l'istanza di scrittura del cluster database Aurora PostgreSQL per rendere effettive le modifiche.

10. Quando l'istanza è disponibile, puoi utilizzare psql (o pgAdmin) per connetterti all'istanza di scrittura del cluster database Aurora PostgreSQL.

    psql --host=111122223333.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=labdb
    

11. Per verificare che l'estensione pglogical sia inizializzata, esegui il seguente comando.

    SHOW shared_preload_libraries;
    shared_preload_libraries 
    --------------------------
    rdsutils,pglogical
    (1 row) 

12. Verifica l'impostazione che abilita la decodifica logica, come indicato di seguito.

    SHOW wal_level;
    wal_level
    -----------
     logical
    (1 row)

13. Crea l'estensione, come indicato di seguito.

    CREATE EXTENSION pglogical;
    EXTENSION CREATED

14. Seleziona Salvataggio delle modifiche.

15. Apri la console di Amazon RDS all'indirizzo https://console.aws.amazon.com/rds/.

16. Scegli l'istanza di scrittura del cluster database Aurora PostgreSQL dall'elenco di database per selezionarla, quindi scegli Reboot (Riavvia) dal menu Actions (Operazioni).

AWS CLI

Per impostare l'estensione pglogical

Per configurare pglogical utilizzandoAWS CLI, si chiama l'modify-db-parameter-groupoperazione per modificare determinati parametri nel gruppo di parametri personalizzato, come illustrato nella procedura seguente.

1. Utilizza il seguente comando AWS CLI per aggiungere pglogical al parametro shared_preload_libraries.

   aws rds modify-db-parameter-group \
      --db-parameter-group-name custom-param-group-name \
      --parameters "ParameterName=shared_preload_libraries,ParameterValue=pglogical,ApplyMethod=pending-reboot" \
      --region aws-region

2. Utilizza il seguente comando AWS CLI per impostare rds.logical_replication su 1 per attivare la funzionalità di decodifica logica per l'istanza di scrittura del cluster database Aurora PostgreSQL.

   aws rds modify-db-parameter-group \
      --db-parameter-group-name custom-param-group-name \
      --parameters "ParameterName=rds.logical_replication,ParameterValue=1,ApplyMethod=pending-reboot" \
      --region aws-region

3. Utilizza il seguente comando AWS CLI per riavviare l'istanza di scrittura del cluster database Aurora PostgreSQL in modo che la libreria pglogical venga inizializzata.

   aws rds reboot-db-instance \
       --db-instance-identifier writer-instance \
       --region aws-region

4. Quando l'istanza è disponibile, utilizza psql per connetterti all'istanza di scrittura del cluster database Aurora PostgreSQL.

   psql --host=111122223333.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=labdb
   

5. Crea l'estensione, come indicato di seguito.

   CREATE EXTENSION pglogical;
   EXTENSION CREATED

6. Riavvia l'istanza di scrittura del cluster database Aurora PostgreSQL utilizzando il comando AWS CLI seguente.

   aws rds reboot-db-instance \
       --db-instance-identifier writer-instance \
       --region aws-region

Impostazione della replica logica per il cluster database Aurora PostgreSQL

La seguente procedura mostra come avviare la replica logica tra due cluster database Aurora PostgreSQL. I passaggi presuppongono che sia l'origine (publisher) che la destinazione (subscriber) abbiano l'estensione pglogical impostata come descritto dettagliatamente in Impostazione dell'estensione pglogical.

Per creare il nodo publisher e definire le tabelle da replicare

Questi passaggi presuppongono che il cluster database Aurora PostgreSQL abbia un'istanza di scrittura con un database contenente una o più tabelle che desideri replicare in un altro nodo. È necessario ricreare la struttura delle tabelle dal publisher nel subscriber, quindi prima, se occorre, recupera la struttura delle tabelle. Puoi farlo utilizzando il metacomando psq1 \d tablename e quindi creando la stessa tabella nell'istanza subscriber. Nella procedura seguente viene illustrato come creare una tabella di esempio nel publisher (origine) a scopo dimostrativo.

1. Utilizza psql per connetterti all'istanza che include la tabella da usare come origine per i subscriber.

   psql --host=source-instance.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=labdb
   

   Se non hai una tabella esistente da replicare, puoi creare una tabella di esempio come indicato di seguito.

   1. Crea una tabella di esempio utilizzando la seguente istruzione SQL.

      CREATE TABLE docs_lab_table (a int PRIMARY KEY);

   2. Popola la tabella con i dati generati utilizzando la seguente istruzione SQL.

      INSERT INTO docs_lab_table VALUES (generate_series(1,5000));
      INSERT 0 5000

   3. Verifica che i dati siano presenti nella tabella utilizzando la seguente istruzione SQL.

      SELECT count(*) FROM docs_lab_table;

2. Identifica il cluster database Aurora PostgreSQL come nodo publisher, come indicato di seguito.

   SELECT pglogical.create_node(
       node_name := 'docs_lab_provider',
       dsn := 'host=source-instance.aws-region.rds.amazonaws.com port=5432 dbname=labdb');
    create_node
   -------------
      3410995529
   (1 row)

3. Aggiungi la tabella da replicare al set di replica predefinito. Per ulteriori informazioni sui set di replica, consulta Replication sets (Set di replica) nella documentazione di pglogical.

   SELECT pglogical.replication_set_add_table('default', 'docs_lab_table', 'true', NULL, NULL);
    replication_set_add_table
     ---------------------------
     t
     (1 row)

L'impostazione del nodo publisher è completata. Ora puoi impostare il nodo subscriber per ricevere gli aggiornamenti dal publisher.

Per impostare il nodo subscriber e creare una sottoscrizione per ricevere gli aggiornamenti

Questi passaggi presuppongono che sia stata eseguita l'impostazione del cluster database Aurora PostgreSQL con l'estensione pglogical. Per ulteriori informazioni, consulta Impostazione dell'estensione pglogical.

1. Utilizza psql per connetterti all'istanza per cui vuoi ricevere gli aggiornamenti dal publisher.

   psql --host=target-instance.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=labdb
   

2. Nel cluster database Aurora PostgreSQL del subscriber crea la stessa tabella presente nel publisher. In questo esempio, la tabella è docs_lab_table. È possibile creare la tabella come indicato di seguito.

   CREATE TABLE docs_lab_table (a int PRIMARY KEY);

3. Verifica che questa tabella sia vuota.

   SELECT count(*) FROM docs_lab_table;
    count
   -------
     0
   (1 row)

4. Identifica il cluster database Aurora PostgreSQL come nodo subscriber, come indicato di seguito.

   SELECT pglogical.create_node(
       node_name := 'docs_lab_target',
       dsn := 'host=target-instance.aws-region.rds.amazonaws.com port=5432 sslmode=require dbname=labdb user=postgres password=********');
    create_node
   -------------
      2182738256
   (1 row)

5. Crea la sottoscrizione.

   SELECT pglogical.create_subscription(
      subscription_name := 'docs_lab_subscription',
      provider_dsn := 'host=source-instance.aws-region.rds.amazonaws.com port=5432 sslmode=require dbname=labdb user=postgres password=*******',
      replication_sets := ARRAY['default'],
      synchronize_data := true,
      forward_origins := '{}' );  
    create_subscription
   ---------------------
   1038357190
   (1 row)

   Una volta completato questo passaggio, i dati della tabella del publisher vengono creati nella tabella del subscriber. È possibile verificare questa operazione utilizzando la seguente query SQL.

   SELECT count(*) FROM docs_lab_table;
    count
   -------
     5000
   (1 row)

Da questo momento in poi, le modifiche apportate alla tabella del publisher vengono replicate nella tabella del subscriber.

Riconnessione della replica logica dopo un aggiornamento principale

Prima di poter eseguire un aggiornamento della versione principale di un cluster database Aurora PostgreSQL impostata come nodo publisher per la replica logica, è necessario rimuovere tutti gli slot di replica, anche quelli non attivi. Si consiglia di deviare temporaneamente le transazioni del database dal nodo publisher, rimuovere gli slot di replica, aggiornare il cluster database Aurora PostgreSQL e quindi riconnettere e riavviare la replica.

Gli slot di replica sono ospitati solo nel nodo publisher. Il nodo subscriber Aurora PostgreSQL in uno scenario di replica logica non ha slot da rimuovere. Il processo di aggiornamento alla versione principale di Aurora PostgreSQL supporta l'aggiornamento del subscriber a una nuova versione principale di PostgreSQL indipendente dal nodo publisher. Tuttavia, il processo di aggiornamento interrompe il processo di replica e interferisce con la sincronizzazione dei dati WAL tra il nodo publisher e il nodo subscriber. È necessario riconnettere la replica logica tra publisher e subscriber dopo aver aggiornato il publisher, il subscriber o entrambi. Nella procedura seguente viene illustrato come determinare se la replica è stata interrotta e come risolvere il problema.

Determinazione della replica logica interrotta

È possibile determinare che il processo di replica è stato interrotto eseguendo una query sul nodo publisher o sul nodo subscriber, come indicato di seguito.

Per controllare il nodo publisher

• Utilizza psql per connetterti al nodo publisher e quindi esegui la query sulla funzione pg_replication_slots. Osserva il valore nella colonna attiva. Normalmente, la query restituisce t (true) per indicare che la replica è attiva. Se restituisce f (false), indica che la replica nel subscriber è stata interrotta.

  SELECT slot_name,plugin,slot_type,active FROM pg_replication_slots;
                      slot_name              |      plugin      | slot_type | active
  -------------------------------------------+------------------+-----------+--------
   pgl_labdb_docs_labcb4fa94_docs_lab3de412c | pglogical_output | logical   | f
  (1 row)

Per controllare il nodo subscriber

Nel nodo subscriber è possibile verificare lo stato della replica in tre modi diversi.

• Esamina i log di PostgreSQL sul nodo subscriber per trovare i messaggi di errore. Il log identifica gli errori nei messaggi che includono il codice di uscita 1, come illustrato di seguito.

  2022-07-06 16:17:03 UTC::@:[7361]:LOG: background worker "pglogical apply 16404:2880255011" (PID 14610) exited with exit code 1
  2022-07-06 16:19:44 UTC::@:[7361]:LOG: background worker "pglogical apply 16404:2880255011" (PID 21783) exited with exit code 1

• Esegui la query sulla funzione pg_replication_origin. Connettiti al database sul nodo subscriber utilizzando psql ed esegui la query sulla funzione pg_replication_origin, come indicato di seguito.

  SELECT * FROM pg_replication_origin;
   roident | roname
  ---------+--------
  (0 rows)

  Se il set di risultati è vuoto, la replica è stata interrotta. Normalmente, viene restituito l'output riportato di seguito.

     roident |                       roname
    ---------+----------------------------------------------------
           1 | pgl_labdb_docs_labcb4fa94_docs_lab3de412c
    (1 row)

• Esegui la query sulla funzione pglogical.show_subscription_status come illustrato nell'esempio seguente.

  SELECT subscription_name,status,slot_name FROM pglogical.show_subscription_status();
       subscription_name | status |              slot_name
  ---====----------------+--------+-------------------------------------
   docs_lab_subscription | down   | pgl_labdb_docs_labcb4fa94_docs_lab3de412c
  (1 row)

  Questo output mostra che la replica è stata interrotta. Lo stato è down e normalmente l'output mostra lo stato replicating.

Se il processo di replica logica è stato interrotto, è possibile riconnettere la replica seguendo questi passaggi.

Per riconnettere la replica logica tra i nodi publisher e subscriber

Per riconnettere la replica, devi prima disconnettere il subscriber dal nodo publisher e quindi riconnettere la sottoscrizione, come descritto in questi passaggi.

1. Connettiti al nodo subscriber utilizzando psql, come indicato di seguito.

   psql --host=222222222222.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=labdb

2. Disattiva la sottoscrizione utilizzando la funzione pglogical.alter_subscription_disable.

   SELECT pglogical.alter_subscription_disable('docs_lab_subscription',true);
    alter_subscription_disable
   ----------------------------
    t
   (1 row)

3. Ottieni l'identificatore del nodo publisher eseguendo la query su pg_replication_origin, come indicato di seguito.

   SELECT * FROM pg_replication_origin;
    roident |               roname
   ---------+-------------------------------------
          1 | pgl_labdb_docs_labcb4fa94_docs_lab3de412c
   (1 row)

4. Utilizza la risposta restituita dal passaggio precedente con il comando pg_replication_origin_create per assegnare l'identificatore che può essere usato dalla sottoscrizione una volta riconnessa.

   SELECT pg_replication_origin_create('pgl_labdb_docs_labcb4fa94_docs_lab3de412c');
     pg_replication_origin_create
   ------------------------------
                               1
   (1 row)

5. Attiva la sottoscrizione specificando il nome con lo stato true, come illustrato nell'esempio seguente.

   SELECT pglogical.alter_subscription_enable('docs_lab_subscription',true);
     alter_subscription_enable
   ---------------------------
    t
   (1 row)

Controlla lo stato del nodo. Lo stato deve essere replicating come mostrato nell'esempio.

SELECT subscription_name,status,slot_name
  FROM pglogical.show_subscription_status();
             subscription_name |   status    |              slot_name
-------------------------------+-------------+-------------------------------------
 docs_lab_subscription         | replicating | pgl_labdb_docs_lab98f517b_docs_lab3de412c
(1 row)

Verifica lo stato dello slot di replica del subscriber sul nodo publisher. La colonna active dello slot deve restituire t (true) per indicare che la replica è stata riconnessa.

SELECT slot_name,plugin,slot_type,active
  FROM pg_replication_slots;
                    slot_name              |      plugin      | slot_type | active
-------------------------------------------+------------------+-----------+--------
 pgl_labdb_docs_lab98f517b_docs_lab3de412c | pglogical_output | logical   | t
(1 row)

Gestione degli slot di replica logica per Aurora PostgreSQL

Prima di poter eseguire un aggiornamento alla versione principale su un'istanza di scrittura del cluster database Aurora PostgreSQL che funge da nodo publisher in uno scenario di replica logica, è necessario rimuovere gli slot di replica nell'istanza. Il processo di verifica preliminare dell'aggiornamento alla versione principale avvisa che l'aggiornamento non può procedere fino a quando gli slot non vengono rimossi.

Per identificare gli slot di replica creati utilizzando l'estensione pglogical, accedi a ciascun database e recupera il nome dei nodi. Quando esegui la query sul nodo subscriber, nell'output viene restituito sia il nodo publisher che il nodo subscriber, come mostrato nell'esempio seguente.

SELECT * FROM pglogical.node;
node_id   |     node_name
------------+-------------------
 2182738256 | docs_lab_target
 3410995529 | docs_lab_provider
(2 rows)

Puoi ottenere i dettagli sulla sottoscrizione con la seguente query.

SELECT sub_name,sub_slot_name,sub_target
  FROM pglogical.subscription;
 sub_name |         sub_slot_name          | sub_target
----------+--------------------------------+------------
  docs_lab_subscription     | pgl_labdb_docs_labcb4fa94_docs_lab3de412c | 2182738256
(1 row)

A questo punto puoi rimuovere la sottoscrizione, come indicato di seguito.

SELECT pglogical.drop_subscription(subscription_name := 'docs_lab_subscription');
 drop_subscription
-------------------
                 1
(1 row)

Dopo aver rimosso la sottoscrizione, puoi eliminare il nodo.

SELECT pglogical.drop_node(node_name := 'docs-lab-subscriber');
 drop_node
-----------
 t
(1 row)

Puoi verificare che il nodo sia stato eliminato, come indicato di seguito.

SELECT * FROM pglogical.node;
 node_id | node_name
---------+-----------
(0 rows)

Riferimento sui parametri dell'estensione pglogical

Nella tabella sono illustrati i parametri associati all'estensione pglogical. Parametri come pglogical.conflict_log_level e pglogical.conflict_resolution vengono utilizzati per gestire i conflitti di aggiornamento. I conflitti possono emergere quando vengono apportate modifiche localmente alle stesse tabelle che hanno una sottoscrizione con il publisher. I conflitti possono verificarsi anche in altri scenari, ad esempio la replica bidirezionale o quando più subscriber eseguono la replica dallo stesso publisher. Per ulteriori informazioni, consulta PostgreSQL bi-directional replication using pglogical (Replica bidirezionale di PostgreSQL utilizzando pglogical).

Parametro	Descrizione
pglogical.batch_inserts	Esegue inserimenti batch, se possibile. Non impostato per impostazione predefinita. Imposta "1" per attivarlo, "0" per disattivarlo.
pglogical.conflict_log_level	Imposta il livello di log da utilizzare per la registrazione dei conflitti risolti. I valori di stringa supportati sono debug5, debug4, debug3, debug2, debug1, info, notice, warning, error, log, fatal, panic.
pglogical.conflict_resolution	Imposta il metodo da utilizzare per risolvere i conflitti quando sono risolvibili. I valori di stringa supportati sono error, apply_remote, keep_local, last_update_wins, first_update_wins.
pglogical.extra_connection_options	Specifica le opzioni di connessione da aggiungere a tutte le connessioni dei nodi peer.
pglogical.synchronous_commit	Valore di commit sincrono specifico pglogical
pglogical.use_spi	Utilizza la SPI (Server Programming Interface) invece dell'API di basso livello per applicare le modifiche. Imposta "1" per attivarlo, "0" per disattivarlo. Per ulteriori informazioni sulla SPI, consulta Server Programming Interface nella documentazione PostgreSQL.