28

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

enter image description here

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.

YogeshR
  • 1,606
  • 2
  • 22
  • 43

2 Answers2

2

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.

darioguarascio
  • 1,077
  • 11
  • 15
1

In my case, I am programming in Go... and got this error message in the system logs, and my app couldn't get to the DB, until I restarted the database server. I was building this exec in a new OS and a later release of postgresql.

In my case, I found that, simply, for every rows, err := db.Query() call I made, I was NOT doing the corresponding rows.Close() call! Luckily, in psql, if you do a "select * from pg_stat_activity where state='idle';" will show the exact queries being made, where the rows weren't closed. Any sneaky exit or break calls in-between? In my case, there was a simple solution!

user60381
  • 61
  • 3