Postgres connection has been closed error in Spring Boot

This is kind of half-answered by the other posts and I wanted to be very explicit. Also I wanted to be more Spring-Boot-esque. Feel free to change the time intervals as necessary.

Option 1: Toss out broken connections from the pool.

Use these properties:

spring.datasource.test-on-borrow=truespring.datasource.validation-query=SELECT 1;spring.datasource.validation-interval=30000

Option 2: Keep connections in the pool alive.

Use these properties:

spring.datasource.test-while-idle=truespring.datasource.validation-query=SELECT 1;spring.datasource.time-between-eviction-runs-millis=60000

Option 3: Proactively toss out idle connections.

Use these properties (Note: I was not able to find reliable documentation on this one for Spring Boot. Also the timeout is in seconds not milliseconds):

spring.datasource.remove-abandoned=truespring.datasource.remove-abandoned-timeout=60

Happy booting!

Very valid question and this problem is usually faced by many. The exception generally occurs, when network connection is lost between pool and database (most of the time due to restart). Looking at the stack trace you have specified, it is quite clear that you are using jdbc pool to get the connection. JDBC pool has options to fine-tune various connection pool settings and log details about whats going on inside pool.

You can refer to to detailed apache documentation on pool configuration to specify abandon timeout

Check for removeAbandoned, removeAbandonedTimeout, logAbandoned parameters

Additionally you can make use of additional properties to further tighten the validation

Use testXXX and validationQuery for connection validity.

I had the exact same problem, with this setup, also using DataSource from Tomcat (org.apache.tomcat.jdbc.pool) to connect to Heroku Postgres:

org.springframework.transaction.CannotCreateTransactionException:     Could not open JPA EntityManager for transactionorg.hibernate.TransactionException: JDBC begin transaction failed: ]     with root causeorg.postgresql.util.PSQLException: This connection has been closed.

What solved it for me was adding this to DataSource init code (borrowing from a Grails question):

dataSource.setTestOnBorrow(true);dataSource.setTestWhileIdle(true);dataSource.setTestOnReturn(true);dataSource.setValidationQuery("SELECT 1");

I'm not sure if all these three are needed to get a stable connection—perhaps not—but having all enabled probably doesn't hurt much.

The JavaDocs clarify what's going on: see e.g. setTestOnBorrow(). A little surprising, perhaps, that by default no such tests are made.