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

本記事は [PostgreSQL Advent Calendar 2023](https://qiita.com/advent-calendar/2023/postgresql) 8 日目の記事です。


## pg_stasinfoとは

[PostgreSQLカンファレンス2023](https://www.postgresql.jp/jpug-pgcon2023)にて、株式会社アシストの保田さんがpg_statsinfoについて紹介していました。

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

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

### pg_stats_reporter

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

![スクリーンショット 2023-12-03 114923.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/70076/0b931f29-d5c5-751e-4f94-4e721371b5fa.png)

## 何が問題なのか？

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に表示してみたいと思います。

![検証環境.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/70076/da863719-72a0-641c-80d9-80d6031cca9b.png)

## 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
```

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

```sh
#!/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の管理画面をブラウザで表示し、[設定]→[ホスト]→監視対象のホストのアイテムリンクをクリックし、右上の「アイテムの作成」をクリックしてホストにアイテムを追加します。

![スクリーンショット 2023-12-04 221832.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/70076/a7786367-6cb5-1aae-b59a-1cf70f4d5a8d.png)

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

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

![スクリーンショット 2023-12-04 222436.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/70076/13c744cd-e716-ba91-7ca8-b9a56e4f4b39.png)

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

![スクリーンショット 2023-12-04 222809.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/70076/80b93800-1997-fc49-f491-41934acda95e.png)

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

## まとめ

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