On Wed, Aug 24, 2011 at 9:33 AM, Greg Smith <greg@xxxxxxxxxxxxxxx> wrote:
On 08/24/2011 07:07 AM, Venkat Balaji wrote:Your requirements include: " I need all the host IPs making a connection to Postgres Cluster (even for a fraction of second)."
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 ).
The only way to do this is to log every connection. Any other approach for grabbing the data, such as looking at pg_stat_activity, will sometimes miss one.
If you're willing to lose a connection sometimes, a cron job that polls pg_stat_activity and saves a summary of what it finds will normally use less resources. But connections that start and end between runs will be missed.
I suppose you could use tcpdump on a separate system with a mirrored switch port and have it log TCP SYN and FIN packets on port 5432 to your database server only. Keeps all I/O off your database server.
tcpdump -w port5423.log -n "tcp and port 5432 and tcp[tcpflags] & (tcp-syn|tcp-fin) != 0 and host IP"
HTH.
Greg
