Sniffing PostgreSQL queries with tshark/wireshark

We got a setup in which PostgreSQL is a fairly important beastie. Trouble is, for some reason, the PostgreSQL server sometimes stops responding. The logging doesn't reveal all that much information without increasing it's level, which would impact performance, which is not what we want for a production database. So we need some way to inspect the queries that are sent to the server, and determine which one is the culprit.

Enter Wireshark. Although a bit complex, it's ideally suited for exactly this kind of stuff. However, it's not the easiest syntax around, if you don't use it every other day. Took me almost the complete weekend to figure this out.

What I want:

  • Established connections.
  • Queries.

What I don't want:

  • The response.
  • Pure TCP ACK messages (which can be a lot for queries returning large datasets).

This is the command I gave it in my test setup: tshark -i lo -p -f "(tcp[13] != 0x10) and dst port 5432" -t ad -w /tmp/output

For a production setup, you probably want to change lo to the actual interface that is being connected on. I'm not sure if the -t ad is really needed, but a bit too tired to find out at the moment. Hope this helps someone else.


Comments powered by Disqus