Replicação bidirecional no OCI Database para PostgreSQL usando pglogical

Introdução

A replicação lógica no OCI PostgreSQL permite replicar objetos de dados e suas alterações com base na identidade de replicação de uma tabela, geralmente a chave primária. Ele usa um modelo de publicação-assinatura, em que um ou mais assinantes assinam publicações definidas em um nó do editor. Os assinantes extraem dados dessas publicações e podem, opcionalmente, republicar as alterações para permitir a replicação em cascata ou topologias mais complexas.

A replicação lógica efetua alterações no nível da linha — operações INSERT, UPDATE e DELETE — decodificando o WAL (Log de Gravação Antecipada) usando a decodificação lógica. Essas alterações decodificadas são independentes do formato de armazenamento físico do OCI PostgreSQL e representam apenas as operações de dados lógicas, fornecendo maior flexibilidade para sistemas downstream.

Neste tutorial, vamos discutir como usar a extensão pglógica para replicação lógica e como ela permite a replicação bidirecional entre dois Sistemas de Banco de Dados no OCI Database para PostgreSQL.

Replicação bidirecional

A replicação bidirecional (BDR) com pglógico significa que dois ou mais Sistemas de Banco de Dados OCI PostgreSQL publicam e se inscrevem nas alterações um do outro. Em outras palavras, cada Sistema de Banco de Dados OCI PostgreSQL pode ler e gravar, e todas as alterações são replicadas em ambas as direções. Consequentemente, quaisquer modificações executadas em um dos Sistemas de Banco de Dados do OCI PostgreSQL serão espelhadas e implementadas em ambos.

A replicação bidirecional geralmente é recomendada quando você precisa de uma arquitetura de banco de dados regional ou global que ofereça acesso de leitura e gravação de baixa latência para usuários em vários locais. Ao co-localizar instâncias de leitura/gravação perto de usuários em cada região, as alterações podem ser feitas localmente e depois replicadas para outras regiões, garantindo que os dados sejam sincronizados em todos os nós.

Mostra duas instâncias do OCI Database with PostgreSQL, com Replicação executada em ambas as direções entre elas.

Na prática, a maioria dos casos de uso requer apenas que um pequeno subconjunto de tabelas seja gravável em várias regiões, enquanto a maioria do banco de dados pode permanecer somente leitura ou gerenciado centralmente por meio da replicação unidirecional.

No entanto, a replicação bidirecional introduz complexidade. A manutenção da consistência de dados e o gerenciamento da topologia de replicação podem tornar-se um desafio à medida que o sistema cresce.

Se os principais objetivos do seu aplicativo forem escalabilidade de leitura e resiliência a interrupções regionais, uma abordagem mais simples e robusta será implantar uma instância do OCI Database para PostgreSQL de alta disponibilidade com várias réplicas de leitura, na mesma região ou em todas as regiões.

Como funciona o trabalho pglógico bidirecional

Em pglogical, você pode configurar várias assinaturas e publicações como esta:

Cada nó:

Para saber mais sobre a replicação pglógica unidirecional entre regiões no OCI PostgreSQL, consulte este blog: pglógica unidirecional entre regiões.

Gerenciando Conflitos e Configuração

O editor (o banco de dados de origem) define uma ou mais publicações e envia dados de alteração (operações DML) ao assinante (o banco de dados de destino). Um assinante pode se conectar a vários editores e aplicar suas alterações ao banco de dados local.

A extensão pglogical fornece a resolução avançada de conflitos através do parâmetro pglogical.conflict_resolution, que suporta os seguintes modos:

Para obter mais detalhes sobre conflict_resolution em pglógico, consulte a documentação oficial sobre o github: Opções de Configuração do 2º Quadrante.

Ao usar keep_local, last_update_wins ou first_update_wins, track_commit_timestamps deve ser ativado no editor e no assinante. Tenha em mente que habilitar essa configuração pode introduzir custos indiretos de desempenho mensuráveis, dependendo da sua carga de trabalho. É altamente recomendável avaliar o impacto no desempenho em seu ambiente de produção antes da implantação.

Configurando a replicação bidirecional no OCI PostgreSQL

Para ativar a extensão pglogical no OCI Database para PostgreSQL, consulte este tutorial: Ativar extensão pglogical no OCI PostgreSQL.

