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

Romanaromanas

maxPoolSize should be lower then max_connections – Regolith

I will set 200 to maxPoolSize and 300 max_connections , I want to know your opinion for this change before putting it into production , as I said the number of users using the application is around 1300 – Romanaromanas

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.

Checkroom

Answer 2

There are superuser_reserved_connections connections slots (3 by default) that are reserved for superusers so that they can connect even in a situation where all connection slots are taken.

So you effectively only have 297 slots available.

Either reduce the maximum number of connections of your connection pool or increase max_connections in PostgreSQL.

By the way, 300 is much too high. You should use a much lower setting with a connection pool (unless you have hundreds of cores in your database machine).

Insectivorous

thank you for the response , juste note that my application and the database are in the same physical server. so as you say the solution is to make config like this : <property name="maxPoolSize" value="200" /> in my application , and we keep the same value 300 of max_connections in PostgreSQL – Romanaromanas

Yes, that would be a good solution. Even better would be to reduce the size to 20 or 30 (depends heavily on your hardware, see this Wiki article). – Insectivorous

@LaurenzAlbe We have set max_connections = 1000 for our postgres box, few days back we got an issue in production in which idle connections number reached to 995 due to which user's requests are not served by postgres server and we start getting Connection is not available, request timed out after 30000ms. Can you tell what all can be the possible reasons of increasing idle connections in postgres box ? – Pallua

@SumitKandoi The only reason can be that your application opens database connections, but does not close them. That is a bug in the application that must be fixed there. But don't keep opening and closing database connections, use a connection pool. – Insectivorous