实时视图——pg_stat_activity

查看所有客户端的连接状态
```
SELECT * FROM pg_stat_activity;  
```
- ClientRead: Waiting to read data from the client.

统计查询

查询目前所在事务的pid
```
SELECT pg_backend_pid();
```

目前所有客户端连接的数量

SELECT count(*) 
FROM pg_stat_activity 
WHERE NOT pid=pg_backend_pid();

系统允许的最大连接数
```
SHOW max_connections;
```
剩余连接数量

SELECT max_conn-now_conn AS resi_conn 
FROM (
    SELECT setting::int8 AS max_conn,
        (SELECT count(*) FROM pg_stat_activity) 
        AS now_conn 
    FROM pg_settings 
    WHERE name = 'max_connections'
) t

实战
- 【简单统计】所有客户端的连接状态
```
SELECT pid, state, wait_event_type, wait_event,
current_timestamp - least(query_start,xact_start) AS runtime,
substr(query,1,50) AS current_query 
FROM pg_stat_activity 
WHERE pid<>pg_backend_pid() ;
```
- 【详细查看】指定pid的状态
```
SELECT * FROM pg_stat_activity where pid=xxxx; 
```
  - 部分列
    - backend_xid
      - 事务号
      - 有写操作才会申请
      - 可用来判断事务中是否有东西提交，这样方便我们确认可以手动关掉这个事务
    - backend_xmin
      - 事务快照
      - 在repeatable read以上的隔离级别才会有
      - 整个事务过程是一个不变的值
- 【详细查看】指定用户的连接状态
```
SELECT * FROM pg_stat_activity WHERE usename = 'xxx';
```
查看是否追踪某个连接的SQL语句
```
SHOW track_activities;
```
pg_stat_activity内的常用列
- datname
- usename
- client_addr
- client_port
- current_query（新版本版本里叫query)
- waiting（9.6版本后变成了 wait_event_type 和 wait_event)
对于锁死阻塞的查询，可选如下两种方式修复（要超级权限）
- 取消
```
SELECT pg_cancel_backend(pid)
```
- 终结
```
SELECT pg_terminate_backend(pid);
```

Keyboard shortcuts

实时视图——pg_stat_activity