PostgreSQL Evolutions: "PSQLException: FATAL: sorry, too many clients already"

Question

I'm trying to set up a dev PostgreSQL server. It's running and I'm able to create roles, tables, etc. from the command line. However when I try to apply evolutions using Play, I get the following error:

org.postgresql.util.PSQLException: FATAL: sorry, too many clients already

I'm able to connect and if I put in a syntax error on purpose, it returns the syntax error so I know that Play is able to connect to the database. However, after the evolution is successfully applied I get the error shown above.

I'm not terribly well versed in PostgreSQl administration so I'm not sure if the problem is with Play or my PostgreSQL installation. I just installed it using Heroku's Postgres.app on a Mac running Mountain Lion.

Here is what gets written to the console:

! @6cnb0blpp - Internal server error, for request [GET /] ->

play.api.db.evolutions.InvalidDatabaseRevision: Database 'default' needs evolution! [An SQL script need to be run on your database.]
    at play.api.db.evolutions.EvolutionsPlugin$$anonfun$onStart$1$$anonfun$apply$1.apply$mcV$sp(Evolutions.scala:427) ~[play_2.9.1.jar:2.0.4]
    at play.api.db.evolutions.EvolutionsPlugin.withLock(Evolutions.scala:448) ~[play_2.9.1.jar:2.0.4]
    at play.api.db.evolutions.EvolutionsPlugin$$anonfun$onStart$1.apply(Evolutions.scala:414) ~[play_2.9.1.jar:2.0.4]
    at play.api.db.evolutions.EvolutionsPlugin$$anonfun$onStart$1.apply(Evolutions.scala:412) ~[play_2.9.1.jar:2.0.4]
    at scala.collection.LinearSeqOptimized$class.foreach(LinearSeqOptimized.scala:59) ~[scala-library.jar:0.11.3]
    at scala.collection.immutable.List.foreach(List.scala:45) ~[scala-library.jar:0.11.3]
[error] c.j.b.h.AbstractConnectionHook - Failed to acquire connection Sleeping for 1000ms and trying again. Attempts left: 10. Exception: null
[error] c.j.b.h.AbstractConnectionHook - Failed to acquire connection Sleeping for 1000ms and trying again. Attempts left: 9. Exception: null
[error] c.j.b.h.AbstractConnectionHook - Failed to acquire connection Sleeping for 1000ms and trying again. Attempts left: 8. Exception: null
[error] c.j.b.h.AbstractConnectionHook - Failed to acquire connection Sleeping for 1000ms and trying again. Attempts left: 7. Exception: null
[error] c.j.b.h.AbstractConnectionHook - Failed to acquire connection Sleeping for 1000ms and trying again. Attempts left: 6. Exception: null
[error] c.j.b.h.AbstractConnectionHook - Failed to acquire connection Sleeping for 1000ms and trying again. Attempts left: 5. Exception: null
[error] c.j.b.h.AbstractConnectionHook - Failed to acquire connection Sleeping for 1000ms and trying again. Attempts left: 4. Exception: null
[error] c.j.b.h.AbstractConnectionHook - Failed to acquire connection Sleeping for 1000ms and trying again. Attempts left: 3. Exception: null
[error] c.j.b.h.AbstractConnectionHook - Failed to acquire connection Sleeping for 1000ms and trying again. Attempts left: 2. Exception: null
[error] c.j.b.h.AbstractConnectionHook - Failed to acquire connection Sleeping for 1000ms and trying again. Attempts left: 1. Exception: null
[error] application - 

Thanks!

Solution 1

You can reduce the number of connections used by your application. Had the same errors on a mac install. As shown in the official documentation :

db.default.partitionCount=2

# The number of connections to create per partition. Setting this to 
# 5 with 3 partitions means you will have 15 unique connections to the 
# database. Note that BoneCP will not create all these connections in   
# one go but rather start off with minConnectionsPerPartition and 
# gradually increase connections as required.
db.default.maxConnectionsPerPartition=5

# The number of initial connections, per partition.
db.default.minConnectionsPerPartition=5

Solution 2

The number of allowed connections is set in postgresql.conf with the GUC ("Grand Unified Configuration") max_connections. Default is 100.

But before you tinker whit the setting, you must ask yourself: where did all those other 100 connections come from, before your cluster hit the limit? That's usually pointing to a bug in your installation or program!

You may also be interested in connection pooling and this article about The Number of Connections in the Postgres Wiki.

Solution 3

Postgres.app defaults to max_connections = 10. See ~/Library/Application Support/Postgres/var/postgresql.conf.

I fixed this by increasing max_connections to 50. On Mountain Lion I also had to reduce shared_buffers to 500kB for Postgres to start.

Comments

[Ryan]

It's a vanilla Postgres install and a brand-new Play 2.0.4 project. All I have so far are some models defined. Is there a log I can look in to figure out where the other connections are coming from?

[Erwin Brandstetter]

@Ryan: You can set up PostgreSQL to log pretty much anything. Start by reading the manual here.

[Ryan]

I've set those settings in my application.conf but I still end up with the same result. I've connected to PostgreSQL in the past with Play and I've never had this issue, I'm really stumped.