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

O 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 dá-lhe controlo detalhado sobre a replicação de dados, incluindo a replicação ao nível da tabela.
   2. Utilizando a extensão pglogical que fornece replicação lógica em streaming e mais capacidades, como copiar o esquema inicial da base de dados, suporte para TRUNCATE, capacidade de replicar DDL, entre outras.

2. Decodificação lógica que é implementada através da decodificação do conteúdo do registo de escrita antecipada (WAL).

Comparar replicação lógica e decodificação lógica

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

• Permitir que você replique dados fora do Postgres.

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

• Use slots de replicação lógica para enviar dados. Um slot representa um fluxo de mudanças.

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

• Não replique alterações DDL.

As duas tecnologias têm as suas diferenças:

Replicação lógica:

• Permite especificar 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. Vai à página de parâmetros no portal.

2. Defina o parâmetro wal_level como logical.

3. Se quiseres usar uma extensão pglogical, procura os parâmetros shared_preload_libraries e azure.extensions e seleciona pglogical na caixa de lista pendente.

4. Atualize max_worker_processes o valor do parâmetro para pelo menos 16. Caso contrário, pode deparar-se com problemas como WARNING: out of background worker slots.

5. Salve as alterações e reinicie o servidor para aplicá-las.

6. Confirme que o seu servidor flexível Base de Dados do Azure para PostgreSQL permite tráfego de rede a partir do seu recurso de ligação.

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

   ALTER ROLE <adminname> WITH REPLICATION;
   

8. Certifica-te de que o papel que estás a usar tem privilégios sobre o esquema que estás a replicar. Caso contrário, você pode encontrar erros como Permission denied for schema.

Usar replicação lógica e decodificação lógica

Usar replicação lógica nativa é a forma mais simples de replicar dados do seu servidor flexível Base de Dados do Azure para PostgreSQL. Você pode usar a interface SQL ou o protocolo de streaming para consumir as alterações. Também pode usar a interface SQL para consumir alterações através da decodificação lógica.

Replicação lógica nativa

A replicação lógica utiliza os termos publisher e subscriber.

• O publicador é a base de dados do Base de Dados do Azure para PostgreSQL – servidor flexível que envia dados.
• O subscritor é a base de dados do Base de Dados do Azure para PostgreSQL em Servidor Flexível que recebe dados.

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

1. Conecte-se ao banco de dados do editor. Crie uma tabela e adicione 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 de assinantes. 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 criada 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. Vês que recebe dados da editora.

   SELECT * FROM basic;
   

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

   Se não conseguires ver os dados, muda para um utilizador que seja membro da azure_pg_admin função e verifica o conteúdo da tabela.

Para saber mais sobre replicação lógica, visite a documentação do PostgreSQL.

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

Para configurar a replicação lógica entre diferentes bases de dados no mesmo servidor flexível Base de Dados do Azure para PostgreSQL, siga diretrizes específicas para evitar restrições de implementação. Atualmente, só pode criar uma subscrição que se ligue ao mesmo cluster de base de dados se o slot de replicação não for criado dentro do mesmo comando. Caso contrário, a CREATE SUBSCRIPTION chamada fica suspensa num LibPQWalReceiverReceive evento de espera. Este comportamento deve-se a uma restrição existente no motor Postgres, que poderá ser removida em versões futuras.

Para configurar uma replicação lógica entre as suas bases de dados "fonte" e "destino" no mesmo servidor, evitando esta restrição, siga estes passos:

Primeiro, crie uma tabela nomeada basic com um esquema idêntico tanto na base de dados de origem como na de destino:

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

Em seguida, na base de dados de origem, crie uma publicação para a tabela e, separadamente, crie um slot de replicação lógica utilizando a função pg_create_logical_replication_slot. Esta abordagem ajuda a evitar o problema de bloqueio que ocorre normalmente quando o slot é criado no mesmo comando que a subscrição. Utilize o plugin pgoutput:

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

