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

Question

I am trying to connect to a Postgresql database, I am getting the following Error:

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

What does the error mean and how do I fix it?

My server.properties file is following:

serverPortData=9042
serverPortCommand=9078
trackConnectionURL=jdbc:postgresql://127.0.0.1:5432/vTrack?user=postgres password=postgres
dst=1
DatabaseName=vTrack
ServerName=127.0.0.1
User=postgres
Password=admin
MaxConnections=90
InitialConnections=80
PoolSize=100
MaxPoolSize=100
KeepAliveTime=100
TrackPoolSize=120
TrackMaxPoolSize=120
TrackKeepAliveTime=100
PortNumber=5432
Logging=1

Answer 1

An explanation of the following error:

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

Summary:

Your code opened up more than the allowed limit of connections to the postgresql database. It ran something like this: Connection conn = myconn.Open(); inside a loop, and forgot to run conn.close();. Just because your class is destroyed and garbage collected does not release the connection to the database. The quickest fix to this is to make sure you have the following code with whatever class that creates a connection:

protected void finalize() throws Throwable  
{  
    try { your_connection.close(); } 
    catch (SQLException e) { 
        e.printStackTrace();
    }
    super.finalize();  
}  

Place that code in any class where you create a Connection. Then when your class is garbage collected, your connection will be released.

Run this SQL to see postgresql max connections allowed:

show max_connections;

The default is 100. PostgreSQL on good hardware can support a few hundred connections at a time. If you want to have thousands, you should consider using connection pooling software to reduce the connection overhead.

Take a look at exactly who/what/when/where is holding open your connections:

SELECT * FROM pg_stat_activity;

The number of connections currently used is:

SELECT COUNT(*) from pg_stat_activity;

Debugging strategy

1. You could give different usernames/passwords to the programs that might not be releasing the connections to find out which one it is, and then look in pg_stat_activity to find out which one is not cleaning up after itself.

2. Do a full exception stack trace when the connections could not be created and follow the code back up to where you create a new Connection, make sure every code line where you create a connection ends with a connection.close();

How to set the max_connections higher:

max_connections in the postgresql.conf sets the maximum number of concurrent connections to the database server.

1. First find your postgresql.conf file
2. If you don't know where it is, query the database with the sql: SHOW config_file;
3. Mine is in: /var/lib/pgsql/data/postgresql.conf
4. Login as root and edit that file.
5. Search for the string: "max_connections".
6. You'll see a line that says max_connections=100.
7. Set that number bigger, restart postgresql database.

What's the maximum max_connections?

Use this query:

select min_val, max_val from pg_settings where name='max_connections';

I get the value 8388607 so in theory that's the most you are allowed to have, but then a runaway process can eat up thousands of connections, and surprise, your database is unresponsive until reboot. If you had a sensible max_connections like 100. The offending program would be denied a new connection and the database is safu.

Answer 2

We don't know what server.properties file is that, we neither know what SimocoPoolSize means (do you?)

Let's guess you are using some custom pool of database connections. Then, I guess the problem is that your pool is configured to open 100 or 120 connections, but you Postgresql server is configured to accept MaxConnections=90 . These seem conflictive settings. Try increasing MaxConnections=120.

But you should first understand your db layer infrastructure, know what pool are you using, if you really need so many open connections in the pool. And, specially, if you are gracefully returning the opened connections to the pool

Answer 3

The offending lines are the following:

MaxConnections=90
InitialConnections=80

You can increase the values to allow more connections.

Answer 4

No need to increase the MaxConnections & InitialConnections. Just close your connections after after doing your work. For example if you are creating connection:

try {
     connection = DriverManager.getConnection(
                    "jdbc:postgresql://127.0.0.1/"+dbname,user,pass);

   } catch (SQLException e) {
    e.printStackTrace();
    return;
}

After doing your work close connection:

try {
    connection.commit();
    connection.close();
} catch (SQLException e) {
    e.printStackTrace();
}

Answer 5

I had postgres and other apps up in docker. I was facing this problem when more than ten apps connected to postgres database. The solution was to increase postgres max_connection count. This is 100 by default. To increase this value, either find max_connection in the /var/lib/pgsql/data/postgresql.conf file and edit it. Another way is to add and run the docker-compose.yml document as follows.

version: '3'

services:
  taxi-postgresql:
    container_name: my-postgresql
    image: postgres:13.3
    volumes:
      - ./postgres-volume:/var/lib/postgresql/data
    environment:
      - POSTGRES_DB=taxi-postgresql
      - POSTGRES_USER=user
      - POSTGRES_PASSWORD=user
      - POSTGRES_HOST_AUTH_METHOD=trust
    command: postgres -c 'max_connections=1000'
    ports:
      - 127.0.0.1:5432:5432

Answer 6

You need to close all your connexions for example: If you make an INSERT INTO statement you need to close the statement and your connexion in this way:

statement.close();
Connexion.close():

And if you make a SELECT statement you need to close the statement, the connexion and the resultset in this way:

resultset.close();
statement.close();
Connexion.close();

I did this and it worked

Answer 7

The same error appears in our microservices deployment, and it is solved by increasing concurrent connections in the Postgresql container:

num_init_children