Tarefa 1: Pré-Requisitos

Depois que a extensão pglógica for criada no seu OCI Database para PostgreSQL, Conceda a seguinte permissão no sistema de banco de dados #1 e no sistema de banco de dados #2,

ALTER ROLE psql WITH REPLICATION;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_replication_origin_session_reset() TO psql; 
GRANT EXECUTE ON FUNCTION pg_catalog.pg_replication_origin_session_setup TO psql; 
GRANT ALL ON FUNCTION pg_catalog.pg_replication_origin_session_setup TO psql;

Observação: psql é uma amostra de usuário de replicação criada especificamente para fins de demonstração neste tutorial.

Tarefa 2: Configurando a Replicação Lógica Unidirecional

No Sistema de Banco de Dados 1:

Depois de concluir os pré-requisitos descritos anteriormente, crie uma tabela de teste e insira alguns registros para verificar o fluxo de trabalho pglógico.

CREATE TABLE test_table (
    id serial PRIMARY KEY,
    data text
);
 
INSERT INTO test_table
    (id, data)
VALUES
    (generate_series(1, 1000), 'Test');

Crie o nó do provedor:

O próximo passo é criar um nó usando a função pglógica create_node.

SELECT pglogical.create_node(
    node_name := 'provider1',
    dsn := 'host=primary.xxxxxxxx.postgresql.us-phoenix-1.oci.oraclecloud.com
            port=5432
            user=psql
            password=xxxxx
            dbname=postgres'
);

Veja a seguir as definições de parâmetro para a função create_node:

Como próxima etapa, identifique as tabelas que precisam ser incluídas na replicação. O pglogical usa conjuntos de replicação para gerenciar quais tabelas e ações (INSERT, UPDATE, DELETE) são replicadas. Você pode criar um conjunto de replicação personalizado usando a função create_replication_set ou usar o conjunto padrão predefinido, que replica todas as ações em suas tabelas designadas. Para incluir tabelas, use replication_set_add_table para tabelas individuais ou replication_set_add_all_tables para adicionar todas as tabelas de um esquema específico.

O seguinte comando adiciona todas as tabelas do esquema público ao conjunto de replicação padrão:

SELECT pglogical.replication_set_add_all_tables(
    'default',
    ARRAY['public']
);

Para saber mais sobre conjuntos de replicação, consulte Conjuntos de replicação.

No Sistema de Banco de Dados 2:

Crie uma tabela correspondente como no Sistema de Banco de Dados #1:

Crie a tabela de teste no Sistema de Banco de Dados #2 para corresponder à estrutura da tabela no Sistema de Banco de Dados #1.

CREATE TABLE test_table (
    id serial PRIMARY KEY,
    data text
);

Crie o nó do assinante:

Em seguida, crie um nó no Sistema de Banco de Dados #2 usando a função pglógica create_node, assim como você fez no Sistema de Banco de Dados #1:

SELECT pglogical.create_node(
    node_name := 'subscriber1',
    dsn := 'host=primary.xxxxxx.postgresql.us-phoenix-1.oci.oraclecloud.com
            port=5432
            user=psql
            password=xxxxxx
            dbname=postgres'
);

Veja a seguir as definições de parâmetro para a função create_node:

Criar Assinatura pglógica:

A próxima etapa é criar a assinatura para iniciar a replicação de dados do Sistema de Banco de Dados nº 1 usando a função create_subscription.

O parâmetro subscription_name pode ser qualquer nome que você escolher para a assinatura. O provider_dsn é a string de conexão para o Sistema de Banco de Dados nº 1, e o parâmetro replication_sets especifica quais conjuntos de replicação usar. Neste exemplo, estamos usando o conjunto de replicação padrão.

O argumento synchronize_data informa ao pglogical se deve ou não copiar dados existentes da origem. Em nosso teste, ele é definido como true porque queremos que todas as linhas sejam copiadas, juntamente com quaisquer alterações futuras.

O parâmetro forward_origins é crucial para ativar a replicação bidirecional. A definição de forward_origins := '{}' garante que somente as alterações originadas do próprio nó sejam encaminhadas e não as que já foram replicadas de outros nós. Isso impede que o processo de replicação entre em um loop infinito, onde as alterações são continuamente replicadas para frente e para trás.

