Consultas para Otimização e Depuração da Replicação do PostgreSQL

A replicação lógica do PostgreSQL fornece o poder e a organização por trás de um cluster de replicação pgEdge, permitindo que você replique tabelas de forma seletiva e, em um nível mais granular, as alterações nessas tabelas. Se você está usando a replicação PostgreSQL distribuída do pgEdge para análises em tempo real, baixa latência ou alta disponibilidade, otimizar a configuração da replicação e o uso de consultas permite que você otimize para desempenho, consistência e confiabilidade.

A replicação do Postgres é uma ferramenta poderosa para replicar dados entre bancos de dados; ao contrário da replicação física, a replicação lógica oferece mais controle e flexibilidade sobre quais dados são replicados e como são utilizados.

Este blog explora consultas que facilitam a gestão da replicação lógica para o seu banco de dados PostgreSQL.

Monitorando o Status da Replicação Lógica do Postgres

Monitorar o status da sua configuração de replicação lógica é crítico para garantir que sua replicação esteja funcionando sem problemas. Consultar a visão pg_stat_subscription pode ajudar você a monitorar o status de todas as assinaturas em seu banco de dados:

SELECT 

    subname AS subscription_name,

    pid AS process_id,

    usename AS user_name,

    application_name,

    client_addr AS client_address,

    state,

    sync_state,

    sent_lsn,

    write_lsn,

    flush_lsn,

    replay_lsn,

    clock_timestamp() - write_lsn_timestamp AS replication_delay

FROM 

    pg_stat_subscription

ORDER BY 

    subscription_name;

​

​

subscription_name | process_id | user_name | application_name | client_address |    state    | sync_state |   sent_lsn   |  write_lsn   |  flush_lsn   |  replay_lsn  | replication_delay

-------------------+------------+-----------+------------------+----------------+-------------+------------+--------------+--------------+--------------+--------------+-------------------

 sub1              |      23456 | postgres  | logical_rep_sub   | 192.168.1.10   | streaming   | synced     | 0/3000128    | 0/3000128    | 0/3000128    | 0/3000128    | 00:00:00.12345

 sub2              |      23478 | postgres  | logical_rep_sub   | 192.168.1.11   | catchup     | async      | 0/4000238    | 0/4000200    | 0/40001F8    | 0/40001E0    | 00:00:02.67890

• subname – O nome da assinatura.
• state – O estado do processo de assinatura (por exemplo, streaming, catchup, inicializando).
• sync_state – O estado de sincronização da assinatura.
• sent_lsn, write_lsn, flush_lsn, replay_lsn – Essas colunas representam vários Números de Sequência de Log (LSNs) que indicam o progresso da replicação.
• replication_delay – O atraso entre o LSN ser escrito e sua aplicação no assinante é crucial para identificar atrasos na replicação.

Esta consulta fornece uma visão abrangente do status da replicação lógica, permitindo que você identifique rapidamente problemas como atrasos na replicação ou assinantes desconectados.

Analisando o Atraso na Replicação do Postgres

Compreender o atraso na replicação é essencial para manter a consistência e a atualidade dos dados em seus bancos de dados replicados. A visualização do sistema pg_replication_slots pode ajudá-lo a calcular o atraso na replicação entre o publicador e o assinante:

SELECT 

    s.slot_name,

    s.active,

    s.restart_lsn,

    pg_wal_lsn_diff(pg_current_wal_lsn(), s.restart_lsn) AS replication_lag_bytes,

    clock_timestamp() - pg_last_xact_replay_timestamp() AS replication_lag_time

FROM 

    pg_replication_slots s

WHERE 

    s.active = true

AND 

    s.plugin = 'pgoutput';

​

​

slot_name | active | restart_lsn | replication_lag_bytes | replication_lag_time 

-----------+--------+-------------+-----------------------+-----------------------

 slot1     | t      | 0/3000128   |                 65536 | 00:00:00.12345

 slot2     | t      | 0/4000238   |                131072 | 00:00:02.67890

• slot_name – O nome do slot de replicação sendo usado.
• replication_lag_bytes – A diferença em bytes entre a posição atual do WAL no publicador e a última posição do WAL reconhecida pelo assinante.
• replication_lag_time – A diferença de tempo entre a última transação reproduzida no assinante e o tempo atual.

Essa consulta ajuda a avaliar o tamanho e o atraso baseado no tempo em sua replicação lógica, permitindo que você tome medidas proativas se o atraso exceder os limites aceitáveis.

Monitorando o Uso de Slots de Replicação

Os slots de replicação são críticos na replicação lógica, garantindo que os segmentos WAL sejam retidos até que todos os assinantes os processem. Você pode consultar a visualização pg_replication_slots para monitorar o uso dos slots de replicação:

