psql: FATAL: sorry, too many clients already

Question

I am suddenly getting this error when either trying to access the website that uses the postgresql database, or even when using the psql utility or pgadmin3.

My database is set to handle 150 maximum connections:

# SHOW max_connections;
 max_connections 
-----------------
 150
(1 row)

After rebooting the ubuntu server which my website is on (which is really the only thing using connections), I see the current amount of connections is 140:

# select count(*) from pg_stat_activity;
 count 
-------
   140
(1 row)

I don't understand how suddenly so many connections after rebooting my server. So I check the postgresql activity:

# SELECT * FROM pg_stat_activity;

And I see over a 100 columns with the same exact query that looks like this:

SELECT  "reports".* FROM "reports"  WHERE (("reports"."time" < '2014-06-28 13:30:42.000000' AND "reports"."unit_id" = 3192)) ORDER BY "reports"."id" DESC LIMIT 1

Even more important is they all have the same client address (my web server).

This web server is using ruby on rails with a connection pool of 50. Even though there is a connection pool of 50, the Passenger process/prefork apache configuration is single-threaded and therefore each process cannot spawn 50 threads and 50 database connections. What's more is this occurred after a system reboot which knocked all users off my web server. The likelihood is that postgresql on the database server is not aware of the web server reboot and is still trying to execute these queries.

To answer Craig's comments, under waiting column it shows the letter 'f'. It appears that the query is still executing and the lock has not been released yet. As I previously stated, what is so strange is that suddenly over 100 queries identical to each other within milliseconds apart suddenly appeared in this execution state. That's the mystery to me:

mydb=# SELECT * FROM pg_stat_activity;

 datid  | datname  | procpid | usesysid | usename |                                                                           current_query                                                                           | waiting |          xact_start           |          query_start          |         backend_start         |  client_addr   | client_port
--------+----------+---------+----------+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+-------------------------------+-------------------------------+-------------------------------+----------------+-------------
 464875 | mydb     |    4992 |    16387 | myuser | SELECT  "reports".* FROM "reports"  WHERE (("reports"."time" < '2014-06-28 13:30:42.000000' AND "reports"."unit_id" = 3192)) ORDER BY "reports"."id" DESC LIMIT 1 | f       | 2014-06-28 22:46:48.437081-04 | 2014-06-28 22:46:48.437081-04 | 2014-06-28 22:46:44.089764-04 | 192.111.11.111 |       37166
 464875 | mydb     |    4993 |    16387 | myuser | SELECT  "reports".* FROM "reports"  WHERE (("reports"."time" < '2014-06-28 13:30:42.000000' AND "reports"."unit_id" = 3192)) ORDER BY "reports"."id" DESC LIMIT 1 | f       | 2014-06-28 22:46:48.497764-04 | 2014-06-28 22:46:48.497764-04 | 2014-06-28 22:46:44.277856-04 | 192.111.11.111 |       37167
 464875 | mydb     |    4994 |    16387 | myuser | SELECT  "reports".* FROM "reports"  WHERE (("reports"."time" < '2014-06-28 13:30:42.000000' AND "reports"."unit_id" = 3192)) ORDER BY "reports"."id" DESC LIMIT 1 | f       | 2014-06-28 22:46:48.504425-04 | 2014-06-28 22:46:48.504425-04 | 2014-06-28 22:46:44.485269-04 | 192.111.11.111 |       37168
 464875 | mydb     |    4996 |    16387 | myuser | SELECT  "reports".* FROM "reports"  WHERE (("reports"."time" < '2014-06-28 13:30:42.000000' AND "reports"."unit_id" = 3192)) ORDER BY "reports"."id" DESC LIMIT 1 | f       | 2014-06-28 22:46:48.482695-04 | 2014-06-28 22:46:48.482695-04 | 2014-06-28 22:46:44.688203-04 | 192.111.11.111 |       37169
 464875 | mydb     |    4998 |    16387 | myuser | SELECT  "reports".* FROM "reports"  WHERE (("reports"."time" < '2014-06-28 13:30:42.000000' AND "reports"."unit_id" = 3192)) ORDER BY "reports"."id" DESC LIMIT 1 | f       | 2014-06-28 22:46:48.432836-04 | 2014-06-28 22:46:48.432836-04 | 2014-06-28 22:46:44.703883-04 | 192.111.11.111 |       37170

