PostgreSQL 杀会话

pg_stat_activity 视图的每一行与每一个服务器进程一一对应，显示与该进程当前活动相关的信息。

pg_cancel_backend和pg_terminate_backend服务器信号函数
pg_cancel_backend(pid   int): 取消一个后端的当前查询。如果调用角色是被取消后端的拥有者角色的成员或者调用角色已经被授予pg_signal_backend，这也是允许的，不过只有超级用户才能取消超级用户的后端。 
pg_terminate_backend(pid int):
中止一个后端。如果调用角色是被取消后端的拥有者角色的成员或者调用角色已经被授予pg_signal_backend，这也是允许的，不过只有超级用户才能取消超级用户的后端。
这两个函数的区别在于：pg_cancel_backend 只是取消当前某一个进程的查询操作，但不能释放数据库连接。但pg_terminate_backend 可以在pg的后台杀死这个进程，从而释放出宝贵的连接资源。
 

-- 1) 当前会话

postgres=# SELECT COUNT(*) FROM pg_stat_activity;
 count 
-------
    12
(1 row)

postgres=# SELECT datname,pid,state,backend_type FROM pg_stat_activity;
  datname  |  pid  | state  |         backend_type         
-----------+-------+--------+------------------------------
           | 23635 |        | logical replication launcher
           | 23633 |        | autovacuum launcher
 postgres  | 63810 | active | client backend
 postgres  | 60710 | idle   | client backend
 postgres  | 59702 | idle   | client backend
 zoo       | 59736 | idle   | client backend
 sample_db | 59772 | idle   | client backend
 zoo       | 59972 | idle   | client backend
 zoo       | 60011 | idle   | client backend
           | 23631 |        | background writer
           | 23630 |        | checkpointer
      

-- 2) 用pg_cancel_backend函数杀会话(其中的pid为60011和59972)
 

postgres=# SELECT pg_cancel_backend(60011);
 pg_cancel_backend 
-------------------
 t
(1 row)

postgres=# SELECT pg_terminate_backend(59972);
 pg_terminate_backend 
----------------------
 t
(1 row)

postgres=# SELECT datname,pid,state,backend_type FROM pg_stat_activity;
  datname  |  pid  | state  |         backend_type         
-----------+-------+--------+------------------------------
           | 23635 |        | logical replication launcher
           | 23633 |        | autovacuum launcher
 postgres  | 63810 | active | client backend
 postgres  | 60710 | idle   | client backend
 postgres  | 59702 | idle   | client backend
 zoo       | 59736 | idle   | client backend
 sample_db | 59772 | idle   | client backend
 zoo       | 60011 | idle   | client backend
           | 23631 |        | background writer
           | 23630 |        | checkpointer
           | 23632 |        | walwriter
(11 rows)

---- 发现pid59972会话杀掉了但pid60011没有杀掉，再执行一遍，发现还是没杀掉该会话
postgres=# SELECT pg_cancel_backend(60011);
 pg_cancel_backend 
-------------------
 t
(1 row)

postgres=# SELECT datname,pid,state,backend_type FROM pg_stat_activity;
  datname  |  pid  | state  |         backend_type         
-----------+-------+--------+------------------------------
           | 23635 |        | logical replication launcher
           | 23633 |        | autovacuum launcher
 postgres  | 63810 | active | client backend
 postgres  | 60710 | idle   | client backend
 postgres  | 59702 | idle   | client backend
 zoo       | 59736 | idle   | client backend
 sample_db | 59772 | idle   | client backend
 zoo       | 60011 | idle   | client backend
           | 23631 |        | background writer
           | 23630 |        | checkpointer
           | 23632 |        | walwriter
(11 rows)


-- 对应进程信息
[root@dmpg1 12661]# ps -ef |grep 60011
postgres  60011  23627  0 17:07 ?        00:00:00 postgres: postgres zoo 192.168.131.1(65478) idle
root      64115  42950  0 18:24 pts/2    00:00:00 grep --color=auto 60011

-- 3) 用pg_terminate_backend函数杀掉会话，能杀掉

postgres=# SELECT datname,pid,state,backend_type FROM pg_stat_activity;
  datname  |  pid  | state  |         backend_type         
-----------+-------+--------+------------------------------
           | 23635 |        | logical replication launcher
           | 23633 |        | autovacuum launcher
 postgres  | 63810 | active | client backend
 postgres  | 60710 | idle   | client backend
 postgres  | 59702 | idle   | client backend
 zoo       | 59736 | idle   | client backend
 sample_db | 59772 | idle   | client backend
 zoo       | 60011 | idle   | client backend
           | 23631 |        | background writer
           | 23630 |        | checkpointer
           | 23632 |        | walwriter
(11 rows)


postgres=# SELECT pg_terminate_backend(60011);
 pg_terminate_backend 
----------------------
 t
(1 row)

postgres=# SELECT datname,pid,state,backend_type FROM pg_stat_activity;
  datname  |  pid  | state  |         backend_type         
-----------+-------+--------+------------------------------
           | 23635 |        | logical replication launcher
           | 23633 |        | autovacuum launcher
 postgres  | 63810 | active | client backend
 postgres  | 60710 | idle   | client backend
 postgres  | 59702 | idle   | client backend
 zoo       | 59736 | idle   | client backend
 sample_db | 59772 | idle   | client backend
           | 23631 |        | background writer
           | 23630 |        | checkpointer
           | 23632 |        | walwriter
(10 rows)

----生成杀会话的语句

postgres=# SELECT 'SELECT pg_terminate_backend(' || PID || ');' KILL_PID,
DATID,
DATNAME,
PID,
USESYSID,
USENAME,
APPLICATION_NAME,
CLIENT_ADDR,
CLIENT_PORT,
STATE,
QUERY
FROM PG_STAT_ACTIVITY WHERE state='idle' limit 1;
              kill_pid               | datid | datname |  pid  | usesysid | usename  |     application_name     |  client_addr  | client_port | state |                                                                           query                                       
                                    
-------------------------------------+-------+---------+-------+----------+----------+--------------------------+---------------+-------------+-------+-----------------------------------------------------------------------------------------------------------------------
------------------------------------
 SELECT pg_terminate_backend(72715); | 16387 | zoo     | 72715 |       10 | postgres | pgAdmin 4 - CONN:4188715 | 192.168.131.1 |       49588 | idle  | SELECT oid, pg_catalog.format_type(oid, NULL) AS typname FROM pg_catalog.pg_type WHERE oid IN (25, 26, 19, 23, 26, 19,
 25, 869, 23, 25, 25) ORDER BY oid;
(1 row)