SoFunction
Updated on 2025-04-08

The solution to too many PostgreSQL connections is reported: too many clients already

1. Problem description

When using Navicat to connect to PostgreSQL database, I suddenly encountered an error: "FATAL: sorry, too many clients already". This error prompt indicates that the number of database connections has reached the upper limit and a new connection cannot be created. To solve this problem, I took a series of steps of query and configuration adjustment, starting from both database and program connection pool.

Database version and program information:

  • Database version: PostgreSQL 11.5
CheckPostgreSQL Version
SELECT version();
  • Programming Languages ​​and Frameworks: Java and Spring Boot

2. Analyze the problem

1. Database level

1.1 Check the number of connections and connection status

By querying the number of database connections and connection status, you can learn about the number of active connections in the database and the details of each connection, including the database name, process ID (PID), application name, and connection status.

1.2 Check the connection timeout configuration

Check the timeout configuration of idle connections in the database to ensure that the connection pool is set reasonably.

2. Procedural aspects

By viewing the database connection pool configuration in the program, especially when using Hikari connection pool, confirm whether the connection pool settings are reasonable and whether the parameters of the connection pool need to be adjusted.

3. Steps to solve the problem

  1. Query the number of database connections and connection status:Use to execute relevant SQL queries to understand the connection status of the database.

  2. Query the connection timeout configuration:Through SQL query, check the timeout configuration of idle connections in the database.

  3. Confirm the program connection pool configuration:Check the connection pool configuration used in the program, especially the parameters of the Hikari connection pool, and adjust the configuration to ensure that the connection pool effectively manages connections.

  4. Modify the idle connection timeout time (if required):If necessary, modify the timeout time of the idle connection in the database through the SQL command.

4. Query the number of database connections

1. Query the available connections in the database

First, I use the following SQL query statement to view the difference between the number of available connections in the current database and the actual number of connections:

SELECT max_conn - now_conn AS resi_conn
FROM (
    SELECT setting::int8 AS max_conn, (SELECT count(*) FROM pg_stat_activity) AS now_conn
    FROM pg_settings WHERE name = 'max_connections'
) t;

This helps to understand the connection status of the database and whether the connection limit needs to be adjusted.

2. Query the database connection status

To get a more detailed look at the current connection status, I performed the following query showing the database name, process ID (PID), application name, and connection status for each connection:

SELECT datname, pid, application_name, state
FROM pg_stat_activity;

This provides details about active connections in the connection pool, helping to locate issues that may cause excessive connections.

3. Group query connection number

With the following query, I counted the number of connections per database, which helped to find out if a particular database occupies too many connections:

SELECT datname, count(0)
FROM pg_stat_activity
GROUP BY datname;

4. Close the connection according to PID

--according toPIDClose the connection
select pg_terminate_backend(pid) from pg_stat_activity;

5. Idle connection timeout

Modify the idle connection timeout (if required): If necessary, modify the timeout time of the idle connection in the database through the SQL command.

-- Sets the timeout time for controlling the sessions that are idle in the transaction
ALTER SYSTEM SET idle_in_transaction_session_timeout = 30000;

-- Query controls the timeout time of a session that is idle in a transaction
SHOW idle_in_transaction_session_timeout;

-- Set the timeout time to control idle sessions
ALTER SYSTEM SET idle_session_timeout = '300s';

-- Query the idle session timeout time
SHOW idle_session_timeout;

5. Optimize the program connection pool configuration

Check the connection pool configuration used in the program, especially when using Hikari connection pool. Here are some recommended configuration items:

-pool-size=10
-timeout=30000
-idle=5
-lifetime=1800000
-timeout=600000

Detailed explanation of parameter configuration:

  • -pool-size: Sets the maximum number of connections allowed by the connection pool. It is recommended to increase or decrease moderately according to actual needs to ensure sufficient but not too much.
  • -timeout: Connection timeout time, defines how long the connection will be released after the idle state to ensure the effective utilization of the connection resources.
  • -idle: Minimum number of idle connections, ensuring that the connection pool always maintains a certain number of active connections, reducing the overhead of connection creation and destruction.
  • -lifetime: The connection life cycle defines the longest time a connection can exist before it is released, preventing the connection from accumulating for a long time and causing waste of resources.
  • -timeout: The idle connection timeout time, the maximum time the connection is idle in the pool, after which it will be released.

These parameters can be adjusted according to actual needs to ensure that the connection pool can better manage and release connections.

Through the above steps, I successfully solved the problem of excessive connections and optimized database connection management. I hope these detailed ideas and steps will be helpful to you in solving similar problems.

The above is the detailed content of the solution to the too many clients already. For more information about the errors caused by excessive PostgreSQL connections, please pay attention to my other related articles!