Postgres too many connections Psql:FATAL:sorry, too many clients already

Today development found me, said their database connection is not up, may be too many connections, and then I log on to the server, and try to log into the database, is also an error:

Psql:FATAL:sorry, too many clients already

It is clear that the database connection is full. Then look at the database connection process:

[Email protected] ~]$ 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

......

In order to be able to log in to the database, only kill some of the idle processes, and then log on with the super user

$ kill 4223

You can then enter into the database through select * from pg_stat_activity where state= ' idle ', to find out which processes are idle and then kill in bulk.

The following individuals summarize some of the connection controls for PostgreSQL:

Max_connections

#数据库最大连接数

Superuser_reserved_connections

#数据库预留给超级用户的连接数

Note:

If max_connections=8,superuser_reserved_connections=3,

5 times no matter what user login I use the average user login count, such as I first use the Superuser Postgres 5 times, keep the connection, the 6th time with the ordinary user is unable to login, but with the super user can log in.

Testing process

#设置参数大小

postgres=# show Max_connections;

Max_connections

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

8

postgres=# show Superuser_reserved_connections;

Superuser_reserved_connections

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

3

#使用普通用户cdhu1和cdhu2连接数据库, open multiple sessions in a row to see exactly 5 connections

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 |& nbsp;  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>

#当再次使用普通用户连接数据库的时候报错, you can use up to 5 ordinary users to connect to the database and retain three Super user connections:

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 is reserved for non-replication superuser connections

# when using Superuser postgres, you can connect with up to 3 super users.

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

#如果连接全部打满, no matter what users are connected, and error

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

Psql:FATAL:sorry, too many clients already

#可以从系统层面看到连接, a total of 8 connections, each session connection for PostgreSQL corresponds to each system process

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

What if the connection is full and the superuser is unable to log on to the data?

(1) Kill one of the idle processes at the system level and then use the Superuser login to disconnect using Pg_terminate_backend (PID)

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

postgres=# Select Pg_terminate_backend (61825);

Pg_terminate_backend

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

T

(2) When one of the processes is kill-9 at the system level, all connections will be disconnected, so use it cautiously

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 have commanded this server process to roll back the current transaction and exit, because another s Erver Process exited abnormally and possibly corrupted shared memory.

Hint:in a moment you should is 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) Restart the database

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

This article is from the "10979687" blog, please be sure to keep this source http://10989687.blog.51cto.com/10979687/1976519

Postgres too many connections Psql:FATAL:sorry, too many clients already