Postgresql | remaining connection slots are reserved for non-replication superuser connections

Question

I am getting an error "remaining connection slots are reserved for non-replication superuser connections" at one of PostgreSQL instances.

However, when I run below query from superuser to check available connections, I found that enough connections are available. But still getting the same error.

select max_conn,used,res_for_super,max_conn-used-res_for_super 
res_for_normal 
from 
  (select count(*) used from pg_stat_activity) t1,
  (select setting::int res_for_super from pg_settings where 
name='superuser_reserved_connections') t2,
  (select setting::int max_conn from pg_settings where name='max_connections') t3

Output

I searched this error and everyone is suggesting to increase the max connections like below link. Heroku "psql: FATAL: remaining connection slots are reserved for non-replication superuser connections"

EDIT

I restarted the server and after some time used connections were almost 210 but i was able to connect to the server from a normal user.

Answer 1

Might not be a direct solution to your problem, but I recommend using middlewares like pgbouncer. It helps keeping a lower, fixed number of open connections to the db server. Your client would connect to pgbouncer and pgbouncer would internally pick one of its already opened connection to use for your client's queries. If the number of clients exceed the amount of possible connections, clients are queued till one is available, therefore allowing some breathing room in situations of high traffic, while keeping the db server under tolerable load.