Come si possono replicare le tabelle in RDS per PostgreSQL utilizzando la replica logica?

Risoluzione

Un tipico caso d'uso per la replica logica è la replica di un set di tabelle tra due istanze database di Amazon RDS per PostgreSQL. RDS per PostgreSQL supporta la replica logica con PostgreSQL 10.4 e versioni successive. Amazon Aurora PostgreSQL Compatible Edition versione 2.2.0 e successive supporta la replica logica con PostgreSQL 10.6 e versioni successive.

Nella risoluzione fornita, due tabelle di origine vengono replicate su due tabelle di destinazione utilizzando la replica logica in RDS per PostgreSQL. La replica logica esegue innanzitutto il caricamento iniziale dei dati già presenti nelle tabelle di origine e poi continua a replicare le modifiche in corso.

Attivazione della replica logica

Per attivare la replica logica in RDS per PostgreSQL, modifica un gruppo di parametri personalizzato per impostare rds.logical_replication su 1 e collega rds.logical_replication all'istanza database. Aggiorna il gruppo di parametri per impostare rds.logical_replication su 1 se un gruppo di parametri personalizzato è collegato a un'istanza database. Il parametro rds.logical_replication è un parametro statico che richiede il riavvio dell'istanza database. Quando l'istanza database viene riavviata, il parametro wal_level viene impostato su logico.

Verifica i valori per wal_level e rds.logical_replication:

postgres=> SELECT name,setting FROM pg_settings WHERE name IN ('wal_level','rds.logical_replication');
          name           | setting
-------------------------+---------
 rds.logical_replication | on
 wal_level               | logical
(2 rows)

Connessione al database di origine nell'istanza database di origine

Connettiti al database di origine nell'istanza database RDS per PostgreSQL di origine. Crea le tabelle di origine:

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

Inserisci i dati nelle tabelle di origine:

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

Creazione di una pubblicazione per le tabelle di origine

Crea una pubblicazione per le tabelle di origine. Utilizza una query SELECT per verificare i dettagli della pubblicazione creata:

source=> CREATE PUBLICATION testpub FOR TABLE reptab1,reptab2;
CREATE PUBLICATION
source=> SELECT * FROM pg_publication;
  oid   | pubname | pubowner | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate | pubviaroot
--------+---------+----------+--------------+-----------+-----------+-----------+-------------+------------
 115069 | testpub |    16395 | f            | t         | t         | t         | t           | f
(1 row)

Verifica che le tabelle di origine vengano aggiunte alla pubblicazione:

source=> SELECT * FROM pg_publication_tables;
 pubname | schemaname | tablename
---------+------------+-----------
 testpub | public     | reptab1
 testpub | public     | reptab2
(2 rows)

Nota: per replicare tutte le tabelle di un database, esegui questo comando:

CREATE PUBLICATION testpub FOR ALL TABLES;

Connessione al database di destinazione e creazione delle tabelle di destinazione

Connettiti al database di destinazione nell'istanza database di destinazione. Crea le tabelle di destinazione con gli stessi nomi delle tabelle di origine. Assicurati che non vi siano dati presenti nelle tabelle di destinazione eseguendo una query SELECT sulle tabelle di destinazione:

target=> CREATE TABLE reptab1 (slno int primary key);
CREATE TABLE
target=> CREATE TABLE reptab2 (name varchar(20));
CREATE TABLE
target=> SELECT count(*) FROM reptab1;
 count
-------
     0
(1 row)
target=> SELECT count(*) FROM reptab2;
 count
-------
     0
(1 row)

Creazione e verifica della sottoscrizione nel database di destinazione

Crea la sottoscrizione nel database di destinazione. Utilizza una query SELECT per verificare se la sottoscrizione è abilitata:

target=> CREATE SUBSCRIPTION testsub CONNECTION 'host=<source RDS/host endpoint> port=5432 dbname=<source_db_name> user=<user> password=<password>' PUBLICATION testpub;
NOTICE:  Created replication slot "testsub" on publisher
CREATE SUBSCRIPTION
target=> SELECT oid,subname,subenabled,subslotname,subpublications FROM pg_subscription;
  oid  | subname | subenabled | subslotname | subpublications
-------+---------+------------+-------------+-----------------
 16434 | testsub | t          | testsub     | {testpub}
