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)

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

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

Answer 3

I suddenly started getting the same error in my django application during development. First thought was that django code was opening a connection somewhere but then not closing it. However, it turns out the problem was with pgadmin4. Anytime I view a table in pgadmin (just a simple "view > all rows"), the connection number is increased by 1.

To figure this out, I viewed some tables in pgadmin and also made some requests to my development server using the test website and after every step I checked the number of connections via

SELECT COUNT(*) from pg_stat_activity;

To close the connections coming from pgadmin, I had to disconnect pgadmin from server and connect again. So, before changing max_connections value, you may want to figure out which app/program is opening and not closing the connections. In my case, it turned out it was not necessary to change the value nor implement a pooling mechanism, just occasionally restart pgadmin during development.