Shulou(Shulou.com)06/01 Report--

Today, the developer came to me and said that their database could not be connected, maybe there were too many connections, and then I logged in to the server and tried to log in to the database, but also reported an error:

Psql: FATAL: sorry, too many clients already

Obviously, the database connection is full. Take a look at the database connection process:

[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

.

In order to be able to log in to the database, only kill drops some processes in the idle state, and then logs in using the superuser

$kill 4223

Then you can go to the database and use select * from pg_stat_activity where state='idle'; to find out which processes are idle, and then batch kill.

The following individuals summarize some connection controls for PostgreSQL:

Max_connections

# maximum number of database connections

Superuser_reserved_connections

# number of connections reserved for superusers in the database

Note:

If max_connections=8,superuser_reserved_connections=3

The first 5 times no matter what user I use to log in, for example, I first use the super user postgres to log in 5 times in a row, keep the connection, the 6th time with the ordinary user is unable to log in, but the super user can log in.

Testing process

# set parameter size

Postgres=# show max_connections

Max_connections

-

eight

Postgres=# show superuser_reserved_connections

Superuser_reserved_connections

-

three

# use ordinary users cdhu1 and cdhu2 to connect to the database, open multiple sessions in succession, and check that the number of connections is exactly 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 |

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 |

# an error is reported when using ordinary users to connect to the database again, indicating that a maximum of 5 ordinary users can be used to connect to the database, and three super user connections can be retained:

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

# you can connect with superuser postgres, and you can only connect to a maximum of 3 superusers.

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

# if all the connections are full, no matter what users are using, they will not be able to connect and report an error

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

Psql: FATAL: sorry, too many clients already

# you can see the connections at the system level, with a total of 8 connections. Each session connection of 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 018: 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 happens when the connection is full and the superuser can't log in to the data?

(1) kill one of the idle processes at the system level, and then use 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) at the system level, when one of the kill-9 processes is disconnected, 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 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) restart the database

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.