So I recently had a problem, where postgresql would run out of max concurrent connections .. and I wasn'T sure what caused it.
So to find out what the problem was I wanted to know what connections were open. After a short search I found the pg_stat_activity table.
of course most info in there is not needed for my case (it has database id, name, pid, usename, application_name, client_addr, state, ...)
but for me this was all I needed:
postgres=# select count(*), datname,state,pid from pg_stat_activity group by datname, state, pid order by datname;
count | datname | state | pid
-------+------------+---------------------+-------
1 | dbmail | idle | 30092
1 | dbmail | idle | 30095
..
or shorter just the connections by state and db
postgres=# select count(*), datname,state from pg_stat_activity group by datname, state order by datname;
count | datname | state
-------+----------+---------------------
15 | dbmail | idle
..
of course one could go into more detail, but this made me realize that i could limit some processes that used a lot of connections, but are not heavy load. Really simple once you know where to look - as usual :)
Share on Twitter Share on Facebook
Comments
There are currently no comments
New Comment