SELECT pglogical.create_subscription(
    subscription_name := 'subscription1',
    provider_dsn := 'host=primary.xxxxxxx.postgresql.us-phoenix-1.oci.oraclecloud.com
                     port=5432
                     sslmode=require
                     dbname=postgres
                     user=psql
                     password=xxxxx',
    replication_sets := ARRAY['default'],
    synchronize_data := true,
    forward_origins := '{}'
);

Verificar status da assinatura:

Verifique o status da assinatura e as informações básicas no sistema de banco de dados #2 usando o seguinte comando:

select * from pglogical.show_subscription_status();
-[ RECORD 1 ]-----+----------------------------------------------------------------------------------------------------------------------------------------------------------------
subscription_name | subscription1
status            | replicating
provider_node     | provider1
provider_dsn      | host=primary.xxxxxxxxx.postgresql.us-phoenix-1.oci.oraclecloud.com port=5432 sslmode=require dbname=postgres user=psql password=xxxxxx
slot_name         | pgl_postgres_provider1_subscription1
replication_sets  | {default}
forward_origins   |

Tarefa 3: Testar a replicação do Sistema de Banco de Dados #1 para o Sistema de Banco de Dados #2

Agora que a replicação está configurada entre Sistema de Banco de Dados #1 (origem) e Sistema de Banco de Dados #2 (destino), vamos testar se as alterações feitas no Sistema de Banco de Dados #1 são copiadas automaticamente para o Sistema de Banco de Dados #2.

  1. Verificar dados iniciais

    No Sistema de Banco de Dados #2, verifique a tabela test_table:

    postgres=> SELECT COUNT(*) FROM test_table;
 count 
-------
  1000
(1 row)

    Isso confirma que a tabela já tem 1000 linhas.

  2. Testar uma INSERT

    Adicione uma nova linha no sistema de banco de dados nº 1:

    postgres=> INSERT INTO test_table VALUES (10000, 'initial load');

  3. Verifique-o no Sistema de Banco de Dados nº 1:

    postgres=> SELECT * FROM test_table WHERE id = 10000;
  id   |     data     
-------+--------------
10000 | initial load

  4. Verifique o Sistema de Banco de Dados #2. A mesma linha aparece lá, mostrando que a replicação funciona:

    postgres=> SELECT * FROM test_table WHERE id = 10000;
  id   |     data     
-------+--------------
10000 | initial load

  5. Testar uma instrução DELETE

    Exclua a mesma linha no Sistema de Banco de Dados #1:

    postgres=> DELETE FROM test_table WHERE id = 10000;

  6. Verifique em ambos os sistemas – a linha se foi de ambos.

    (0 rows)

  7. Testar uma Atualização

    Atualizar uma linha existente no Sistema de Banco de Dados #1:

    postgres=> UPDATE test_table SET data = 'Initial load' WHERE id = 1;

    Verifique o Sistema de Banco de Dados #1:

      id   |     data     
-------+--------------
  1 | Initial load

    Verifique o Sistema de Banco de Dados #2 — o valor atualizado também está lá.

Com esses testes, confirmamos que as operações INSERT, UPDATE e DELETE são replicadas corretamente de Database System #1 → Database System #2.

Tarefa 4: Configurar replicação lógica bidirecional

As tarefas anteriores configuram a replicação unidirecional de Sistema de Banco de Dados Nº 1 para Sistema de Banco de Dados Nº 2. Em seguida, executaremos comandos adicionais para ativar a replicação na direção inversa também.

No Sistema de Banco de Dados 2:

Configure um conjunto de replicação da mesma forma que você fez no Sistema de Banco de Dados #1:

SELECT pglogical.replication_set_add_all_tables(
    'default',
    ARRAY['public']
);

No Sistema de Banco de Dados #1:

Crie uma assinatura no Sistema de Banco de Dados #1 para que ele comece a extrair as alterações do Sistema de Banco de Dados #2:

SELECT pglogical.create_subscription(
    subscription_name := 'subscription2',
    provider_dsn := 'host=primary.xxxxxxxx.postgresql.us-phoenix-1.oci.oraclecloud.com
                     port=5432
                     sslmode=require
                     dbname=postgres
                     user=psql
                     password=xxxxx',
    replication_sets := ARRAY['default'],
    synchronize_data := false,
    forward_origins := '{}'
);

