停止卡住的 Postgres Session 的 SQL 指令

在 Postgres 遇到有奇怪的 Session 執行「好像」卡很久的 SQL 查詢指令，想要先把它停下來，再找人來罵一罵～，怎麼處理呢？

首先，資料庫有一個保留 Superuser 登入管理的特別保留連線，以確保資料庫繁忙或是連線滿了，Superuser 仍然可以進去。

edb=# show superuser_reserved_connections;
 superuser_reserved_connections 
--------------------------------
 3
(1 row)

登入 Superuser 後，可以用 pg_stat_activity 這個 System View 可以查詢 Session 狀況；而中止程序可以用 pg_cancel_backend() 或 pg_terminate_backend() 函數：參考 Table 9-67. Server Signaling Functions。
兩個函數的差異是，pg_cancel_backend() 是取消當前執行的查詢，不把 Session 中斷，而 pg_terminate_backend() 則是直接把該登入 Session 斷開。

使用上，可以透過 pg_stat_activity 找到要停止的 Session 的 pid，然後直接把 pid 數值放入 SELECT pg_terminate_backend(pid_number); 的 pid_number 裡面。

下面範例，開一個 Session，進行 Sleep（數饅頭～），然後另外開一個 Session，把他中止

edb=# SEELCT pid,
             usename,
             now() - backend_start AS duration,
             wait_event,
             state,
             query
      FROM pg_stat_activity;
 pid  |   usename    |    duration     | wait_event | state  |                                                 query                                                 
------+--------------+-----------------+------------+--------+-------------------------------------------------------------------------------------------------------
 1534 | enterprisedb | 00:02:24.392624 |            | active | select pid, usename, now()-backend_start AS duration, wait_event, state, query FROM pg_stat_activity;
 1493 | enterprisedb | 00:09:53.637098 |            | active | select pg_sleep(10000);
(2 rows)

另外，中止所有 idle 的 Session 則可以這樣作：

select pg_terminate_backend(pid)
 from pg_stat_activity
 where state = 'idle';

再來，如果真的資料庫卡住，連不太進去資料庫，只好把資料庫重啟動（安全的重啟）

[enterprisedb@edbvm ~]$ pg_ctl restart -m fast
waiting for server to shut down.... done
server stopped
server starting

這時候被中止的 Session 會出現被中止的訊息

[enterprisedb@edbvm ~]$ psql
Password: 
psql.bin (9.6.2.7)
Type "help" for help.
edb=# select pg_sleep(1000);
FATAL:  terminating connection due to administrator command
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!> 
!> exit
[enterprisedb@edbvm ~]$

這時沒有 Commit 的活動都會被 Rollback，有 Commit 而尚未 Flush Dirty Data 則會在啟動過程自動進行 Point-in-Time Recovery，從 WAL Segments 把資料寫回來，這時候所耗的時間就得取決於沒恢復的資料量決定。

[Update 20181207] 最近看到實際去從外部以 kill 砍掉資料庫 Session 會發生什麼事了：資料庫基於保護機制，會認定資料庫服務異常，便將所有 Session 都中斷，進行 Crash Recovery。所以，千萬不要隨便從作業系統執行 kill 指令，要使用資料庫內建的服務中止函數才行！

 參考資料

用以上的東西去查，就會有資料了～
或是以下關鍵字

hanging query postgres - Google 搜尋