Replicação lógica e descodificação lógica na Base de Dados do Azure para PostgreSQL – Servidor Flexível

Artigo
04/27/2024

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 dá suporte às seguintes metodologias lógicas de extração e replicação de dados:

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 controlo otimizado da replicação de dados, incluindo a replicação de dados ao nível da tabela.
2. Usando extensão pglogical que fornece replicação de streaming lógico e mais recursos, como copiar o esquema inicial do banco de dados, suporte para TRUNCATE, capacidade de replicar DDL, etc.
Decodificação lógica que é implementada decodificando o conteúdo do write-ahead log (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 log write-ahead (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.
Use 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

Vá para a página de parâmetros do servidor no portal.
Defina o parâmetro wal_level server como logical.
Se você quiser usar uma extensão pglógica, procure o shared_preload_libraries, e azure.extensions parâmetros e selecione pglogical na caixa de listagem suspensa.
Atualize max_worker_processes o valor do parâmetro para pelo menos 16. Caso contrário, você pode encontrar problemas como WARNING: out of background worker slots.
Salve as alterações e reinicie o servidor para aplicá-las.
Confirme se sua instância de servidor flexível do Banco de Dados do Azure para PostgreSQL permite o tráfego de rede do seu recurso de conexão.
Conceda permissões de replicação ao usuário administrador.
```
ALTER ROLE <adminname> WITH REPLICATION;
```
Talvez você queira certificar-se de que a função que você está usando tem privilégios no esquema que você está replicando. Caso contrário, você pode encontrar erros como Permission denied for schema.

Nota

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

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

Usar a replicação lógica nativa é a maneira mais simples de replicar dados do Banco de Dados do Azure para o servidor flexível PostgreSQL. Você pode usar a interface SQL ou o protocolo de streaming para consumir as alterações. Você também pode usar a interface SQL para consumir alterações usando decodificação lógica.

Replicação lógica nativa

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

O editor é o Banco de Dados do Azure para banco de dados de servidor flexível PostgreSQL do qual você está enviando dados.
O assinante é o Banco de Dados do Azure para o banco de dados de servidor flexível 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.

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

Crie uma publicação para a tabela.

CREATE PUBLICATION pub FOR TABLE basic;

Conecte-se ao banco de dados de assinantes. Crie uma tabela com o mesmo esquema do editor.
```
CREATE TABLE basic (id INTEGER NOT NULL PRIMARY KEY, a TEXT);
```

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;

Agora você pode consultar a tabela no assinante. Você vê que ele recebeu dados do editor.
```
SELECT * FROM basic;
```
Você pode adicionar mais linhas à tabela do editor e exibir as alterações no assinante.

Se não conseguir ver os dados, ative o privilégio azure_pg_admin de início de sessão 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 diferentes bancos de dados 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 atualmente presentes. 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 dentro do mesmo comando; caso contrário, a CREATE SUBSCRIPTION chamada trava, em um evento de LibPQWalReceiverReceive espera. Isso acontece 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 enquanto contorna essa restrição, siga as etapas descritas abaixo:

Primeiro, crie uma tabela chamada "basic" 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, o pg_create_logical_replication_slot que ajuda a evitar o problema de deslocamento que normalmente ocorre quando o slot é criado no mesmo comando da assinatura. Você precisa usar o pgoutput plugin:

-- 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 create_slot o servidor flexível do Banco de Dados do Azure 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, em seguida, verificando se ele replica 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 pglógica

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

Instale a extensão pglógica no banco de dados nos servidores de banco de dados do provedor e do assinante.
```
\c myDB
CREATE EXTENSION pglogical;
```
Se o usuário de replicação for diferente do usuário de administração do servidor (que criou o servidor), certifique-se de conceder associação em uma função azure_pg_admin ao usuário e atribuir atributos REPLICATION e LOGIN ao usuário. Consulte a documentação pglógica para obter detalhes.
```
GRANT azure_pg_admin to myUser;
ALTER ROLE myUser REPLICATION LOGIN;
```

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

Crie um conjunto de replicação.

select pglogical.create_replication_set('myreplicationset');

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']);
```

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

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

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

SELECT subscription_name, status FROM pglogical.show_subscription_status();

Atenção

Atualmente, o Pglogical não suporta 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. Por favor, esteja ciente de outras limitações da extensão listada aqui.

Descodificação lógica

A decodificação lógica pode ser consumida através do protocolo de streaming ou da interface SQL.

Protocolo de streaming

Consumir alterações usando o protocolo de streaming geralmente é preferível. Você pode criar seu próprio consumidor / conector, ou usar um serviço de terceiros como Debezium.

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

Interface SQL

No exemplo abaixo, usamos a interface SQL com o plugin wal2json.

Crie um slot.

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

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

Consuma as alterações.

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

O resultado tem o seguinte aspeto:

{
      "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"]
                        }
               }
      ]
}

Solte o slot assim que 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.

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 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, os slots de replicação lógica devem ser consumidos continuamente. Se um slot de replicação lógica não for mais usado, solte-o imediatamente.

A coluna "ativo" na pg_replication_slots vista indica se existe um consumidor ligado a uma ranhura.

SELECT * FROM pg_replication_slots;

Defina alertas nas métricas flexíveis de servidor Maximum Used Transaction IDs e Storage Used Azure Database for PostgreSQL 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 failover de HA - Ao usar servidores habilitados para alta disponibilidade (HA) com o servidor flexível do Banco de Dados do Azure para PostgreSQL, lembre-se de que os slots de replicação lógica não são preservados durante eventos de failover. Para manter os slots de replicação lógica e garantir a consistência dos dados após um failover, é recomendável usar a extensão PG Failover Slots. Para obter mais informações sobre como ativar essa extensão, consulte a documentação.

Importante

Você deve 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 o slot de replicação lógica não esteja em uso (devido a um assinante indisponível). Nesse caso, a instância flexível do servidor do Banco de Dados do Azure para PostgreSQL descarta automaticamente esse slot de replicação lógica não utilizado. Essa ação libera arquivos WAL acumulados e evita que seu servidor fique indisponível devido à situação de armazenamento cheio.

Partilhar via

Replicação lógica e descodificação lógica na Base de Dados do Azure para PostgreSQL – Servidor Flexível

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

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

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

Replicação lógica nativa

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

extensão pglógica

Descodificação lógica

Protocolo de streaming

Interface SQL

Monitor

Limitações

Recursos adicionais

Partilhar via

Replicação lógica e descodificação lógica na Base de Dados do Azure para PostgreSQL – Servidor Flexível

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

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

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

Replicação lógica nativa

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

extensão pglógica

Descodificação lógica

Protocolo de streaming

Interface SQL

Monitor

Limitações

Conteúdos relacionados

Recursos adicionais