spring boot postgres: FATAL: sorry, too many clients already

Question

I used postgres as a database in my spring boot application, when i run my application i get an error FATAL: sorry, too many clients already. I configured a connection pool in application.yml but i still have the same problem

spring:
  jpa:
    database: postgresql
    hibernate:
      ddl-auto: update
  datasource:
    url: jdbc:postgresql://localhost:5432/sp
    username: sp
    password: admin
    continueOnError: true
    platform: dev
    tomcat:
      maxIdle: 10
      max-active: 100
      max-wait: 10000
      validationQuery: select 1
      removeAbandoned: true
      removeAbandonedTimeout: 120
      logAbandoned: true
      testOnBorrow: true
      testOnConnect: true
      testWhileIdle: true

2018-06-13 09:29:47.311 [ERROR] [main] [logging.DirectJDKLog:181 ] Unable to create initial connections of pool. org.postgresql.util.PSQLException: FATAL: désolé, trop de clients sont déjà connectés at org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:443) at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:217) at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:51) at org.postgresql.jdbc.PgConnection.(PgConnection.java:215) at org.postgresql.Driver.makeConnection(Driver.java:404) at org.postgresql.Driver.connect(Driver.java:272) at org.apache.tomcat.jdbc.pool.PooledConnection.connectUsingDriver(PooledConnection.java:310) at org.apache.tomcat.jdbc.pool.PooledConnection.connect(PooledConnection.java:203) at org.apache.tomcat.jdbc.pool.ConnectionPool.createConnection(ConnectionPool.java:735) at org.apache.tomcat.jdbc.pool.ConnectionPool.borrowConnection(ConnectionPool.java:667) at org.apache.tomcat.jdbc.pool.ConnectionPool.init(ConnectionPool.java:482) at org.apache.tomcat.jdbc.pool.ConnectionPool.(ConnectionPool.java:154) at org.apache.tomcat.jdbc.pool.DataSourceProxy.pCreatePool(DataSourceProxy.java:118) at org.apache.tomcat.jdbc.pool.DataSourceProxy.createPool(DataSourceProxy.java:107) at org.apache.tomcat.jdbc.pool.DataSourceProxy.getConnection(DataSourceProxy.java:131)

Solution 1

Check the parameter max_connections in postgresql.conf file with total number of connection showing in application.yml

ALTER SYSTEM SET max_connections ='150';

and restart your instance using

select pg_reload_conf();

Note: Number of connection depends upon the active and idle connection, setting more number in connection will over-killing the process.

Solution 2

Working example

application.yml

version: '3.6'
services:
  db:
    image: postgres
    ports:
      - 54321:5432
    environment:
      - POSTGRES_PASSWORD=myPassword
      - POSTGRES_USER=sa
      - POSTGRES_DB=testdatabase
    volumes:
      - ./src/main/resources/pg-init-scripts:/docker-entrypoint-initdb.d

resources/pg-init-scripts/connections.sql

ALTER SYSTEM SET max_connections ='1000';
select pg_reload_conf();

resources/pg-init-scripts/init.sql

-- your stuff

All scripts within the init folders will be executed.

Comments

[Aymen Kanzari]

i don't have access to postgresql.conf file, i use pgAdmin, i execute SHOW max_connections; it shows me max_connections=100

[Pawan Sharma]

oh, increase the number of connection in postgresql.conf because as i have seen in your application.yml you have 101010 connection in idle,active and wait state..

[Aymen Kanzari]

is there an another solution without modifying postgresql.conf file ?

[Mark Rotteveel]

@PawanSharma where are you pulling that 101010 from? The max according to the config is 100 (max-active). So increasing the max_connections to a little over 100 should be enough (assuming a single instance, and maybe a small number of connections from other tools. Setting it to 20000 is overkill.

[Pawan Sharma]

@Mark Rotteveel That is just an example