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

Question

An explanation of the following error:

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

Summary:

You opened up more than the allowed limit of connections to the database. You ran something like this: Connection conn = myconn.Open(); inside of 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, check the limit for your postgresql version.
8. Restart the postgresql database for the changes to take effect.

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

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

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();
}

The offending lines are the following:

MaxConnections=90
InitialConnections=80

You can increase the values to allow more connections.