GrafanaでPostgreSQLのデータをグラフ化する

OSSのグラフ生成ツールGrafanaを使って、PostgreSQLデータベース内のデータをグラフで表示したいと、そういう記事。

Grafanaは、Kubernetes界隈ではPrometheusと組み合わせてCPU使用率とかのメトリクス情報をグラフ表示するツールとして一般的に用いられるが、データソースがPrometheusなのが難儀というか、一定期間以上前（半年前とか）に遡ってグラフを表示するというのが難しいので、そういう古いデータもDB内に保管して（あるいは、DBにデータをロードしなおして）表示したいという要件とか何かには有用なんじゃないかと。

AWSのアカウント持っていれば、慣れれば５分くらいで出来ます。

# 仮想マシンを作る
とりあえずAWS Lightsailに仮想マシンを作る。PostgreSQLとGrafanaサーバーを兼ねる。
https://aws.amazon.com/jp/lightsail-vps/

OSがCentOSのインスタンスを一つ。
この記事で書いてあることを実行するだけなら、初月無料の512MBメモリ、1vCPUのインスタンスでも動く。

# PostgreSQLをインストールする
１．作った仮想マシンにsshでログインし、rootになる。

```
Using username "centos".
Authenticating with public key "imported-openssh-key"
[centos@ip-172-26-4-90 ~]$ sudo -i
[root@ip-172-26-4-90 ~]#
```

２．PostgreSQLをインストールする。

```
# yum -y install postgresql-server
# postgresql-setup initdb
# systemctl enable postgresql.service
# systemctl start postgresql.service
```

３．grafanaが接続する先のデータベースを作る。

```
# sudo -u postgres psql -c "CREATE DATABASE grafana;"
```

４．グラフの元となるdata1というテーブルを作る。PUBLICにSELECTも許可する。

```
# cat > create_table.sql << 'EOF'
CREATE TABLE data1
(
  id serial,
  time timestamp with time zone NOT NULL DEFAULT now(),
  data integer NOT NULL,
  PRIMARY KEY (id)
)
EOF

# sudo -u postgres psql -d grafana < create_table.sql
# sudo -u postgres psql -d grafana -c "GRANT SELECT ON data1 TO PUBLIC;"
```

５．後のデータ表示のため、とりあえずいくつかのレコードを作成。

```
# sudo -u postgres psql -d grafana -c "INSERT INTO data1 (data) values (1);"
# sudo -u postgres psql -d grafana -c "INSERT INTO data1 (data) values (2);"
# sudo -u postgres psql -d grafana -c "INSERT INTO data1 (data) values (3);"

# sudo -u postgres psql -d grafana -c "SELECT * from data1;"
could not change directory to "/root"
 id |             time              | data
----+-------------------------------+------
  1 | 2020-12-09 06:39:12.954322+00 |    1
  2 | 2020-12-09 06:39:16.469155+00 |    2
  3 | 2020-12-09 06:39:19.221617+00 |    3
(3 rows)
```

６．grafanaユーザーでログインしてデータ表示できるよう、PostgreSQLの認証周りを調整。

```
# sed -i /var/lib/pgsql/data/pg_hba.conf -e "s/host    all             all             127.0.0.1\/32            ident/host    all             all             127.0.0.1\/32            md5/"
# systemctl restart postgresql.service
# sudo -u postgres psql -d grafana -c "CREATE USER grafana WITH LOGIN PASSWORD 'grafana';"

# psql -h 127.0.0.1 -d grafana -U grafana -W -c "SELECT * FROM data1;"
Password for user grafana:
 id |             time              | data
----+-------------------------------+------
  1 | 2020-12-09 06:39:12.954322+00 |    1
  2 | 2020-12-09 06:39:16.469155+00 |    2
  3 | 2020-12-09 06:39:19.221617+00 |    3
(3 rows)
```

# Grafanaをインストールする

詳細は以下。
https://grafana.com/docs/grafana/latest/installation/rpm/

１．Grafanaをインストールする。

```
# cat > /etc/yum.repos.d/grafana-oss.repo << 'EOF'
[grafana]
name=grafana
baseurl=https://packages.grafana.com/oss/rpm
repo_gpgcheck=1
enabled=1
gpgcheck=1
gpgkey=https://packages.grafana.com/gpg.key
sslverify=1
sslcacert=/etc/pki/tls/certs/ca-bundle.crt
EOF

# yum install -y grafana
# systemctl daemon-reload
# systemctl enable grafana-server
# systemctl start grafana-server
```

