Replicação lógica e decodificação lógica no Banco de Dados do Azure para PostgreSQL – Servidor Flexível

APLICA-SE A: Banco de Dados do Azure para PostgreSQL – Servidor Flexível

O servidor flexível do Banco de Dados do Azure para PostgreSQL oferece suporte às seguintes metodologias de replicação e extração de dados lógicos:

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 a 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.

Observação

É sempre uma boa prática separar o usuário de replicação da conta de administrador regular.

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

Usar a replicação lógica nativa é a maneira mais simples de replicar dados do 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 editor é o banco de dados do servidor flexível do Banco de Dados do Azure para PostgreSQL do qual você está enviando dados.
• O assinante é o banco de dados 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 conectará à 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.

   Caso não consiga visualizar os dados, habilite o privilégio de entrar para azure_pg_admin e verifique o conteúdo da tabela.

   ALTER ROLE azure_pg_admin login;
   

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 bancos de dados diferentes que residem na mesma instância de 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 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 que a 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 ela esteja definida para false impedir que o servidor flexível do Banco de Dados do Azure para PostgreSQL crie um novo slot e especifique corretamente o nome do slot que create_slot 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 no 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. Além disso, não deixe de realizar 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=myPassword');
   

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=myPasword' );
   

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=myPassword');
   

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

   SELECT subscription_name, status FROM pglogical.show_subscription_status();
   

Cuidado

Atualmente, o Pglogical não dá suporte a uma replicação DDL automática. O esquema inicial pode ser copiado manualmente usando pg_dump --schema-only. As instruções DDL podem ser executadas no provedor e no assinante simultaneamente usando a função pglogical.replicate_ddl_command. Esteja ciente das outras limitações da extensão listadas aqui.

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 tem a seguinte aparência:

   {
         "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. Descarte 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 replicação lógica.

Monitor

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 consumidor falhar ou se não tiver sido configurado corretamente, os logs não consumidos vão compilar e preencher o armazenamento. Além disso, os logs não consumidos aumentam o risco da ID de transação wraparound. 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 de servidor flexível Máximo de IDs de Transação Usadas e Armazenamento Usado do Banco de Dados do Azure para PostgreSQL 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 habilitados para alta disponibilidade (HA) com o Banco de Dados do Azure para servidor flexível PostgreSQL, esteja ciente de 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.

Importante

Você deverá descartar o slot de replicação lógica no servidor primário se o assinante correspondente não existir mais. Caso contrário, os arquivos WAL se acumulam no primário, preenchendo o armazenamento. Suponha que o limite de armazenamento exceda um determinado limite e que o slot de replicação lógica não esteja em uso (devido a um assinante não disponível). Nesse caso, a instância do servidor flexível do Banco de Dados do Azure para PostgreSQL descarta automaticamente esse slot de replicação lógica não utilizado. Essa ação liberará arquivos WAL acumulados e evitará que o servidor fique indisponível devido à situação de armazenamento cheio.

Conteúdo relacionado

• Opções de rede
• extensions
• Alta disponibilidade