No comando anterior, o argumento synchronize_data foi definido como false, diferente de antes. Isso acontece porque não precisamos copiar dados existentes do Sistema de Banco de Dados #2 para o Sistema de Banco de Dados #1, pois ambas as tabelas já contêm dados idênticos.

Com este comando, a configuração de replicação bidirecional agora está concluída e todas as alterações feitas em um servidor serão replicadas para o outro. Em seguida, verifique o status da assinatura no Sistema de Banco de Dados nº 1.

Verifique o status de assinatura do Sistema de Banco de Dados nº 1:

Verifique o status da assinatura e as informações básicas no Database System #1 usando o seguinte comando:

select * from pglogical.show_subscription_status();
-[ RECORD 1 ]-----+----------------------------------------------------------------------------------------------------------------------------------------------------------------
subscription_name | subscription2
status            | replicating
provider_node     | subscriber1
provider_dsn      | host=primary.xxxxxxxx.postgresql.us-phoenix-1.oci.oraclecloud.com port=5432 sslmode=require dbname=postgres user=psql password=xxxxx
slot_name         | pgl_postgres_subscriber1_subscription2
replication_sets  | {default}
forward_origins   |

Tarefa 5: Testar replicação bidirecional

Vamos começar adicionando uma linha aos dois Sistemas de Banco de Dados para verificar se a replicação bidirecional está funcionando corretamente.

  1. Insira uma linha no sistema de banco de dados nº 1:

    Execute o seguinte comando no Sistema de Banco de Dados #1:

    postgres=# INSERT INTO test_table VALUES (1001, 'Initial_load');
INSERT 0 1

  2. Verifique a linha inserida no Sistema de banco de dados nº 1:

    postgres=# SELECT * FROM test_table WHERE id = 1001;
  id  |     data     
------+--------------
1001 | Initial_load
(1 row)

  3. Verificar Replicação no Sistema de Banco de Dados #2:

    Agora, verifique o Sistema de Banco de Dados #2 para confirmar se a linha foi replicada:

    postgres=> SELECT * FROM test_table WHERE id = 1001;
  id  |     data     
------+--------------
1001 | Initial_load
(1 row)

    Isso mostra que a inserção no Sistema de Banco de Dados #1 foi replicada para o Sistema de Banco de Dados #2.

  4. Insira uma linha no sistema de banco de dados nº 2:

    Execute o seguinte comando no Sistema de Banco de Dados #2:

    postgres=> INSERT INTO test_table VALUES (1002, 'Initial_load');
INSERT 0 1

  5. Verifique a linha inserida no Sistema de banco de dados nº 2:

    postgres=> SELECT * FROM test_table WHERE id = 1002;
  id  |     data     
------+--------------
1002 | Initial_load
(1 row)

  6. Verificar Replicação no Sistema de Banco de Dados #1:

    Agora, verifique o Sistema de Banco de Dados #1 para confirmar se a linha foi replicada novamente:

    postgres=# SELECT * FROM test_table WHERE id = 1002;
  id  |     data     
------+--------------
1002 | Initial_load
(1 row)

    Este teste confirma que as alterações de dados são replicadas com sucesso nas duas direções entre o Sistema de Banco de Dados Nº 1 e o Sistema de Banco de Dados Nº 2.

Monitorar lag de replicação lógica

Uma vez concluída a configuração pglógica, você deve monitorar o log de replicação para rastrear slots de replicação, atraso e outras métricas relevantes.

select * from pg_stat_replication;

Este comando retorna o status atual da replicação no OCI PostgreSQL.

Conclusão

Este tutorial orienta você na configuração da replicação bidirecional do PostgreSQL com pglogical. A replicação bidirecional adiciona complexidade à sua infraestrutura de banco de dados e requer esforço adicional, por isso é importante confirmar se ela se encaixa no seu caso de uso. Se o seu aplicativo exigir vários gravadores em diferentes regiões e o sharding de banco de dados não for viável, a replicação bidirecional será a solução ideal.

Confirmações

Mais Recursos de Aprendizado

Explore outros laboratórios em docs.oracle.com/learn ou acesse mais conteúdo de aprendizado gratuito no canal do Oracle Learning YouTube. Além disso, acesse education.oracle.com/learning-explorer para se tornar um Oracle Learning Explorer.

Para obter a documentação do produto, visite o Oracle Help Center.