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

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>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
>
>