図解で、Datadog DBM データベースのモニタリング設定（PostgreSQL）

# サーバーがホストするPostgreSQLのモニタリング設定手順
前回のMySQLに続きPostgreSQLの図解で設定手順を記載する。Database Monitoring (DBM) を対象に最小限の設定手順を記載し、後半は詳細モニタリングを追加する設定箇所を記録する。

**設定でのつまづきポイントは３つ**

-  DBログファイルの権限
Datadogエージェントが読み込むDBログファイルに読み込みできる権限を設定すること
- PostgreSQLのバージョン毎
スキーマの設定コマンドが異なるので、バージョンに合わせてコマンドを選ぶこと
- DBパフォーマンス設定
パフォーマンスの詳細は、いくつかのオプションを追加設定することをおススメ

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/189936/1262a51c-4abd-ae95-d61c-389cc2a412d3.png)


## PostgreSQL モニタリング対象の構成
セルフホスト型PostgreSQL（サーバーOS にインストールした PostgreSQL）をモニタリングする。モニタリングは、クエリの応答時間とエラー率を可視化し、詳細モニタリングを追加する設定方法を検証する。検証環境：Ubuntu20.04 + PostgreSQL12.22

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/189936/b40eccf5-5f69-7dbe-9bec-944d81dc525a.png)


## 設定手順
Datadogエージェントがインストール済みであることを前提とする。
1. PostgreSQL のパラメータを設定
2. PostgreSQL にOSユーザー[datadog]を追加し、パフォーマンスの読み取り権限を付与する
3. Datadogエージェントの postgres.yaml コンフィグを設定する
4. PostgreSQL のログを取得する


![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/189936/81698d02-fb4e-3839-d038-9a8416f25118.png)


## （１）PostgreSQL のパラメータを設定
Datadogエージェントがクエリのメトリクスを収集するには下記パラメータ[*1]が必須だが、ほとんどはMySQLデフォルトなら有効になっていた(*1:MySQL5.7以降の場合/5.6以下の場合は公式ドキュメント参照方)。

