postgresql 中的动态统计视图中的pg_stat_activity

一、postgresql 中的动态统计视图中的pg_stat_activity

业务场景：在进行数据库备份的时候，准备删除数据库 drop database leetcode 却发现不能删除，存在活跃的数据库链接。pg_stat_activity提供了一种获取所有客户端在服务器上的动作快照。

1.1 pg_stat_activity

每个服务器进程一行，显示与那个进程的当前活动相关的信息，例如状态和当前查询

select * from pg_stat_activity where datname = 'leetcode';

• query_start: 当前活动查询被开始的时间，如果state不是active，这个域为上一个查询被开始的时间
• state:
  

select count(*) from pg_stat_activity where state=‘idle’; 查询闲置连接数。注意是否忘记关闭链接。 另外还可以查看连接数是不是过多等问题。

• query: 这个后端最近查询的文本。如果state为active，这个域显示当前正在执行的查询。在所有其他状态下，它显示上一个被执行的查询。

wait_event和state列是独立的。如果一个后端处于active状态，它可能是也可能不是某个事件上的waiting。如果状态是active并且wait_event为非空，它意味着一个查询正在被执行，但是它被阻塞在系统中某处。

---

1.2 pg_terminate_backend(pid)

pg_cancel_backend() 取消后台操作，回滚未提交事物

select concat('select  pg_terminate_backend(',pid,');') from pg_stat_activity where datname='${databasename}'

-- 获取所有相关的pid。并取消相关操作。
select  pg_terminate_backend(3802);
select  pg_terminate_backend(20484);
select  pg_terminate_backend(25389);

-- 直接取消后台进程
select pg_terminate_backend(pid) from  (select pid from pg_stat_activity where datname = '${databasename}') t;

……

可以根据PID取消所有后台操作。 然后才能删库跑路

---

1.3 pg_stat_activity 其他应用

• 对这个视图最简单的用法是统计当前有多少活跃的客户端

select count(*) from pg_stat_activity where not pid = pg_backend_pid();

这个数字 可以随时告诉用户距离服务器的 max_connections 有多近。

• 查看一个后端进程运行了多久，以及它当前是否在等待

select pid,state,CURRENT_TIMESTAMP - least(query_start,xact_start) AS runtime,
substr(query,1,25) AS current_query
from pg_stat_activity
where not pid = pg_backend_pid();

当进入空闲状态时（idle 、idle in transaction），知道它是如何进入这种状态的。

---

小结：
还可以通过pg_stat_activity 来检查是否连接数过多，如果多了，查看一下程序是否存在异常，像没有忘记关闭连接等。

-- 可以查看其它字段，指定自己想要的。字段意义，可以查看中文文档
select datname,client_addr,client_port,backend_start,query_start,waiting,query ,其它字段
from pg_stat_activity
where datname = 'datname'

---

参考

• http://www.postgres.cn/docs/9.6/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW