PostgreSQL で実行中のSQL をキャンセルあるいは接続を終了させる
PostgreSQL で長時間に渡って実行されている SQL や暴走してしまっている SQL がある場合、サービスに影響したり他の処理の妨げになってパフォーマンス低下に繋がる場合があります。特にシステム開発中には安定していない SQL や実行速度がとてつもなく遅い SQL が頻繁に実行されます。そのような場合に長時間実行されている SQL の実行をキャンセルしたり、ゾンビのように残ってしまったデータベース接続を強制終了させて PostgreSQL データベースを安定させましょう。
実行中の SQL をキャンセル、データベース接続を終了させる関数
下記２つの PostgreSQL 関数で SQL をキャンセルあるいはデータベース接続(セッション)を強制終了させることが出来ます。pg_cancel_backend(pid int) ===> 実行中の SQL をキャンセルする pg_terminate_backend(pid int) ===> セッションを終了させるどちらの関数もユーザー自身が実行している SQL をキャンセルしたりやセッションを終了させたり出来ますが、他ユーザーの処理をキャンセルするためにはスーパーユーザー(postgres)でデータベースに接続して実行する 必要があります。
実行中の SQL をキャンセル
まず実行中の SQL をキャンセルするには実行中の SQL のプロセス ID (procpid) を知る必要があります。そのためには次の SQL でキャンセルしたい SQL を実行中のプロセス ID (procpid) を探します。
$ psql -U postgres =# <pre class="prettyprint lang-sql">SELECT procpid, usename, application_name, client_addr, query_start, current_query FROM pg_stat_activity ; -[ RECORD 1 ]----+------------------------------- procpid | 16018 usename | crewfs_owner application_name | psql client_addr | query_start | 2017-09-21 15:50:48.716026-06 current_query | </idle> -[ RECORD 3 ]----+------------------------------- procpid | 16088 usename | crewfs_owner application_name | psql client_addr | 192.168.161.150 query_start | 2017-09-21 16:15:31.004167-06 current_query | SELECT pg_sleep(600);テストのためにここでは "SELECT pg_sleep(600);" という SQL を他のマシンから実行しました。pg_sleep() 関数は 指定された秒数だけ処理を遅延させる関数で、600 なので 10 分間 SQL 実行中のまま待機します。
ここではキャンセルしたい SQL は "SELECT pg_sleep(100);" ですので、このプロセス ID (procpid) は 16023 です。この番号を覚えておいて、以下の関数を実行します。
# SELECT pg_cancel_backend(16023); ------------------- pg_cancel_backend ------------------- t ------------------- (1 row)pg_cancel_backend() 関数を呼び出した後に、実際に SQL を実行していた側の画面にはユーザーの要求により SQL がキャンセルされたというメッセージが表示されます。
=> SELECT pg_sleep(100); ERROR: canceling statement due to user requestなお SQL はキャンセルされましたが、データベース接続はちゃんと残っていることが確認できますので、そのまま再接続しなくても再度 SQL を実行することが可能です。
データベース接続(セッション)を切断する
今度は現在接続されているデータベースの接続(セッション)を切断してみましょう。先程と同様に SQL のプロセス ID (procpid) を知る必要がありますが、ここでは先程 SQL をキャンセルした接続のプロセス ID (procpid) を利用しましょう。以下のように接続中のデータベース接続を切断します。
# SELECT pg_terminate_backend(16023); ---------------------- pg_terminate_backend ---------------------- t ---------------------- (1 row)
そして実際にデータベース接続を切断された側の画面を見ると、次のように接続が管理者のコマンドによって終了させられたというメッセージが表示されています。
しかしこの場合、接続は切断されたが psql コマンドは自動的に再接続されたというメッセージが表示されています (The connection to the server was lost. Attempting reset: Succeeded. という部分です)。
=> SELECT pg_sleep(600); FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command The connection to the server was lost. Attempting reset: Succeeded.