ファイルの場所：PostgreSQLバージョン12の場合： /etc/postgresql/12/main/postgresql.conf
- shared_preload_libraries: pg_stat_statements（必須）
- track_activity_query_size: 4096（推奨。デフォルト1024）
- その他: 任意, 推奨のパラメータは[公式ドキュメント](https://docs.datadoghq.com/ja/database_monitoring/setup_postgres/selfhosted/?tab=postgres15#postgres-%E8%A8%AD%E5%AE%9A%E3%82%92%E6%A7%8B%E6%88%90%E3%81%99%E3%82%8B)を参照方

設定値の確認手順

``` Ubuntuコマンド
$ sudo nano /etc/postgresql/12/main/postgresql.conf
```

## （２）PostgreSQL にOSユーザー[datadog]を追加し、パフォーマンスの読み取り権限を付与する

``` Ubuntuコマンド
$ sudo su - postgres
$ psql
```

DBを読み取りするためにユーザー[datadog]を任意のパスワードを決めて作成。
```
CREATE USER datadog WITH password '任意のdatadogパスワード';
```

ロール[datadog]に関連権限を付与。ここからPostgreSQLバージョン毎に異なる。
[PostgreSQL 15以上](https://docs.datadoghq.com/ja/database_monitoring/setup_postgres/selfhosted/?tab=postgres15)　※下記の通り
[PostgreSQL 10~14](https://docs.datadoghq.com/ja/database_monitoring/setup_postgres/selfhosted/?tab=postgres10)　※公式ドキュメントを参照方
[PostgreSQL 9.6](https://docs.datadoghq.com/ja/database_monitoring/setup_postgres/selfhosted/?tab=postgres96)　※公式ドキュメントを参照方


```
ALTER ROLE datadog INHERIT;
```

スキーマ[datadog]を作成し、利用権限を[USAGE]をユーザー[datadog]に付与。
スキーマ[public]にも利用権限をユーザー[datadog]に付与。
ロール[pg_monitor]をユーザー[datadog]に付与。
[pg_stat_statements]はSQL統計情報を収集するもので、存在しなければ作成する。
```
CREATE SCHEMA datadog;
GRANT USAGE ON SCHEMA datadog TO datadog;
GRANT USAGE ON SCHEMA public TO datadog;
GRANT pg_monitor TO datadog;
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
```

パフォーマンス分析するための関数を作成する。関数の内容は、スキーマ[datadog]に、[explain_statement]という関数を作成、テキストで受取、JSONで出力。宣言[Declare]で変数[curs, plan]を定義。BEGINからENDまでが関数でplanに出力を返すもの。

```
CREATE OR REPLACE FUNCTION datadog.explain_statement(
   l_query TEXT,
   OUT explain JSON
)
RETURNS SETOF JSON AS
$$
DECLARE
curs REFCURSOR;
plan JSON;

BEGIN
   OPEN curs FOR EXECUTE pg_catalog.concat('EXPLAIN (FORMAT JSON) ', l_query);
   FETCH curs INTO plan;
   CLOSE curs;
   RETURN QUERY SELECT plan;
END;
$$
LANGUAGE 'plpgsql'
RETURNS NULL ON NULL INPUT
SECURITY DEFINER;
```

psqlを終了する
```
# \q
```

設定できたことを３つ、OKが表示されることを検査する。パスワードを聞かれたらユーザー[datadog]で設定したパスワードを入力する。
```
ubuntu $ psql -h localhost -U datadog postgres -A \
  -c "select * from pg_stat_database limit 1;" \
  && echo -e "\e[0;32mPostgres connection - OK\e[0m" \
  || echo -e "\e[0;31mCannot connect to Postgres\e[0m"

Postgres connection - OK

ubuntu $ psql -h localhost -U datadog postgres -A \
  -c "select * from pg_stat_activity limit 1;" \
  && echo -e "\e[0;32mPostgres pg_stat_activity read OK\e[0m" \
  || echo -e "\e[0;31mCannot read from pg_stat_activity\e[0m"

Postgres pg_stat_activity read OK

ubuntu $ psql -h localhost -U datadog postgres -A \
  -c "select * from pg_stat_statements limit 1;" \
  && echo -e "\e[0;32mPostgres pg_stat_statements read OK\e[0m" \
  || echo -e "\e[0;31mCannot read from pg_stat_statements\e[0m"

Postgres pg_stat_statements read OK
```

## （３）Datadogエージェントの PostgreSQLコンフィグを設定する
PostgreSQLメトリックスを取得するため postgres.yamlを作成、コンフィグを設定する。
``` 
$ cd /etc/datadog-agent/conf.d
$ cp postgres.d/conf.yaml.example postgres.yaml
```

datadogのコンフィグは /etc/datadog-agent/conf.d ディレクトリに yaml ファイルを置いても参照してくれるので SSH接続したときに色分けして見やすいようにすることも可能。どのチェックが有効なのかディレクトリ内を出入りしなくても見つけやすくなる。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/189936/9d6e38fe-3df9-0cb9-1a40-11270fd018d2.png)

下記はPostgreSQL10以降の場合。[PostgreSQL9.6の場合はこちら](https://docs.datadoghq.com/ja/database_monitoring/setup_postgres/selfhosted/?tab=postgres96)２行追加する。

``` postgres.yaml
init_config:
instances:
  - dbm: true
    host: localhost
    port: 5432
    username: datadog
    password: '任意のdatadogパスワード'
```

## 再起動して PostgreSQL ダッシュボードを検査

PostgreSQLとDatadogエージェントを再起動
```
$ sudo systemctl restart postgresql
$ sudo systemctl restart datadog-agent
$ sudo datadog-agent status "collector"

    postgres (22.4.0)
    -----------------
      Instance ID: postgres:1b28631d1f4f9ff0 [OK]
      Configuration Source: file:/etc/datadog-agent/conf.d/postgres.yaml
      <略>
      Database Monitoring Activity Samples: Last Run: 2, Total: 385
      Database Monitoring Metadata Samples: Last Run: 1, Total: 30
      Database Monitoring Query Metrics: Last Run: 1, Total: 423
      Database Monitoring Query Samples: Last Run: 3, Total: 82
      <略>
```


Datadogダッシュボードの検査
Dashbards > Dashboard List

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/189936/44540f05-c73f-72ed-fdbf-47109eab3bab.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/189936/b73e3316-6a62-669a-f154-a2f2192fee03.png)


## （４）PostgreSQL のログを出力する設定

デフォルトでは出力する設定が無効になっている。
``` /etc/postgresql/12/main/postgresql.conf
# ログ出力を有効化
logging_collector = on

# ログファイルを保存するディレクトリを指定。下記の場合[/etc/postgresql/12/main/log]
log_directory = '/etc/postgresql/12/pg_log'

# ファイル名を指定
log_filename = 'postgresql.log'

# ファイル読み取り権限を追加
log_file_mode = 0644

# 出力するログレベル
log_min_duration_statement = 100  # 100 m秒以上かかるクエリをログ

#クエリ実行計画をログに出力する場合
log_statement = 'all'
log_duration = on
```

PostgreSQLを再起動する
```
sudo systemctl restart postgresql
```


これだけだとユーザー[dd-agent]がログファイルのアクセス権がない。
```
# sudo -u dd-agent cat /etc/postgresql/12/pg_log/postgresql.log: Permission denied
```

ログファイルの所有グループは[postgres]なので、ユーザー[dd-agent]を追加して検査する。
```
drwxr-x--- 2 postgres dd-agent 4096 Feb  2 21:53 pg_log
-rw-r----- 1 postgres dd-agent 1656651 Feb  2 22:12 postgresql.log
```

PostgreSQL がログを出力したら、Datadogエージェントのコンフィグ[postgres.yaml]にログファイルのパスを追加する。

``` /etc/datadog-agent/conf.d/postgres.yaml
logs:
  - type: file
    path: /etc/postgresql/12/pg_log/postgresql.log
    source: postgresql
```


postgresqlとdatadog-agentを再起動してMySQLダッシュボードでログを検査する
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/189936/174e32ed-7d24-ea11-d31e-2b9d99e7e604.png)

以上、DBMを設定したときの手順確認。

## （５）ここからPostgreSQLをモニタリングするオプション

### PostgreSQLの設定を読み込む

``` postgres.yaml
collect_settings:
  enabled: true
  collection_interval: 6000   # デフォルト600（10分毎）
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/189936/363e3732-3cd3-055c-60be-a33bd9d432e8.png)

### データベースを自動検出

``` /etc/datadog-agent/conf.d/postgres.yaml
#    database_autodiscovery:
#      enabled: true
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/189936/e920b0ec-054e-f4c1-4155-0df175be3fab.png)

### PostgreSQL のスキーマをモニタリングする設定

Datadogエージェントの postgres コンフィグに追加

``` /etc/datadog-agent/conf.d/postgres.yaml
    collect_schemas:
      enabled: true
    relations:
      - relation_regex: .*
```

### サーバー及びAPMサービスの関連付け

``` /etc/datadog-agent/conf.d/postgres.yaml
    reported_hostname: dojo.funnygeekjp.com

    tags:
      - 'env:prod'
      - 'team:funnygeek'
      - 'service:heatrun'
      - 'appname:postgres'
```

ここまでオプションを設定すると詳細のパフォーマンスモニタがスタートした。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/189936/0ab68593-e2b6-995a-8cc4-583160297a7b.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/189936/adad2353-7ffa-0a1c-643c-05b0c11d846d.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/189936/81fdb6a5-ff90-7d87-106d-7a6315d3ef79.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/189936/a5ba4aca-8e43-f5c9-b78a-67abb68395d5.png)

