PostgreSql-pg_stat_activity 系统视图应用

表结构介绍

pg_stat_activity 定义

pg_stat_activity 是 PostgreSQL 内置的一个系统视图， 是 PostgreSQL 实例维护的一个进程相关的视图，是实时变化的。
pg_stat_activity视图在src/backend/catalog/system_views.sql定义:

CREATE VIEW pg_stat_activity AS
    SELECT S.datid AS datid, D.datname AS datname, S.pid, S.usesysid, U.rolname AS usename, S.application_name, S.client_addr,
            S.client_hostname, S.client_port, S.backend_start, S.xact_start, S.query_start, S.state_change, S.wait_event_type,
            S.wait_event, S.state, S.backend_xid, s.backend_xmin, S.query, S.backend_type
    FROM pg_stat_get_activity(NULL) AS S
        LEFT JOIN pg_database AS D ON (S.datid = D.oid)
        LEFT JOIN pg_authid AS U ON (S.usesysid = U.oid);

官方对他的定义是：每一行都表示一个系统进程，显示与当前会话的活动进程的一些信息，比如当前回话的状态和查询等。

字段说明

列	类型	描述
datid	oid	连接后端的数据库id(OID)
datname	name	连接后端的数据库名称
pid	integer	后端进程ID（PID）
leader_pid	integer	并行组负责人进程 ID，如果此进程是并行查询工作者。NULL如果这个进程是并行组长或者不参与并行查询。
usesysid	oid	登陆后端的用户OID
usename	name	登录到该后端的用户名
application_name	text	连接到后端的应用名
client_addr	inet	连接到此后端的客户端的 IP 地址。如果此字段为空，则表明客户端是通过服务器机器上的 Unix 套接字连接的，或者这是一个内部进程，例如 autovacuum。
client_hostname	text	连接客户端的主机名，通过client_addr的反向DNS查找报告。这个字段将只是非空的IP连接，并且仅在启用log_hostname时。
client_port	integer	客户端用于与此后端通信的 TCP 端口号，或者-1如果使用 Unix 套接字。如果该字段为空，则表明这是一个内部服务器进程。
backend_start	timestamp with time zone	该过程开始的时间。对于客户端后端，这是客户端连接到服务器的时间。
xact_start	timestamp with time zone	该进程的当前事务开始的时间，如果没有事务处于活动状态，则为 null。如果当前查询是其事务的第一个，则该列等于该query_start列。
query_start	timestamp with time zone	当前活动查询开始的时间，如果state不是active，则为最后一个查询开始的时间
state_change	timestamp with time zone	state上次更改的时间
wait_event_type	text	后端正在等待的事件类型（如果有）；否则为空。见等待事件类型表
wait_event	text	如果后端当前正在等待，则等待事件名称，否则为 NULL。
state	text	此后端的当前整体状态。可能的值为： active(活动)：后端正在执行查询； idle(空闲)：后端正在等待新的客户端命令； idle in transaction(事务空闲)：后端处于事务中，但当前未执行查询； idle in transaction (aborted)(事务空闲-退出)：此状态类似于idle in transaction，只是事务中的一条语句导致了错误； fastpath function call(快速道函数调用)：后端正在执行快速路径功能； disabled(禁用)：如果在此后端禁用track_activities ，则会报告此状态。
backend_xid	xid	此后端的顶级事务标识符（如果有）
backend_xmin	xid	当前后端的xmin范围
query_id	bigint	此后端最近查询的标识符。如果state是active此字段显示当前正在执行的查询的标识符。在所有其他状态下，它显示最后执行的查询的标识符。默认情况下不计算查询标识符，因此该字段将为空，除非启用了compute_query_id参数或配置了计算查询标识符的第三方模块。
query	text	此后端最近查询的文本。如果state是active此字段显示当前正在执行的查询。在所有其他状态下，它显示最后执行的查询。默认情况下，查询文本被截断为 1024 字节；这个值可以通过参数track_activity_query_size改变。
backend_type	text	当前后端的类型。可能的类型有autovacuum launcher, autovacuum worker, logical replication launcher, logical replication worker, parallel worker, background writer, client backend, checkpointer, archiver, startup, walreceiver,walsender和walwriter. 此外，通过扩展注册的后台进程可能还有其他类型。

主要应用

遇到数据库查询慢，或者数据库故障问题，可以查询此表，快速定位问题

查询是否有未提交的事务

SELECT
	pid,
	usesysid,
	usename,
	application_name,
	client_addr,
	query_start,
	wait_event_type,
	"state",
	query 
FROM
	pg_stat_activity 
WHERE
	datname = 'TableName'

如果 state状态为 idle in transaction 就说明存在事务未提交，导致查询异常，可以通过以下方式强制关闭

杀掉pg数据库正在运行的sql

1. 使用 pg_cancel_backend 函数：连接到 PostgreSQL 数据库，并执行以下命令以停止所有正在执行的任务：

// 这个是温柔的杀，向后台发送sigint信号，关闭当前后台进程，用户只能关闭自己的后台进程，事务回滚。

// 向指定pid的任务发送取消信号，并尝试终止执行
SELECT pg_cancel_backend(pid)

// 向所有处于活动状态的任务发送取消信号，并尝试终止它们的执行
SELECT pg_cancel_backend(pid)
FROM pg_stat_activity
WHERE state = 'active';

2. 使用 pg_terminate_backend 函数：如果需要强制终止任务，而不是仅取消执行，可以使用 pg_terminate_backend 函数：

// 这个是强杀，向后台发送sigterm信号，关闭当前后台进程，需要有超级用户权限，超级用户可以关闭所有后台进程，事务回滚。当pg_cancel_backend取消不了当前sql时，可以使用此函数

// 此命令将强制终止所有活动状态的任务。
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'active';

3. 使用 系统函数kill -9 pid

// 当pg_terminate_backend 不起作用时，我们就要从操作系统层面使用kill命令来杀掉连接了。
// 通过上面查到的pid，在操作系统上ps -ef |grep pid查看当前连接的状态，然后kill -9 pid杀掉该连接。

postgres=# select pid,query_start,state,query from pg_stat_activity where state='active';
  pid  |          query_start          | state  |                                     query                                      
-------+-------------------------------+--------+--------------------------------------------------------------------------------
  5001 | 2019-08-13 12:45:16.652909+08 | active | select * from perf_analyse;
 18876 | 2019-08-13 12:45:19.019691+08 | active | select pid,query_start,state,query from pg_stat_activity where state='active';
(2 rows)

postgres=# \q
postgres@xxx:~> ps -ef |grep 5001
postgres  5001 23550 64 12:45 ?        00:00:13 postgres: postgres postgres [local] SELECT
postgres  7677  6228  0 12:45 pts/3    00:00:00 grep --color=auto 5001
postgres@xxx:~> kill -9 5001

---

参考：
PostgreSQL数据库查询监控技术——pg_stat_activity简介

如何杀掉pg数据库正在运行的sql