title: why does spring-boot and postgres connection breaks after certain time?
body: <p>I run Spring-Boot with gradle using the tomcat-connection-pool. All the standard spring-boot-tools. I run several soap-webservices on that webserver. It all works fine when testing the load of the server. But after a doing nothing for ~7.5hours this exception occurs. Sure its a <code>timeout</code> but I try to prevent it with the following:</p>
  
  <ul>
  <li><code>spring.datasource.url=jdbc:postgresql://mydb?autoReconnect=true</code></li>
  <li>I use <code>@transactional</code> for certain statements. But in general I only use the <code>JPA-Repository</code> from spring-boot.</li>
  <li>The connections are managed by the tomcat-connection-pool so there is no idle-connection-problem.</li>
  <li>When I restart the application-server everything runs fine again.</li>
  </ul>
  
  <p>My Database-server runs <code>PostgreSQL 9.4.1 on x86_64-unknown-linux-gnu</code> and there is NO firewall between the database and the app-server. </p>
  
  <p>Do I need <a href="https://stackoverflow.com/questions/22296601/postgresql-connection-timeout">tcp_keep alives</a>?</p>
  
  <p><strong>Why does my connection breaks after a certain time and is no more recoverable?</strong></p>
  
  <p>My App-properties:</p>
  
  <pre><code>#
  # [ Database Configuration Section ]
  #
  spring.jpa.database=POSTGRESQL
  spring.jpa.show-sql=false
  hibernate.format_sql=true
  hibernate.hbm2ddl.auto=validate
  spring.jpa.hibernate.naming-strategy=org.hibernate.cfg.DefaultNamingStrategy
  
  spring.datasource.platform=postgres
  spring.database.driverClassName=org.postgresql.Driver
  spring.datasource.url=jdbc:postgresql://*****:5434/******
  spring.datasource.username=*****
  spring.datasource.password=*****
  
  logging.file=*******.log
  logging.level.=WARNING
  </code></pre>
  
  <hr>
  
  <pre><code>2015-09-29 11:58:50.598  INFO 10498 --- [nio-9092-exec-1] o.a.c.c.C.[Tomcat].[localhost].[/]       : Initializing Spring FrameworkServlet 'dispatcherServlet'
  2015-09-29 11:58:50.598  INFO 10498 --- [nio-9092-exec-1] o.s.web.servlet.DispatcherServlet        : FrameworkServlet 'dispatcherServlet': initialization started
  2015-09-29 11:58:50.674  INFO 10498 --- [nio-9092-exec-1] o.s.web.servlet.DispatcherServlet        : FrameworkServlet 'dispatcherServlet': initialization completed in 76 ms
  </code></pre>
  
  <hr>
  
  <pre><code>2015-09-29 19:23:03.777  WARN 10498 --- [ool-3-thread-16] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: 08006
  2015-09-29 19:23:03.779 ERROR 10498 --- [ool-3-thread-16] o.h.engine.jdbc.spi.SqlExceptionHelper   : An I/O error occurred while sending to the backend.
  2015-09-29 19:23:03.785  INFO 10498 --- [ool-3-thread-16] o.h.e.j.b.internal.AbstractBatchImpl     : HHH000010: On release of batch it still contained JDBC statements
  2015-09-29 19:23:03.836 ERROR 10498 --- [ool-3-thread-16] o.s.orm.jpa.JpaTransactionManager        : Commit exception overridden by rollback exception
  java.net.SocketException: Connection timed out
      at java.net.SocketInputStream.socketRead0(Native Method) ~[na:1.8.0_25]
      at java.net.SocketInputStream.read(SocketInputStream.java:150) ~[na:1.8.0_25]
      at java.net.SocketInputStream.read(SocketInputStream.java:121) ~[na:1.8.0_25]
      at org.postgresql.core.VisibleBufferedInputStream.readMore(VisibleBufferedInputStream.java:143) ~[postgresql-9.4-1202-jdbc41.jar!/:9.4]
      at org.postgresql.core.VisibleBufferedInputStream.ensureBytes(VisibleBufferedInputStream.java:112) ~[postgresql-9.4-1202-jdbc41.jar!/:9.4]
      at org.postgresql.core.VisibleBufferedInputStream.read(VisibleBufferedInputStream.java:71) ~[postgresql-9.4-1202-jdbc41.jar!/:9.4]
      at org.postgresql.core.PGStream.ReceiveChar(PGStream.java:282) ~[postgresql-9.4-1202-jdbc41.jar!/:9.4]
      at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1718) ~[postgresql-9.4-1202-jdbc41.jar!/:9.4]
      at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:173) ~[postgresql-9.4-1202-jdbc41.jar!/:9.4]
      ... 61 common frames omitted
  Wrapped by: org.postgresql.util.PSQLException: An I/O error occurred while sending to the backend.
      at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:201) ~[postgresql-9.4-1202-jdbc41.jar!/:9.4]
      at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:615) ~[postgresql-9.4-1202-jdbc41.jar!/:9.4]
      at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:465) ~[postgresql-9.4-1202-jdbc41.jar!/:9.4]
      at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:411) ~[postgresql-9.4-1202-jdbc41.jar!/:9.4]
      at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:208) ~[hibernate-core-4.3.11.Final.jar!/:4.3.11.Final]
      ... 57 common frames omitted
  Wrapped by: org.hibernate.exception.JDBCConnectionException: could not execute statement
  </code></pre>
  
  <p><em>EDIT:</em></p>
  
  <p>I've added this and testing it currently:</p>
  
  <pre><code>  spring.datasource.testOnBorrow=true
    spring.datasource.validationQuery=SELECT 1
  </code></pre>
  
  <p>And removed <code>autoReconnect</code>.</p>