Postgresql 连接数查看，死锁问题解决

-- 查看所有连接
select * -- datname,pid,application_name,state 
from pg_stat_activity;
-- 查询最大连接数
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;
show max_connections; -- 192
 
-- 查询超级用户连接数
show superuser_reserved_connections;
 
-- 杀掉空闲进程
select 'select pg_terminate_backend('||pid||');' as terminate_script
      ,datname,pid,application_name,state,client_addr,query
from pg_stat_activity
where state = 'idle'
and usename = 'tzq'
order by client_addr;
 
 
-- 检索死锁进程ID
 
 select * from pg_stat_activity where datname='tzydb' and wait_event_type='Lock';
 select pid,query,* from pg_stat_activity where datname='vulnerability-management' and wait_event_type = 'Lock';
 
-- 找到对用的pid列的值或
-- oid是每个表隐藏的id
　select oid from pg_class where relname='可能锁表了的表' 
　select pid from pg_locks where relation='上面查出的oid'
 
-- 将进程杀掉
  select pg_cancel_backend("死锁那条数据的pid值")；

--查询当前所有空闲链接：
 
select client_addr,datname,pid,application_name,state from pg_stat_activity where state='idle' order by client_addr ;
 
select * from pg_stat_activity limit 10;
 
-- 查询当前所有连接数：
select count(1) from pg_stat_activity;
 
-- 查询最大连接数：
show max_connections;
 
select current_setting('max_connections');
 
-- 终止空闲链接
 
select pg_terminate_backend(pid) from pg_stat_activity where pid<>pg_backend_pid() and state='idle';
 
-- 查看为超级用户保留的连接数: 
 
show superuser_reserved_connections;
 
-- 查看每个用户的而连接数：
select usename, count(*) from pg_stat_activity group by usename order by count(*) desc;
 
-- 查看数据库剩余连接数：
 
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;

修改最大连接数配置：

修改完成后，重启服务才会生效！！！

文中需要的资源及相关文档可以在群共享中下载，任何技术问题都可以群里交流，请注意：本群禁广告！本群禁广告！本群禁广告！重要的事情说三遍，感谢！