BitBarでPostgreSQLサーバーの状態を常に意識する

# はじめに

2000年頃にはPostgreSQLが優勢だったんです。それがいつの間にか「MySQLを使わない理由は何ですか？」という時代が到来し、近年になって再び並列に語られるようになりました。先日「[BitBarでMySQLサーバーの状態を常に意識する](http://qiita.com/artifactsauce/items/d111bb1e6676f235094d)」という記事を公開した勢いで、用途が似通っているなら要件も似通っているだろうという推測のもとに、PostgreSQLのステータスを確認し、起動／停止まで行えるプラグインを作成してみました。

# ローカルで動かすPostgreSQLサーバー

## Homebrewでインストール

今回もPostgreSQLはHomebrewでインストールします。PostgreSQLをコマンドラインからインストールした場合、いくつか実行するべきコマンドがあるので、ここに列挙しておきます。詳細は述べません。

```console
$ brew install postgresql
$ /usr/local/bin/initdb -D /usr/local/var/postgres
$ /usr/local/bin/pg_ctl -D /usr/local/var/postgres start
$ /usr/local/bin/createuser postgres
```

## 状態に気が付かない

今回もPostgreSQLは自動起動にしていません。開発機とは言え、無駄にリソースを消費するのはいかがなものかと思いますので、必要なときにだけ起動することにしましょう。そしてデータベースのデーモンが起動していないために処理がエラーになること、よくありますよね。ありませんか？ありますよね？ありますよね？

MySQLと同様にBitBarのプラグインで状態をメニューバーに表示しましょう。

# BitBar

BitBarはメニューバーを簡単に拡張するユーティリティであることはすでにご説明しましたが、視点を少し変えると、コマンドラインを実行して結果を表示する、限定的なインターフェイスであると捉えることもできます。

- [BitBarで自分の人生の残り時間を常に意識する](http://qiita.com/artifactsauce/items/1caca90e91c6ca90ebbf)
- [BitBarで自分のやるべきタスクを常に意識する](http://qiita.com/artifactsauce/items/2f933a1dbddf19ab008a)
- [BitBarでMySQLサーバーの状態を常に意識する](http://qiita.com/artifactsauce/items/d111bb1e6676f235094d)

これまでに倣って実装してみましょう。

# 実装

実装内容はMySQLのプラグインとほぼ同じです。サーバー・ステータスを取得する部分だけは知識が無かったせいもあってちょっと面倒でした。

そんなこんなで作成したプラグインがこちらです。

```bash:postgresql-status.1m.sh
#!/usr/bin/env bash
# <bitbar.title>PostgreSQL server status</bitbar.title>
# <bitbar.version>v1.0</bitbar.version>
# <bitbar.author>Kenji Akiyama</bitbar.author>
# <bitbar.author.github>artifactsauce</bitbar.author.github>
# <bitbar.desc>Show the status of PostgreSQL server installed by Homebrew on localhost and manage server boot with shortcut menus</bitbar.desc>
# <bitbar.image>http://i.imgur.com/l5E4yg8.png</bitbar.image>
# <bitbar.dependencies>bash,perl,postgresql</bitbar.dependencies>

# TODO Selecting some menus will return warnings because the process has not been completed.

set -eu

# Change here depending on your preference
MENUBAR_ICON_ENABLED=":elephant:"
MENUBAR_ICON_DISABLED=":sleepy:"
STATUS_ITEM_COLOR="green"
DISABLED_ITEM_COLOR="#C0C0C0"

# Below is no need to change basically.
SERVER_CMD="/usr/local/bin/pg_ctl"
PGDATA="/usr/local/var/postgres"

SUBCMD_START="start"
SUBCMD_STOP="stop"
SUBCMD_RESTART="restart"
SUBCMD_RELOAD="reload"
SUBCMD_STATUS="status"

if $SERVER_CMD -D $PGDATA $SUBCMD_STATUS | grep -Fq 'server is running'; then
  IS_SERVER_RUNNING=true
  echo "$MENUBAR_ICON_ENABLED"
else
  IS_SERVER_RUNNING=false
  echo "$MENUBAR_ICON_DISABLED"
fi

echo "---"
echo "PostgreSQL Server"

# Server Status from PostgreSQL's `pg_stat_database` table
if $IS_SERVER_RUNNING; then
  /usr/local/bin/psql -U postgres -w -q -A -F $'\x09' -c "select * from pg_stat_database;" | perl -nle 'BEGIN { $_=<>; chomp; @headers = split "\t"} last if eof(); @records{@headers} = split "\t"; next if $records{datname} =~/^template/; print "---"; print "$_: $records{$_} | color='$STATUS_ITEM_COLOR'" for @headers'
fi

# Server management shortcuts
echo "---"
if $IS_SERVER_RUNNING; then
  echo "Start | color=$DISABLED_ITEM_COLOR"
  echo "Stop | bash=$SERVER_CMD param1=-D param2=$PGDATA param4=$SUBCMD_STOP refresh=true terminal=false"
  echo "Restart | bash=$SERVER_CMD param1=-D param2=$PGDATA param4=$SUBCMD_RESTART refresh=true terminal=false"
  echo "Reload | bash=$SERVER_CMD param1=-D param2=$PGDATA param4=$SUBCMD_RELOAD refresh=true terminal=false"
else
  echo "Start | bash=$SERVER_CMD param1=-D param2=$PGDATA param4=$SUBCMD_START refresh=true terminal=false"
  echo "Stop | color=$DISABLED_ITEM_COLOR"
  echo "Restart | color=$DISABLED_ITEM_COLOR"
  echo "Reload | color=$DISABLED_ITEM_COLOR"
fi

echo "---"
echo "Refresh | refresh=true color=$DISABLED_ITEM_COLOR"
```

これをテキストファイルで保存して所定のディレクトリに配置し、 **実行権限を与えてください** 。

![](http://i.imgur.com/l5E4yg8.png)

正常に動作すると、サーバーの状態が表示されつつ、起動／停止のコマンドへのショートカットも表示されています。

# 解説

## 管理コマンド

MySQLの `mysql.server` に相当するPostgreSQLのコマンドが `pg_ctl` です。Homebrewでインストールした場合には `/usr/local/bin/pg_ctl` にSymlinkが張られています。このスクリプトは引数に `start`, `stop`, `restart`, `reload`,  `status` の文字列を取ることができます。

メニュー項目の選択によってこれらのコマンドを実行するためには、下記のような結果になるように標準出力に出力するだけです。

```
Start | bash=/usr/local/bin/pg_ctl param1=-D param2=$PGDATA param3=start refresh=true terminal=false
```

`pg_ctl` コマンドは、実行時にデータ・ディレクトリのPATHを要求するので、ここでは第1引数、第2引数としてオプション名と値を与えています。データ・ディレクトリのPATHは環境変数 `PGDATA` に設定しておくのが定石ですが、メニューバーから呼び出す際には `.bashrc` などで読み込ませた環境変数は効いていないので、コマンドライン・オプションで設定する必要があります。

同様に `param3` の値を `stop`, `restart`, `reload`,  `status` に変更した項目も出力すれば完成です。簡単ですね。

ちなみに、 `refresh` は実行後に表示内容を再生成するか否かを指定します。また、 `terminal` は実行時に新たなターミナル画面を起動するか否かを指定します。

## PostgreSQLのステータスを取得

さて、MySQLでもあった「適度に詳細かつ簡単なステータス」はPostgreSQLではどのように取得するのでしょうか？PostgreSQLには `mysqladmin` のようなShellから管理者コマンドを実行するWrapperコマンドのようなものはありません。そこで、ShellからSQLを与えて実行結果をShellに出力する方法を採用します。

PostgrSQLは、各データベースの状態をテーブル `pg_stat_database` に保持しています。これをメニューバーに出力しましょう。下記が出力した結果です。

```console
$ /usr/local/bin/psql -U postgres -w -q -A -F $'\x09' -c "select * from pg_stat_database;"
datid   datname numbackends     xact_commit     xact_rollback   blks_read       blks_hit        tup_returned    tup_fetched     tup_inserted    tup_updated     tup_deleted     conflicts       temp_files      temp_bytes      deadlocks       blk_read_time   blk_write_time stats_reset
1       template1       0       0       0       0       0       0       0       0       0       0       0       0       0       0      00
12636   template0       0       0       0       0       0       0       0       0       0       0       0       0       0       0      00
12641   postgres        1       6179    2       431     317062  1561344 123647  0       0       0       0       0       0       0      00       2016-10-31 20:33:03.144392+09
(3 rows)
```

PostgreSQLのコマンドライン・クライアントは `psql` です。 `-U` でユーザー名を指定しています。先ほど `brew` コマンドでインストールした後に作成したユーザーです。 `-w` はパスワードの入力を要求しないというオプションです。開発環境ならパスワードを設定しなくても良いんじゃないでしょうか？

出力をタブ区切りテキストにすることで、後の処理がやりやすくなります。ここでは `-A` で桁揃えなしのテーブル出力を指定し、そのテーブルのカラム区切りとして `-F $'\x09'` でタブを指定しています。

## `perl` で出力を整形する

MySQLプラグインを作成した際には `sed` で出力を整形しましたが、今回はそういうレベルではありません。もう少し表現力のある言語を利用した方が良いでしょう。私はたまたま `perl` に馴染みがありますので、 `perl` で書きます。

ポイントは二つ。一つは1行目がヘッダー行であること、もう一つはデータベースごとに出力されていることです。

### ヘッダー行の取得

まずはヘッダー行を取得しましょう。

```console
$ /usr/local/bin/psql -U postgres -w -q -A -F $'\x09' -c "select * from pg_stat_database;" | perl -MData::Dumper -ne 'BEGIN { $_=<>; chomp; @headers = split "\t"} END { print Dumper \@headers }'
$VAR1 = [
          'datid',
          'datname',
          'numbackends',
          'xact_commit',
          'xact_rollback',
          'blks_read',
          'blks_hit',
          'tup_returned',
          'tup_fetched',
          'tup_inserted',
          'tup_updated',
          'tup_deleted',
          'conflicts',
          'temp_files',
          'temp_bytes',
          'deadlocks',
          'blk_read_time',
          'blk_write_time',
          'stats_reset'
        ];
```

ヘッダー行は最初の行に記載されており、このデータは後にも使いまわす特有の処理を書く必要があるので、 `BEGIN` サブルーチンにしています。上記の通り、期待通りにヘッダーを取得できました。

### データ行の取得

次にデータ行を取得しましょう。

```console
$ /usr/local/bin/psql -U postgres -w -q -A -F $'\x09' -c "select * from pg_stat_database;" | perl -MData::Dumper -ne 'BEGIN { $_=<>; chomp; @headers = split "\t"} @records{@headers} = split "\t"; print Dumper \%records'
$VAR1 = {
          'blk_read_time' => '0',
          'xact_commit' => '0',
          'temp_files' => '0',
          'conflicts' => '0',
          'stats_reset' => '
',
          'tup_deleted' => '0',
          'xact_rollback' => '0',
          'numbackends' => '0',
          'tup_fetched' => '0',
(...)
          'tup_returned' => undef,
          'blk_write_time' => undef,
          'datid' => '(3 rows)
',
          'tup_inserted' => undef,
          'tup_updated' => undef,
          'datname' => undef,
          'deadlocks' => undef
        };
```

行ごとにカラム名をKeyにしたハッシュを構築します。

下記のような短い構文で、keyの配列とvalueの配列のそれぞれを合わせて1つのハッシュにすることができます。

```perl
@record{@keys} = @values;
```

ほぼ期待通りに出力されました。

とりあえずデータ行は取得できたのですが、例えば `template0` は新たにデータベースを作るときのために利用されるものであり、ここでステータスを表示する必要はないでしょう。また、最後の行は影響を受けた行の数であり、これも必要ないでしょう。

### 不要な行の除外

データベース名が `template` から始まっている場合はスキップします。通常は `postgres` データベースも除外してよいと思うのですが、今回は他に何も表示するものがないので含めています。

```console
$ /usr/local/bin/psql -U postgres -w -q -A -F $'\x09' -c "select * from pg_stat_database;" | perl -MData::Dumper -nle 'BEGIN { $_=<>; chomp; @headers = split "\t"} last if eof(); @records{@headers} = split "\t"; next if $records{datname} =~/^template/; print Dumper \%records'
$VAR1 = {
          'tup_fetched' => '45296',
          'tup_updated' => '0',
          'temp_bytes' => '0',
          'tup_inserted' => '0',
          'tup_returned' => '565944',
          'datid' => '12641',
          'blks_read' => '239',
          'datname' => 'postgres',
          'deadlocks' => '0',
          'blk_write_time' => '0',
          'tup_deleted' => '0',
          'xact_commit' => '2209',
          'numbackends' => '1',
          'blk_read_time' => '0',
          'conflicts' => '0',
          'stats_reset' => '2016-11-09 01:06:07.773202+09',
          'blks_hit' => '116199',
          'temp_files' => '0',
          'xact_rollback' => '0'
        };
```

データベース名を取得し、正規表現でマッチしたものはスキップしましょう。

```perl
next if $records{datname} =~/^template/;
```

最終行は下記のように、ファイルの終端を検出してスキップ（と言うかループを抜ける）します。

```perl
last if eof();
```

# 最後に

PostgreSQLは [digdag](https://www.digdag.io/) のステータス管理に採用されるなど、何度目かの再注目を集めています。開発の現場で採用されるという場合もありますが、データ解析基盤として採用されることも多くなっていると小耳に挟んでいます。このタイミングでこれらの開発者（Mac限定ですが）に対してちょっとしたツールを提供できたのは良かったかなと思います。