【PostgreSQL]PostgreSQL数据库层限流脚本

with tmp3 as
(select count(*) as cnt from pg_stat_activity WHERE pid <> pg_backend_pid() and datname='mypg1' and usename='postgres' and state='idle' and query like '%select now%')
select 
case
 when cnt <= 400 then 
 (with tmp1 as ( select pg_terminate_backend(pid) from  (select pid from pg_stat_activity WHERE 1=2 ) as foo1)
 select count(*) from tmp1 )
 when cnt > 400 then 
 (with tmp2 as ( select pg_terminate_backend(pid) from  (select pid from pg_stat_activity WHERE pid <> pg_backend_pid() and datname='mypg1' and usename='postgres' 
                and state='active' and query like '%select now%' order by backend_start 
                limit 10) as foo2)
 select count(*) from tmp2 )
end as kill_if_too_many_process
from tmp3 \watch 1

　　

作用：程序执行的语句为类似select now语句，并且该进程是active的，当这样的进程超过400个时，按照进程发起的先后顺序，先杀掉早期的进程。该语句每秒检查一次，每次发现有超过400个时，kill掉最早的10个。

注：语句内容，进程数上限，每次杀的进程数，检查时间，都可以根据自己的情况酌情修改。

原文地址：https://oracleblog.org/useful-script/script-to-limit-process-for-postgresql/