pg_stat_activity视图表

pg_stat_activity视图属性

字段	含义
query	执行SQL语句
query_start	查询开始时间
state	运行状态 active:正在查询；idle:空闲；idle in transaction:后端是一个事务，但是尚未执行查询；idle in transaction(aborted):事务执行出现错误
datid	数据库oid
datname	数据库名称
procpid	线程id
usesysid	用户oid
usename	用户名
waiting	布尔型，为真则说明被锁
query_start	时间戳，开始执行的时间
backend_start	时间戳，线程开始的时间
client_addr	客户端连接地址
client_port	客户端连接端口
application_name	应用名称
xact_start	事务开始执行的时间
waiting_reason	被锁的原因
state_change	时间戳，state最后修改的时间

pg_stat_activity是进程相关视图，用于排除异常很重要的途径（show track_activities默认为on）
pg_stat_activity在11版本中，新增和减少了部分字段，如waiting,procpid,在13新增了leader_pid

##11.4
postgres=# \d+ pg_stat_activity
                                  View "pg_catalog.pg_stat_activity"
      Column      |           Type           | Collation | Nullable | Default | Storage  | Description
------------------+--------------------------+-----------+----------+---------+----------+-------------
 datid            | oid                      |           |          |         | plain    |
 datname          | name                     |           |          |         | plain    |
 pid              | integer                  |           |          |         | plain    |
 usesysid         | oid                      |           |          |         | plain    |
 usename          | name                     |           |          |         | plain    |
 application_name | text                     |           |          |         | extended |
 client_addr      | inet                     |           |          |         | main     |
 client_hostname  | text                     |           |          |         | extended |
 client_port      | integer                  |           |          |         | plain    |
 backend_start    | timestamp with time zone |           |          |         | plain    |
 xact_start       | timestamp with time zone |           |          |         | plain    |
 query_start      | timestamp with time zone |           |          |         | plain    |
 state_change     | timestamp with time zone |           |          |         | plain    |
 wait_event_type  | text                     |           |          |         | extended |
 wait_event       | text                     |           |          |         | extended |
 state            | text                     |           |          |         | extended |
 backend_xid      | xid                      |           |          |         | plain    |
 backend_xmin     | xid                      |           |          |         | plain    |
 query            | text                     |           |          |         | extended |
 backend_type     | text                     |           |          |         | extended |
View definition:
 SELECT s.datid,
    d.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::integer) s(datid, pid, usesysid, application_name, state, query, wait_event_type, wait_event, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port, backend_xid, backend_xmin, backend_type, ssl, sslversion, sslcipher, sslbits, sslcompression, sslclientdn)
     LEFT JOIN pg_database d ON s.datid = d.oid
     LEFT JOIN pg_authid u ON s.usesysid = u.oid;

1.获取慢查询

• now()-xact_start:事务截止当前已运行时间 now()-query_start:query截止当前已运行时间
• pg_cancel_backend()函数：回滚未提及的事务，中断线程，但是不会中断会话
• pg_terminate_backend()函数：回滚未提交的事务，中断会话
• ||:连接符号
• “::”：类型转换或者CASE
• extract(field from source)函数：提取时间或者日期的子域，其中field常用的有
  a.年，月，日：year，month，day
  b.小时，分钟，秒：hour，min，second
  c.季度，世纪，星期：quarter，century，week
• epoch:计算时间差

select 'select pg_cancel_backend('|| a0.pid || ');' as cancel_pid,
'select pg_terminate_backend('|| a0.pid || ');' as terminate_pid,
round(extract(epoch from (now()-xact_start))::numeric,1) as xact_second,
round(extract(epoch from (now()-query_start))::numeric,1) as xact_second,
datname,pid,usename,application_name,client_addr,client_port,xact_start,query_start,query
from pg_stat_activity a0
where 1=1 and a0.state<>'idle' and (a0.backend_xid is not null or backend_xmin is not null)
order by now()-xact_start;

2. 查询当前正在运行的SQL

select datname,usename,query 
from pg_stat_activity
where current_query != '<IDLE>';

select pid,usename,datname,query,client_addr
from pg_catalog.pg_stat_activity
where pid<>pg_backend_pid() and state='active'
order by query;

3. 查看耗时比较长的查询

select current_timestamp - query_start as runtime,datname,usename,query
from pg_stat_activity
where query != '<IDLE>'
order by runtime desc;

4. 查看执行SQL是否被锁

select * from pg_locks;

5. 查看锁的具体信息

select b.relname,a.pid,a.locktype,a.database,a.mode,a.relation
from pg_locks a
join pg_class b on a.relation = b.oid
where b.relname = 'tb_name';

6. 通过cancel取消一个正在运行的query

select pg_cancel_backend(pid);

7. 使用pg_terminate_backend来清理idle session

select pg_terminate_backend(pid);

资料来源：
1.https://blog.csdn.net/luojinbai/article/details/44586917
2.https://blog.csdn.net/weixin_30329623/article/details/101092530?utm_medium=distribute.pc_relevant.none-task-blog-baidujs_title-0&spm=1001.2101.3001.4242