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

But, the information vanishes if the application logs off.

I am looking for an alternative to track the total amount of the connections
with the host IPs through a Cron job.

What could be the frequency of cron ?

I know the best is using log_connections and log_disconnections parameters,
but, information logged would be too high and is also IO intensive.

Thanks
Venkat

On Wed, Aug 24, 2011 at 4:39 PM, Adarsh Sharma <adarsh(dot)sharma(at)orkash(dot)com>wrote:

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