Postgresql how to see how many db connections

Databases
1

Hello all,

I have a problem with at least one of my apps. There are a group of 6-8 apps that all connect to the local PostgreSQL database. Over time, at least one is connecting to the db but not closing the connection. These instances are adding up to the point where the Postgre has reached its 100 limit. I understand that this 100 can be increased, but since there is an obvious issue with at least one app, I’d rather track that down and correct it.

Using this SQL Code:
select * from
(select count(*) used from pg_stat_activity) q1,
(select setting::int res_for_super from pg_settings where name=$$superuser_reserved_connections$$) q2,
(select setting::int max_conn from pg_settings where name=$$max_connections$$) q3;

image

Using this code in the Terminal I get this:

image
image1237×768 179 KB

But none of these shows which app is creating the connection and not releasing. They are all using the same Login ID and Password for Postgres, which is making it harder. But I was hoping that someone may have some other ideas.

Thanks,
Tim

2

Is it possible to stop the apps one at a time while checking the connections?

3

Not really since this is a production machine in the UK, and I cannot replicate here.
Tim

4

This idea to help ID the app came from NPalardy

Do you set the AppName property of the connections ?
If so then a simple query like this should show you whats up