psql: FATAL: sorry, too many clients already

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 |

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.

StackExchange Database Administrators Q#69438, answer score: 11