Abfragen zur Optimierung und Fehlerbehebung von PostgreSQL-Replikation

Tutorials

PostgreSQL

PostgreSQL-Logische Replikation bietet die Leistung und Organisation hinter einem pgEdge-Replikationscluster, das es Ihnen ermöglicht, Tabellen selektiv zu replizieren und auf einer granularen Ebene die Änderungen in diesen Tabellen zu replizieren. Egal, ob Sie pgEdge Distributed PostgreSQL-Replikation für Echtzeitanalyse, geringe Latenz oder hohe Verfügbarkeit nutzen, durch die Optimierung der Replikationskonfiguration und Abfrageverwendung können Sie die Leistung, Konsistenz und Zuverlässigkeit optimieren.

Postgres-Replikation ist ein leistungsstolles Tool zur Replikation von Daten zwischen Datenbanken; im Gegensatz zur physischen Replikation bietet die logische Replikation mehr Kontrolle und Flexibilität darüber, welche Daten repliziert werden und wie sie verwendet werden.

Dieser Blog untersucht Abfragen, die es einfacher machen, die logische Replikation für Ihre PostgreSQL-Datenbank zu verwalten.

Überwachung des Status der logischen Postgres-Replikation

Die Überwachung des Status Ihres logischen Replikations-Setups ist entscheidend, um sicherzustellen, dass Ihre Replikation reibungslos läuft. Durch Abfragen der pg_stat_subscription-Ansicht können Sie den Status aller Abonnements in Ihrer Datenbank überwachen:

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 – Der Name des Abonnements.
state – Der Status des Replikationsprozesses (z. B. Streaming, Catchup, Initialisieren).
sync_state – Der Synchronisierungsstatus des Abonnements.
sent_lsn, write_lsn, flush_lsn, replay_lsn – Diese Spalten stellen verschiedene Log Sequence Numbers (LSNs) dar, die den Replikationsfortschritt anzeigen.
replication_delay – Die Verzögerung zwischen dem Schreiben des LSN und seiner Anwendung beim Abonnenten ist entscheidend, um Verzögerungen in der Replikation zu identifizieren.

Diese Abfrage bietet einen umfassenden Überblick über den Status der logischen Replikation, der es Ihnen ermöglicht, schnell Probleme wie Replikationsverzögerungen oder getrennte Abonnenten zu identifizieren.

Analysieren von Postgres Replikationsverzögerungen

Das Verständnis von Replikationsverzögerungen ist entscheidend für die Aufrechterhaltung der Konsistenz und Aktualität von Daten in Ihren replizierten Datenbanken. Die pg_replication_slots Systemansicht kann Ihnen helfen, die Replikationsverzögerung zwischen dem Publisher und dem Abonnenten zu berechnen:

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 – Der Name des verwendeten Replikationsschlitzes.
replication_lag_bytes – Der Unterschied in Bytes zwischen der aktuellen WAL-Position auf dem Publisher und der zuletzt vom Abonnenten bestätigten WAL-Position.
replication_lag_time – Der Zeitunterschied zwischen der zuletzt auf dem Abonnenten wiederholten Transaktion und der aktuellen Zeit.

Diese Abfrage hilft Ihnen, die Größe und die zeitliche Verzögerung in Ihrer logischen Replikation zu bewerten, sodass Sie proaktiv Maßnahmen ergreifen können, wenn die Verzögerung akzeptable Grenzwerte überschreitet.

Überwachung der Nutzung von Replikationsslots

Replikationsslots sind entscheidend für die logische Replikation, da sie sicherstellen, dass WAL-Segmente gespeichert werden, bis alle Abonnenten sie verarbeitet haben. Sie können die Ansicht pg_replication_slots abfragen, um die Nutzung der Replikationsslots zu überwachen:

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 – Der Name des Replikationsslots.
slot_lag_bytes – Die Verzögerung in Bytes zwischen der aktuellen WAL-Position und der zuletzt bestätigten als gespült geltenden Position des Slots.

