本記事は 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で詳細を確認する、という運用もやればできるのではないでしょうか。