Reconnect after db reboot not possible

[giselher9]

We have applications, which were not able to reconnect to the db, after the db server has been rebooted during an outage (lasted several hours).
Problem: Applications only reconnected after application restart.

We got the following exception:
org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is java.sql.SQLTransientConnectionException: HikariPool-2 - Connection is not available, request timed out after 10000ms.

When we turn down the db instance in a friendly way, applications reconnect at once, after we restart the db instance.

So, do we miss any configuration or other hints?

Environment

HikariCP version: 2.6.2
JDK version     : 1.8
Database        : PostgreSQL
Driver version  : 9.4.1212
SpringBoot      : 1.5.8

Config

datasource:
  default:
    jdbcUrl:
    minimum-idle: 8
    maximum-pool-size: 100
    idle-timeout: 10000
    connection-timeout: 10000
    max-lifetime: 120000

[brettwooldridge]

@giselher9 This is caused by unacknowledged TCP -- and TCP connections that never entered the FIN /FINACK state.

The fix for this is two fold.

The first part is to ensure that socketTimeout is configured. Be careful with this, socketTimeout should be set to at least 2-3x longer than your longest expected query. If it were me I would set it to one minute longer than your longest expected query.

The second part is not yet possible. The PostgreSQL driver does not currently support Connection.setNetworkTimeout(), which should be configured through HikariCP. However, I personally added this feature to the driver back in June 2017. It was eventually merged in November 2017, but it is not yet available in a released driver. Though it sounds like it could be "any day now".

Either way, the global socketTimeout should get you out of your pickle -- possibly not quite as quickly as when connection-by-connection timeouts will be available -- but it will do the job.

[jrochette-lmi]

Just to save the trouble of scanning through the PostgreSQL driver release notes, the Connection.setNetworkTimeout() was released in version 42.2.0 of the driver (https://jdbc.postgresql.org/documentation/changelog.html#version_42.2.0)

[ldeck]

Just to save the trouble of scanning through the PostgreSQL driver release notes, the Connection.setNetworkTimeout() was released in version 42.2.0 of the driver (https://jdbc.postgresql.org/documentation/changelog.html#version_42.2.0)

And to further save trouble searching for an answer on configuring these properties, use something like the following:

spring:
  datasource:
    hikari:
      connection-timeout: 10000
      idle-timeout: 10000
      pool-name: foobar
      max-lifetime: 120000
      maximum-pool-size: 10
      minimum-idle: 8
      data-source-properties:
        currentSchema: foobar
        networkTimeout: 120000
        socketTimeout: 60

See also https://www.theguardian.com/info/2019/dec/02/faster-postgresql-connection-recovery.

NB: the above shouldn't be considered recommended values. YMMV.