Reputation: 4999
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
Upvotes: 208
Views: 468957
Reputation: 2250
The same error appears in our microservices deployment, and it is solved by increasing concurrent connections in the Postgresql container:
num_init_children
Upvotes: 0
Reputation: 154083
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
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.
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.
SHOW config_file;
/var/lib/pgsql/data/postgresql.conf
max_connections=100
.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.
Upvotes: 308
Reputation: 251
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
Upvotes: 18
Reputation: 51
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
Upvotes: 5
Reputation: 103
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();
}
Upvotes: 7
Reputation: 76006
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
Upvotes: 66
Reputation: 81472
The offending lines are the following:
MaxConnections=90
InitialConnections=80
You can increase the values to allow more connections.
Upvotes: 7