２．ポート3000番にアクセス可能なようネットワークを構成する。
Grafanaはデフォルトで3000/tcpポートでアクセスされるように起動するので、それに接続できるようネットワークを調整する。
LightsailのCentOS仮想マシンは、OSのfirewalldは無効だが、外側から3000番ポートでアクセスするにはLightsailのファイアウォールにルールを追加する必要がある。
Lightsailの仮想マシンの管理画面より、「ネットワーキング」>「ファイアウォール」でルールを追加する。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/146381/32748d0d-6179-8ba7-ed98-9354c6ee6839.png)


３．以下のURLにWebブラウザで接続し、Grafanaにログインする。
http://＜仮想マシンのIPアドレス＞:3000

初期ユーザー名、パスワードは admin / admin なので、速やかにログインしてパスワード変更するのが望ましい。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/146381/3c3d3196-0ea7-0b0e-0fe8-1a861d01c347.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/146381/59c14d16-f073-211b-49b3-307cfbe067c4.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/146381/42b347fd-b24b-00cf-8f83-b06fe5b68a68.png)


# PostgreSQLのデータソースを追加する

１．先のPostgreSQLを読み込むよう、データソースを追加する。
左メニューの「歯車」>「Data Sources」。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/146381/b5b57134-a72e-82ed-bcf5-64e28b1fb3ce.png)

２．「Add data source」。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/146381/12e213ef-731e-0859-3538-acd2f980807e.png)

３．データソースの一覧から「PostgreSQL」を選択する。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/146381/72853ecd-d128-3ca0-8ea2-24cfb0d4aa11.png)

４．以下を指定した後、「Save & Test」をクリックする。
　　Name: PostgreSQL
　　Host: localhost:5432
　　Database: grafana
　　User: grafana
　　Password: grafana
　　SSL Mode: disable
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/146381/db9c5474-f0ea-04fe-2e68-5964271d7c71.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/146381/ae34aecf-f15a-6c78-bd98-7d75a81cf4fc.png)

# PostgreSQLのデータをグラフ表示する

１．左メニューの「+」をクリックしてダッシュボードを作る。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/146381/3eb2f221-b461-c387-7243-404b7a660b89.png)

２．「Add new panel」からパネルを作る。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/146381/176fdfc6-faee-a0f3-c2b7-610a52f909fb.png)

３．Queryで、データソースにPostgreSQLを選択する。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/146381/8a786add-15cb-6a9b-51df-488d24a8dcc7.png)

４．引き続き、グラフにするクエリを作る。
テーブル名とかは勝手にデータベースから引っ張ってマウスクリックで選択できるはず。
　　FROM: data1
　　FROM/Time cloumn: "time"
　　SELECT: Column:data
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/146381/23f6e0e4-c387-b28d-647b-da1219316510.png)

とりあえず上記を指定するだけでも、先に入力したレコードがグラフにプロットされる。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/146381/b2c7c228-63b7-981e-d85f-28d312355651.png)


# 分毎のレコード件数をグラフにする
ここまで来れば、あとは画面を色々弄ってパネルのタイトル変えたりも出来るはず。
もう一つ、先に表示したグラフはdata1テーブルのdataの値をグラフにするものだったが、data1テーブルにあるレコードの件数を時系列のグラフにすることも簡単にできる。ログ件数とかで応用できる。

１．先のクエリ編集画面でGROUP_BYに「`time ($__interval, none)`」を追加する。
２．「`$__interval`」をクリックし、集計するインターバル（1m＝１分とか）を選択する。
３．SELECT/Aggregateで「count」を選択する。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/146381/29a6ad1b-06b1-3b3d-440a-710616e9b672.png)

上記を設定後、サーバー側のプロンプトで以下のコマンドをパカパカ叩けば、１分あたりに実行したコマンドの回数がグラフに反映される。

```
# sudo -u postgres psql -d grafana -c "INSERT INTO data1 (data) values (1);"
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/146381/0846ff47-f17c-c9f7-476e-00bef0dd65a7.png)

これを実現するSQL文を自分で書こうとすると難儀なので、マウスクリック数回でグラフ表示を実現してくれるGrafanaは偉い。

（おまけ）[Grafanaのグラフをhtml文書に埋め込む](https://qiita.com/rk05231977/items/d4841a72209e626894b4)