-- many more

 464875 | mydb     |    5052 |    16387 | myuser | SELECT  "reports".* FROM "reports"  WHERE (("reports"."time" < '2014-06-28 13:30:42.000000' AND "reports"."unit_id" = 3192)) ORDER BY "reports"."id" DESC LIMIT 1 | f       | 2014-06-28 22:46:59.584386-04 | 2014-06-28 22:46:59.584386-04 | 2014-06-28 22:46:51.85682-04  | 192.111.11.111 |       37360
 464875 | mydb     |    5053 |    16387 | myuser | SELECT  "reports".* FROM "reports"  WHERE (("reports"."time" < '2014-06-28 13:30:42.000000' AND "reports"."unit_id" = 3192)) ORDER BY "reports"."id" DESC LIMIT 1 | f       | 2014-06-28 22:46:59.506483-04 | 2014-06-28 22:46:59.506483-04 | 2014-06-28 22:46:52.083316-04 | 192.111.11.111 |       37367
 464875 | mydb     |    8958 |    16387 | myuser | <IDLE>                                                                                                                                                            | f       |                               | 2014-06-29 00:05:06.735249-04 | 2014-06-27 16:34:39.307312-04 | 192.111.11.111 |       52759
 464875 | mydb     |    5054 |    16387 | myuser | SELECT  "reports".* FROM "reports"  WHERE (("reports"."time" < '2014-06-28 13:30:42.000000' AND "reports"."unit_id" = 3192)) ORDER BY "reports"."id" DESC LIMIT 1 | f       | 2014-06-28 22:46:59.52573-04  | 2014-06-28 22:46:59.52573-04  | 2014-06-28 22:46:52.285867-04 | 192.111.11.111 |       37371
 464875 | mydb     |    5055 |    16387 | myuser | SELECT  "reports".* FROM "reports"  WHERE (("reports"."time" < '2014-06-28 13:30:42.000000' AND "reports"."unit_id" = 3192)) ORDER BY "reports"."id" DESC LIMIT 1 | f       | 2014-06-28 22:46:59.530804-04 | 2014-06-28 22:46:59.530804-04 | 2014-06-28 22:46:52.303562-04 | 192.111.11.111 |       37372
 464875 | mydb     |    5056 |    16387 | myuser | SELECT  "reports".* FROM "reports"  WHERE (("reports"."time" < '2014-06-28 13:30:42.000000' AND "reports"."unit_id" = 3192)) ORDER BY "reports"."id" DESC LIMIT 1 | f       | 2014-06-28 22:46:59.572198-04 | 2014-06-28 22:46:59.572198-04 | 2014-06-28 22:46:52.31447-04  | 192.111.11.111 |       37373
 464875 | mydb     |    5057 |    16387 | myuser | SELECT  "reports".* FROM "reports"  WHERE (("reports"."time" < '2014-06-28 13:30:42.000000' AND "reports"."unit_id" = 3192)) ORDER BY "reports"."id" DESC LIMIT 1 | f       | 2014-06-28 22:46:59.872037-04 | 2014-06-28 22:46:59.872037-04 | 2014-06-28 22:46:52.323721-04 | 192.111.11.111 |       37374
 464875 | mydb     |    5058 |    16387 | myuser | SELECT  "reports".* FROM "reports"  WHERE (("reports"."time" < '2014-06-28 13:30:42.000000' AND "reports"."unit_id" = 3192)) ORDER BY "reports"."id" DESC LIMIT 1 | f       | 2014-06-28 22:46:59.961803-04 | 2014-06-28 22:46:59.961803-04 | 2014-06-28 22:46:52.334238-04 | 192.111.11.111 |       37375
 464875 | mydb     |    5059 |    16387 | myuser | SELECT  "reports".* FROM "reports"  WHERE (("reports"."time" < '2014-06-28 13:30:42.000000' AND "reports"."unit_id" = 3192)) ORDER BY "reports"."id" DESC LIMIT 1 | f       | 2014-06-28 22:46:59.53713-04  | 2014-06-28 22:46:59.53713-04  | 2014-06-28 22:46:52.347227-04 | 192.111.11.111 |       37376
 464875 | mydb     |    5060 |    16387 | myuser | SELECT  "reports".* FROM "reports"  WHERE (("reports"."time" < '2014-06-28 13:30:42.000000' AND "reports"."unit_id" = 3192)) ORDER BY "reports"."id" DESC LIMIT 1 | f       | 2014-06-28 22:47:00.208948-04 | 2014-06-28 22:47:00.208948-04 | 2014-06-28 22:46:52.360008-04 | 192.111.11.111 |       37377
 464875 | mydb     |    5061 |    16387 | myuser | SELECT  "reports".* FROM "reports"  WHERE (("reports"."time" < '2014-06-28 13:30:42.000000' AND "reports"."unit_id" = 3192)) ORDER BY "reports"."id" DESC LIMIT 1 | f       | 2014-06-28 22:46:59.938983-04 | 2014-06-28 22:46:59.938983-04 | 2014-06-28 22:46:52.369496-04 | 192.111.11.111 |       37378

