How do I get Spring Boot to automatically reconnect to PostgreSQL?

Question

I am running Spring Boot connecting to a PostgreSQL database. I have verified that data is written to the database if Spring Boot is started after the database.

spring.datasource.url = jdbc:postgresql://localhost/dbname
spring.datasource.username = user
spring.datasource.password = secret
spring.datasource.driver-class-name = org.postgresql.Driver
spring.datasource.testOnBorrow=true
spring.datasource.validationQuery=SELECT 1

When there are changes in the database in development, I run into exceptions: PreparedStatementCallback; SQL []; This connection has been closed.; nested exception is org.postgresql.util.PSQLException: This connection has been closed.

Note that the database is accessible again when the exceptions occur; I think the problem is that connection is stale because the database endpoint it originally connected to is no longer available because of the restart. Restarting Spring Boot resolves the issue.

How do I configure Spring Boot to reconnect to PostgreSQL so that I do not need to restart it?

I have attempted to follow the answers in Spring Boot JPA - configuring auto reconnect and How to reconnect database if the connection closed in spring jpa?. I am not sure whether the PostgreSQL behavior is different from MySQL. My reading of the Spring Boot documentation has not helped; I do not know the components described well enough to understand what documentation I should be looking at.

Solution

Spring boot should be configured to reconnect automatically, problem is that it is not aware of the broken connection.

Since you are already using test on borrow and validation query, just try reducing validation interval so it is executed every time.

spring.datasource.tomcat.test-on-borrow=true
spring.datasource.tomcat.validation-query=SELECT 1
spring.datasource.tomcat.validation-interval=0

Tomcat jdbc connection pool on testOnBorrow:

(boolean) The indication of whether objects will be validated before being borrowed from the pool. If the object fails to validate, it will be dropped from the pool, and we will attempt to borrow another. NOTE - for a true value to have any effect, the validationQuery or validatorClassName parameter must be set to a non-null string. In order to have a more efficient validation, see validationInterval. Default value is false

But be aware of validationInterval:

(long) avoid excess validation, only run validation at most at this frequency - time in milliseconds. If a connection is due for validation, but has been validated previously within this interval, it will not be validated again. The default value is 30000 (30 seconds).