Die Überwachung der Nutzung von Replikationsslots ist entscheidend, um Probleme im Zusammenhang mit der Speicherung von WAL-Segmenten zu vermeiden, die potenziell zu einer Erschöpfung des Festplattenspeichers beim Publisher führen könnten.

Verwerfen ungenutzter Replikationsslots

Im Laufe der Zeit können sich ungenutzte Replikationsslots ansammeln, insbesondere nach dem Entfernen von Abonnenten oder Ändern von Replikationskonfigurationen. Diese ungenutzten Slots können zu unnötiger Speicherung von WAL-Dateien führen und zu einer Verschwendung von Festplattenspeicherplatz führen. Die folgende Abfrage identifiziert und verwirft ungenutzte Replikationsslots:

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

Diese Abfrage iteriert über Ihre inaktiven Replikationsslots und verwendet die pg_drop_replication_slot-Verwaltungsfunktion, um sie zu löschen. Durch regelmäßiges Bereinigen ungenutzter Replikationsslots wird sichergestellt, dass Ihre Datenbank effizient bleibt und potenzielle Probleme mit der WAL-Dateiaufbewahrung vermieden werden.

Erstellen von Replikationsslots

Wenn Sie einen neuen logischen Replikationsslot erstellen müssen, ist die folgende Abfrage nützlich:

SQL

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

​

slot_name | xlog_position 

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

 my_slot   | 0/3000128

Diese Abfrage verwendet die Funktion pg_create_logical_replication_slot, um einen neuen logischen Replikationsslot mit dem angegebenen Namen und dem Ausgabestecker (pgoutput in unserem Beispiel) zu erstellen. Die Abfrage ist nützlich beim Einrichten neuer logischer Replikationskonfigurationen; nutzen Sie sie, um zu bestätigen, dass der Abonnent Änderungen ab dem richtigen Zeitpunkt in den WAL-Einträgen empfangen kann.

Optimierung der logischen Replikation mitpglogical

Wenn Sie die pglogical-Erweiterung für erweiterte logische Replikationsfunktionen verwenden, kann Ihnen die folgende Abfrage helfen, den Status aller pglogical-Abonnements zu überprüfen:

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 – Der Name des pglogical-Abonnements.
replay_lag – Die Verzögerung zwischen der zuletzt empfangenen Änderung und der aktuellen Zeit.
pending_changes – Die Anzahl der ausstehenden Änderungen, die auf den Abonnenten angewendet werden sollen.

Diese Abfrage bietet einen detaillierten Überblick über Ihre pglogical-Abonnements, um Ihnen bei der Feinabstimmung von Replikationseinstellungen und der Fehlerbehebung zu helfen.

Schlussfolgerung

pgEdge Distributed PostgreSQL verwendet die logische Replikation über Ihren Cluster, um eine größere Kontrolle und Flexibilität darüber zu bieten, welche Daten genau repliziert werden und wie diese Daten gespeichert werden. pgEdge entwickelt weiterhin vielseitige Tools, die eine fein abgestimmte Kontrolle über die Datenreplikationsprozesse bieten. Die in diesem Blog beschriebenen Abfragen können Ihnen dabei helfen, Ihre logischen Replikationscluster effektiv zu überwachen, zu verwalten und zu optimieren. Diese Abfragen helfen dabei, die Datenkonsistenz sicherzustellen, die Replikationsverzögerung zu minimieren und Konflikte zu verhindern, was alles wesentlich ist, um eine robuste und zuverlässige Datenbankumgebung aufrechtzuerhalten.

Wenn Sie weiterhin mit logischer Replikation arbeiten, sollten Sie erwägen, diese Abfragen in Ihre regelmäßigen Überwachungs- und Wartungsroutinen zu integrieren, um sicherzustellen, dass Ihre PostgreSQL-Datenbanken und pgEdge-Cluster optimal funktionieren.

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