Replicação lógica e decodificação lógica no Banco de Dados do Azure para PostgreSQL

Uma instância de servidor flexível do Banco de Dados do Azure para PostgreSQL dá suporte às seguintes metodologias lógicas de extração e replicação de dados:

1. Replicação lógica

   1. Usando a replicação lógica nativa do PostgreSQL para replicar objetos de dados. A replicação lógica permite um controle refinado sobre a replicação de dados, incluindo a replicação de dados no nível da tabela.
   2. Usar a extensão pglogical que fornece replicação de streaming lógica e funcionalidades adicionais, como copiar o esquema inicial do banco de dados, suporte para TRUNCAR, habilidade de replicar a DDL etc.

2. Decodificação lógica implementada decodificando o conteúdo do WAL (log write-ahead).

Compare a replicação lógica e a decodificação lógica

A replicação lógica e a decodificação lógica têm várias semelhanças. As duas:

• Permitem que você replique dados do Postgres.

• Usam o WAL (log write-ahead) como origem das alterações.

• Usam slots de replicação lógica para enviar dados. Um slot representa um fluxo de alterações.

• Usam a propriedade REPLICA IDENTITY de uma tabela para determinar quais alterações podem ser enviadas.

• Não replique alterações de DDL.

As duas tecnologias têm suas diferenças:

Replicação lógica:

• Permite que você especifique uma tabela ou um conjunto de tabelas a serem replicadas.

Decodificação lógica:

• Extrai alterações em todas as tabelas em um banco de dados.

Pré-requisitos para replicação lógica e decodificação lógica

1. Acesse a página de parâmetros do servidor no portal.

2. Defina o parâmetro de servidor wal_level como logical.

3. Se desejar usar uma extensão pglogical, pesquise os parâmetros shared_preload_libraries e azure.extensions e selecione pglogical na caixa de lista suspensa.

4. Atualize o valor do parâmetro max_worker_processes para pelo menos 16. Caso contrário, você poderá encontrar problemas como WARNING: out of background worker slots.

5. Salve as alterações e reinicie o servidor para aplicar as alterações.

6. Confirme se a instância do servidor flexível do Banco de Dados do Azure para PostgreSQL permite o tráfego de rede do recurso de conexão.

7. Conceda permissões de replicação de usuário administrador.

   ALTER ROLE <adminname> WITH REPLICATION;
   

8. Talvez queira garantir que a função que você está usando tenha privilégios no esquema que você está replicando. Caso contrário, você poderá se deparar com erros como Permission denied for schema.

Use a replicação lógica e a decodificação lógica

Usar replicação lógica nativa é a forma mais simples de replicar dados da sua instância de servidor flexível do Banco de Dados do Azure para PostgreSQL. Use a interface SQL ou o protocolo de streaming para consumir as alterações. Também poderá usar a interface do SQL para consumir alterações usando a decodificação lógica.

Replicação lógica nativa

A replicação lógica usa os termos 'publicador' e 'assinante'.

• O fornecedor é o banco de dados de instância do servidor flexível do Banco de Dados do Azure para PostgreSQL do qual você está enviando dados.
• O assinante é o banco de dados de instância do servidor flexível do Banco de Dados do Azure para PostgreSQL para o qual você está enviando dados.

Aqui está um código de exemplo que você pode usar para experimentar a replicação lógica.

1. Conectar ao banco de dados publicador. Criar uma tabela e adicionar alguns dados.

   CREATE TABLE basic (id INTEGER NOT NULL PRIMARY KEY, a TEXT);
   INSERT INTO basic VALUES (1, 'apple');
   INSERT INTO basic VALUES (2, 'banana');
   

2. Crie uma publicação para a tabela.

   CREATE PUBLICATION pub FOR TABLE basic;
   

3. Conecte-se ao banco de dados do assinante. Crie uma tabela com o mesmo esquema do publicador.

   CREATE TABLE basic (id INTEGER NOT NULL PRIMARY KEY, a TEXT);
   

4. Crie uma assinatura que se conecte à publicação que você criou anteriormente.

   CREATE SUBSCRIPTION sub CONNECTION 'host=<server>.postgres.database.azure.com user=<rep_user> dbname=<dbname> password=<password>' PUBLICATION pub;
   

5. Agora, você pode consultar a tabela no assinante. Você observa que ele recebeu dados do publicador.

   SELECT * FROM basic;
   

   Você pode adicionar mais linhas à tabela do publicador e exibir as alterações no assinante.

   Se você não conseguir ver os dados, alterne para um usuário que seja membro da azure_pg_admin função e verifique o conteúdo da tabela.

