はじめに

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

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

Homebrewでインストール

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

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

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

実装

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

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

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"

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

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

解説

管理コマンド

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 に保持しています。これをメニューバーに出力しましょう。下記が出力した結果です。

$ /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行目がヘッダー行であること、もう一つはデータベースごとに出力されていることです。

ヘッダー行の取得

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

$ /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 サブルーチンにしています。上記の通り、期待通りにヘッダーを取得できました。

データ行の取得

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

$ /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つのハッシュにすることができます。

@record{@keys} = @values;

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

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

不要な行の除外

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

$ /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'
        };

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

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

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

last if eof();

最後に

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