Query per ottimizzare e debuggare la replica di PostgreSQL

Tutorial

PostgreSQL

La replica logica di PostgreSQL fornisce la potenza e l’organizzazione dietro un cluster di replica pgEdge, consentendoti di replicare tabelle in modo selettivo e, a un livello più granulare, le modifiche a quelle tabelle. Che tu stia utilizzando la replica PostgreSQL distribuita di pgEdge per analisi in tempo reale, bassa latenza o alta disponibilità, ottimizzare la configurazione della replica e l’uso delle query ti consente di ottimizzare le prestazioni, la coerenza e l’affidabilità.

La replica di Postgres è uno strumento potente per replicare dati tra database; a differenza della replica fisica, la replica logica ti offre maggiore controllo e flessibilità su quali dati vengono replicati e come vengono utilizzati.

Questo blog esplora le query che rendono più facile gestire la replica logica per il tuo database PostgreSQL.

Monitoraggio dello stato della replica logica di Postgres

Monitorare lo stato della tua configurazione di replica logica è fondamentale per garantire che la tua replica funzioni senza intoppi. Interrogare la vista pg_stat_subscription può aiutarti a monitorare lo stato di tutte le sottoscrizioni nel tuo database:

SQL

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 – Il nome della sottoscrizione.
state – Lo stato del processo di sottoscrizione (ad es., streaming, recupero, inizializzazione).
sync_state – Lo stato di sincronizzazione della sottoscrizione.
sent_lsn, write_lsn, flush_lsn, replay_lsn – Queste colonne rappresentano vari numeri di sequenza di log (LSN) che indicano il progresso della replica.
replication_delay – Il ritardo tra la scrittura dell’LSN e la sua applicazione sul subscriber è cruciale per identificare il ritardo nella replica.

Questa query fornisce una panoramica completa dello stato della replica logica, consentendoti di identificare rapidamente problemi come il ritardo nella replica o i subscriber disconnessi.

Analisi del Ritardo di Replica di Postgres

Comprendere il ritardo di replica è essenziale per mantenere la coerenza e la freschezza dei dati tra i tuoi database replicati. La vista di sistema pg_replication_slots può aiutarti a calcolare il ritardo di replica tra il publisher e il subscriber:

SQL

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 – Il nome dello slot di replica in uso.
replication_lag_bytes – La differenza in byte tra la posizione corrente del WAL sul publisher e l’ultima posizione del WAL riconosciuta dal subscriber.
replication_lag_time – La differenza di tempo tra l’ultimo replay della transazione sul subscriber e l’ora attuale.

Questa query ti aiuta a valutare la dimensione e il ritardo temporale basato nel tuo replica logica, consentendoti di adottare misure preventive se il ritardo supera le soglie accettabili.

Monitoraggio dell’Utilizzo dello Slot di Replica

Gli slot di replica sono fondamentali nella replica logica, garantendo che i segmenti WAL siano conservati fino a quando tutti i sottoscrittori li elaborano. Puoi interrogare la vista pg_replication_slots per monitorare l’utilizzo degli slot di replica:

SQL

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 – Il nome dello slot di replica.
slot_lag_bytes – Il ritardo in byte tra la posizione corrente del WAL e l’ultima posizione confermata come scritta dallo slot.

Monitorare l’utilizzo degli slot di replica è cruciale per prevenire problemi legati alla conservazione dei segmenti WAL, che potrebbero portare all’esaurimento dello spazio su disco del publisher.

Eliminazione degli Slot di Replica Non Utilizzati

Col passare del tempo, potresti accumulare slot di replica non utilizzati, specialmente dopo aver rimosso dei sottoscrittori o modificato le configurazioni di replica. Questi slot inutilizzati possono causare una conservazione non necessaria dei file WAL, portando a uno spreco di spazio su disco. La seguente query identifica ed elimina gli slot di replica non utilizzati:

SQL

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

Questa query itera sui tuoi slot di replica inattivi e utilizza la funzione di gestione pg_drop_replication_slot per eliminarli. Pulire regolarmente gli slot di replica inutilizzati garantirà che il tuo database rimanga efficiente e eviterà potenziali problemi di conservazione dei file WAL.

Creazione di Slot di Replica

Se hai bisogno di creare un nuovo slot di replica logica, la seguente query è utile:

SQL

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

​

slot_name | xlog_position 

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

 my_slot   | 0/3000128

Questa query utilizza la funzione pg_create_logical_replication_slot per creare un nuovo slot di replica logica con il nome specificato e il plugin di output (pgoutput nel nostro esempio). La query è utile durante la configurazione di nuove configurazioni di replica logica; usala per confermare che il subscriber possa iniziare a ricevere cambiamenti dal punto corretto nei record WAL.

Ottimizzazione della Replica Logica Con pglogical

Se stai utilizzando l’estensione pglogical per capacità di replica logica più avanzate, la seguente query può aiutarti a controllare lo stato di tutte le sottoscrizioni di pglogical:

SQL

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 – Il nome della sottoscrizione pglogical.
replay_lag – Il ritardo tra l’ultimo cambiamento ricevuto e il tempo corrente.
pending_changes – Il numero di modifiche in sospeso da applicare al subscriber.

Questa query fornisce una panoramica dettagliata delle tue sottoscrizioni pglogical, aiutandoti a ottimizzare le impostazioni di replica e risolvere problemi.

Conclusione

pgEdge Distributed PostgreSQL utilizza la replica logica su tutto il cluster, offrendo un maggiore controllo e flessibilità su quali dati vengono replicati e come tali dati vengono archiviati. pgEdge continua a sviluppare strumenti versatili che offrono un controllo dettagliato sui processi di replica dei dati. Le query descritte in questo blog possono aiutarti a monitorare, gestire ed ottimizzare efficacemente i tuoi cluster di replica logica. Queste query aiutano a garantire la coerenza dei dati, a ridurre al minimo il ritardo di replica e a prevenire conflitti, tutti essenziali per mantenere un ambiente di database robusto e affidabile.

Mentre continui a lavorare con la replica logica, considera l’incorporazione di queste query nelle tue routine regolari di monitoraggio e manutenzione per garantire che i tuoi database PostgreSQL e cluster pgEdge funzionino in modo ottimale.

Source:
https://dzone.com/articles/optimizing-debugging-postgresql-replication-queries