Postgres waitingがたまったら強制解除

前回、Postgres waitingがたまっているかをチェックにて作った仕組みを今回はDBがロックしてしまった時に、強制的に解除する仕組みを作りました。
あくまで暫定的なもので、問題となっている部分を見つけ出すまでの間にアプリが動作しなくならないようにするための仕組みとして準備したものです。

○show_locks.sql　ロックして待ち状態になっているSQLをリスト

select * from (select pid from pg_locks group by pid) l,pg_stat_activity a
where l.pid=a.procpid order by query_start

○kill_locks.sql　ロックして待ち状態になっているSQLを強制的に解除する

SELECT pg_terminate_backend(procpid) FROM pg_stat_activity
WHERE current_query = 'in transaction'

○lockcheck.sh　データベースはhogeとし、mailコマンドが使えることが前提

#!/bin/sh
unalias -a
cd `dirname $0`

##対象のPostgreSQLが使えるように、pathやexportを指定しているファイル（あくまで独自のもの）
. /etc/pgenv

##日付取得と
NOW=`date '+%Y/%m/%d %H:%M:%S'`

#waitingの数カウント　なければ終了
WAIT_CNT=`ps -ef | fgrep 'postgres:' | fgrep 'waiting' | wc -l`
[ `expr ${WAIT_CNT} > 1` = 0 ] && exit 0

#hogeデータベースで現在動いているSQLの一覧をテキストに吐き出す
cat show_locks.sql | psql --no-psqlrc -U hoge > lockview.txt

#ロックしているだろうプロセスを強制的に解除（症状が重い場合は一回では解除できないので4回程実行します）
cat kill_locks.sql | psql --no-psqlrc -U postgres > lockkill.txt
cat kill_locks.sql | psql --no-psqlrc -U postgres > lockkill.txt
cat kill_locks.sql | psql --no-psqlrc -U postgres > lockkill.txt
cat kill_locks.sql | psql --no-psqlrc -U postgres > lockkill.txt

#指定した宛先にメール送信
SEND_TO='alert@hogehoge.co.jp'
SUBJECT="PostgreSQL lock waitting"

cat DATE: ${NOW}
SERVER: hoge-server
INFO:
* Postgres Process waiting count ${WAIT_CNT}
LOCKS:
`cat lockkill.txt`
`cat lockview.txt`
EOF
exit 0

あとはcronを使って、適当な間隔でこのシェルを設定する形になります。

上記を設定して、実際にアプリの動作が停止する前に、 問題となっていた部分を見つけ出すことができました。

こちらの記事はなかの人（kenjionsoku）監修のもと掲載しています。
元記事は、こちら