Postgres 連接配接數過多 psql: FATAL: sorry, too many clients already

    今天開發找到我，說他們的資料庫連接配接不上了，可能是連接配接數太多，然後我登入到伺服器，并且嘗試登陸資料庫，也是報錯：

psql: FATAL:  sorry, too many clients already

很明顯，是資料庫連接配接滿了。于是檢視一下資料庫連接配接程序：

[postgres@ec2s-autodenalicontentpoi-01 ~]$ ps -ef |grep postgres

postgres  3406 18212  0 00:35 ?        00:01:00 postgres: denaliadmin region_na 172.16.60.16(51976) idle

postgres  4221 18212  0 01:09 ?        00:00:03 postgres: denaliadmin region_anz 10.66.40.44(61006) idle

postgres  4223 18212  0 01:09 ?        00:00:00 postgres: denaliadmin region_anz 10.66.40.44(61009) idle

postgres  4390 18212  0 01:16 ?        00:00:00 postgres: denaliadmin region_sa 10.66.40.46(63779) idle

postgres  4391 18212  0 01:16 ?        00:00:00 postgres: denaliadmin region_sa 10.66.40.46(63784) idle

postgres  5587 18212  0 02:04 ?        00:00:00 postgres: denaliadmin postgres 172.16.60.16(53018) idle

postgres  5782 18212  2 02:13 ?        00:01:29 postgres: denaliadmin region_sa 10.189.101.98(40704) idle

postgres  5793 18212  1 02:13 ?        00:01:06 postgres: denaliadmin region_sa 10.189.101.98(40705) idle

postgres  5794 18212  1 02:13 ?        00:01:10 postgres: denaliadmin region_sa 10.189.101.98(40706) idle

......

為了能夠登入資料庫，隻有kill掉一些處于idle狀态的程序,再使用超級使用者登入

$ kill  4223 

然後就可以進到資料庫中通過select * from pg_stat_activity where state='idle';來查到哪些程序處于空閑，然後批量kill.

下面個人總結了一些關于PostgreSQL的連接配接控制：

max_connections

#資料庫最大連接配接數

superuser_reserved_connections

#資料庫預留給超級使用者的連接配接數

Note:

如果max_connections=8，superuser_reserved_connections=3,

前面5次無論我使用什麼使用者登入都算普通使用者登入次數，比如我先用超級使用者postgres連續登陸5次，保持連接配接，第6次用普通使用者是無法登陸，但是用超級使用者是可以登入的。

測試過程

#設定參數大小

postgres=# show max_connections ;

-----------------

8

postgres=# show superuser_reserved_connections;

--------------------------------

3

#使用普通使用者cdhu1和cdhu2連接配接資料庫，連續開多個會話，檢視連接配接數正好5個

testdb1=> select datid,datname,pid,usesysid,usename,application_name,client_addr,client_port,state,query from  pg_stat_activity;

datid | datname |  pid  | usesysid | usename | application_name |   client_addr   | client_port | state  |                                                         query                                                          

-------+---------+-------+----------+---------+------------------+-----------------+-------------+--------+------------------------------------------------------------------------------------------------------------------------

16615 | testdb1 | 60240 |    16642 | cdhu2   | psql             |                 |             |        | <insufficient privilege>

16615 | testdb1 | 60165 |    16638 | cdhu1   | psql             | 192.168.163.102 |       58292 | active | select datid,datname,pid,usesysid,usename,application_name,client_addr,client_port,state,query from  pg_stat_activity;

16615 | testdb1 | 60180 |    16638 | cdhu1   | psql             | 192.168.163.102 |       58293 | idle   | select current_database();

16615 | testdb1 | 60194 |    16638 | cdhu1   | psql             | 192.168.163.102 |       58294 | idle   | select current_database();

16615 | testdb1 | 60196 |    16642 | cdhu2   | psql             |                 |             |        | <insufficient privilege>

#當再次使用普通使用者連接配接資料庫的時候報錯，說明最多可以使用5個普通使用者連接配接資料庫，保留三個超級使用者連接配接：

Darren2:postgres:/usr/local/pgsql/data:>psql -U cdhu2 -d testdb1 -h 192.168.163.101

