How do I debug the following error org.postgresql.util.PSQLException. message: FATAL: connection limit exceeded for non-superusers?

[Mariana_Cedica]

Caused by: org.hibernate.exception.GenericJDBCException: Cannot open connection at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:126) at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:114) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:52) at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:449) at org.hibernate.jdbc.ConnectionManager.getConnection(ConnectionManager.java:167) at org.hibernate.jdbc.AbstractBatcher.prepareSelectStatement(AbstractBatcher.java:145) at org.hibernate.id.SequenceGenerator.generate(SequenceGenerator.java:96) at org.hibernate.event.def.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:122) at org.hibernate.ejb.event.EJB3PersistEventListener.saveWithGeneratedId(EJB3PersistEventListener.java:49) at org.hibernate.event.def.DefaultPersistEventListener.entityIsTransient(DefaultPersistEventListener.java:154) at org.hibernate.event.def.DefaultPersistEventListener.onPersist(DefaultPersistEventListener.java:110) at org.hibernate.event.def.DefaultPersistEventListener.onPersist(DefaultPersistEventListener.java:61) at org.hibernate.impl.SessionImpl.firePersist(SessionImpl.java:646) at org.hibernate.impl.SessionImpl.persist(SessionImpl.java:620) at org.hibernate.impl.SessionImpl.persist(SessionImpl.java:624) at org.hibernate.ejb.AbstractEntityManagerImpl.persist(AbstractEntityManagerImpl.java:220) ... 14 more Caused by: org.postgresql.util.PSQLException: FATAL: connection limit exceeded for non-superusers at org.postgresql.core.v3.ConnectionFactoryImpl.readStartupMessages(ConnectionFactoryImpl.java:469) at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:112)

[slacoin_Lacoin · Accepted solution]

You've got in trouble with the database connections. That's mean that you should start to monitor the connection pools of your server. This is do-able by just adding a new bundle in your runtime nuxeo-core-management-jtajca and activate the remote JMX monitoring in your nuxeo.conf.

Once the bundle is installed, you should inspect the following resources using your JMX browser :

• Catalina:class=javax.sql.DataSource,name="jdbc/nuxeo",type=DataSource
• org.nuxeo:name=ConnectionMonitoring,type=service

As an example, using the command line tool jmx term, the following command will figure out how much connections is currently used by your server.

$ java -jar jmxterm-1.0-alpha-4-uber.jar -l localhost:1089 -n <<!
bean Catalina:class=javax.sql.DataSource,name="jdbc/nuxeo",type=DataSource
get numActive
get numIdle
get maxActive
get maxIdle
bean org.nuxeo:name=ConnectionMonitoring,type=service
get ConnectionCount
get IdleConnectionCount
get PartitionCount
get PartitionMaxSize
!

The ouput will be something like

#bean is set to Catalina:class=javax.sql.DataSource,name="jdbc/nuxeo",type=DataSource
#mbean = Catalina:class=javax.sql.DataSource,name="jdbc/nuxeo",type=DataSource:
numActive = 0;

#mbean = Catalina:class=javax.sql.DataSource,name="jdbc/nuxeo",type=DataSource:
maxActive = 100;

#mbean = Catalina:class=javax.sql.DataSource,name="jdbc/nuxeo",type=DataSource:
numIdle = 1;

#bean is set to org.nuxeo:name=ConnectionMonitoring,type=service
#mbean = org.nuxeo:name=ConnectionMonitoring,type=service:
ConnectionCount = 1;

#mbean = org.nuxeo:name=ConnectionMonitoring,type=service:
IdleConnectionCount = 1;

#mbean = org.nuxeo:name=ConnectionMonitoring,type=service:
PartitionCount = 1;

#mbean = org.nuxeo:name=ConnectionMonitoring,type=service:
PartitionMaxSize = 20;

By summing all the counters, we see that our server is currently using 3 connections at all. Also, we know now that the maximum connection our server may open is 120.

Looking deeper in the stack trace, we can see that the error comes from the database itself. That means that you've configured your app server for more connections than what it's available on the database.

You can check what is configured on your database server by executing the following command line :

$ /usr/local/bin/check_postgres.pl --warning='80%' --critical='90%' \
  -H localhost -p 5433 --action=backends --dbname=nuxeo \
  --dbuser=monitor --dbpass=monitor://nx

The output will be something like

 3 of 100 connections (3%) | time=0.08  'nuxeo'=5;80;90;0;100 \
 'postgres'=0;80;90;0;100 'template0'=0;80;90;0;100 'template1'=0;80;90;0;100

So, there is a mismatch of 20 connections. You should always allocate more connection on your database server than what you're using on your app server.

As this stage, you have two options :

• reconfigure nuxeo for using less connections, just read the nuxeo configuration parameters documentation for the pools.
• allocate more connections on your postgresql server (I let you investigate this option by reading the postgresql administration documentation).

[Julien_Carsique]

That means you have reached the configured maximum number of concurrent connections to your PostgreSQL, see max_connections postgres parameter.
You can see the currently opened connections with select * from pg_stat_activity; or netstat -an|grep 5432 (assuming your postgres is using port 5432)

[Wojciech_Sulejm]

You can check your connections using this request: select * from pg_stat_activity;

To fix it you could: Change your max_connections entry in postgresql.conf

Check also this: http://www.postgresql.org/docs/current/static/kernel-resources.html

For the above query and the postgresql.conf location you can use the pgadmin tool. Don't forget to restart the server to make your changes active.

You could check out this connection poller: http://pgfoundry.org/projects/pgbouncer

NOTE: for this kind of issues it would be helpful if you provided your exact postgresql version and any custom configuration you provided, as there might already be an answer here: Configuring Postgresq for Nuxeo