Come posso utilizzare la replica logica per replicare le tabelle tra le mie istanze database Amazon RDS per PostgreSQL?

Risoluzione

Amazon RDS per PostgreSQL supporta la replica logica con PostgreSQL 10.4 e versioni successive. Amazon Aurora compatibile con PostgreSQL 2.2.0 e versioni successive supporta la replica logica con PostgreSQL 10.6 e versioni successive.

Per ulteriori informazioni, consulta Esecuzione della replica logica per Amazon RDS per PostgreSQL.

La seguente risoluzione replica due tabelle di origine in due tabelle di destinazione.

Attiva la replica logica

Completa i seguenti passaggi:

1. Crea un gruppo di parametri personalizzato e imposta il parametro rds.logical_replication su 1.
   Nota: poiché il parametro rds.logical_replication è un parametro statico, devi riavviare l'istanza database. Dopo aver riavviato l'istanza database, il parametro wal_level diventa logical.
2. Associa il gruppo di parametri all'istanza database.
3. Esegui questa query per verificare che wal_level sia logical e rds.logical_replication sia attivo:

   SELECT name,setting FROM pg_settings WHERE name IN ('wal_level','rds.logical_replication');

   Output atteso:

             name           | setting
   -------------------------+---------
    rds.logical_replication | on
    wal_level               | logical
   (2 rows)

Crea le tabelle di origine e inserisci i dati

Connettiti all'istanza database PostgreSQL, quindi completa i seguenti passaggi nel database di origine:

1. Esegui questi comandi per creare le tabelle di origine:

   CREATE TABLE reptab1 (slno int primary key);  
   CREATE TABLE reptab2 (name varchar(20));

2. Esegui questi comandi per aggiungere dati nelle tabelle di origine:

   INSERT INTO reptab1 VALUES (generate_series(1,1000));  
   INSERT INTO reptab2 SELECT SUBSTR ('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',((random()*(36-1)+1)::integer),1) FROM generate_series(1,50);

Crea una pubblicazione per le tabelle di origine

Nel database di origine, completa i passaggi seguenti:

1. Esegui questo comando per creare una pubblicazione delle due tabelle:

   CREATE PUBLICATION testpub FOR TABLE reptab1, reptab2;

2. Esegui questa query per verificare che i dettagli della pubblicazione siano corretti:

   SELECT * FROM pg_publication;

   Output atteso:

     oid   | pubname | pubowner | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate | pubviaroot  
   --------+---------+----------+--------------+-----------+-----------+-----------+-------------+------------  
    115069 | testpub |    16395 | f            | t         | t         | t         | t           | f  
   (1 row)

3. Esegui questa query per verificare che le tabelle di origine siano presenti nella pubblicazione:

   SELECT * FROM pg_publication;

   Output atteso:

    pubname | schemaname | tablename  
   
   ---------+------------+-----------  
   
    testpub | public | reptab1  
   
    testpub | public | reptab2  
   
   (2 rows)

Connettiti al database di destinazione e crea le tabelle di destinazione

Nel database di destinazione, completa i seguenti passaggi:

1. Esegui questi comandi per creare le tabelle di destinazione:

   CREATE TABLE reptab1 (slno int primary key);  
   CREATE TABLE reptab2 (name varchar(20));

   Nota: utilizza gli stessi nomi delle tabelle di origine.

2. Esegui queste query per verificare che i dati non siano presenti nelle tabelle di destinazione.
   Tabella uno:

   SELECT count(*) FROM reptab1;

   Output atteso:

    count  
   -------  
        0  
   (1 row)

   Tabella due:

   SELECT count(*) FROM reptab2;

   Output atteso:

    count  
   -------  
        0  
   (1 row)

Crea una sottoscrizione nel database di destinazione

Prima di creare la sottoscrizione, esegui questi comandi per verificare di non aver memorizzato una versione in chiaro del nome utente e della password nei log del database:

SET log_min_messages to 'PANIC';  
SET log_statement to NONE;

Per creare la sottoscrizione nel database di destinazione, completa i seguenti passaggi:

1. Esegui questo comando per creare la sottoscrizione:

   CREATE SUBSCRIPTION testsub CONNECTION 'host=source RDS/host endpoint port=5432 dbname=source_db_name user=user password=password' PUBLICATION testpub;

   Nota: sostituisci source RDS/host endpoint con l'endpoint della tua istanza database di origine e source_db_name con il nome della tua istanza database. Sostituisci user con il tuo nome utente e password con la tua password.

2. Esegui questa query per verificare che la sottoscrizione sia attiva:

   SELECT oid,subname,subenabled,subslotname,subpublications FROM pg_subscription;

   Output atteso:

     oid  | subname | subenabled | subslotname | subpublications  
   -------+---------+------------+-------------+-----------------  
    16434 | testsub | t          | testsub     | {testpub}  
   (1 row)

