Related

Product Now Available: Managed PostgreSQL Databases Product
Migration of data from PostgreSQL 9.1.13 to PostgreSQL 9.3 Question Question
How to give root files permision to postgres Question Question

Question

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

Posted June 13, 2020 318 views
PostgreSQL

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?

Add a comment
rosspatton
By:
rosspatton
Add a comment

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

×
1 answer
rosspatton June 13, 2020

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.

Reply Report
Submit an Answer

Related

Looking for something else?

Ask a new question Search for more help