tcpdump PostgreSQL

29 Dec 2016

I'm always Googling this: how to use tcpdump to watch PostgreSQL traffic.

First off, it's nice to specify the host (even localhost) when connecting to PostgreSQL, to be sure you are actually using TCP and not Unix sockets:

psql -U postgres -d postgres -h localhost

Here's the tcpdump command:

tcpdump -nX -i lo port 5432

Here's what the switches mean:

-n don't convert addresses to names

-X print data of each packet in hex and ascii

-i interface (no idea why loopback device is working when I specified the host above... ahh, must be because the server itself is on the loopback device.)

Here's a snippet of sample traffic:

17:57:57.965463 IP 127.0.0.1.33174 > 127.0.0.1.5432: Flags [P.], seq 2521464336:2521464403, ack 414052419, win 1418, options [nop,nop,TS val 6835604 ecr 6823158], length 67
    0x0000:  4500 0077 a8d0 4000 4006 93ae 7f00 0001  E..w..@.@.......
    0x0010:  7f00 0001 8196 1538 964a 7e10 18ad f043  .......8.J~....C
    0x0020:  8018 058a fe6b 0000 0101 080a 0068 4d94  .....k.......hM.
    0x0030:  0068 1cf6 5100 0000 4273 656c 6563 7420  .h..Q...Bselect.
    0x0040:  7265 6c61 636c 2066 726f 6d20 7067 5f63  relacl.from.pg_c
    0x0050:  6c61 7373 2077 6865 7265 2072 656c 6163  lass.where.relac
    0x0060:  6c20 6973 206e 6f74 206e 756c 6c20 6c69  l.is.not.null.li
    0x0070:  6d69 7420 313b 00                        mit.1;.
17:57:57.965821 IP 127.0.0.1.5432 > 127.0.0.1.33174: Flags [P.], seq 1:91, ack 67, win 397, options [nop,nop,TS val 6835604 ecr 6835604], length 90
    0x0000:  4500 008e 3dcf 4000 4006 fe98 7f00 0001  E...=.@.@.......
    0x0010:  7f00 0001 1538 8196 18ad f043 964a 7e53  .....8.....C.J~S
    0x0020:  8018 018d fe82 0000 0101 080a 0068 4d94  .............hM.
    0x0030:  0068 4d94 5400 0000 1f00 0172 656c 6163  .hM.T......relac
    0x0040:  6c00 0000 04eb 001a 0000 040a ffff ffff  l...............
    0x0050:  ffff 0000 4400 0000 2500 0100 0000 1b7b  ....D...%......{
    0x0060:  706f 7374 6772 6573 3d61 7277 6444 7874  postgres=arwdDxt
    0x0070:  2f70 6f73 7467 7265 737d 4300 0000 0d53  /postgres}C....S
    0x0080:  454c 4543 5420 3100 5a00 0000 0549       ELECT.1.Z....I
17:57:57.965843 IP 127.0.0.1.33174 > 127.0.0.1.5432: Flags [.], ack 91, win 1418, options [nop,nop,TS val 6835604 ecr 6835604], length 0
    0x0000:  4500 0034 a8d1 4000 4006 93f0 7f00 0001  E..4..@.@.......
    0x0010:  7f00 0001 8196 1538 964a 7e53 18ad f09d  .......8.J~S....
    0x0020:  8010 058a fe28 0000 0101 080a 0068 4d94  .....(.......hM.
    0x0030:  0068 4d94                                .hM.