Re: How to track number of connections and hosts to Postgres cluster

pg_stat_activity keeps track of all this information.

select * from pg_stat_activity where datname='databasename';

Venkat Balaji wrote:
> Thanks Guillaume !!
>
> But, if put log_connections to on and log_disconnections to on
> wouldn't the Postgres be logging in lot of data ?
>
> Will this not be IO intensive ? I understand that this is the best
> way, but, would want to know if there is an other way to reduce IO (
> may be through queries to catalog tables ).
>
> Thanks
> Venkat
>
> On Wed, Aug 24, 2011 at 1:19 PM, Guillaume Lelarge
> <guillaume(at)lelarge(dot)info <mailto:guillaume(at)lelarge(dot)info>> wrote:
>
> On Wed, 2011-08-24 at 13:05 +0530, Venkat Balaji wrote:
> > Hello Everyone,
> >
> > I am working on an alert script to track the number of
> connections with the
> > host IPs to the Postgres cluster.
> >
> > 1. I need all the host IPs making a connection to Postgres
> Cluster (even for
> > a fraction of second).
>
> You should set log_connections to on.
>
> > 2. I would also want to track number of IDLE connections, IDLE IN
> > TRANSACTION connections and length of the connections as well.
> >
>
> IDLE and IDLE in transactions are the kind of informations you get in
> pg_stat_activity.
>
> Length of connections, you can get it with log_disconnections.
>
> > I would be making use of pg_stat_activity and also thought of
> enabling
> > logging the host ips in the db server log files which seems to
> be expensive
> > for me (in terms of IO and logfile size).
> >
>
> Using pg_stat_activity won't get you really small connections. You
> need
> log_connections for that, and log_disconnections for the duration of
> connections. So you'll have to work on a tool that could get some
> informations with queries on pg_stat_activity, and that could read
> PostgreSQL log files.
>
>
> --
> Guillaume
> http://blog.guillaume.lelarge.info
> http://www.dalibo.com
>
>