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.

I accept NO RESPONSIBILITY FOR YOUR USE OF THIS CODE. It could toss your database/network/server/grandma out the window. YMMV, caveat emptor, etc, etc.

I am putting this code out here for two reasons:

1. Like me, Google has not led you to any other solution to sniff packets of Postgres queries without making the database log (which requires a database restart).
2. As soon as I post this code, someone is going to come along and tell me that I could do it in a single command line, and that my 2 hours building this were for nothing. I say bring it on, prove me wrong. You know why? a) my C skills are non-existent, and b) I wish I knew how to do this on the command line

UPDATE: Forgot to give credit for the help. Thanks to Kris, as well as the libpcap packet capture tutorial.

Popularity: 61%