Thread: pgqueryspy
This seems new: http://dotnot.org/blog/archives/2006/02/22/spying-on-postgresql/ "You have tuned your PostgreSQL database, and you, of course, turned off query logging, but a problem has cropped up. How can you see the activity on your database at any slice of time? Like me, you have probably turned on command stats to view pg_stat_activity, but that gives you only moment in time. You could use ethereal to capture the Postgres queriy packets, but then what? What if you don’t have X installed? tcpdump you say? What do you do with the dump? Enter pgqueryspy. Simply type pgqueryspy 2000 db0 to capture the next 2000 packets to the server named ‘db0′. pgqueryspy then prints out the queries that it intercepts in the packet stream for your consumption on the command line. It even tosses those pesky “BEGIN” and “END” queries for you. Not too terribly invasive, no load on your database, and you find out NOW what is happening with your database. Code is available here: pgqueryspy.c."
On Tue, Jun 27, 2006 at 04:47:24PM +0800, Christopher Kings-Lynne wrote: > This seems new: > > http://dotnot.org/blog/archives/2006/02/22/spying-on-postgresql/ Have you used it? Does it work? Is it as non-invasive as the author claims? Cheers, D? -- David Fetter <david@fetter.org> http://fetter.org/ phone: +1 415 235 3778 AIM: dfetter666 Skype: davidfetter Remember to vote!
On Tue, 2006-06-27 at 10:19 -0700, David Fetter wrote: > Is it as non-invasive as the author claims? Well, libpcap is libpcap... From looking at the source, it is kind of a hack (e.g. it will only work for queries submitted via the simple query protocol), but its a neat idea. I believe Abhijit Menon-Sen wrote an Ethereal plugin to let it grok the FEBE protocol, which would let you do similar things. -Neil
On Jun 27, 2006, at 1:19 PM, David Fetter wrote: > On Tue, Jun 27, 2006 at 04:47:24PM +0800, Christopher Kings-Lynne > wrote: >> This seems new: >> >> http://dotnot.org/blog/archives/2006/02/22/spying-on-postgresql/ > > Have you used it? Does it work? Is it as non-invasive as the author > claims? > I wrote a tool a year or two ago that does this sort of thing. Uses libpcap to find PG packets and decodes v2 and v3 protocols. http://pgfoundry.org/projects/pgspy Not the best code, but it worked super good for my uses. Can also provide TPS information as well. -- Jeff Trout <jeff@jefftrout.com> http://www.dellsmartexitin.com/ http://www.stuarthamm.net/