觀察PostgreSQL的connection activity

因為懷疑正在實作的應用程式在運作過程中有connection沒有關閉，找了下面這個方法去觀察PostgreSQL目前正在連線的connection，以及他們正在做什麼事情。

SELECT * FROM pg_stat_activity;

由於欄位太多，畫面變得很難閱讀，改成只拿必要的欄位：

SELECT datid, datname, current_query FROM pg_stat_activity;

 datid | datname| current_query
-------+--------+------------------------------------------------------------
 17075 | foobar | <IDLE>
 17075 | foobar | <IDLE>
 17075 | foobar | <IDLE>
 17075 | foobar | <IDLE> in transaction
 17075 | foobar | update public..., sele...
 17075 | foobar | <IDLE> in transaction
 17075 | foobar | SELECT datid, datname, current_query FROM pg_stat_activity;
(7 rows)

其中有幾個連線時間長到有點誇張，加入age()函式檢查這幾個connection的存續時間：

SELECT datid,datname,current_query,age(now(),query_start) as age
    FROM pg_stat_activity ORDER BY age DESC;

 datid | datname| current_query                                              |  age
-------+--------+------------------------------------------------------------+-------
 17075 | foobar | <IDLE> in transaction                                      |2 days 19:59:37.157806
 17075 | foobar | update public..., sele...                                  |2 days 19:59:37.067323
 17075 | foobar | <IDLE>                                                     |00:49:42.900198
 17075 | foobar | <IDLE>                                                     |00:49:42.899244
 17075 | foobar | <IDLE>                                                     |00:49:42.606484
 17075 | foobar | <IDLE>                                                     |00:17:38.719693
 17075 | foobar | SELECT datid, datname, current_query FROM pg_stat_activity;|00:00:00
(7 rows)

Reference: chrismils