Caused by: org.postgresql.util.PSQLException: FATAL: remaining connection slots are reserved for non-replication superuser connections

Question

I use c3p0-0.9.5.2.jar and mchange-commons-java-0.2.11.jar to manage the pool connection, And I use postgreSql 9.3.

I get these messages at least once a day in my Prod environment :

Caused by: java.sql.SQLException: Connections could not be acquired from the underlying database!
    at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:118)
    at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:692)
    at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(AbstractPoolBackedDataSource.java:140)
    at org.springframework.orm.hibernate3.LocalDataSourceConnectionProvider.getConnection(LocalDataSourceConnectionProvider.java:81)
    at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:446)
    ... 212 more
Caused by: com.mchange.v2.resourcepool.CannotAcquireResourceException: A ResourcePool could not acquire a resource from its primary factory or source.
    at com.mchange.v2.resourcepool.BasicResourcePool.awaitAvailable(BasicResourcePool.java:1469)
    at com.mchange.v2.resourcepool.BasicResourcePool.prelimCheckoutResource(BasicResourcePool.java:644)
    at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:554)
    at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutAndMarkConnectionInUse(C3P0PooledConnectionPool.java:758)
    at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:685)
    ... 215 more
Caused by: org.postgresql.util.PSQLException: FATAL: remaining connection slots are reserved for non-replication superuser connections
    at org.postgresql.core.v3.ConnectionFactoryImpl.readStartupMessages(ConnectionFactoryImpl.java:712)

I have this config in my aplication :

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN"
"http://www.springframework.org/dtd/spring-beans.dtd">

<beans>
         <bean id="dataSource" class = "com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
       <property name="driverClass" value="org.postgresql.Driver"/>

        <property name="jdbcUrl" value="jdbc:postgresql://localhost:5432/Test"/> 

        <property name="user" value="postgres"/>
        <property name="password" value="postgres"/>
           <!-- pool sizing -->
     <!-- pool sizing -->
        <property name="initialPoolSize" value="32" />
        <property name="minPoolSize" value="30" />
        <property name="maxPoolSize" value="300" />
        <property name="acquireIncrement" value="10" />
        <property name="maxStatements" value="0" />

        <!-- retries -->
        <property name="acquireRetryAttempts" value="30" />
        <property name="acquireRetryDelay" value="1000" /> <!-- 1s -->
        <property name="breakAfterAcquireFailure" value="false" />

        <!-- refreshing connections -->
        <property name="maxIdleTime" value="180" /> <!-- 3min -->
        <property name="maxConnectionAge" value="10" /> <!-- 1h -->

        <!-- timeouts and testing -->
        <property name="checkoutTimeout" value="0" /> <!-- 60s -->
        <property name="idleConnectionTestPeriod" value="60" /> <!-- 60 -->
        <property name="testConnectionOnCheckout" value="true" />
        <property name="preferredTestQuery" value="SELECT 1" />
        <property name="testConnectionOnCheckin" value="true" /> 

    </bean>
</beans>

in postgresql.conf I have this config :

max_connections = 300
shared_buffers = 32GB

my server has this performance: 24 cpu, 256 GB memory

the number of users using the application is around 1300

is there anyone who can help me to solve this problem

thank you in advance

Answer 1

The error you are seeing is an indication that postgres does not have any more connections available.

Run this command on your SQL database to see if you can better understand what commands/users are using up so many connections :

 select * from pg_stat_activity;

If you just want to know the count of open connections, use :

SELECT count(*) FROM pg_stat_activity;

Sometimes the number of open connections reaches the max_connections limit.

For me, restarting the pgAdmin app resolved the issue.