Thread: pgqueryspy

pgqueryspy

From
Christopher Kings-Lynne
Date:
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."




Re: pgqueryspy

From
David Fetter
Date:
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!

Re: pgqueryspy

From
Neil Conway
Date:
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



Re: pgqueryspy

From
Jeff Trout
Date:
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/