PostgreSql ストリーミングレプリケーション動作状況確認

はじめに
レプリケーションの動作状況の確認
レプリケーション監視の重要性

はじめに

PostgreSQLのストリーミングレプリケーションは、データの可用性と冗長性を高める強力な仕組みです。しかし、構築しただけでは安心できません。運用フェーズでは、レプリケーションの状態監視、障害対応、遅延対策など、複数の観点から注意を払う必要があります。

本ブログでは、Windows環境でPostgreSQLのストリーミングレプリケーションを構築済みの方を対象に、運用時の状態監視方法を紹介します。

ストリーミングレプリケーションの構築方法は下記ブログを参照ください。

ストリーミングレプリケーション構築方法

レプリケーションの動作状況の確認

まずは、レプリケーションが正常に動作しているかを定期的に確認することが重要です。

プライマリ動作の確認

以下のSQLで、スタンバイとの接続状況や遅延を確認します。

SELECT pid, client_addr, state, sync_state, write_lag, flush_lag, replay_lag
FROM pg_stat_replication;

state
ストリーミングレプリケーションなので、streaming なら正常

sync_state
同期レプリケーションなので、sync なら正常

write_lag
WALをスタンバイに送信した後、スタンバイがそのWALを書き込むまでにかかった時間。
正常値は、0～数百ミリ秒。５秒以上ならば、スタンバイのI/O性能低下、ネットワーク障害、WAL送信設定不備などが疑われる。

flush_lag
プライマリサーバがWALを送信してから、スタンバイサーバがそのWALを「ディスクにフラッシュ（書き込み確定）」するまでにかかった時間を示す指標。
正常値は、0～数百ミリ秒。５秒以上は異常と考えられる。

replay_lag
スタンバイサーバがプライマリから受信したWALを「実際に適用（replay）」するまでにかかった時間を示す指標。
正常値は、0～数百ミリ秒。５秒以上は異常と考えられる。

コマンドの実行例

※write_lag、flush_log、replay_log がNULLなのは、現在新しいWALが生成されていない為。

スタンバイ側動作の確認

スタンバイ側では、WAL受信状況を以下で確認できます。

SELECT status, receive_start_lsn, receive_start_tli, written_lsn, flushed_lsn, latest_end_lsn, latest_end_time FROM pg_stat_wal_receiver;

status
ストリーミングレプリケーションなので、streaming なら正常

receive_start_lsn
WAL受信開始位置。
通常運用では頻繁に変化しないため、急な変更があれば再構築や障害の兆候有り。

receive_start_tli
受信開始時のタイムラインID。
フェイルオーバー後にタイムラインが変わるため、プライマリとの整合性確認に使用する。タイムラインが一致していない場合、スタンバイが古い状態である可能性がある。

written_lsn
スタンバイがWALを書き込んだ最新位置。
latest_end_lsn との差が大きいと、WAL受信はしているが書き込みが遅れている可能性あり。

flushed_lsn
スタンバイがWALをフラッシュ（ディスクに確定書き込み）した最新位置。
written_lsn より遅れている場合、フラッシュ処理が滞っている。

latest_end_lsn
プライマリが送信した最新のWALの位置。
written_lsn や flushed_lsn と比較して遅延を把握する。位置。

latest_end_time
latest_end_lsn に対応するWALのタイムスタンプ。
スタンバイが最新WALをどれだけ遅れて受信しているかの目安になる。

コマンドの実行例

また、現在のWAL適用位置は以下で確認できます。

SELECT pg_last_wal_replay_lsn();

PowerShellでの監視例

Windows環境では、PowerShellを使って定期監視スクリプトを作成できます。タスクスケジューラで定期実行すれば、ログ監視や通知にも応用可能です。

スクリプト例（replication_monitor.ps1）

#スクリプトの実行制限（ポリシー）の確認と変更
Get-ExecutionPolicy
Set-ExecutionPolicy RemoteSigned -Scope CurrentUser

# PostgreSQL接続情報
$PGUSER = "postgres"
$PGDATABASE = "postgres"
$PGHOST = "localhost"
$PGPORT = "5432"

# psqlコマンドのパス（環境に応じて変更）
$PSQL = "C:\Program Files\PostgreSQL\bin\psql.exe"

# ログファイル出力先
$LOGFILE = "C:\pg_backup\pg_shell_log\replication_monitor.log"

# 日時取得
$timestamp = Get-Date -Format "yyyy-MM-dd HH:mm:ss"

# pg_stat_replicationの取得（プライマリ側のレプリケーション状態の取得）
$replication = & $PSQL -U $PGUSER -d $PGDATABASE -h $PGHOST -p $PGPORT -c "SELECT state, sync_state, write_lag, flush_lag, replay_lag FROM pg_stat_replication;" 2>&1

# pg_stat_wal_receiverの取得（スタンバイ側のWAL受信状態取得）
$receiver = & $PSQL -U $PGUSER -d $PGDATABASE -h $PGHOST -p $PGPORT -c "SELECT status, written_lsn, flushed_lsn, latest_end_lsn FROM pg_stat_wal_receiver;" 2>&1

# ログ出力
Add-Content -Path $LOGFILE -Value "[$timestamp] Replication Status:"
Add-Content -Path $LOGFILE -Value $replication
Add-Content -Path $LOGFILE -Value "[$timestamp] WAL Receiver Status:"
Add-Content -Path $LOGFILE -Value $receiver
Add-Content -Path $LOGFILE -Value "----------------------------------------"