Visite a documentação do PostgreSQL para entender mais sobre a replicação lógica.

Usar replicação lógica entre bancos de dados no mesmo servidor

Quando você pretende configurar a replicação lógica entre diferentes bancos de dados que residem na mesma instância do servidor flexível do Banco de Dados do Azure para PostgreSQL, é essencial seguir diretrizes específicas para evitar restrições de implementação que estão presentes no momento. A partir de agora, a criação de uma assinatura que se conecta ao mesmo cluster de banco de dados só terá êxito se o slot de replicação não for criado no mesmo comando. Caso contrário, a chamada CREATE SUBSCRIPTION trava, em um evento de espera LibPQWalReceiverReceive. Isso ocorre devido a uma restrição existente no mecanismo do Postgres, que pode ser removida em versões futuras.

Para configurar efetivamente a replicação lógica entre seus bancos de dados de "origem" e "destino" no mesmo servidor ao contornar essa restrição, siga as etapas descritas abaixo:

Primeiro, crie uma tabela chamada "básico" com um esquema idêntico nos bancos de dados de origem e de destino:

-- Run this on both source and target databases
CREATE TABLE basic (id INTEGER NOT NULL PRIMARY KEY, a TEXT);

Em seguida, no banco de dados de origem, crie uma publicação para a tabela e crie separadamente um slot de replicação lógica usando a função pg_create_logical_replication_slot, o que ajuda a evitar o problema de suspensão que normalmente ocorre quando o slot é criado no mesmo comando da assinatura. Use o plug-in pgoutput :

-- Run this on the source database
CREATE PUBLICATION pub FOR TABLE basic;
SELECT pg_create_logical_replication_slot('myslot', 'pgoutput');

Depois disso, em seu banco de dados de destino, crie uma assinatura para a publicação criada anteriormente, garantindo que create_slot esteja definido como false para impedir que a instância de servidor flexível do Azure Database para PostgreSQL crie um novo slot e especifique corretamente o nome do slot que foi criado na etapa anterior. Antes de executar o comando, substitua os espaços reservados na cadeia de conexão pelas credenciais reais do banco de dados:

-- Run this on the target database
CREATE SUBSCRIPTION sub
   CONNECTION 'dbname=<source dbname> host=<server>.postgres.database.azure.com port=5432 user=<rep_user> password=<password>'
   PUBLICATION pub
   WITH (create_slot = false, slot_name='myslot');

Depois de configurar a replicação lógica, agora você pode testá-la inserindo um novo registro na tabela "básica" no banco de dados de origem e verificando se ela é replicada para o banco de dados de destino:

-- Run this on the source database
INSERT INTO basic SELECT 3, 'mango';

-- Run this on the target database
TABLE basic;

Se tudo estiver configurado corretamente, você deverá testemunhar o novo registro do banco de dados de origem no banco de dados de destino, confirmando a configuração bem-sucedida da replicação lógica.

extensão pglogical

Aqui está um exemplo de configuração de pglogical no servidor de banco de dados do provedor e no assinante. Confira a documentação sobre a extensão pglogical para obter mais detalhes. Certifique-se também de executar as tarefas de pré-requisito listadas acima.

1. Instale a extensão pglogical no banco de dados no provedor e nos servidores de banco de dados do assinante.

   \c myDB
   CREATE EXTENSION pglogical;
   

2. Se o usuário de replicação for diferente do usuário de administração do servidor (que criou o servidor), conceda a associação em uma função azure_pg_admin ao usuário e atribua os atributos REPLICATION e LOGIN a ele. Confira a documentação pglogical para obter detalhes.

   GRANT azure_pg_admin to myUser;
   ALTER ROLE myUser REPLICATION LOGIN;
   

3. No servidor do banco de dados do provedor (origem/publicador), crie o nó do provedor.

   select pglogical.create_node( node_name := 'provider1',
   dsn := ' host=myProviderServer.postgres.database.azure.com port=5432 dbname=myDB user=myUser password=<password>');
   

4. Criar conjunto de replicação.

   select pglogical.create_replication_set('myreplicationset');
   

5. Adicione todas as tabelas no banco de dados ao conjunto de replicação.

   SELECT pglogical.replication_set_add_all_tables('myreplicationset', '{public}'::text[]);
   

   Como um método alternativo, também pode-se adicionar tabelas de um esquema específico (por exemplo, testUser) a um conjunto de replicação padrão.

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

