استعلامات لتحسين وتصحيح استنساخ PostgreSQL

يوفر التكرار المنطقي في PostgreSQL القوة والتنظيم وراء مجموعة تكرار pgEdge، مما يتيح لك تكرار الجداول بشكل انتقائي و، على مستوى أكثر تفصيلاً، التغييرات في تلك الجداول. سواء كنت تستخدم تكرار PostgreSQL الموزع pgEdge للتحليلات في الوقت الحقيقي، أو الكفاءة الحية، أو التوفر العالي، يسمح لك تحسين تكوين التكرار واستخدام الاستعلام بتحسين الأداء والاتساق والموثوقية.

تكرار بوستغرس هو أداة قوية لتكرار البيانات بين قواعد البيانات؛ على عكس التكرار المادي، يمنحك التكرار المنطقي مزيدًا من التحكم والمرونة فيما يتعلق بالبيانات التي يتم تكرارها وكيفية استخدامها.

يستكشف هذا المدونة الاستعلامات التي تجعل من السهل إدارة التكرار المنطقي لقاعدة بيانات PostgreSQL الخاصة بك.

مراقبة حالة تكرار بوستغرس المنطقي

مراقبة حالة إعداد التكرار المنطقي الخاص بك أمر حاسم لضمان سلسلة تشغيل التكرار الخاص بك. يمكن أن يساعدك استعلام عرض pg_stat_subscription في مراقبة حالة جميع الاشتراكات في قاعدة البيانات الخاصة بك:

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 – اسم الاشتراك.
• state – حالة عملية الاشتراك (على سبيل المثال، التدفق، اللحاق، التهيئة).
• sync_state – حالة المزامنة للإشتراك.
• sent_lsn، write_lsn، flush_lsn، replay_lsn – تمثل هذه الأعمدة مجموعات تسلسل السجل (LSN) المختلفة التي تشير إلى تقدم التكرار.
• replication_delay – التأخير بين كتابة LSN وتطبيقه على المشترك أمر حاسم لتحديد التأخر في التكرار.

يوفر هذا الاستعلام نظرة شاملة على حالة التكرار اللوجي، مما يتيح لك تحديد المشاكل بسرعة مثل تأخر التكرار أو المشتركين المفصولين.

تحليل تأخر التكرار في بوستجريس

فهم تأخر التكرار أمر أساسي للحفاظ على اتساق وجدارة البيانات عبر قواعد البيانات المستنسخة الخاصة بك. يمكن لعرض النظام pg_replication_slots مساعدتك في حساب تأخر التكرار بين الناشر والمشترك:

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 – اسم فتحة التكرار المستخدمة.
• replication_lag_bytes – الفرق في البايتات بين الموضع الحالي لـ WAL على الناشر وآخر موضع WAL تم الاعتراف به من قبل المشترك.
• replication_lag_time – الفرق الزمني بين آخر معاملة تمت إعادتها على المشترك والوقت الحالي.

هذا الاستعلام يساعدك على تقييم الحجم وتأخير الزمن في التكرار المنطقي الخاص بك، مما يمكنك من اتخاذ تدابير استباقية إذا تجاوز التأخير الحدود المقبولة.

مراقبة استخدام فتحة التكرار

تعتبر فتحات التكرار حاسمة في التكرار المنطقي، حيث تضمن الاحتفاظ بقطع WAL حتى يتم معالجتها من قبل جميع المشتركين. يمكنك الاستعلام عن عرض pg_replication_slots لمراقبة استخدام فتحات التكرار:

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 – اسم فتحة التكرار.
• slot_lag_bytes – التأخير بالبايت بين وضع WAL الحالي وآخر وضع تم تأكيده كمتدفق بواسطة الفتحة.

مراقبة استخدام فتحة التكرار أمر حاسم لمنع المشاكل المتعلقة بالاحتفاظ بقطع WAL، والتي قد تؤدي بالإمكان إلى نفاد مساحة القرص على الناشر.

إسقاط فتحات التكرار غير المستخدمة

مع مرور الوقت، قد تتراكم فتحات تكرار غير مستخدمة، خاصة بعد إزالة المشتركين أو تغيير تكوينات التكرار. يمكن أن تتسبب هذه الفتحات غير المستخدمة في الاحتفاظ غير الضروري بملفات WAL، مما يؤدي إلى هدر مساحة القرص. يحدد الاستعلام التالي ويسقط الفتحات غير المستخدمة:

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

تكرار هذا الاستعلام على فتحات النسخ الاحتياطي الخاملة الخاصة بك واستخدام وظيفة الإدارة pg_drop_replication_slot لإسقاطها. تنظيف فتحات النسخ الاحتياطي غير المستخدمة بانتظام سيضمن أن تظل قاعدة البيانات الخاصة بك فعالة ويمنع المشاكل المحتملة المتعلقة باحتفاظ ملفات WAL.

إنشاء فتحات النسخ الاحتياطي

إذا كنت بحاجة إلى إنشاء فتحة نسخ تكرارية منطقية جديدة، فإن الاستعلام التالي مفيد:

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

​

slot_name | xlog_position 

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

 my_slot   | 0/3000128

يستخدم هذا الاستعلام وظيفة pg_create_logical_replication_slot لإنشاء فتحة نسخ تكرارية منطقية جديدة بالاسم المحدد والإضافة المخرجة (pgoutput في مثالنا). الاستعلام مفيد عند إعداد تكوينات تكرارية منطقية جديدة؛ استخدمه للتحقق من أن المشترك يمكنه بدء تلقي التغييرات من النقطة الصحيحة في سجلات WAL.

تحسين التكرار اللوجي بـ
pglogical

إذا كنت تستخدم الامتداد pglogical لميزات تكرارية لوجية متقدمة أكثر، فإن الاستعلام التالي يمكن أن يساعدك في التحقق من حالة جميع الاشتراكات الخاصة بـ 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 – اسم الاشتراك pglogical.

• replay_lag – تأخر الإعادة بين آخر تغيير تم استلامه والوقت الحالي.
• pending_changes – عدد التغييرات المعلقة التي يجب تطبيقها على المشترك.

يوفر هذا الاستعلام نظرة عامة مفصلة عن اشتراكاتك في pglogical، مما يساعدك على ضبط إعدادات النسخ وحل المشاكل.

الاستنتاج

يستخدم pgEdge Distributed PostgreSQL التكرار المنطقي عبر عنقودتك، مما يوفر تحكمًا أكبر ومرونة أكبر في تحديد بدقة البيانات التي يتم تكرارها وكيفية تخزين تلك البيانات. يستمر pgEdge في تطوير أدوات متعددة الاستخدامات تقدم تحكمًا دقيقًا على عمليات تكرار البيانات. يمكن أن تساعدك الاستعلامات الموضحة في هذه المدونة في مراقبة وإدارة وتحسين عنقودات التكرار المنطقي الخاصة بك بشكل فعال. تساعد هذه الاستعلامات في ضمان تناسق البيانات، وتقليل تأخر التكرار، ومنع الصراعات، كلها أمور أساسية للحفاظ على بيئة قاعدة بيانات قوية وموثوقة.

أثناء مواصلتك العمل مع التكرار المنطقي، فكر في دمج هذه الاستعلامات في rutines الخاصة بمراقبة وصيانة العادية لضمان أداء قواعد البيانات الخاصة بك وعنقودات pgEdge بشكل مثلى.