postgresql 数据库连接数查询

使用 psql 无法连接数据库，并报错 FATAL:53300

psql: FATAL: 53300: remaining connection slots are reserved for non-replication superuser connections

普通用户的连接已满，保留用于非复制的超级用户连接。

由于连接已满，可以关闭空闲的连接

1）查询当前所有连接的状态

select datname,pid,application_name,state from pg_stat_activity;

2）关闭当前 state 为 idle 空闲状态的连接

查看数据库剩余连接数：

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 superuser_reserved_connections;

psql: FATAL: 53300: sorry, too many clients already

数据库连接已满，无法建立新的连接。
1、关闭空闲连接

select datname,pid,application_name,state from pg_stat_activity;

– 查看目前所有的连接的进程 id、应用名称、状态。

select pg_terminate_backend(pid) from pg_stat_activity;

– 通过 pid 终止空闲连接

当前总共正在使用的连接数：

select count(1) from pg_stat_activity;

显示系统允许的最大连接数

show max_connections;

显示系统保留的用户数

show superuser_reserved_connections ;

–按照用户分组查看

select usename, count(*) from pg_stat_activity group by usename order by count(*) desc;

结束连接的进程

SELECT pg_terminate_backend(15278);

– 杀死所有 idle 的进程：

select pg_terminate_backend(pid) from pg_stat_activity where state=’idle’;