Depois, na sua base de dados alvo, crie uma subscrição para a publicação previamente criada. Defina create_slot para false impedir que o seu servidor flexível Base de Dados do Azure para PostgreSQL crie um novo slot, e especifique o nome do slot que criou 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 configurares a replicação lógica, testa-a inserindo um novo registo na basic tabela da tua base de dados de origem e depois verifica se ele replica para a base de dados alvo:

-- 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, vê o novo registo da base de dados de origem na sua base de destino, confirmando a configuração bem-sucedida da replicação lógica.

extensão pglogical

Aqui está um exemplo de configuração do pglogical no servidor de banco de dados do provedor e no assinante. Para mais detalhes, consulte a documentação de extensões pglogic. Também certifica-te de que completas as tarefas pré-requisito listadas anteriormente.

1. Instale a extensão pglogical na base de dados tanto no fornecedor como no servidor de base de dados do assinante.

   \c myDB
   CREATE EXTENSION pglogical;
   

2. Se o utilizador de replicação não for o utilizador de administração do servidor (o utilizador que criou o servidor), conceda ao utilizador a pertença ao azure_pg_admin papel e atribua-lhe os atributos REPLICATION e LOGIN. Consulte a documentação pglogical para obter detalhes.

   GRANT azure_pg_admin to myUser;
   ALTER ROLE myUser REPLICATION LOGIN;
   

3. No servidor de banco de dados do provedor (origem/editor), 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. Crie um conjunto de replicação.

   select pglogical.create_replication_set('myreplicationset');
   

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

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

   Como um método alternativo, você também pode 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. Verifica o estado da subscrição.

   SELECT subscription_name, status FROM pglogical.show_subscription_status();
   

Descodificação lógica

Pode consumir decodificação lógica através do protocolo de streaming ou da interface SQL.

Protocolo de streaming

Consumir alterações através do protocolo de streaming é frequentemente preferível. Pode criar o seu próprio consumidor ou conector, ou usar um serviço de terceiros como o Debezium.

Para um exemplo que utiliza o protocolo de streaming com pg_recvlogical, veja a documentação wal2json: um exemplo usando o protocolo de streaming com pg_recvlogical.

Interface SQL

No exemplo seguinte, use a interface SQL com o plugin 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 é semelhante a:

   {
         "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. Tira o slot quando terminares de o usar.

   SELECT pg_drop_replication_slot('test_slot');
   

Para saber mais sobre descodificação lógica, consulte a documentação PostgreSQL: decodificação lógica.

Monitor

Você deve monitorar a decodificação lógica. Elimina qualquer slot de replicação não utilizado. Os slots retêm os registos WAL do Postgres e os catálogos de sistema relevantes até que as alterações sejam lidas. Se o seu assinante ou consumidor falhar ou se estiver configurado incorretamente, os logs não consumidos se acumulam e enchem seu armazenamento. Além disso, os logs não consumidos aumentam o risco de encapsulamento de ID de transação. Ambas as situações podem fazer com que o servidor fique indisponível. Portanto, deve consumir os slots de replicação lógica de forma contínua. Se um slot de replicação lógica não for mais usado, solte-o imediatamente.

A active coluna na pg_replication_slots vista indica se há um consumidor ligado a uma slot.

SELECT * FROM pg_replication_slots;

Defina alertas nas métricas Máximo de IDs de Transação Usados 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 aplicam-se conforme documentado aqui.

• Slots e ativação pós-falha de HA - No PostgreSQL 16 e em versões anteriores, ao utilizar servidores com alta disponibilidade (HA) no Base de Dados do Azure para PostgreSQL, os slots de replicação lógica não são preservados durante eventos de ativação pós-falha. Para manter os slots de replicação lógica e garantir a consistência dos dados após um failover, utilize a extensão PG Failover Slots e configure definições de suporte como hot_standby_feedback = on. Para mais informações sobre como ativar esta extensão, consulte a documentação.

Suporte de failover para slots de replicação lógica

Para PostgreSQL 17 e versões posteriores, a sincronização de slots é suportada nativamente. Se você habilitar as configurações corretas do PostgreSQL (sync_replication_slots, hot_standby_feedback), os slots de replicação lógica serão preservados automaticamente após o failover e nenhuma extensão será necessária.

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.