postgresqlspring-bootdatasource

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

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?
Solution

  • 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.