title: All Postgres connections permanently idle after Tomcat restart
body: <p>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.</p>
  <p>I configured the connection pool according to this <a href="http://tomcat.apache.org/tomcat-7.0-doc/jndi-datasource-examples-howto.html#PostgreSQL" rel="nofollow noreferrer">example</a>.</p>
  <p>This is my data source declaration, which I put into <code>$CATALINA_BASE/conf/context.xml</code> so I assume, it will be used for all applications.</p>
  <pre><code>&lt;Resource name=&quot;jdbc/dbResource&quot; auth=&quot;Container&quot;
                  type=&quot;javax.sql.DataSource&quot; driverClassName=&quot;org.postgresql.Driver&quot;
                  url=&quot;jdbc:postgresql://localhost:5432/db&quot;
                  username=&quot;admin&quot; password=&quot;pass&quot; maxActive=&quot;20&quot; maxIdle=&quot;10&quot; maxWait=&quot;-1&quot; /&gt;
  </code></pre>
  <p>In the applications web.xml I have the resource-ref defined</p>
  <pre><code>&lt;resource-ref&gt;
          &lt;description&gt;pg db datasource&lt;/description&gt;
          &lt;res-ref-name&gt;jdbc/dbResource&lt;/res-ref-name&gt;
          &lt;res-type&gt;javax.sql.DataSource&lt;/res-type&gt;
          &lt;res-auth&gt;Container&lt;/res-auth&gt;
  &lt;/resource-ref&gt;
  </code></pre>
  <p><strong>EDIT:</strong></p>
  <p>In my source code I get a connection by the InitialContext object:</p>
  <pre><code>DataSource ds = (DataSource) new InitialContext().lookup(&quot;java:/comp/env/jdbc/dbResource&quot;);
  connection = ds.getConnection();
  </code></pre>
  <p><strong>END EDIT</strong></p>
  <p>The pool seems to be used, according to error messages in my catalina.out log file.</p>
  <p>But directly after restarting tomcat I have many idle connections like this:</p>
  <pre><code> 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
  </code></pre>
  <p>When querying Server Status from pgAdmin, it also says <code>SET extra_float_digits = 3</code> as the query for each connection and I have no clue, where those might come from.</p>
  <p>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.</p>
  <p>The real problem for me is, that in this state, querying my applications results in the error message</p>
  <pre><code>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.&lt;init&gt;(AbstractJdbc2Connection.java:144)
          at org.postgresql.jdbc3.AbstractJdbc3Connection.&lt;init&gt;(AbstractJdbc3Connection.java:29)
          at org.postgresql.jdbc3g.AbstractJdbc3gConnection.&lt;init&gt;(AbstractJdbc3gConnection.java:21)
          at org.postgresql.jdbc4.AbstractJdbc4Connection.&lt;init&gt;(AbstractJdbc4Connection.java:31)
          at org.postgresql.jdbc4.Jdbc4Connection.&lt;init&gt;(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)
  </code></pre>
  <p>I use Tomcat 7.0.52, PostgreSQL 9.3.15 with the driver jar <code>postgresql-9.3-1102-jdbc41.jar</code> in my <code>$CATALINA_BASE/lib folder</code>.</p>
  <p>Is anyone able to spot the problem here?</p>