SELECT

    slot_name,

    plugin,

    slot_type,

    active,

    confirmed_flush_lsn,

    pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn) AS slot_lag_bytes

FROM 

    pg_replication_slots

WHERE 

    slot_type = 'logical';

​

​

slot_name | plugin  | slot_type | active | confirmed_flush_lsn | slot_lag_bytes 

-----------+---------+-----------+--------+---------------------+----------------

 slot1     | pgoutput| logical   | t      | 0/3000128           |           65536

 slot2     | pgoutput| logical   | t      | 0/4000238           |          131072

• slot_name – O nome do slot de replicação.
• slot_lag_bytes – O atraso em bytes entre a posição atual do WAL e a última posição confirmada como gravada pelo slot.

Monitorar o uso de slots de replicação é crucial para evitar problemas relacionados à retenção de segmentos WAL, o que poderia levar potencialmente à exaustão do espaço em disco no publicador.

Descartando Slots de Replicação Não Utilizados

Ao longo do tempo, você pode acumular slots de replicação não utilizados, especialmente após remover assinantes ou alterar configurações de replicação. Esses slots não utilizados podem causar retenção desnecessária de arquivos WAL, levando ao desperdício de espaço em disco. A seguinte consulta identifica e descarta slots de replicação não utilizados:

DO $$

DECLARE

    slot_record RECORD;

BEGIN

    FOR slot_record IN

        SELECT slot_name FROM pg_replication_slots WHERE active = false

    LOOP

        EXECUTE format('SELECT pg_drop_replication_slot(%L)', slot_record.slot_name);

    END LOOP;

END $$;

Esta consulta itera sobre seus slots de replicação inativos e utiliza a função de gerenciamento pg_drop_replication_slot para removê-los. Limpar regularmente os slots de replicação não utilizados garantirá que seu banco de dados permaneça eficiente e evitará possíveis problemas com a retenção de arquivos WAL.

Criando Slots de Replicação

Se você precisa criar um novo slot de replicação lógica, a seguinte consulta é útil:

SELECT * FROM pg_create_logical_replication_slot('my_slot', 'pgoutput');

​

slot_name | xlog_position 

-----------+---------------

 my_slot   | 0/3000128

Esta consulta utiliza a função pg_create_logical_replication_slot para criar um novo slot de replicação lógica com o nome e o plugin de saída especificados (pgoutput em nosso exemplo). A consulta é útil ao configurar novas configurações de replicação lógica; use-a para confirmar que o assinante pode começar a receber alterações a partir do ponto correto nos registros WAL.

Otimizando a Replicação Lógica Com pglogical

Se você está utilizando a extensão pglogical para capacidades avançadas de replicação lógica, a seguinte consulta pode ajudá-lo a verificar o status de todas as assinaturas pglogical:

SELECT 

    subscription_name,

    status,

    received_lsn,

    replay_lag,

    last_received_change,

    pending_changes

FROM 

    pglogical.show_subscription_status();

​

​

subscription_name | status   | received_lsn | replay_lag | last_received_change | pending_changes 

-------------------+----------+--------------+------------+---------------------+-----------------

 sub_pglogical1    | replicating | 0/3000128 | 00:00:01.234 | 2024-08-22 10:30:00 | 5

 sub_pglogical2    | idle      | 0/4000238 | 00:00:00.000 | 2024-08-22 10:29:30 | 0

• subscription_name – O nome da assinatura pglogical.
• replay_lag – O atraso entre a última alteração recebida e o momento atual.
• pending_changes – O número de alterações pendentes a serem aplicadas ao assinante.

Esta consulta fornece uma visão detalhada de suas assinaturas do pglogical, ajudando você a ajustar as configurações de replicação e solucionar problemas.

Conclusão

O pgEdge Distributed PostgreSQL utiliza replicação lógica em todo o seu cluster, proporcionando maior controle e flexibilidade sobre exatamente quais dados são replicados e como esses dados são armazenados. O pgEdge continua desenvolvendo ferramentas versáteis que oferecem controle detalhado sobre os processos de replicação de dados. As consultas destacadas neste blog podem ajudá-lo a monitorar, gerenciar e otimizar efetivamente seus clusters de replicação lógica. Essas consultas ajudam a garantir a consistência dos dados, minimizar o atraso na replicação e evitar conflitos, todos essenciais para manter um ambiente de banco de dados robusto e confiável.

Ao continuar trabalhando com a replicação lógica, considere incorporar essas consultas em suas rotinas regulares de monitoramento e manutenção para garantir que seus bancos de dados PostgreSQL e clusters pgEdge funcionem de forma otimizada.