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


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

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

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

仮想マシンを作る

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

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

PostgreSQLをインストールする

1.作った仮想マシンに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 ~]#

2.PostgreSQLをインストールする。

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

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

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

4.グラフの元となる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;"

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

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

6.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/

1.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

2.ポート3000番にアクセス可能なようネットワークを構成する。
Grafanaはデフォルトで3000/tcpポートでアクセスされるように起動するので、それに接続できるようネットワークを調整する。
LightsailのCentOS仮想マシンは、OSのfirewalldは無効だが、外側から3000番ポートでアクセスするにはLightsailのファイアウォールにルールを追加する必要がある。
Lightsailの仮想マシンの管理画面より、「ネットワーキング」>「ファイアウォール」でルールを追加する。

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

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



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

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

2.「Add data source」。

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

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

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

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

2.「Add new panel」からパネルを作る。

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

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

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

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

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

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

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

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

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

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