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.sha...@orkash.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 <guilla...@lelarge.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 >> >> > >
