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 to fix it?

My server.properties following file:

 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

We do not know what the server.properties file is, we do not know what SimocoPoolSize means (you?)

Suppose you are using some sort of custom database connection pool. Then, I think the problem is that your pool is configured to open 100 or 120 connections, but the Postgresql server is configured to accept MaxConnections=90 . These are conflicting settings. Try increasing MaxConnections=120 .

But first you need to understand your db level infrastructure, know which pool you are using, if you really need so many open connections in the pool. And especially if you gracefully return open pool connections

Answer 2

Explanation of the following error:

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

Summary:

You have opened more than the allowable limit for database connections. You ran something like this: Connection conn = myconn.Open(); inside the loop and forgot to run conn.close(); . Just because your class is destroyed and garbage collection does not release the database connection. The quickest fix for this is to make sure you have the following code with any class that creates the connection:

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

Put this code in any class where you create the connection. Then, when your class collects garbage, your connection will be released.

Run this SQL to allow valid postgresql max connections:

 show max_connections; 

The default is 100. PostgreSQL with good hardware can support several hundred connections at once. If you want to have thousands, you should consider using pooling software to reduce connection overhead.

Take a look at who / what / when / where holds your connections:

 SELECT * FROM pg_stat_activity; 

Number of currently used compounds:

 SELECT COUNT(*) from pg_stat_activity; 

Debugging strategy

• You can specify different usernames / passwords for programs that may not release connections to find out which one is there, and then look in pg_stat_activity to see which one is not cleared after itself.

• Perform a full trace of the exception stack when connections cannot be created, and follow the code where you create the new Connection , make sure that each line of code in which you create the connection ends with connection.close();

How to set max_connections above:

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

• First find the postgresql.conf file
• If you do not know where it is, query the database using sql: SHOW config_file;
• My is in: /var/lib/pgsql/data/postgresql.conf
• Log in as root and edit this file.
• Search for the string: "max_connections".
• You will see a line with the text max_connections=100 .
• Set this number more, check the limit for your version of postgresql.
• Restart the postgresql database for the change to take effect.

What are the maximum max_connections?

Use this query:

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

I get the value 8388607 , theoretically, what you are most allowed to have, but then the escape process can eat thousands of connections and surprise your database is not responsible for rebooting. If you had reasonable max_connections, such as 100. The abusive program will be denied a new connection.

Answer 3

No need to increase MaxConnections and InitialConnections. Just close your connections after doing your work. For example, if you create a connection:

 try { connection = DriverManager.getConnection( "jdbc:postgresql://127.0.0.1/"+dbname,user,pass); } catch (SQLException e) { e.printStackTrace(); return; } 

After completing your work, follow these steps:

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

Answer 4

The offensive lines are as follows:

 MaxConnections=90 InitialConnections=80 

You can increase the values ​​to make more connections.

Answer 5

You need to close all your connections, for example: If you make an INSERT INTO statement, you need to close the operator and your connection in this way:

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

And if you make a SELECT statement, you need to close the statement, join and result set as follows:

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

I did it and it worked

Answer 6

1. Check current configuration

Log in to PostgreSQL using the psql command,

  postgres=# show max_connections; max_connections ----------------- 100 (1 row) 

2. Open the PostgreSQL configuration file

 vi /var/lib/pgsql/data/postgresql.conf 

3. Change the following default configurations

  max_connections = 100 # (change requires restart) # Note: Increasing max_connections costs ~400 bytes of shared memory per .... shared_buffers = 400MB # min 128kB # (change requires restart) 

4. Restart the PostgreSQL server to apply the changes.

  $:service postgresql restart Stopping postgresql service: [ OK ] Starting postgresql service: [ OK ] 

Link

http://www.devdummy.com/2017/10/postgresql-psqlexception-fatal-sorry.html