Spring Boot "PSQLException: FATAL: sorry, too many clients already" when running tests

Question

I have a Spring Boot application that provides a REST API to front-ends. I am using jOOQ and Postgresql. I am currently getting this error when executing all integration tests locally (around 1000 tests, this starts happening after executing 700-800 tests):

org.postgresql.util.PSQLException: FATAL: sorry, too many clients already

I tried to limit the max idle and active connections via application.properties, but it seems that these values are somewhat ignored. I simply monitor the open connections when executing the tests with this statement:

SELECT datname, state, query FROM pg_stat_activity;

That's how my application.properties looks like:

spring.datasource.driverClassName = org.postgresql.Driver
spring.datasource.url = jdbc:postgresql://localhost:5432/xxx
spring.datasource.username = xxx
spring.datasource.password = xxx
spring.datasource.initialize = true
spring.datasource.continue-on-error = false
spring.jooq.sql-dialect = POSTGRES
spring.datasource.max-active = 50
spring.datasource.max-idle = 5

That's how I create my data source:

@ConfigurationProperties(prefix = "spring.datasource")
@Bean
public DataSource dataSource() {
    return DataSourceBuilder
            .create()
            .build();
}

I see that jOOQ uses the correct data source and the connection is handled by jOOQ properly (acquiring and releasing connection from data source). So the problem shouldn't be on the jOOQ side.

I have max_connections = 200 in my postgresql.conf, so my Spring configuration should be fine. While running tests I see a lot more idle connections in pg_stat_activity than what I specify in my config. Eventually when the tests start failing due to that PSQLException I see around 90-100 idle connections in pg_stat_activity. So this yields two problems:

1. Why do my tests fail although my local database should allow more connections than what I see in pg_stat_activity?
2. It seems that the data source configuration in my application.properties is ignored. Any idea why?

Protrusive

spring.datasource.max-active = 20 : it is a typo I assume. – Wellfed

@Wellfed no why? Do you mean max-active vs maxActive? Both should work fine. – Protrusive

Not on this point. I said it because you refer to 200 connection max in PostgreSQL. If the number of connection exceeds 20, the error is expected. No ? In fact, you don't tell how many connections are currently opened as you have the error. This information would be useful. – Wellfed

@Wellfed Is also pointing out that your max-idle is more than your max-active, which does not really make sense, so it looks like the value for max-active is incorrect. – Maryleemarylin

200 is the max_connections config in my postgresql.conf. In my application.properties I tried various combinations of max-active and max-idle (I also tried having max-active = max_connections and not specifying max-idle), but I always encounter that PSQLException. I get between 90 -100 results from pg_stat_activity when I receive the exception. – Protrusive

Update: I re-phrased the question and edited the values for more clarity. – Protrusive

get same error boot bootstrap 2 – Ault

Answer 1

Since there hasn't been a suggested answer I am posting my solution. Short version: decrease the connection pool size in test properties:

spring.datasource.hikari.maximum-pool-size=2

Longer version: Spring Boot 2 uses HikariCP by default for connection pooling, which has a default value of 10 for connection pool size (as of Jan 2019). While running a lot of ITs the Spring context is created multiple times, which means each context acquires 10 connections from the database. As far as I've observed, tests allocate connections faster than they are released. Therefore, max_connections limit allowed by the database server (which is typically 100 by default) is reached at some point, which leads to that "too many clients" error.

By limiting the connection pool size to 2 in test properties I was able to fix that problem.

Protrusive

Answer 2

You should change the minimum-idle property, instead of maximum-pool-size

spring.datasource.hikari.minimum-idle=5

The default value for maximum-pool-size is 10, and minimum-idle defaults to the same value as max-pool-size. Changing it to a smaller value than max-pool-size did the trick for me.

My hunch is, the application tries to make a lot of connections to the db when executing tests as they run in parallel. These connections, however, are used for very short period and one connection can easily be reused by multiple test instances. By specifying the minimum-idle property to a smaller value than max-pool size, we are telling HikariCP to add additional connections only when the number of idle connections falls below that threshold. This prevents the connection pool from saturating, and consequently, encountering too many clients scenario.

However, HikariCP did recommend to not set this minimum-idle value, in order to maximise performance and responsiveness to spike demands. I faced my issue when trying to run the tests, hence I have changed the property solely for test env.

I've found Hikari's Github Page to be quite helpful. Over there they have listed all these parameters with brief explanations. Do take a look!

Incult

I set it to 1 spring.datasource.hikari.minimum-idle=1 – Martens

Answer 3

I tried several solutions including the one proposed by Egemen but they did not work.

For me the solution was to limit the connection pool size using this configuration in application-test.properties:

spring.datasource.maximumPoolSize=2

Pop

There is no such configuration property. At least not in Spring Boot 2. – Incult

Answer 4

None of these solutions worked for me. Application contexts are cached between tests to help speed up tests, so annotating functional test classes (i.e. the ones with @SpringBootTest) with @DirtiesContext did the trick for me.

When a test is marked with @DirtiesContext, Spring destroys and recreates the application context before or after the test is executed, depending on the options specified. This ensures that each test starts from a clean application state that is independent of other tests.

Darbie

Yes, it's the same situation, only @DirtiesContext helped. thx – Uric

Answer 5

I had this problem when integration testing a Spring Boot application and using Testcontainers. I noticed that I had a connection leak which caused this to happen.

IMO the best thing to do is to make sure that all opened connections are also being closed in your code.

Haigh

I'm using JPA.. any clues how I might go about finding this leak? – Dornick