Password for user cdhu2:

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

#當用超級使用者postgres可以連接配接，并且最多隻能再連接配接3個超級使用者了

Darren1:postgres:/usr/local/pgsql/data:>psql -h192.168.163.101 -Upostgres -d postgres

postgres=# select datid,datname,pid,usesysid,usename,application_name,client_addr,client_port,state,query from  pg_stat_activity;

datid | datname  |  pid  | usesysid | usename  | application_name |   client_addr   | client_port | state  |                                                         query                                                          

-------+----------+-------+----------+----------+------------------+-----------------+-------------+--------+------------------------------------------------------------------------------------------------------------------------

16615 | testdb1  | 60240 |    16642 | cdhu2    | psql             | 192.168.163.102 |       58299 | idle   |

16615 | testdb1  | 60165 |    16638 | cdhu1    | psql             | 192.168.163.102 |       58292 | idle   | select current_user;

16615 | testdb1  | 60180 |    16638 | cdhu1    | psql             | 192.168.163.102 |       58293 | idle   | select current_database();

16615 | testdb1  | 60194 |    16638 | cdhu1    | psql             | 192.168.163.102 |       58294 | idle   | select current_database();

16615 | testdb1  | 60196 |    16642 | cdhu2    | psql             | 192.168.163.102 |       58295 | idle   | select current_database();

13269 | postgres | 60467 |       10 | postgres | psql             | 192.168.163.101 |       53674 | active | select datid,datname,pid,usesysid,usename,application_name,client_addr,client_port,state,query from  pg_stat_activity;

#如果連接配接全部打滿，無論使用什麼使用者都連接配接不上，并報錯

Darren2:postgres:/usr/local/pgsql/data:>psql -U postgres -d testdb1 -h 192.168.163.101

psql: FATAL:  sorry, too many clients already

#可以從系統層面看到連接配接,共8個連接配接，PostgreSQL的每個會話連接配接對應每個系統程序

Darren1:postgres:/usr/local/pgsql:>ps -ef|grep postgres

...... 

postgres  60165  60127  0 18:53 ?        00:00:00 postgres: cdhu1 testdb1 192.168.163.102(58292) idle

postgres  60180  60127  0 18:53 ?        00:00:00 postgres: cdhu1 testdb1 192.168.163.102(58293) idle in transaction

postgres  60194  60127  0 18:54 ?        00:00:00 postgres: cdhu1 testdb1 192.168.163.102(58294) idle

postgres  60196  60127  0 18:54 ?        00:00:00 postgres: cdhu2 testdb1 192.168.163.102(58295) idle

postgres  60240  60127  0 18:55 ?        00:00:00 postgres: cdhu2 testdb1 192.168.163.102(58299) idle

postgres  60467  60127  0 19:00 ?        00:00:00 postgres: postgres postgres 192.168.163.101(53674) idle

postgres  60568  60127  0 19:02 ?        00:00:00 postgres: postgres postgres [local] idle

postgres  60583  60127  0 19:02 ?        00:00:00 postgres: postgres postgres [local] idle

當連接配接打滿了，超級使用者也無法登陸資料的時候怎麼辦？

（1）在系統層面kill其中一個idle程序,然後使用超級使用者登入可以使用pg_terminate_backend(pid)斷開連接配接

Darren1:postgres:/usr/local/pgsql:>kill 60467  

postgres=# select pg_terminate_backend(61825);

pg_terminate_backend

----------------------

t

（2）在系統層面kill -9其中一個程序時，全部的連接配接都會斷開，是以慎重使用

Darren1:postgres:/usr/local/pgsql:>kill -9 60240

WARNING:  terminating connection because of crash of another server process

DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.

HINT:  In a moment you should be able to reconnect to the database and repeat your command.

server closed the connection unexpectedly

    This probably means the server terminated abnormally

    before or while processing the request.

The connection to the server was lost. Attempting reset: Succeeded.

（3）重新開機資料庫

Darren1:postgres:/usr/local/pgsql:>pg_ctl restart

本文轉自 Darren_Chen 51CTO部落格，原文連結：http://blog.51cto.com/darrenmemos/1976519，如需轉載請自行聯系原作者