All Postgres connections permanently idle after Tomcat restart

Question

After switching from DriverManager to a Tomcat/ Postgres JNDI pool, I face a problem with all connections being used by the deployed applications directly after restart of Tomcat. I wasn't able to find a solution for this problem yet, even after checking several similar posts.

I configured the connection pool according to this example.

This is my data source declaration, which I put into $CATALINA_BASE/conf/context.xml so I assume, it will be used for all applications.

<Resource name="jdbc/dbResource" auth="Container"
                type="javax.sql.DataSource" driverClassName="org.postgresql.Driver"
                url="jdbc:postgresql://localhost:5432/db"
                username="admin" password="pass" maxActive="20" maxIdle="10" maxWait="-1" />

In the applications web.xml I have the resource-ref defined

<resource-ref>
        <description>pg db datasource</description>
        <res-ref-name>jdbc/dbResource</res-ref-name>
        <res-type>javax.sql.DataSource</res-type>
        <res-auth>Container</res-auth>
</resource-ref>

EDIT:

In my source code I get a connection by the InitialContext object:

DataSource ds = (DataSource) new InitialContext().lookup("java:/comp/env/jdbc/dbResource");
connection = ds.getConnection();

END EDIT

The pool seems to be used, according to error messages in my catalina.out log file.

But directly after restarting tomcat I have many idle connections like this:

 4679 ?        Ss     0:00 postgres: admin db 127.0.0.1(41577) idle
 4680 ?        Ss     0:00 postgres: admin db 127.0.0.1(41578) idle
 4681 ?        Ss     0:00 postgres: admin db 127.0.0.1(41579) idle
 4682 ?        Ss     0:00 postgres: admin db 127.0.0.1(41580) idle
 4683 ?        Ss     0:00 postgres: admin db 127.0.0.1(41581) idle
 4684 ?        Ss     0:00 postgres: admin db 127.0.0.1(41582) idle
 4685 ?        Ss     0:00 postgres: admin db 127.0.0.1(41583) idle
 4686 ?        Ss     0:00 postgres: admin db 127.0.0.1(41584) idle
 4687 ?        Ss     0:00 postgres: admin db 127.0.0.1(41585) idle
 4688 ?        Ss     0:00 postgres: admin db 127.0.0.1(41586) idle
 4885 ?        Ss     0:00 postgres: admin db 127.0.0.1(41650) idle
 4886 ?        Ss     0:00 postgres: admin db 127.0.0.1(41651) idle
 4887 ?        Ss     0:00 postgres: admin db 127.0.0.1(41652) idle
 4888 ?        Ss     0:00 postgres: admin db 127.0.0.1(41653) idle
 4889 ?        Ss     0:00 postgres: admin db 127.0.0.1(41654) idle
 4890 ?        Ss     0:00 postgres: admin db 127.0.0.1(41655) idle
 4891 ?        Ss     0:00 postgres: admin db 127.0.0.1(41656) idle
 4892 ?        Ss     0:00 postgres: admin db 127.0.0.1(41657) idle
 4893 ?        Ss     0:00 postgres: admin db 127.0.0.1(41658) idle
 4894 ?        Ss     0:00 postgres: admin db 127.0.0.1(41659) idle

When querying Server Status from pgAdmin, it also says SET extra_float_digits = 3 as the query for each connection and I have no clue, where those might come from.

To test, if I were closing all my connections, statements and ResultSets correctly, I removed all database connections from my code and deployed it to tomcat again. Even now, those idle connections appear. So I suppose, it is not my application having a connection leak but rather a problem with the configuration of the underlying pool.

The real problem for me is, that in this state, querying my applications results in the error message

