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

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

A replicação 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 é fundamental para garantir que sua replicação esteja funcionando sem problemas. Consultar a visualização pg_stat_subscription pode ajudar 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, inicialização).
• 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 gravado 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 todos os seus bancos de dados replicados. A visualização do sistema pg_replication_slots pode ajudar 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.

Esta consulta ajuda a avaliar o tamanho e a defasagem baseada no tempo em sua replicação lógica, permitindo que você tome medidas proativas se a defasagem 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 do 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 – A defasagem em bytes entre a posição atual do WAL e a última posição confirmada como descarregada pelo slot.

Monitorar o uso de slots de replicação é crucial para prevenir problemas relacionados à retenção de segmentos do WAL, que poderiam levar à 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 do 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 os seus slots de replicação inativos e utiliza a função de gestão pg_drop_replication_slot para os eliminar. Limpar regularmente os slots de replicação não utilizados garantirá que o seu banco de dados permaneça eficiente e evitará possíveis problemas de retenção de arquivos WAL.

Criando Slots de Replicação

Se precisar de 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 plugin de saída especificados (pgoutput no nosso exemplo). A consulta é útil ao configurar novas configurações de replicação lógica; utilize-a para confirmar que o assinante pode começar a receber alterações a partir do ponto correto nos registos WAL.

Otimizando a Replicação Lógica Com pglogical

Se estiver a utilizar a extensão pglogical para capacidades de replicação lógica mais avançadas, a seguinte consulta pode ajudá-lo a verificar o estado de todas as subscrições 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 subscrição 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 PostgreSQL Distribuído pgEdge usa replicação lógica em todo o seu cluster, oferecendo maior controle e flexibilidade sobre exatamente quais dados são replicados e como esses dados são armazenados. O pgEdge continua a desenvolver ferramentas versáteis que oferecem controle detalhado sobre os processos de replicação de dados. As consultas delineadas 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.