PostgreSQLレプリケーションの最適化とデバッグのためのクエリ

PostgreSQLの論理レプリケーションは、pgEdgeレプリケーションクラスターの裏にあるパワーと組織を提供し、テーブルを選択的にレプリケートし、それらのテーブルの変更をより詳細にレプリケートすることができます。 pgEdge Distributed PostgreSQLレプリケーションをリアルタイムアナリティクス、低遅延、または高可用性に使用している場合、レプリケーション構成とクエリの使用を最適化することで、パフォーマンス、一貫性、信頼性を最適化することができます。 Postgresレプリケーションは、データベース間でデータをレプリケートする強力なツールです。物理的なレプリケーションとは異なり、論理レプリケーションはどのデータをレプリケートするか、そしてそれをどのように使用するかについて、より多くの制御と柔軟性を提供します。このブログでは、PostgreSQLデータベースの論理レプリケーションを管理しやすくするクエリについて探求します。Postgres論理レプリケーションの状態を監視することは重要です。レプリケーションがスムーズに実行されていることを確認するためです。

Postgresレプリケーション。監視するために、データベース内のすべてのサブスクリプションの状態を監視するのに

、

Postgres論理レプリケーションの状態を監視する

重要です。 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 – これらの列は複製の進捗を示すさまざまなログシーケンス番号（LSNs）を表します。
• replication_delay – LSNの書き込みとサブスクライバーへの適用の間の遅延は、複製における遅延を特定するのに重要です。

このクエリは、論理複製のステータスの包括的な概要を提供し、複製の遅延や切断されたサブスクライバーなどの問題を迅速に特定できます。

Postgresの複製遅延の分析

理解することは、レプリケートされたデータベース全体でデータの一貫性と新鮮さを維持する上で不可欠です。 複製遅延を理解することは、レプリケーション遅延を計算するのに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

このクエリは、指定した名前と出力プラグイン（この例ではpgoutput）で新しい論理レプリケーションスロットを作成するためにpg_create_logical_replication_slot関数を使用します。新しい論理レプリケーション構成を設定する際に、このクエリを使用して、購読者がWALレコードの正しいポイントから変更を受信できることを確認してください。

 pglogicalを使用した論理レプリケーションの最適化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 – サブスクライバに適用される予定の変更の数。

This query provides a detailed overview of your pglogical subscriptions, helping you fine-tune replication settings and troubleshoot issues.

Conclusion

pgEdge Distributed PostgreSQLは、クラスタ全体で論理レプリケーションを使用し、どのデータを正確にレプリケートし、そのデータをどのように保存するかについての詳細な制御と柔軟性を提供します。 pgEdgeは、データレプリケーションプロセスに対して細かい制御を提供する多目的ツールを開発し続けています。このブログで概説されたクエリは、論理レプリケーションクラスタを効果的に監視、管理、最適化するのに役立ちます。これらのクエリは、データの整合性を確保し、レプリケーションの遅延を最小限に抑え、競合を防ぐのに役立ちます。これらは、堅牢で信頼性のあるデータベース環境を維持するために不可欠です。

論理レプリケーションを継続的に使用する際には、これらのクエリを定期的なモニタリングとメンテナンスルーチンに組み込んで、PostgreSQLデータベースとpgEdgeクラスタが最適に機能することを確認してください。