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

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)
1
2
3
4
5
6
7
8
9
10
11
12
可以看到数据库warehouse有三个连接，在我们业务确认可以取消的情况下，使用 pg_cancel_backend 来使我们的SQL停止运行。

warehouse=# select pg_cancel_backend(8883);
pg_cancel_backend
-------------------
t
(1 row)
1
2
3
4
5
再查看我们数据库的连接情况：

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)
1
2
3
4
5
6
7
8
9
10
11
12
可以明显看到之前的 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)
1
2
3
4
5
6
7
8
9
10
11
此时我们可以查看一下数据库的连接情况：

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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
至此数据库正常取消会话的操作就完成了。
可以进行 create database with template= xxx 的操作了。

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

这些疑问留在后续解答。
————————————————
版权声明：本文为CSDN博主「GaryZhang000」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/zhang50303/article/details/108188940