postgres鏈接太多(sorry, too many clients already)-CSDN博客

postgres 由于用戶鏈接數(shù)已滿無(wú)法進(jìn)行新的鏈接，同時(shí)提示錯(cuò)誤 sorry, too many clients already

首先進(jìn)入postgres數(shù)據(jù)庫(kù)，我使用的容器部署，那就是先要進(jìn)入對(duì)應(yīng)的postgres 容器

$ docker exec -u root -it postgresId /bin/bash

1、登錄postgres

$ su postgres

2、進(jìn)入sql 查詢

$ psql

2、當(dāng)前總共正在使用的連接數(shù)

postgres=# select count(1) from pg_stat_activity;
 count 
-------
    23
(1 row)

3、顯示系統(tǒng)允許的最大連接數(shù)

$ show max_connections;

4、顯示系統(tǒng)保留的用戶數(shù)

$ show superuser_reserved_connections ;

5、按照用戶分組查看

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

6、查詢當(dāng)前所有連接的狀態(tài)

postgres=# select datname,pid,application_name,state from pg_stat_activity; 
 datname  | pid  |    application_name    | state  
----------+------+------------------------+--------
 postgres |   47 | psql                   | idle
 app      | 5884 | PostgreSQL JDBC Driver | idle
 app      | 5888 | PostgreSQL JDBC Driver | idle
 app      | 5875 | PostgreSQL JDBC Driver | idle

7、關(guān)閉當(dāng)前state為 idle 空閑狀態(tài)的連接

postgres=# select pg_terminate_backend(5884) from pg_stat_activity;
 pg_terminate_backend 

8、修改最大鏈接數(shù)據(jù)

如果不想關(guān)閉空閑鏈接，可以增加最大鏈接數(shù)

postgres=# show max_connections;
postgres=# alter system set max_connections=1000;

重啟數(shù)據(jù)庫(kù)再查看鏈接就已經(jīng)是1000了