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.

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