Thread: "sniffing" postgres queries
Hi, I am having real trouble with a project that is a little out of my league and I just can't find out how to find which table the app is getting its values from (it's a long story...). I know what values I am looking for, and would like to log everything (even if only for one call of a web page...) that comes in and everything that goes out. Is this possible at the db level? Even debug5 doesn't seem to give me the result of a query... If it is only possible at the driver level (I did something vaguely similar with the odbc driver I think), then any pointers for a jdbc2 on 8.1.4? Thanks Anton
You might be able to get enough information looking at the raw frontend/backend protocol exchange (http://www.postgresql.org/docs/ 8.2/static/protocol.html) using something like tcpflow or Ethereal. On Mar 20, 2007, at 5:45 PM, Anton Melser wrote: > I am having real trouble with a project that is a little out of my > league and I just can't find out how to find which table the app is > getting its values from (it's a long story...). I know what values I > am looking for, and would like to log everything (even if only for one > call of a web page...) that comes in and everything that goes out. Is > this possible at the db level? Even debug5 doesn't seem to give me the > result of a query... If it is only possible at the driver level (I did > something vaguely similar with the odbc driver I think), then any > pointers for a jdbc2 on 8.1.4? John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL
"Anton Melser" <melser.anton@gmail.com> writes:
> I am having real trouble with a project that is a little out of my
> league and I just can't find out how to find which table the app is
> getting its values from (it's a long story...). I know what values I
> am looking for, and would like to log everything (even if only for one
> call of a web page...) that comes in and everything that goes out. Is
> this possible at the db level? Even debug5 doesn't seem to give me the
> result of a query... If it is only possible at the driver level (I did
> something vaguely similar with the odbc driver I think), then any
> pointers for a jdbc2 on 8.1.4?
Doesn't log_statement = ALL get the job done for you? If you need to
correlate queries with results then I guess not; there is no provision
for logging query results. The nearby suggestion to use a packet
sniffer is probably your only recourse for that. I'd suggest that you
may also need to set the driver to use protocol version 2, so that all
the queries come in in plain text --- otherwise you'll have some
difficulty interpreting the packet streams ...
regards, tom lane
On 21/03/07, Tom Lane <tgl@sss.pgh.pa.us> wrote: > "Anton Melser" <melser.anton@gmail.com> writes: > > I am having real trouble with a project that is a little out of my > > league and I just can't find out how to find which table the app is > > getting its values from (it's a long story...). I know what values I > > am looking for, and would like to log everything (even if only for one > > call of a web page...) that comes in and everything that goes out. Is > > this possible at the db level? Even debug5 doesn't seem to give me the > > result of a query... If it is only possible at the driver level (I did > > something vaguely similar with the odbc driver I think), then any > > pointers for a jdbc2 on 8.1.4? > > Doesn't log_statement = ALL get the job done for you? If you need to > correlate queries with results then I guess not; there is no provision > for logging query results. The nearby suggestion to use a packet > sniffer is probably your only recourse for that. I'd suggest that you > may also need to set the driver to use protocol version 2, so that all > the queries come in in plain text --- otherwise you'll have some > difficulty interpreting the packet streams ... Thanks both for your replies. I remember now that it was indeed ethereal that we used last time (for the odbc problem). I will probably try and avoid this solution for the moment... Thanks for your help. Cheers Anton