POSTGRESQL pg_stat_activity 一个表360度对PG的管理

pg_stat_activity 实际上对于PG 的管理是非常重要的，下面我们看看如何从多个角度来从pg_stat_activity 对PG 进行管理。

1  pg_stat_activity  , 看似是一个人尽皆知的查看和解决问题的手段，可以获知当前有多少进程在工作。

那我们先来问几个问题，在看看pg_stat_activity 是否我们已经利用了

1  idel 和 active的比例是多少，如果给了大连接数是500，则统计active和idel的比例，如果大连接数和active的比例之间达到80%，那就需要关系后续的大连接数和先关的缓冲池是不是要建立起来了。

2  查看当前事务的一般运行时间，如果运行时间中transaction时间比较长，那就需要注意 idle in transaction 的查询时间，为什么事务打开的这么长时间，还没有执行完毕。

因为这两点直接与我们的vacuum 有关，如果长时间的transaction 无法完成，导致vacuum无法进行工作则表必然会有 bloating的可能。

select pid,query,xact_start,now() - xact_start as duration from pg_stat_activity
 where state like '%transaction%'
 order by 3 desc;

通过上面的语句可以定期的去收集例如超过多少秒的语句该被关注了，然后去后面进行分析和研究。

 select  datname,
         count(*) as open,
         count(*) filter (where state = 'active') as active,
         count(*) filter (where state = 'idle') as idle,
         count(*) filter (where state = 'idle in transaction') as idle_in_transaction
         from pg_stat_activity
         where backend_type='client backend'
         group by rollup(1);

上面的语句就是来对postgresql 当前PG中的active 和 idle之间的连接进行比对。

如果想自动的KILL 一些比较过分的长transaction

WITH inactive_connections AS (
    SELECT   pid, rank() over (partition by client_addr order by backend_start ASC) as rank
    FROM     pg_stat_activity
    WHERE    pid <> pg_backend_pid( ) AND
    application_name !~ '(?:psql)|(?:pgAdmin.+)'
    AND  datname = current_database()
    AND  usename = current_user
    AND  state in ('idle', 'idle in transaction', 'idle in transaction (aborted)', 'disabled')
    AND  current_timestamp - state_change > interval '5 minutes'
)
SELECT pg_terminate_backend(pid)
FROM   inactive_connections
WHERE  rank > 1;

例如通过上面的语句可以将超过5分钟的进程给清理掉，执行这个语句时要注意，实际上好还是生成执行语句，然后在通过人工来进行，而不是全自动，万一有一些是需要进行长期运行的语句。

另外还有一个问题就是展开我们的pg_stat_activity的语句中的query 语句的长度问题，实际上这的确是一个问题，展示不全的语句不利于我们对情况进行分析。

show track_activity_query_size;

那么我们可以更改 track_activity_query_size

然后重新启动我们的postgresql 所以这个变量需要在安装PG的时候就进行一个设置。

说道这里，其实我们并没有关注到pg_stat_activity一些可能平时么有注意的字段含义

其中state 字段中分别有 

1  active  backend正在运行任务，表达进程正在工作

2  idle 此时的进程并未进行实际事务的运行

3 idle in transaction  这意味着 backend 在事务中，但是此时的事务使用的这个query并没有有实际任务在运行

4  idle in transaction (aborted)  这与生命的状态是一致的但是问题在于事务中包含错误

5  fastpath function call

6  disabled  这说明当前的进程被使用正常手段被禁止了

backend_type 这个字段表明当前的backend的状态是什么

如

autovacuum launcher, autovacuum worker , logical replication ,logical replication launcher, parallel worker, background writer, client backend , checkpointer, startup ,walreceiver, walsender , walwriter 

所以一个pg_stat_acivity 其中包含的内容和可以进行学习的东西很多，好好利用，对慢查询，系统运行的状态理解都很有益。