### APM アプリパフォーマンスモニタも可視化された

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/189936/e6544e20-0c52-a1c6-77b1-22159a03dcf0.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/189936/79beb25a-221a-d078-be96-916967b132c4.png)


## DB種類/制限事項/条件

### DBモニタリングの対象DB
- AWS RDS,
- AWS Aurora
- Self Hosted（サーバーOSにインストール）
- Google CloudSQL
- Azure Database for Postgres

### 条件
- MySQLバージョン 9.6以降
- Datadogエージェント 7.36.1以降

### 制限事項
- パフォーマンス影響 ワークロードの1%未満
- Datadogエージェントのローカル127.0.0.1。プロキシ、ロードバランサー、コネクションプーラーを介してDB接続しないこと。
- [PostgreSQL設定詳細](https://docs.datadoghq.com/ja/database_monitoring/setup_postgres/selfhosted?tab=postgres15)
- [機密情報は難読化される](https://docs.datadoghq.com/ja/database_monitoring/data_collected/#%E6%A9%9F%E5%AF%86%E6%83%85%E5%A0%B1)

### モニタリング負荷（オーバーヘッド）
- チェックの最小収集間隔15秒
- クエリメトリクスの収集間隔10秒
- クエリアクティビティの収集間隔10秒
- クエリサンプルの収集間隔1秒
- 設定の収集間隔600秒

### オーバーヘッド（Agentバージョン: 7.50.2）
- CPU: 平均CPUの約2%
- メモリ: 約300 MiB
- ネットワーク帯域幅: IN約40KB/秒 | OUT約30KB/秒

以上

## まとめ
仮想サーバーがホストする PostgreSQL を対象に、Datadog データベースモニタリング(DBM)設定手順を検証した。DBモニタリングでできること、設定手順まで確認できた。