I have an issue with a server (v10) that’s seeing increasing connections until it’s maxxed-out.
max_connections for my 4-core server is set to 12.
I’ve installed pg_stat_activity and pg_stat_statements.
Do you see anything in pg_stat_activity that stays idle for a while and then *does* disappear on its own? Perhaps some types of connections are doing client side/application stuff before telling the DB to close the connection.
pg_stat_activity has shown a handful of idle queries that are quite simple. I’ve tested those queries and they seem fine on a workstation client. I’m assuming these queries somehow don’t finish and leave the connection open, but I could be wrong. All of this is new to me.
Idle means the query finished and that was the last query run. It isn't active or waiting on another process, that connection is open by idle.
I am running no connection pool tool of any sort, and I’ve never used one.
It sounds like a good time to set one up.
How can I figure this out so the connections remain within the max_connections limit, and connections are not dropped?
I would increase the limit directly, or with a pooler and research which connections are behaving, and which are taking too long to close or not closing at all. You could set up a process to snapshot pg_stat_activity every minute or 5 and trace which pids are terminating properly, and/or make logging very verbose.
