Requêtes pour optimiser et déboguer la réplication PostgreSQL

Tutoriels

PostgreSQL

La réplication logique de PostgreSQL fournit la puissance et l’organisation derrière un cluster de réplication pgEdge, vous permettant de répliquer des tables de manière sélective et, à un niveau plus granulaire, les modifications apportées à ces tables. Que vous utilisiez la réplication PostgreSQL distribuée pgEdge pour des analyses en temps réel, une faible latence ou une haute disponibilité, l’optimisation de la configuration de la réplication et de l’utilisation des requêtes vous permet d’optimiser les performances, la cohérence et la fiabilité.

La réplication Postgres est un outil puissant pour répliquer des données entre des bases de données ; contrairement à la réplication physique, la réplication logique vous offre plus de contrôle et de flexibilité sur les données qui sont répliquées et sur la manière dont elles sont utilisées.

Ce blog explore les requêtes qui facilitent la gestion de la réplication logique pour votre base de données PostgreSQL.

Surveillance de l’état de la réplication logique Postgres

Surveiller l’état de votre configuration de réplication logique est essentiel pour garantir que votre réplication fonctionne correctement. Interroger la vue pg_stat_subscription peut vous aider à surveiller l’état de toutes les abonnements dans votre base de données :

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 – Le nom de l’abonnement.
state – L’état du processus d’abonnement (par exemple, streaming, rattrapage, initialisation).
sync_state – L’état de synchronisation de l’abonnement.
sent_lsn, write_lsn, flush_lsn, replay_lsn – Ces colonnes représentent divers numéros de séquence de journal (LSN) qui indiquent la progression de la réplication.
replication_delay – Le délai entre l’écriture du LSN et son application sur l’abonné est crucial pour identifier le retard dans la réplication.

Cette requête offre un aperçu complet de l’état de la réplication logique, vous permettant d’identifier rapidement des problèmes tels que le retard de réplication ou des abonnés déconnectés.

Analyse du retard de réplication de Postgres

Comprendre le retard de réplication est essentiel pour maintenir la cohérence et la fraîcheur des données dans vos bases de données répliquées. La vue système pg_replication_slots peut vous aider à calculer le retard de réplication entre l’éditeur et l’abonné :

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 – Le nom de l’emplacement de réplication utilisé.
replication_lag_bytes – La différence en octets entre la position actuelle du WAL sur l’éditeur et la dernière position du WAL acquittée par l’abonné.
replication_lag_time – La différence de temps entre la dernière transaction rejouée sur l’abonné et l’heure actuelle.

Cette requête vous aide à évaluer la taille et le retard temporel dans votre réplication logique, vous permettant de prendre des mesures proactives si le retard dépasse des seuils acceptables.

Surveillance de l’utilisation des slots de réplication

Les slots de réplication sont critiques dans la réplication logique, garantissant que les segments WAL sont conservés jusqu’à ce que tous les abonnés les traitent. Vous pouvez interroger la vue pg_replication_slots pour surveiller l’utilisation des slots de réplication :

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 – Le nom du slot de réplication.
slot_lag_bytes – Le retard en octets entre la position WAL actuelle et la dernière position confirmée comme flushée par le slot.

Surveiller l’utilisation des slots de réplication est crucial pour prévenir les problèmes liés à la rétention des segments WAL, ce qui pourrait potentiellement entraîner une exhaustion de l’espace disque sur le serveur d’origine.

Suppression des slots de réplication inutilisés

Au fil du temps, vous pouvez accumuler des slots de réplication inutilisés, surtout après avoir supprimé des abonnés ou modifié des configurations de réplication. Ces slots inutilisés peuvent provoquer une rétention inutile des fichiers WAL, entraînant un gaspillage d’espace disque. La requête suivante identifie et supprime les slots de réplication inutilisés :

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

Cette requête itère sur vos slots de réplication inactifs et utilise la fonction de gestion pg_drop_replication_slot pour les supprimer. Nettoyer régulièrement les slots de réplication inutilisés garantira que votre base de données reste efficace et évitera d’éventuels problèmes de rétention des fichiers WAL.

Création de Slots de Réplication

Si vous devez créer un nouveau slot de réplication logique, la requête suivante est utile:

SQL

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

​

slot_name | xlog_position 

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

 my_slot   | 0/3000128

Cette requête utilise la fonction pg_create_logical_replication_slot pour créer un nouveau slot de réplication logique avec le nom spécifié et le plugin de sortie (pgoutput dans notre exemple). Utilisez la requête lors de la configuration de nouvelles réplications logiques; utilisez-la pour confirmer que l’abonné peut commencer à recevoir les modifications à partir du bon point dans les enregistrements WAL.

Optimisation de la Réplication Logique Avec pglogical

Si vous utilisez l’extension pglogical pour des capacités de réplication logique plus avancées, la requête suivante peut vous aider à vérifier l’état de toutes les souscriptions 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

nom_souscription – Le nom de la souscription pglogical.
délai_rejeu – Le décalage entre la dernière modification reçue et l’instant présent.
pending_changes – Le nombre de changements en attente d’être appliqués au abonné.

Cette requête fournit un aperçu détaillé de vos abonnements pglogical, vous aidant à affiner les paramètres de réplication et à résoudre les problèmes.

Conclusion

pgEdge Distributed PostgreSQL utilise la réplication logique à travers votre cluster, offrant un plus grand contrôle et une plus grande flexibilité sur ce qui est précisément répliqué et sur la manière dont ces données sont stockées. pgEdge continue de développer des outils polyvalents offrant un contrôle précis sur les processus de réplication des données. Les requêtes présentées dans ce blog peuvent vous aider à surveiller, gérer et optimiser efficacement vos clusters de réplication logique. Ces requêtes aident à garantir la cohérence des données, à minimiser le délai de réplication et à éviter les conflits, éléments essentiels pour maintenir un environnement de base de données robuste et fiable.

Alors que vous continuez à travailler avec la réplication logique, envisagez d’intégrer ces requêtes dans vos routines régulières de surveillance et de maintenance pour garantir des performances optimales de vos bases de données PostgreSQL et de vos clusters pgEdge.

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