How to fix Postgres error remaining connection slots are reserved for non-replication superuser connections

09 June,2021 by Jack Vamvas

Question: I'm getting an error message on the application connecting to a PostgreSQL database  Caused by: org.postgresql.util.PSQLException: FATAL: remaining connection slots are reserved for non-replication superuser connections

How can I troubleshoot? & fix?

 

Answer:    First step is to check the max_connections setting

SHOW max_connections;

Second step - check the pg_stat_activity   view . This view exists in the pg_catalog schema

SELECT count(*) FROM pg_stat_activity;

 

Compare the figures from max_connections & pg_stat_activity , and you'll probably find the numbers are very close. 

Depending on the setup - e..g if the PostgreSQL instance is shared - you may need to analyse which applications are consuming the connections and work with the application owners to manage the connections, using various techniques including connection pooling. 

 

PostgreSQL cheatsheet

 

If you want to breakdown the connections per database - use this query 

SELECT datname, numbackends FROM pg_stat_database;

 

The other method is to increase the max_connections setting . The max_connections is managed through the postgresql config file. 

Find the config_file location through SHOW config_file

Don't just blindly change the setting . It may be necessary to review the server capacity and  confirm the planned connections can be supported 

 

 


Author: Jack Vamvas (http://www.dba-ninja.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to fix Postgres error remaining connection slots are reserved for non-replication superuser connections


dba-ninja.com