Vragen voor het optimaliseren en debuggen van PostgreSQL-replicatie

PostgreSQL logische replicatie biedt de kracht en organisatie achter een pgEdge replicatiecluster, waardoor je tabellen selectief kunt repliceren en, op een meer gedetailleerd niveau, de wijzigingen in die tabellen. Of je nu pgEdge Gedistribueerde PostgreSQL-replicatie gebruikt voor real-time analytics, lage latentie of hoge beschikbaarheid, het optimaliseren van replicatieconfiguratie en het gebruik van query’s stelt je in staat te optimaliseren voor prestaties, consistentie en betrouwbaarheid.

Postgres replicatie is een krachtige tool voor het repliceren van gegevens tussen databases; in tegenstelling tot fysieke replicatie geeft logische replicatie je meer controle en flexibiliteit over welke gegevens worden gerepliceerd en hoe deze worden gebruikt.

Deze blog verkent query’s die het gemakkelijker maken om logische replicatie voor je PostgreSQL-database te beheren.

Monitoring van de status van Postgres logische replicatie

Het monitoren van de status van je logische replicatieopstelling is cruciaal om ervoor te zorgen dat je replicatie soepel verloopt. Het bevragen van de pg_stat_subscription view kan je helpen de status van alle abonnementen in je database te monitoren:

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 – De naam van het abonnement.
• state – De status van het abonnementsproces (bijv. streamen, catchup, initialiseren).
• sync_state – De synchronisatiestatus van het abonnement.
• sent_lsn, write_lsn, flush_lsn, replay_lsn – Deze kolommen vertegenwoordigen verschillende Log Sequence Numbers (LSNs) die de voortgang van replicatie aangeven.
• replication_delay – De vertraging tussen het schrijven van de LSN en de toepassing ervan op de abonnee is cruciaal voor het identificeren van vertraging in replicatie.

Deze query biedt een uitgebreid overzicht van de status van logische replicatie, waardoor u snel problemen zoals replicatievertraging of losgekoppelde abonnees kunt identificeren.

Het analyseren van Postgres Replicatievertraging

Het begrijpen van replicatievertraging is essentieel voor het handhaven van de consistentie en actualiteit van gegevens over uw gerepliceerde databases. De pg_replication_slots systeemweergave kan u helpen de replicatievertraging tussen de uitgever en abonnee te berekenen:

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 – De naam van de gebruikte replicatiesleuf.
• replication_lag_bytes – Het verschil in bytes tussen de huidige WAL-positie op de uitgever en de laatste door de abonnee erkende WAL-positie.
• replication_lag_time – Het tijdsverschil tussen de laatste transactie die op de abonnee is afgespeeld en de huidige tijd.

Deze query helpt u de omvang en tijdsvertraging van uw logische replicatie te beoordelen, zodat u proactieve maatregelen kunt nemen als de vertraging de acceptabele drempels overschrijdt.

Monitoring van het gebruik van replicatieslots

Replicatieslots zijn cruciaal in logische replicatie, waarbij wordt gegarandeerd dat WAL-segmenten worden behouden totdat alle abonnees ze verwerken. U kunt de weergave pg_replication_slots bevragen om het gebruik van replicatieslots te controleren:

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 – De naam van het replicatieslot.
• slot_lag_bytes – De vertraging in bytes tussen de huidige WAL-positie en de laatste positie die bevestigd is als geflusht door het slot.

Het monitoren van het gebruik van replicatieslots is cruciaal om problemen met betrekking tot de retentie van WAL-segmenten te voorkomen, die mogelijk kunnen leiden tot uitputting van de schijfruimte op de uitgever.

Verwijderen van ongebruikte replicatieslots

In de loop van de tijd kunt u ongebruikte replicatieslots accumuleren, vooral na het verwijderen van abonnees of het wijzigen van replicatieconfiguraties. Deze ongebruikte slots kunnen onnodige retentie van WAL-bestanden veroorzaken, wat leidt tot verspilde schijfruimte. De volgende query identificeert en verwijdert ongebruikte replicatieslots:

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

Deze query itereert over uw inactieve replicatieslots en gebruikt de beheerfunctie pg_drop_replication_slot om ze te verwijderen. Door regelmatig ongebruikte replicatieslots op te ruimen, zorgt u ervoor dat uw database efficiënt blijft en voorkomt u mogelijke problemen met de retentie van WAL-bestanden.

Replicatieslots maken

Als u een nieuwe logische replicatieslot moet maken, is de volgende query nuttig:

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

​

slot_name | xlog_position 

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

 my_slot   | 0/3000128

Deze query maakt gebruik van de functie pg_create_logical_replication_slot om een nieuw logisch replicatieslot met de opgegeven naam en uitvoerplug-in (pgoutput in ons voorbeeld) aan te maken. De query is handig bij het instellen van nieuwe logische replicatieconfiguraties; gebruik het om te bevestigen dat de abonnee wijzigingen kan beginnen ontvangen vanaf het juiste punt in de WAL-records.

Optimaliseren van logische replicatie met pglogical

Als u de pglogical-extensie gebruikt voor meer geavanceerde logische replicatiemogelijkheden, kan de volgende query u helpen bij het controleren van de status van alle pglogical-abonnementen:

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 – De naam van het pglogical-abonnement.
• replay_lag – De vertraging tussen de laatst ontvangen wijziging en de huidige tijd.
• pending_changes – Het aantal wijzigingen dat nog moet worden toegepast op de abonnee.

Deze query biedt een gedetailleerd overzicht van uw pglogical-abonnementen, waardoor u de replicatie-instellingen kunt verfijnen en problemen kunt oplossen.

Conclusie

pgEdge Distributed PostgreSQL maakt gebruik van logische replicatie over uw cluster, met meer controle en flexibiliteit over welke gegevens precies worden gerepliceerd en hoe die gegevens worden opgeslagen. pgEdge blijft veelzijdige tools ontwikkelen die fijnmazige controle bieden over de gegevensreplicatieprocessen. De queries die in deze blog worden beschreven, kunnen u helpen bij het effectief monitoren, beheren en optimaliseren van uw logische replicatieclusters. Deze queries helpen ervoor te zorgen dat de gegevens consistent blijven, de replicatievertraging wordt geminimaliseerd en conflicten worden voorkomen, allemaal essentieel voor het behouden van een robuuste en betrouwbare databaseomgeving.

Terwijl u blijft werken met logische replicatie, overweeg dan om deze queries op te nemen in uw reguliere monitoring- en onderhoudsroutines om ervoor te zorgen dat uw PostgreSQL-databases en pgEdge-clusters optimaal presteren.