Connection Pool Timeout - Pool is full error. App is not live - should have no activity

Question

So this has been baffling me.

I setup a connection pool for use on production. It maxes out at 47 connections.

Inevitably, after awhile, the pool maxes out. And never lets go of any of the connections.

When I investigate, via insights, I see that the DB that the pool connects to shows almost no activity at all, only using a connection here or there. It never maxes out.

Yet, when I look at the connection pool tab, I am seeing 0 of 47 connections available, I can’t connect locally, and I can’t connect via staging. Only prod seems to work. I imagine caching has something to do with that.

When I look at queries, I am seeing a nonstop, several a second stream of queries that don’t make sense to me. A steady stream of connection unauthorized, connection timeout errors. Over and over again. To users and dbs that aren’t hooked up to the pool, and should not be public. I only whitelist 3 ips - my blue deploy server, my green deploy server, and my home ip for local development.

What’s going on?

rosspatton · Answer

Some additional info.

I took down all instances of the site that were running, including locally. And then waited a few minutes. Still 0 of 47 available connections.

Under the logs and queries tab, I am STILL seeing constant failed queries. Here is what they look like:

06-12-2020 11:36:30PM 	postgresql-11.service 	[11-1] user=postgres,db=prod,app=do-stats,client=[local] LOG: disconnection: session time: 0:00:00.029 user=postgres database=prod host=[local]

06-12-2020 11:36:30PM 	postgresql-11.service 	[10-1] user=postgres,db=prod,app=[unknown],client=[local] LOG: connection authorized: user=postgres database=prod

06-12-2020 11:36:30PM 	postgresql-11.service 	[9-1] user=[unknown],db=[unknown],app=[unknown],client=[local] LOG: connection received: host=[local]

06-12-2020 11:36:30PM 	postgresql-11.service 	[11-1] user=postgres,db=dev,app=do-stats,client=[local] LOG: disconnection: session time: 0:00:00.021 user=postgres database=dev host=[local]

06-12-2020 11:36:30PM 	postgresql-11.service 	[10-1] user=postgres,db=dev,app=[unknown],client=[local] LOG: connection authorized: user=postgres database=dev

06-12-2020 11:36:30PM 	postgresql-11.service 	[11-1] user=postgres,db=defaultdb,app=do-stats,client=[local] LOG: disconnection: session time: 0:00:00.029 user=postgres database=defaultdb host=[local]

None of my connections are setup to use the default postgres user, nor am I using defaultdb. The prod db is the one that has the connection pool in front of it - and since all versions of the site are down, shouldn’t be getting any queries at all.

Related

Question

Connection Pool Timeout - Pool is full error. App is not live - should have no activity

Related