6. No servidor de banco de dados do assinante, crie um nó de assinante.

   select pglogical.create_node( node_name := 'subscriber1',
   dsn := ' host=mySubscriberServer.postgres.database.azure.com port=5432 dbname=myDB user=myUser password=<password>' );
   

7. Crie uma assinatura para iniciar a sincronização e o processo de replicação.

   select pglogical.create_subscription (
   subscription_name := 'subscription1',
   replication_sets := array['myreplicationset'],
   provider_dsn := 'host=myProviderServer.postgres.database.azure.com port=5432 dbname=myDB user=myUser password=<password>');
   

8. Em seguida, você pode verificar o status da assinatura.

   SELECT subscription_name, status FROM pglogical.show_subscription_status();
   

Decodificação lógica

A decodificação lógica pode ser consumida via protocolo de streaming ou interface do SQL.

Protocolo de streaming

O consumo de alterações usando o protocolo de streaming geralmente é preferível. Você pode criar seu próprio consumidor/conector ou usar uma ferramenta como Debezium.

Visite a documentação do wal2json para obter um exemplo usando o protocolo de streaming com pg_recvlogical.

Interface do SQL

No exemplo a seguir, usamos a interface do SQL com o plug-in wal2json.

1. Crie um slot.

   SELECT * FROM pg_create_logical_replication_slot('test_slot', 'wal2json');
   

2. Emita comandos SQL. Por exemplo:

   CREATE TABLE a_table (
      id varchar(40) NOT NULL,
      item varchar(40),
      PRIMARY KEY (id)
   );
   
   INSERT INTO a_table (id, item) VALUES ('id1', 'item1');
   DELETE FROM a_table WHERE id='id1';
   

3. Consuma as alterações.

   SELECT data FROM pg_logical_slot_get_changes('test_slot', NULL, NULL, 'pretty-print', '1');
   

   A saída se parece com isso:

   {
         "change": [
         ]
   }
   {
         "change": [
                  {
                           "kind": "insert",
                           "schema": "public",
                           "table": "a_table",
                           "columnnames": ["id", "item"],
                           "columntypes": ["character varying(40)", "character varying(40)"],
                           "columnvalues": ["id1", "item1"]
                  }
         ]
   }
   {
         "change": [
                  {
                           "kind": "delete",
                           "schema": "public",
                           "table": "a_table",
                           "oldkeys": {
                                 "keynames": ["id"],
                                 "keytypes": ["character varying(40)"],
                                 "keyvalues": ["id1"]
                           }
                  }
         ]
   }
   

4. Solte o slot quando terminar de usá-lo.

   SELECT pg_drop_replication_slot('test_slot');
   

Visite a documentação do PostgreSQL para entender mais sobre a decodificação lógica.

Monitoramento

Você deve monitorar a decodificação lógica. Qualquer slot de replicação não utilizado deve ser descartado. Os slots mantêm os logs WAL do Postgres e os catálogos de sistema relevantes até que as alterações tenham sido lidas. Se o assinante ou o consumidor falhar ou se ele estiver configurado incorretamente, os logs não armazenados se acumularão e preencherão o armazenamento. Além disso, os logs não consumidos aumentam o risco da delimitação de ID da transação. Ambas as situações podem fazer com que o servidor fique indisponível. Portanto, os slots de replicação lógica devem ser consumidos continuamente. Se um slot de replicação lógica não for mais usado, descarte-o imediatamente.

A coluna "ativa" no modo de exibição pg_replication_slots indicará se há um consumidor conectado a um slot.

SELECT * FROM pg_replication_slots;

Defina alertas nas métricas Máximo de IDs de Transação Usadas e Armazenamento Usado para notificá-lo quando os valores aumentarem além dos limites normais.

Limitações

• As limitações de replicação lógica se aplicam conforme documentado aqui.

• Slots e failover de HA - Ao usar servidores com alta disponibilidade (HA) habilitada no Banco de Dados do Azure para PostgreSQL, saiba que os slots de replicação lógica não são preservados durante eventos de failover. Para manter slots de replicação lógica e garantir a consistência de dados após um failover, é recomendável usar a extensão de Slots de Failover PG. Para obter mais informações sobre como habilitar essa extensão, consulte a documentação.

Conteúdo relacionado

• Regras de firewall no Banco de Dados do Azure para PostgreSQL.
• Visão geral de rede do Banco de Dados do Azure para PostgreSQL com acesso público.
• Integração de rede virtual no Banco de Dados do Azure para PostgreSQL.
• Como usar extensões.
• Alta disponibilidade no Banco de Dados do Azure para PostgreSQL.