How to resolve Data Retrieval Error in PostgreSQL due to "Too Many Connections"

Issue

In some cases, there may occur data retrieval error for a live connection made to the Postgres database, both in preview and reports with the maximum connection limit reached message.

Cause

The error “53300: too many connections for role,” occurs when the PostgreSQL server reaches the maximum connection limit, causing data retrieval issues.

Solution

1. First, verify the issue is related to the PostgreSQL connections by checking your error logs.

2. Confirm if you have reached the maximum connections allowed, either by checking with your PostgreSQL server or using an external application.

3. If you still experience the error and have not reached the maximum connections, investigate whether any other external applications use your PostgreSQL server.

4. If the issue occurs again, execute the following command to check all open connections and the client related to the connection:

select * from pg_stat_activity

5. If increasing the connection limit resolves the issue, adjust the PostgreSQL configuration file to allow for more connections. Locate the postgresql.conf file, which is usually found in the data directory of your PostgreSQL installation.
6. Open the postgresql.conf file in a text editor and search for the max_connections parameter. Increase the value to the desired number of connections. For example:

   max_connections = 200
   

7. Save the changes to the postgresql.conf file and restart the PostgreSQL server for the changes to take effect.

References

• PostgreSQL Documentation: 9.5 Configuration Settings
• PostgreSQL Documentation: 11.0 Runtime Configuration - Connections