(1 row)

Importante: per evitare di memorizzare una versione in chiaro del nome utente e della password nei registri del database, prima di creare la sottoscrizione emetti i seguenti comandi:

target=> SET log_min_messages to 'PANIC';
SET
target=> SET log_statement to NONE;
SET

Quando viene creata la sottoscrizione, questa carica tutti i dati presenti nelle tabelle di origine nelle tabelle di destinazione. Esegui una query SELECT sulle tabelle di destinazione per verificare che i dati iniziali vengano caricati:

target=> SELECT count(*) FROM reptab1;
 count
-------
  1000
(1 row)
target=> SELECT count(*) FROM reptab2;
 count
-------
    50
(1 row)

Verifica dello slot di replica nel database di origine

La creazione di una sottoscrizione nel database di destinazione crea uno slot di replica nel database di origine. Verifica i dettagli dello slot di replica emettendo la seguente query SELECT sul database di origine:

source=> SELECT * FROM pg_replication_slots;
 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)

Test della replica dalle tabelle di origine

Verifica se le modifiche ai dati nelle tabelle di origine vengono replicate nelle tabelle di destinazione inserendo righe nelle tabelle di origine:

source=> INSERT INTO reptab1 VALUES(generate_series(1001,2000));
INSERT 0 1000
source=> INSERT INTO reptab2 SELECT SUBSTR('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',((random()*(36-1)+1)::integer),1) FROM generate_series(1,50);
INSERT 0 50
source=> SELECT count(*) FROM reptab1;
 count
-------
  2000
(1 row)
source=> SELECT count(*) FROM reptab2; count
-------
   100
(1 row)

Test della replica verificando il numero di righe nelle tabelle di destinazione

Verifica il numero di righe nelle tabelle di destinazione per confermare che i nuovi inserti vengono replicati nelle tabelle di destinazione:

target=> SELECT count(*) FROM reptab1;
 count
-------
  2000
(1 row)
target=> SELECT count(*) FROM reptab2;
 count
-------
   100
(1 row)

Pulizia e disattivazione della replica logica

Pulisci e disattiva la replica logica quando la replica è completa e non è più necessaria. Gli slot di replica inattivi causano l'accumulo di file WAL nelle istanze database di origine. I file WAL potrebbero riempire lo spazio di archiviazione e causare interruzioni.

Elimina la sottoscrizione sul database di destinazione:

target=> DROP SUBSCRIPTION testsub;
NOTICE:  Dropped replication slot "testsub" on publisher
DROP SUBSCRIPTION
target=> SELECT * FROM pg_subscription;
oid | subdbid | subname | subowner | subenabled | subconninfo | subslotname | subsynccommit | subpublications
----+---------+---------+----------+------------+-------------+-------------+---------------+-----------------
(0 rows)

Nota: l'eliminazione della sottoscrizione comporta anche l'eliminazione dello slot di replica creato dalla sottoscrizione stessa.

Verifica che lo slot di replica venga eliminato dal database di origine emettendo la seguente istruzione di query SELECT sull'origine:

source=> SELECT * FROM pg_replication_slots;
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)

Elimina la pubblicazione. Verifica che la pubblicazione sia stata eliminata correttamente:

source=> DROP PUBLICATION testpub;
DROP PUBLICATION
source=> SELECT * FROM pg_publication;
 oid | pubname | pubowner | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate | pubviaroot
-----+---------+----------+--------------+-----------+-----------+-----------+-------------+------------
(0 rows)
source=> SELECT * FROM pg_publication_tables;
 pubname | schemaname | tablename
---------+------------+-----------
(0 rows)

Modifica rds.logical_replication su 0 nel gruppo di parametri personalizzato associato all'istanza database. Se l'istanza database non utilizza la replica logica, riavvia l'istanza database come richiesto.

Rivedi max_replication_slots, max_wal_senders, max_logical_replication_workers, max_worker_processes e max_sync_workers_per_subscription in base al tuo utilizzo.

Nota: i seguenti comandi controllano se sono presenti slot di replica inattivi, determinano le rispettive dimensioni degli slot e quindi rilasciano gli slot, se necessario.

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

SELECT pg_drop_replication_slot('Your_slotname_name');

---

Informazioni correlate

Documentazione di PostgreSQL per la replica