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の仮想マシンの管理画面より、「ネットワーキング」>「ファイアウォール」でルールを追加する。

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

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

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

１．先のPostgreSQLを読み込むよう、データソースを追加する。
左メニューの「歯車」>「Data Sources」。

２．「Add data source」。

３．データソースの一覧から「PostgreSQL」を選択する。

４．以下を指定した後、「Save & Test」をクリックする。
　　Name: PostgreSQL
　　Host: localhost:5432
　　Database: grafana
　　User: grafana
　　Password: grafana
　　SSL Mode: disable

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

１．左メニューの「+」をクリックしてダッシュボードを作る。

２．「Add new panel」からパネルを作る。

３．Queryで、データソースにPostgreSQLを選択する。

４．引き続き、グラフにするクエリを作る。
テーブル名とかは勝手にデータベースから引っ張ってマウスクリックで選択できるはず。
　　FROM: data1
　　FROM/Time cloumn: "time"
　　SELECT: Column:data

とりあえず上記を指定するだけでも、先に入力したレコードがグラフにプロットされる。

分毎のレコード件数をグラフにする

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

１．先のクエリ編集画面でGROUP_BYに「time ($__interval, none)」を追加する。
２．「$__interval」をクリックし、集計するインターバル（1m＝１分とか）を選択する。
３．SELECT/Aggregateで「count」を選択する。

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

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

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

（おまけ）Grafanaのグラフをhtml文書に埋め込む