On Sep 27, 2016, at 10:55 AM, Melvin Davidson <melvin6...@gmail.com> wrote: > > > > On Tue, Sep 27, 2016 at 2:46 PM, Israel Brewster <isr...@ravnalaska.net > <mailto:isr...@ravnalaska.net>> wrote: >> >I'm still curious as to how I can track concurrent connections, ... >> >> Have you considered enabling the following in postgresql.conf? >> log_connections=on >> log_disconnections=on >> >> It will put a bit of a bloat in you postgres log, but it will all allow you >> extract connects/disconnects over a time range. That should allow you >> to determine concurrent connections during that that. > > I do have those on, and I could write a parser that scans through the logs > counting connections and disconnections to give a number of current > connections at any given time. Trying to make it operate "in real time" would > be interesting, though, as PG logs into different files by day-of-the-week > (at least, with the settings I have), rather than into a single file that > gets rotated out. I was kind of hoping such a tool, such as pgbadger (which, > unfortunately, only seems to track connections per second and not consecutive > connections), already existed, or that there was some way to have the > database itself track this metric. If not, well, I guess that's another > project :) > > ----------------------------------------------- > Israel Brewster > Systems Analyst II > Ravn Alaska > 5245 Airport Industrial Rd > Fairbanks, AK 99709 > (907) 450-7293 <tel:%28907%29%20450-7293> > ----------------------------------------------- > >> >> >> Melvin Davidson >> I reserve the right to fantasize. Whether or not you >> wish to share my fantasy is entirely up to you. > > > Does this help? > > --Total concurrent connections > SELECT COUNT(*) > FROM pg_stat_activity; > > --concurrent connections by user > SELECT usename, > count(*) > FROM pg_stat_activity > GROUP BY 1 > ORDER BY 1; > > --concurrent connections by database > SELECT datname, > usename, > count(*) > FROM pg_stat_activity > GROUP BY 1, 2 > ORDER BY 1, 2; > > -- database connections by user > SELECT usename, > datname, > count(*) > FROM pg_stat_activity > GROUP BY 1, 2 > ORDER BY 1, 2; > > -- > Melvin Davidson > I reserve the right to fantasize. Whether or not you > wish to share my fantasy is entirely up to you.
That helps for one-time stat collection, but as I mentioned in my original message, since connections may not last long, I could be getting close to, or even hitting, my connection limit while still getting values back from those that show plenty of connections remaining, depending on how often I checked. I guess what would be ideal in my mind is that whenever Postgresql logged an opened/closed connection, it also looked the *total* number of open connections at that time. I don't think that's possible, however :-) ----------------------------------------------- Israel Brewster Systems Analyst II Ravn Alaska 5245 Airport Industrial Rd Fairbanks, AK 99709 (907) 450-7293 -----------------------------------------------
