Stevus.com

TCPDUMP to Postgres

April 30, 2019

Today I was trying to debug what my OrmLite queries were doing, but didn’t know how to print out the equivalent SQL being generated.

I needed to find a way to see this in real time as the queries were coming through.

After some quick consultation with a colleague, I needed to go into a little more depth on the research, so I turned to Google.

The recommendation I got was to tcpdump to postgresql, but there wasn’t time to elaborate further as they were busy with some other pressing issues. I decided to go out and search myself, armed with the basic knowledge of the basics of what to look for.

My first search was

tcpdump print data packet as stringhttps://superuser.com/questions/766887/tcpdump-how-to-get-grepable-output

This was sort of helpful, but I don’t know how to read HEX/Ascii. I needed a little bit more textual output. The search turned into

tcpdump traffic to postgresql see queryhttps://community.liferay.com/blogs/-/blogs/how-to-catch-mysql-sql-with-tcpdump-in-linux

which seemed so much more helpful. I had to read some of the comments, but eventually came to the conclusion to use the following:

sudo tcpdump -i any -s 0 -l -w - dst port postgresql | strings | perl -e '
while(<>) { chomp; next if /^[^ ]+[ ]*$/;
    if(/^(SELECT|UPDATE|DELETE|INSERT|SET|COMMIT|ROLLBACK|CREATE|DROP|ALTER|CALL)/i)
    {
        if (defined $q) { print "$q\n"; }
        $q=$_;
    } else {
        $_ =~ s/^[ \t]+//; $q.=" $_";
    }
}'

which produced:

SELECT min(checkpointnumber) from commits
SELECT max(checkpointnumber) from commits DISCARD ALL
SELECT "bucket_id", "stream_id", "stream_revision"  FROM "todo_projection" WHERE (1=0) OR ((("bucket_id" = $1) AND ("stream_id" = $2)) AND (("stream_revision" >= $3) OR ("stream_revision" < $4))) OR ((("bucket_id" = $5) AND ("stream_id" = $6)) AND (("stream_revision" >= $7) OR ("stream_revision" < $8))) OR ((("bucket_id" = $9) AND ("stream_id" = $10)) AND (("stream_revision" >= $11) OR ("stream_revision" < $12))) OR ((("bucket_id" = $13) AND ("stream_id" = $14)) AND (("stream_revision" >= $15) OR ("stream_revision" < $16))) OR ((("bucket_id" = $17) AND ("stream_id" = $18)) AND (("stream_revision" >= $19) OR ("stream_revision" < $20))) OR ((("bucket_id" = $21) AND ("stream_id" = $22)) AND (("stream_revision" >= $23) OR ("stream_revision" < $24))) OR ((("bucket_id" = $25) AND ("stream_id" = $26)) AND (("stream_revision" >= $27) OR ("stream_revision" < $28))) DISCARD ALL
SELECT min(checkpointnumber) from commits
SELECT max(checkpointnumber) from commits DISCARD ALL
SELECT "bucket_id", "stream_id", "stream_revision"  FROM "rdp" WHERE (1=0) OR ((("bucket_id" = $1) AND ("stream_id" = $2)) AND (("stream_revision" >= $3) OR ("stream_revision" < $4))) DISCARD ALL
SELECT min(checkpointnumber) from commits
SELECT max(checkpointnumber) from commits DISCARD ALL
SELECT min(checkpointnumber) from commits
SELECT max(checkpointnumber) from commits DISCARD ALL bucketid, streamid, streamidoriginal, checkpointnumber, streamrevision, commitstamp FROM commits WHERE bucketid = $1 AND checkpointnumber BETWEEN $2 AND $3 DISCARD ALL bucketid, streamid, streamidoriginal, checkpointnumber, streamrevision, commitstamp FROM commits WHERE bucketid = $1 AND checkpointnumber BETWEEN $2 AND $3 DISCARD ALL bucketid, streamid, streamidoriginal, checkpointnumber, streamrevision, commitstamp FROM commits WHERE bucketid = $1 AND checkpointnumber BETWEEN $2 AND $3 DISCARD ALL bucketid, streamid, streamidoriginal, checkpointnumber, streamrevision, commitstamp FROM commits WHERE bucketid = $1 AND checkpointnumber BETWEEN $2 AND $3 DISCARD ALL bucketid, streamid, streamidoriginal, checkpointnumber, streamrevision, commitstamp FROM commits WHERE bucketid = $1 AND checkpointnumber BETWEEN $2 AND $3 DISCARD ALL bucketid, streamid, streamidoriginal, checkpointnumber, streamrevision, commitstamp FROM commits WHERE bucketid = $1 AND checkpointnumber BETWEEN $2 AND $3 DISCARD ALL bucketid, streamid, streamidoriginal, checkpointnumber, streamrevision, commitstamp FROM commits WHERE bucketid = $1 AND checkpointnumber BETWEEN $2 AND $3 DISCARD ALL
SELECT min(checkpointnumber) from commits

Success! I had never imagined to use tcpdump to get this data. I just assumed I would have to use a SQL profiler or some other obnoxious application.Â


Steven Wright

Written by Steven Wright who lives and works in Sacramento building useful things.