Mar 24, 2017 11:53:14 AM org.apache.catalina.core.NamingContextListener addResource
WARNING: Failed to register in JMX: javax.naming.NamingException: FATAL: remaining connection slots are reserved for non-replication superuser connections
Mar 24, 2017 11:53:14 AM org.apache.naming.NamingContext lookup
WARNING: Unexpected exception resolving reference
org.postgresql.util.PSQLException: FATAL: remaining connection slots are reserved for non-replication superuser connections
        at org.postgresql.core.v3.ConnectionFactoryImpl.readStartupMessages(ConnectionFactoryImpl.java:582)
        at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:185)
        at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:64)
        at org.postgresql.jdbc2.AbstractJdbc2Connection.<init>(AbstractJdbc2Connection.java:144)
        at org.postgresql.jdbc3.AbstractJdbc3Connection.<init>(AbstractJdbc3Connection.java:29)
        at org.postgresql.jdbc3g.AbstractJdbc3gConnection.<init>(AbstractJdbc3gConnection.java:21)
        at org.postgresql.jdbc4.AbstractJdbc4Connection.<init>(AbstractJdbc4Connection.java:31)
        at org.postgresql.jdbc4.Jdbc4Connection.<init>(Jdbc4Connection.java:24)
        at org.postgresql.Driver.makeConnection(Driver.java:410)
        at org.postgresql.Driver.connect(Driver.java:280)
        at org.apache.tomcat.jdbc.pool.PooledConnection.connectUsingDriver(PooledConnection.java:278)
        at org.apache.tomcat.jdbc.pool.PooledConnection.connect(PooledConnection.java:182)

I use Tomcat 7.0.52, PostgreSQL 9.3.15 with the driver jar postgresql-9.3-1102-jdbc41.jar in my $CATALINA_BASE/lib folder.

Is anyone able to spot the problem here?

The idle connections are perfectly fine - that's what a connection pool does. You told it it's OK to have 10 idle sessions: `maxIdle="10"` — , Mar 24 '17 at 11:51
For the `extra_float_digits` see here: http://stackoverflow.com/questions/24680696/set-extra-float-digits-3-in-postgresql or here: http://stackoverflow.com/questions/39488849/how-do-i-pass-a-jdbc-paramater-using-springboot-and-hibernate or here: http://dba.stackexchange.com/questions/31108/set-extra-float-digits-3 — , Mar 24 '17 at 11:53
Ok, that's a good message. But how is it possible then, that I get the error message that no connection slots are remaining and I am not able to execute queries on the database? — ulrich, Mar 24 '17 at 11:57
Maybe you started Tomcat several times? I have seen situations where Tomcat was apparently shut down (released port 8080, so it could be restarted) but the JVM was still running and held connections to the database. — , Mar 24 '17 at 11:59
Also, I have several times the block of 10 connections to the same database. Is it for each application using the pool? — ulrich, Mar 24 '17 at 11:59
What does postgres say? How many connection does it list as open? And what's its maximum connections? — slowy, Mar 24 '17 at 12:02
@a_horse_with_no_name When I restart tomat, the connections are removed, according to the `ps ax | grep postgres` command. They appear again after deploying the applications. — ulrich, Mar 24 '17 at 12:02
"*I have several times the block of 10 connections to the same database*" - what exactly do you mean with that? Do you have multiple pool definitions in your `context.xml` each one with `maxActive=20`? — , Mar 24 '17 at 12:07
I don't think there's anything wrong with either your code or Tomcat configuration (context.xml and web.xml) though possibly there's something that you haven't shown us. It's hard to be certain what conditions led to this exception but take a look at here https://stackoverflow.com/questions/13640871/heroku-psql-fatal-remaining-connection-slots-are-reserved-for-non-replication — dsp_user, Mar 24 '17 at 12:22
@a_horse_with_no_name Actually, I have three resources defined in context.xml each with `maxActive=20` and `maxIdle=10`. On top of that I have two applications using those data sources. So now it makes sense: after restarting tc again, I now get clearly blocks of 10 connections so in total 60, from the ps command. — ulrich, Mar 24 '17 at 12:26
Now I get, that my problem might be my test scenario. I was trying to access the same (remote) database from my local workstation whereas the remote tomcat alredy blocks those idle connections. Seems like my local connection is then not getting a chance because there is no connection slots left when the remote tomcat alredy used many. — ulrich, Mar 24 '17 at 12:29
Lowering the maxIdle and maxActive connections seems to resolve it for now, when I access postgres from different clients/ pools. Many thanks for that help in understanding, much appreciated! — ulrich, Mar 24 '17 at 12:43

All Postgres connections permanently idle after Tomcat restart

0 Answers0