Como uso replicação lógica para replicar tabelas entre minhas instâncias de banco de dados Amazon RDS para PostgreSQL?

Resolução

O Amazon RDS para PostgreSQL oferece suporte à replicação lógica com o PostgreSQL 10.4 e versões posteriores. A edição do Amazon Aurora compatível com PostgreSQL versão 2.2.0 e posterior oferece suporte à replicação lógica com o PostgreSQL 10.6 e versões posteriores.

Para obter mais informações, consulte Executar replicação lógica para o Amazon RDS para PostgreSQL.

A resolução a seguir replica duas tabelas de origem em duas tabelas de destino.

Ative a replicação lógica

Conclua as etapas a seguir:

1. Crie um grupo de parâmetros personalizado e defina o parâmetro rds.logical_replication como 1.
   Observação: Como o parâmetro rds.logical_replication é estático, você deve reinicializar a instância de banco de dados. Depois de reinicializar a instância de banco de dados, o parâmetro wal_level muda para logical.
2. Associe o grupo de parâmetros à sua instância de banco de dados.
3. Execute a consulta a seguir para verificar se wal_level é logical e se rds.logical_replication está ativado:

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

   Saída esperada:

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

Crie as tabelas de origem e insira dados

Conecte-se à instância de banco de dados PostgreSQL e conclua as etapas a seguir no banco de dados de origem:

1. Execute os comandos a seguir para criar as tabelas de origem:

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

2. Execute os comandos a seguir para adicionar dados às tabelas de origem:

   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);

Crie uma publicação para as tabelas de origem

No banco de dados de origem, conclua as etapas a seguir:

1. Execute o comando a seguir para criar uma publicação das duas tabelas:

   CREATE PUBLICATION testpub FOR TABLE reptab1, reptab2;

2. Execute a consulta a seguir para verificar se os detalhes da publicação estão corretos:

   SELECT * FROM pg_publication;

   Saída esperada:

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

3. Execute a consulta a seguir para verificar se as tabelas de origem estão na publicação:

   SELECT * FROM pg_publication;

   Saída esperada:

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

Conecte-se ao banco de dados de destino e crie as tabelas de destino

No banco de dados de destino, conclua as etapas a seguir:

1. Execute os comandos a seguir para criar as tabelas de destino:

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

   Observação: Use os mesmos nomes das tabelas de origem.

2. Execute as consultas a seguir para verificar se os dados não estão nas tabelas de destino.
   Tabela um:

   SELECT count(*) FROM reptab1;

   Saída esperada:

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

   Tabela dois:

   SELECT count(*) FROM reptab2;

   Saída esperada:

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

Crie uma assinatura no banco de dados de destino

Antes de criar a assinatura, execute os comandos a seguir para confirmar que você não armazenou uma versão em texto simples do seu nome de usuário e senha nos logs do banco de dados:

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

Para criar a assinatura no banco de dados de destino, conclua as etapas a seguir:

1. Execute o comando a seguir para criar a assinatura:

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

   Observação: Substitua o endpoint RDS/Host de origem pelo endpoint da sua instância de banco de dados de origem e source_db_name pelo nome da sua instância de banco de dados. Substitua usuário pelo seu nome de usuário e senha pela sua senha.

2. Execute a consulta a seguir para verificar se a assinatura está ativa:

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

   Saída esperada:

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

3. Execute as consultas a seguir para verificar se os dados estão nas tabelas de destino.
   Tabela um:

   SELECT count(*) FROM reptab1;

   Saída esperada:

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

   Tabela dois:

   SELECT count(*) FROM reptab2;

   Saída esperada:

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

Verifique os detalhes do slot de replicação no banco de dados de origem

Quando você cria uma assinatura no banco de dados de destino, ele cria um slot de replicação no banco de dados de origem.

Para verificar os detalhes do slot de replicação, execute a consulta a seguir no banco de dados de origem:

SELECT * FROM pg_replication_slots;

Saída esperada:

 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)

Teste a replicação a partir das tabelas de origem

No banco de dados de origem, conclua as etapas a seguir:

1. Execute os comandos a seguir para adicionar linhas às tabelas de origem:

   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. Execute as consultas a seguir para verificar se os dados foram alterados nas tabelas de origem.
   Tabela um:

   SELECT count(*) FROM reptab1;

   Saída esperada:

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

   Tabela dois:

   SELECT count(*) FROM reptab2;

   Saída esperada:

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

Verifique se os dados nas tabelas de origem foram replicados para as tabelas de destino

No banco de dados de destino, execute as consultas a seguir para confirmar se os dados das tabelas de origem foram replicados para as tabelas de destino.

Tabela um:

SELECT count(*) FROM reptab1;

Saída esperada:

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

Tabela dois:

SELECT count(*) FROM reptab2;

Saída esperada:

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

Limpe os slots de replicação e desative a replicação lógica

Depois de concluir a replicação e não precisar mais dela, limpe os slots e desative a replicação lógica. Os slots de replicação inativos fazem com que os arquivos Write-Ahead Logging (WAL) se acumulem na instância de banco de dados de origem. Os arquivos WAL podem preencher o armazenamento e causar tempo de inatividade.

Conclua as etapas a seguir:

1. No banco de dados de destino, execute o comando a seguir para remover a assinatura:

   DROP SUBSCRIPTION testsub;

2. No banco de dados de destino, execute a consulta a seguir para verificar se a assinatura foi removida:

   SELECT * FROM pg_subscription;

   Saída esperada:

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

   Observação: Quando você remove a assinatura no banco de dados de destino, ele também remove os slots de replicação no banco de dados de origem.

3. No banco de dados de origem, execute a consulta a seguir para verificar se os slots de replicação foram removidos do banco de dados de origem:

   SELECT * FROM pg_replication_slots;

   Saída esperada:

   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. No banco de dados de origem, execute o comando a seguir para remover a publicação:

   DROP PUBLICATION testpub;

5. No banco de dados de origem, execute as consultas a seguir para verificar se a publicação foi removida:

   SELECT * FROM pg_publication;  
   SELECT * FROM pg_publication_tables;

   Saída esperada:

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

6. No grupo de parâmetros personalizado da sua instância de banco de dados, defina o parâmetro rds.logical_replication como 0. 
   Observação: Reinicialize a instância de banco de dados para que as alterações sejam aplicadas.

7. Analise os parâmetros max_replication_slots, max_wal_senders, max_logical_replication_workers, max_worker_processes e max_sync_workers_per_subscription com base em seu uso.

8. Execute a consulta a seguir para verificar se há slots de replicação inativos e o tamanho dos slots:

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

9. (Opcional) Execute o seguinte comando para remover os slots de replicação:

   SELECT pg_drop_replication_slot('Your_slotname_name')

Informações relacionadas

Replication (Replicação) no site do PostgreSQL

Logical replication (Replicação lógica) no site do PostgreSQL