3. Esegui queste query per verificare che i dati si trovino nelle tabelle di destinazione.
   Tabella uno:

   SELECT count(*) FROM reptab1;

   Output atteso:

    count  
   -------  
     1000  
   (1 row)

   Tabella due:

   SELECT count(*) FROM reptab2;

   Output atteso:

    count  
   -------  
       50  
   (1 row)

Verifica i dettagli dello slot di replica nel database di origine

Quando crei una sottoscrizione nel database di destinazione, il database di destinazione crea uno slot di replica nel database di origine.

Per verificare i dettagli dello slot di replica, esegui questa query sul database di origine:

SELECT * FROM pg_replication_slots;

Output atteso:

 slot_name |  plugin  | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn | wal_status | safe_wal_size  
 ----------+----------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+---------------------+------------+---------------  
 testsub   | pgoutput | logical   | 115048 | source   | f         | t      |        846 |      |         6945 | 58/B4000568 | 58/B40005A0         | reserved   |  
(1 row)

Verifica la replica dalle tabelle di origine

Nel database di origine, completa i passaggi seguenti:

1. Esegui questi comandi per aggiungere righe nelle tabelle di origine:

   INSERT INTO reptab1 VALUES(generate_series(1001,2000));  
   INSERT INTO reptab2 SELECT SUBSTR('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',((random()*(36-1)+1)::integer),1) FROM generate_series(1,50);

2. Esegui queste query per verificare che i dati siano cambiati nelle tabelle di origine.
   Tabella uno:

   SELECT count(*) FROM reptab1;

   Output atteso:

    count  
   -------  
      2000  
   (1 row)

   Tabella due:

   SELECT count(*) FROM reptab2;

   Output atteso:

    count  
   -------  
      100  
   (1 row)

Verifica che i dati delle tabelle di origine siano stati replicati nelle tabelle di destinazione

Nel database di destinazione, esegui queste query per verificare che i dati delle tabelle di origine siano stati replicati nelle tabelle di destinazione.

Tabella uno:

SELECT count(*) FROM reptab1;

Output atteso:

count  
-------  
   2000  
(1 row)

Tabella due:

SELECT count(*) FROM reptab2;

Output atteso:

 count  
-------  
   100  
(1 row)

Cancella gli slot di replica e disattiva la replica logica

Una volta completata la replica, se non è più necessaria, svuota gli slot e disattiva la replica logica. Gli slot di replica inattivi causano l'accumulo di file Write-Ahead Logging (WAL) nell'istanza database di origine. I file WAL potrebbero riempire lo spazio di archiviazione e causare tempi di inattività.

Completa i seguenti passaggi:

1. Nel database di destinazione, esegui questo comando per rimuovere la sottoscrizione:

   DROP SUBSCRIPTION testsub;

2. Nel database di destinazione, esegui questa query per verificare che la sottoscrizione sia stata rimossa:

   SELECT * FROM pg_subscription;

   Output atteso:

   oid | subdbid | subname | subowner | subenabled | subconninfo | subslotname | subsynccommit | subpublications  
   ----+---------+---------+----------+------------+-------------+-------------+---------------+-----------------  
   (0 rows)

   Nota: quando rimuovi la sottoscrizione nel database di destinazione, il database di destinazione rimuove anche gli slot di replica nel database di origine.

3. Nel database di origine, esegui questa query per verificare che gli slot di replica vengano rimossi dal database di origine:

   SELECT * FROM pg_replication_slots;

   Output atteso:

   slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn | wal_status | safe_wal_size  
   ----------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+---------------------+------------+--------------  
   (0 rows)

4. Nel database di origine, esegui questo comando per rimuovere la pubblicazione:

   DROP PUBLICATION testpub;

5. Nel database di origine, esegui queste query per verificare che la pubblicazione sia stata rimossa:

   SELECT * FROM pg_publication;  
   SELECT * FROM pg_publication_tables;

   Output atteso:

    oid | pubname | pubowner | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate | pubviaroot  
   -----+---------+----------+--------------+-----------+-----------+-----------+-------------+------------  
   (0 rows)  
   pubname | schemaname | tablename  
   ---------+------------+-----------  
   (0 rows)

6. Nel gruppo di parametri personalizzati dell'istanza database, imposta il parametro rds.logical_replication su 0. 
   Nota: riavvia l'istanza database per applicare le modifiche.

7. Rivedi i parametri max_replication_slots, max_wal_senders, max_logical_replication_workers, max_worker_processes e max_sync_workers_per_subscription in base all'utilizzo.

8. Esegui questa query per verificare se sono presenti slot di replica inattivi e la dimensione degli slot:

   SELECT slot_name, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(),restart_lsn)) AS replicationSlotLag, active FROM pg_replication_slots;

9. (Facoltativo) Esegui questo comando per rimuovere gli slot di replica:

   SELECT pg_drop_replication_slot('Your_slotname_name')

Informazioni correlate

Replication (Replica) sul sito web PostgreSQL

Logical replication (Replica logica) sul sito web PostgreSQL