Take a look at pg_stat_activity.backend_start. Were these connections created before or after the web server reboot? If they're all new connections, I'd imagine that means the problem is on the web server's end. — Nick Barnes, Jun 29 '14 at 05:03
@NickBarnes these connections all have the same query under the "current_query" column and the backend_start time is practically the same for all of them (milliseconds apart). That is what is so strange and I believe if memory serves me correct they were all before the reboot. But I assumed that the reboot would break the connection. — JohnMerlino, Jun 29 '14 at 05:08
It does break the connection, but Postgres may not find out that the connection's broken until it tries to send something back to the client. Are these queries actually running? (i.e. What does pg_stat_activity.state say?) — Nick Barnes, Jun 29 '14 at 05:16
Ok... You might need to check top on the server to see if these processes are busy. If they are, then I think the connections should disappear once the queries finish (or alternatively, you can just kill them now). If they're idle, and the connections are definitely dead, then I'm not sure what's going on, or how to prevent it next time... — Nick Barnes, Jun 29 '14 at 05:59
Check the waiting flag in pg_stat_activity, see if they're stuck on a lock. — Craig Ringer, Jun 30 '14 at 09:36
The output you pasted from SELECT * FROM pg_stat_activity; is not believable--there are not enough columns. What does the state column say? That's the most important field for this question. — eradman, Nov 18 '15 at 08:42

score 11 · Answer 1 · answered Jan 05 '18 at 19:46

This seems to be client programming specific problem. You won't be able to fix this by e.g. raising "max_connections" parameter.

I've found a possible related issue: Ruby database connection pooling

Allthough You could also do some more server side debugging:

Enable "log_connections" and "log_disconnections". Also use "log_line_prefix" with "%m %a %p".

Very useful applications for debugging PostgreSQL servers are powa or much more top like: pg_activity

For realtime server debugging I prefer pg_activity - especially with it's feature to display blockers and to kill sessions.

score -1 · Answer 2 · answered Aug 05 '21 at 08:42

-1

Probably mainly due to the development env.

Check this issue if use knex.js for pql + next.js

answered Aug 05 '21 at 08:42

Thyme

1

score -3 · Answer 3 · answered Aug 10 '16 at 15:15

-3

This is the best way to solve the problem... it works

Log in to the server using SSH putty,

sudo /etc/init.d/postgresql stop

this kills the dead log processes in the database then,

sudo /etc/init.d/postgresql start

answered Aug 10 '16 at 15:15

Maduka

19

10

And then next time you stop again a production server? Your solution clearly removes the stuck processes, but does not explain why they are there, nor is a sustainable one. – András Váczi Aug 10 '16 at 16:01

psql: FATAL: sorry, too many clients already

3 Answers3