All Postgres connections are constantly idle after restarting Tomcat

Question

All Postgres connections are constantly idle after restarting Tomcat

After switching from DriverManager to Tomcat / Postgres JNDI pool, I ran into an issue where all connections are used by deployed applications immediately after restarting Tomcat. I haven't been able to find a solution to this problem yet, even after checking several similar posts.

I've set up a connection pool according to this example .

This is my datasource declaration that I entered in $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 web.xml apps I have a specific resource

<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 original code, I am getting a connection to the InitialContext object:

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

END EDIT

The pool appears to be in use as per the error messages in my catalina.out log file.

But right after restarting tomcat I have a lot of 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 the server status from pgAdmin, it also speaks SET extra_float_digits = 3

as a request for every connection and I have no clue where they might come from.

To check if I closed all my connections, statements and ResultSets correctly, I removed all database connections from my code and redeployed it to tomcat. Even now, these unused connections appear. So I suppose it is not my application having a connection leak, but rather a problem with the underlying pool configuration.

The real problem for me is, in this state, requesting my apps results in an 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)