HashData如何找到并合法清理进程会话

查看指定数据库存在的用户进程

此处我们以 warehouse 数据库为例：

warehouse=# select * from pg_stat_activity;
 datid |  datname  | procpid | sess_id | usesysid | usename |          current_query          | waiting |          query_start          |         backend_start         | client_add
r | client_port | application_name |          xact_start           | waiting_reason | rsgid | rsgname | rsgqueueduration 
-------+-----------+---------+---------+----------+---------+---------------------------------+---------+-------------------------------+-------------------------------+-----------
--+-------------+------------------+-------------------------------+----------------+-------+---------+------------------
 16387 | warehouse |    8883 |      11 |       10 | gpadmin | <IDLE>                          | f       | 2020-08-23 21:15:29.419925+08 | 2020-08-23 21:15:29.413947+08 |           
  |          -1 |                  |                               |                |     0 | unknown | 
 16387 | warehouse |   20364 |      11 |       10 | gpadmin | <IDLE>                          | f       | 2020-08-23 21:15:29.404434+08 | 2020-08-23 21:04:42.593598+08 |           
  |          -1 | psql             |                               |                |     0 | unknown | 
 16387 | warehouse |    1387 |      15 |       10 | gpadmin | select * from pg_stat_activity; | f       | 2020-08-23 21:15:44.449141+08 | 2020-08-23 21:11:37.067246+08 |           
  |          -1 | psql             | 2020-08-23 21:15:44.449141+08 |                |     0 | unknown | 
(3 rows)

可以看到数据库warehouse有三个连接，在我们业务确认可以取消的情况下，使用 pg_cancel_backend 来使我们的SQL停止运行。

warehouse=# select pg_cancel_backend(8883);
 pg_cancel_backend 
-------------------
 t
(1 row)

再查看我们数据库的连接情况：

warehouse=# select * from pg_stat_activity;
 datid |  datname  | procpid | sess_id | usesysid | usename |          current_query          | waiting |          query_start          |         backend_start         | client_add
r | client_port | application_name |          xact_start           | waiting_reason | rsgid | rsgname | rsgqueueduration 
-------+-----------+---------+---------+----------+---------+---------------------------------+---------+-------------------------------+-------------------------------+-----------
--+-------------+------------------+-------------------------------+----------------+-------+---------+------------------
 16387 | warehouse |    8883 |      11 |       10 | gpadmin | <IDLE>                          | f       | 2020-08-23 21:15:29.419925+08 | 2020-08-23 21:15:29.413947+08 |           
  |          -1 |                  |                               |                |     0 | unknown | 
 16387 | warehouse |   20364 |      11 |       10 | gpadmin | <IDLE>                          | f       | 2020-08-23 21:15:29.404434+08 | 2020-08-23 21:04:42.593598+08 |           
  |          -1 | psql             |                               |                |     0 | unknown | 
 16387 | warehouse |    1387 |      15 |       10 | gpadmin | select * from pg_stat_activity; | f       | 2020-08-23 21:15:44.449141+08 | 2020-08-23 21:11:37.067246+08 |           
  |          -1 | psql             | 2020-08-23 21:15:44.449141+08 |                |     0 | unknown | 
(3 rows)

可以明显看到之前的 insert 操作取消了，此时我们再进行断开会话的操作，使用 pg_terminate_backend

warehouse=# select pg_terminate_backend(8883);
WARNING:  PID 8883 is not a PostgreSQL server process
 pg_terminate_backend 
----------------------
 f
(1 row)
warehouse=# select pg_terminate_backend(20364);
 pg_terminate_backend 
----------------------
 t
(1 row)

此时我们可以查看一下数据库的连接情况：

warehouse=# select * from pg_stat_activity;
 datid |  datname  | procpid | sess_id | usesysid | usename |          current_query          | waiting |          query_start          |         backend_start         | client_add
r | client_port | application_name |          xact_start           | waiting_reason | rsgid | rsgname | rsgqueueduration 
-------+-----------+---------+---------+----------+---------+---------------------------------+---------+-------------------------------+-------------------------------+-----------
--+-------------+------------------+-------------------------------+----------------+-------+---------+------------------
 16387 | warehouse |    1387 |      15 |       10 | gpadmin | select * from pg_stat_activity; | f       | 2020-08-23 21:20:24.642656+08 | 2020-08-23 21:11:37.067246+08 |           
  |          -1 | psql             | 2020-08-23 21:20:24.642656+08 |                |     0 | unknown | 
(1 row)

warehouse=# \c postgres
You are now connected to database "postgres" as user "gpadmin".
postgres=# select * from pg_stat_activity where datname='warehouse';
 datid | datname | procpid | sess_id | usesysid | usename | current_query | waiting | query_start | backend_start | client_addr | client_port | application_name | xact_start | wait
ing_reason | rsgid | rsgname | rsgqueueduration 
-------+---------+---------+---------+----------+---------+---------------+---------+-------------+---------------+-------------+-------------+------------------+------------+-----
-----------+-------+---------+------------------
(0 rows)

至此数据库正常取消会话的操作就完成了。
可以进行 create database with template= xxx 的操作了。

存在一些疑问点

1 - 为什么一个SQL会产生两个insert进程呢？
2 - 为什么 pg_cancel_bakend 取消一个进程，两个进程都结束了？
3 - 为什么 pg_terminate_backend 会报warning？

这些疑问留在后续解答。