pg_statsinfoの内容をZabbixから見たい！

本記事は PostgreSQL Advent Calendar 2023 8 日目の記事です。

pg_stasinfoとは

PostgreSQLカンファレンス2023にて、株式会社アシストの保田さんがpg_statsinfoについて紹介していました。

PostgreSQLサーバの利用統計情報をスナップショットとして定期的に収集・蓄積し、レポートを生成してDB設計やPostgreSQLの運用(日々の処理傾向の把握、性能劣化などの兆候や問題発生時の原因の把握等)に役立つ拡張機能です。
詳細は公式のドキュメントを参照してください。

さらに、pg_stats_reporterという別の拡張機能を導入することで、収集した情報をブラウザで確認することができます。

pg_stats_reporter

pg_statsinfoが収集した情報をブラウザで確認するための拡張機能です。
導入すると、次のような画面で情報の確認が可能です。PostgreSQLの運用や監視に実に便利ですね。

何が問題なのか？

pg_stasinfoとpg_stats_reporterは、PostgreSQLの管理のために必要な情報を収集・表示してくれる非常に便利なツールです。しかし当然ですが、PostgreSQLに関係しないことは表示してくれません。

でも私の経験では、実際の運用管理では基本的にシステム全体で統一した監視ツールを使用することが多い印象です。
運用チームに「DBだけは別のツール使って」とは言い出しにくいですし、私も言われたら「えーめんどい」と言うと思います。

Zabbixにpg_statsinfoの内容を表示させたい！

というわけで、よく使われる（であろう）監視ツールであるZabbixにpg_statsinfoの内容を表示できれば、運用監視ツールが一本化できるのではないかと考えました！

Zabbixとは

Zabbix社が開発しているオープンソースの統合監視ツールです。
標準的なプロトコルでサーバやサービスの死活監視を行ったり、専用エージェントをインストールすればOSやハードウェアの情報を収集できたり、それをサーバから確認できたりします。
割りとなんでもできてしまう便利な監視ツールです。

検証環境

• 監視対象のPostgreSQLサーバ
  • OS : Rocky Linux 8.9
  • PostgreSQL 15.5
  • pg_statsinfo 15.2
  • Zabbix Agent 6.0

監視対象のPostgreSQLが最新版の16ではないのは、本記事の執筆時点でpg_statsinfoが15までしか対応していないためです。

• 運用監視サーバ
  • OS : Rocky Linux 8.9
  • PostgreSQL 15.5
  • Zabbix Server 6.0
  • Nginx 1.25.3
  • PHP 8.0
  • pg_stats_reporter 15.1

ZabbixはデータベースとしてMySQLかPostgreSQLを使用します。PostgreSQLを使用したのは趣味であると同時に、pg_statsinfoが情報を蓄積するリポジトリDBとして使用するためです。

ZabbixのHTTPサーバとしてNginxを使用したのは完全に趣味です。Apacheでも問題ありませんし、pg_stats_reporterをデフォルトでインストールすると、Apacheも一緒にインストールされます。
仕方がないのでNginxをリバースプロキシとして使用し、Apacheには外部から直接アクセスしないようにします。

というわけで以下のような環境でpg_statsinfoが収集した内容をZabbixに表示してみたいと思います。

pg_statsinfoのコマンド

pg_statsinfoはpg_stats_reporterで情報を確認できるだけではなく、コマンドによってテキストでレポートを出力することもできます。

$ pg_statsinfo -r REPORTID [-i INSTANCEID] [-b SNAPID] [-e SNAPID] [-B DATE] [-E DATE] [-o FILENAME] [connection-options]

Zabbix Serverの中身とpg_statsinfoのリポジトリDBを調べてZabbix Serverを改造するよりは、このレポートを加工して送れればいけそうな気がします。

REPORTIDに指定できるIDはたくさんあります。「ALL」を指定すればすべての情報をレポートとして出力してくれますが、情報が多いので今回は「Summary」を指定します。今回はpg_statsinfoが監視するDBと情報を蓄積しているリポジトリDBを別のサーバにしていますので、レポート出力の際はリポジトリDBが存在する運用監視サーバを指定する必要があります。

$ pg_statsinfo -r Summary -h <運用監視サーバ>

次のような結果が表示されるはずです。

---------------------------------------------
STATSINFO Report (host: hosts, port: 5432)
---------------------------------------------

----------------------------------------
/* Summary */
----------------------------------------
Database System ID   : 7307271813869373234
Host                 : hosts
Port                 : 5432
PostgreSQL Version   : 15.5
Snapshot Begin       : 2023-12-02 23:00:00
Snapshot End         : 2023-12-03 13:00:00
Snapshot Duration    : 14:00:00
Total Database Size  : 9117 KiB
Total Commits        : 4927447
Total Rollbacks      : 0

監視スクリプトの作成

ディレクトリを適当に作っておきます。

# mkdir -p /var/lib/zabbix/bin

そこにこんな感じのスクリプトを作成します。

#!/bin/sh

cd `dirname $0`

REPOSITORY=リポジトリDBのIPアドレス
DBUSER=postgres
DATABASE=postgres

export PATH=/usr/pgsql-15/bin:${PATH}

pg_statsinfo -r Summary -h ${REPOSITORY} -d ${DATABASE} -U ${DBUSER}

パスワード無しで接続できるように、zabbixユーザ用の.pgpassも用意しておきましょう。

# vi /var/lib/zabbix/.pgpass

フォルダとファイルの準備ができたら所有者や権限を変更します。

# chmod 600 /var/lib/zabbix/.pgpass
# chmod 755 /var/lib/zabbix/bin/pg_statsinfo_summary.sh
# chown -R zabbix:zabbix /var/lib/zabbix

Zabbix Agentから監視スクリプトを実行させる

Zabbix Agentの設定ファイルを編集します。

# vi /etc/zabbix/zabbix_agentd.conf

以下の項目を追加します。

AllowKey=system.run[*]
UserParameter=pg_statsinfo.summary,HOME=/var/lib/zabbix /var/lib/zabbix/bin/pg_statsinfo_summary.sh

準備ができたらZabbix Agentを再起動します。

# systemctl restart zabbix-agent

Zabbix Serverで表示してみる

Zabbixの管理画面をブラウザで表示し、[設定]→[ホスト]→監視対象のホストのアイテムリンクをクリックし、右上の「アイテムの作成」をクリックしてホストにアイテムを追加します。

名前は他のアイテムと重複しなければ何でも構いません。大事なのはキーで、ユーザパラメータに指定したものを間違いなく指定する必要があります。データ型はpg_statsinfoの結果を考えるとテキストがよいでしょう。

アイテムの追加ができたら試しにダッシュボードにアイテムを追加してみます。

なにか見えます。クリックしてみます。

pg_statsinfoのコマンドの結果が表示できました！

まとめ

今回はとりあえず表示できることを確認したかったのでpg_statsinfoのサマリを表示してみました。
運用の際は、通常時はZabbixのみを確認するようにして異常に気づけるような情報をZabbixに表示し、異常を検知したらpg_stats_reporterで詳細を確認する、という運用もやればできるのではないでしょうか。