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)

Try to find the underlying cause - e.g. set logging level of connection pooling framework to DEBUG — Strinder
– Strinder, Commented Jun 3, 2022 at 10:42

Pawan Sharma · Accepted Answer · 2018-06-13 15:17:34Z

11

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.

edited Jun 13, 2018 at 15:17

answered Jun 13, 2018 at 9:19

Pawan Sharma

3193 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Aymen Kanzari Over a year ago

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 Over a year ago

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 Over a year ago

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

Mark Rotteveel Over a year ago

@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 Over a year ago

@Mark Rotteveel That is just an example

|

Marcel · Accepted Answer · 2019-07-30 07:33:14Z

1

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.

answered Jul 30, 2019 at 7:33

Marcel

78010 silver badges14 bronze badges

Comments

Gunce Su · Accepted Answer · 2020-11-26 12:23:20Z

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.

Collectives™ on Stack Overflow

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

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related