背景

Amazon Aurora Postgresのエンジンバージョンアップすることになった。
検証環境で動作確認する際に、ノードごとに「アクセス可能か」と「Writeノードか、Read Replicaか」
を確認するスクリプトを作って、ポーリングしたいと思った。

その手段として、

バージョン select version from version(); → SQL文
ReadOnlyか否か show transaction_read_only; → コマンド
- https://stackoverflow.com/questions/9275253/postgres-query-to-find-whether-database-is-read-only-mode

を定期的に実行すれば良いと思いつく。
そして、（個人的な宗教的好みで）これを一つのpsqlコマンドで実行したかった。

しかし意外と苦戦したので、供養がてら、その履歴を晒す。
プラスで、もっと良い方法がありそうなので、「こうすればできたよ」という知見者のコメントを期待。

結論

-cではなくEOFを使えばpsqlで複数コマンドを実行できる。
https://orebibou.com/ja/home/201609/20160923_001/

なので、インスタンスが2つある状態では、以下のようにすればとりあえず目的は達成できた。

$ cat check_pgver.sh 
date

echo instance0
psql -h {instance0のエンドポイント} -U {ユーザ名} -d {DB名} -t -A -w << EOF
select version from version();
show transaction_read_only;
EOF

echo instance1
psql -h {instance1のエンドポイント} -U {ユーザ名} -d {DB名} -t -A -w << EOF
select version from version();
show transaction_read_only;
EOF

$ ./check_pgver.sh 
2020年  8月 26日 水曜日 20:35:10 JST
instance0
PostgreSQL 11.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.9.3, 64-bit
off
instance1
PostgreSQL 11.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.9.3, 64-bit
on

あとは、これを無限ループさせる。

{ while true; do ./check_pgver.sh; sleep 5; done > db_status.log; } &

psqlの実行にパスワードが必要なら、事前に.pgpassを作っておく

echo ${エンドポイント}:5432:{ユーザ名}:{DB名}:${DBパスワード} >> .pgpass
chmod 600 .pgpass

失敗例

二つ並べて実行するだけだと、最後の結果しか表示されない。

$ psql -h ${エンドポイント} -U {ユーザ名} -d {DB名} -t -A -w -c "select version from version();show transaction_read_only;"
off

showコマンドをSQL内に入れる方法がわからない

方法をご存知の方はぜひコメントください。

> select * from (show transaction_read_only);
ERROR:  syntax error at or near ")"
行 1: select * from (show transaction_read_only);

gsetがセミコロンで連結できない

gsetを使えば、インタラクティブには目的が達成できた。

> show transaction_read_only;
 transaction_read_only 
-----------------------
 off
(1 行)

> \gset
> \echo :transaction_read_only
off
> select version, ':transaction_read_only' as readonly from version();
                                   version                                   |        readonly        
-----------------------------------------------------------------------------+------------------------
 PostgreSQL 11.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.9.3, 64-bit | :transaction_read_only
(1 行)

しかし、これを一行にしようとするとエラーが起こる

$ psql -h ${エンドポイント} -U {ユーザ名} -d {DB名} -c "show transaction_read_only;\gset;select version, ':transaction_read_only' as readonly from version();"
ERROR:  syntax error at or near "\"
行 1: show transaction_read_only;\gset;select version, ':transacti...

方法をご存知の方はぜひコメントください。