今天開發(fā)找到我，說他們的數(shù)據(jù)庫連接不上了，可能是連接數(shù)太多，然后我登錄到服務(wù)器，并且嘗試登陸數(shù)據(jù)庫，也是報(bào)錯(cuò)：
創(chuàng)新互聯(lián)公司是一家專注于成都網(wǎng)站建設(shè)、網(wǎng)站建設(shè)與策劃設(shè)計(jì),尼開遠(yuǎn)網(wǎng)站建設(shè)哪家好?創(chuàng)新互聯(lián)公司做網(wǎng)站,專注于網(wǎng)站建設(shè)10多年,網(wǎng)設(shè)計(jì)領(lǐng)域的專業(yè)建站公司;建站業(yè)務(wù)涵蓋:尼開遠(yuǎn)等地區(qū)。尼開遠(yuǎn)做網(wǎng)站價(jià)格咨詢:13518219792
psql: FATAL: sorry, too many clients already
很明顯，是數(shù)據(jù)庫連接滿了。于是查看一下數(shù)據(jù)庫連接進(jìn)程：
[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
......
為了能夠登錄數(shù)據(jù)庫，只有kill掉一些處于idle狀態(tài)的進(jìn)程,再使用超級(jí)用戶登錄
$ kill 4223
然后就可以進(jìn)到數(shù)據(jù)庫中通過select * from pg_stat_activity where state='idle';來查到哪些進(jìn)程處于空閑，然后批量kill.
下面?zhèn)€人總結(jié)了一些關(guān)于PostgreSQL的連接控制：
max_connections
#數(shù)據(jù)庫最大連接數(shù)
superuser_reserved_connections
#數(shù)據(jù)庫預(yù)留給超級(jí)用戶的連接數(shù)
Note:
如果max_connections=8，superuser_reserved_connections=3,
前面5次無論我使用什么用戶登錄都算普通用戶登錄次數(shù)，比如我先用超級(jí)用戶postgres連續(xù)登陸5次，保持連接，第6次用普通用戶是無法登陸，但是用超級(jí)用戶是可以登錄的。
測(cè)試過程
#設(shè)置參數(shù)大小
postgres=# show max_connections ;
max_connections
-----------------
8
postgres=# show superuser_reserved_connections;
superuser_reserved_connections
--------------------------------
3
#使用普通用戶cdhu1和cdhu2連接數(shù)據(jù)庫，連續(xù)開多個(gè)會(huì)話，查看連接數(shù)正好5個(gè)
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 | | | |
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 | | | |
#當(dāng)再次使用普通用戶連接數(shù)據(jù)庫的時(shí)候報(bào)錯(cuò)，說明最多可以使用5個(gè)普通用戶連接數(shù)據(jù)庫，保留三個(gè)超級(jí)用戶連接：
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
#當(dāng)用超級(jí)用戶postgres可以連接，并且最多只能再連接3個(gè)超級(jí)用戶了
Darren1:postgres:/usr/local/pgsql/data:>psql -h292.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;
#如果連接全部打滿，無論使用什么用戶都連接不上，并報(bào)錯(cuò)
Darren2:postgres:/usr/local/pgsql/data:>psql -U postgres -d testdb1 -h 192.168.163.101
psql: FATAL: sorry, too many clients already
#可以從系統(tǒng)層面看到連接,共8個(gè)連接，PostgreSQL的每個(gè)會(huì)話連接對(duì)應(yīng)每個(gè)系統(tǒng)進(jìn)程
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
當(dāng)連接打滿了，超級(jí)用戶也無法登陸數(shù)據(jù)的時(shí)候怎么辦？
（1）在系統(tǒng)層面kill其中一個(gè)idle進(jìn)程,然后使用超級(jí)用戶登錄可以使用pg_terminate_backend(pid)斷開連接
Darren1:postgres:/usr/local/pgsql:>kill 60467
postgres=# select pg_terminate_backend(61825);
pg_terminate_backend
----------------------
t
（2）在系統(tǒng)層面kill -9其中一個(gè)進(jìn)程時(shí)，全部的連接都會(huì)斷開，所以慎重使用
Darren1:postgres:/usr/local/pgsql:>kill -9 60240
postgres=# select datid,datname,pid,usesysid,usename,application_name,client_addr,client_port,state,query from pg_stat_activity;
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）重啟數(shù)據(jù)庫
Darren1:postgres:/usr/local/pgsql:>pg_ctl restart
重慶分公司
重慶分公司
