第一种方式:
参考资料:https://access.redhat.com/solutions/3223031
1.在PostgreSQL数据库服务器上执行tcpdump -i any port 7432 -nnnvvvSex
2.在PostgreSQL客户端使用D:\PostgreSQL\pg11.5\bin>psql -d postgres -U pg12bt2 -p 7432 -h 192.168.80.187命令连接服务器
3.tcpdump -i any port 7432 -nnnvvvSex的输出有如下结果,注意输出结果中有“proto TCP (6)”
[root@dbserver ~]# tcpdump -i any port 7432 -nnnvvvSex
tcpdump: listening on any, link-type LINUX_SLL (Linux cooked), capture size 262144 bytes
18:31:15.720332 In 00:50:56:c0:00:00 ethertype IPv4 (0x0800), length 68: (tos 0x0, ttl 64, id 21242, offset 0, flags [DF], proto TCP (6), length 52)
192.168.80.1.65494 > 192.168.80.187.7432: Flags [S], cksum 0x3d5d (correct), seq 3125362060, win 64240, options [mss 1460,nop,wscale 8,nop,nop,sackOK], length 0
0x0000: 4500 0034 52fa 4000 4006 c5bc c0a8 5001
0x0010: c0a8 50bb ffd6 1d08 ba49 3d8c 0000 0000
0x0020: 8002 faf0 3d5d 0000 0204 05b4 0103 0308
0x0030: 0101 0402 0000 0000 0000 0000 0000 0000
0x0040: 0000 0000
18:31:15.720413 Out 00:0c:29:49:ce:3c ethertype IPv4 (0x0800), length 68: (tos 0x0, ttl 64, id 0, offset 0, flags [DF], proto TCP (6), length 52)
192.168.80.187.7432 > 192.168.80.1.65494: Flags [S.], cksum 0x2234 (incorrect -> 0xce05), seq 843695580, ack 3125362061, win 29200, options [mss 1460,nop,nop,sackOK,nop,wscale 9], length 0
0x0000: 4500 0034 0000 4000 4006 18b7 c0a8 50bb
0x0010: c0a8 5001 1d08 ffd6 3249 c5dc ba49 3d8d
0x0020: 8012 7210 2234 0000 0204 05b4 0101 0402
0x0030: 0103 0309 0000 0000 0000 0000 0000 0000
0x0040: 0000 0000
18:31:15.721180 In 00:50:56:c0:00:00 ethertype IPv4 (0x0800), length 62: (tos 0x0, ttl 64, id 21243, offset 0, flags [DF], proto TCP (6), length 40)
192.168.80.1.65494 > 192.168.80.187.7432: Flags [.], cksum 0x78e5 (correct), seq 3125362061, ack 843695581, win 2053, length 0
0x0000: 4500 0028 52fb 4000 4006 c5c7 c0a8 5001
0x0010: c0a8 50bb ffd6 1d08 ba49 3d8d 3249 c5dd
0x0020: 5010 0805 78e5 0000 0000 0000 0000 0000
0x0030: 0000 0000 0000 0000 0000 0000 0000
18:31:15.738036 In 00:50:56:c0:00:00 ethertype IPv4 (0x0800), length 64: (tos 0x0, ttl 64, id 21246, offset 0, flags [DF], proto TCP (6), length 48)
192.168.80.1.65494 > 192.168.80.187.7432: Flags [P.], cksum 0x5dcc (correct), seq 3125362061:3125362069, ack 843695581, win 2053, length 8
0x0000: 4500 0030 52fe 4000 4006 c5bc c0a8 5001
0x0010: c0a8 50bb ffd6 1d08 ba49 3d8d 3249 c5dd
0x0020: 5018 0805 5dcc 0000 0000 0008 04d2 162f
0x0030: 0000 0000 0000 0000 0000 0000 0000 0000
18:31:15.739412 Out 00:0c:29:49:ce:3c ethertype IPv4 (0x0800), length 56: (tos 0x0, ttl 64, id 45829, offset 0, flags [DF], proto TCP (6), length 40)
192.168.80.187.7432 > 192.168.80.1.65494: Flags [.], cksum 0x2228 (incorrect -> 0x80a8), seq 843695581, ack 3125362069, win 58, length 0
0x0000: 4500 0028 b305 4000 4006 65bd c0a8 50bb
0x0010: c0a8 5001 1d08 ffd6 3249 c5dd ba49 3d95
0x0020: 5010 003a 2228 0000 0000 0000 0000 0000
0x0030: 0000 0000 0000 0000
18:31:15.744947 Out 00:0c:29:49:ce:3c ethertype IPv4 (0x0800), length 57: (tos 0x0, ttl 64, id 45830, offset 0, flags [DF], proto TCP (6), length 41)
192.168.80.187.7432 > 192.168.80.1.65494: Flags [P.], cksum 0x2229 (incorrect -> 0x329f), seq 843695581:843695582, ack 3125362069, win 58, length 1
0x0000: 4500 0029 b306 4000 4006 65bb c0a8 50bb
0x0010: c0a8 5001 1d08 ffd6 3249 c5dd ba49 3d95
0x0020: 5018 003a 2229 0000 4e00 0000 0000 0000
0x0030: 0000 0000 0000 0000 00
18:31:15.760319 In 00:50:56:c0:00:00 ethertype IPv4 (0x0800), length 138: (tos 0x0, ttl 64, id 21247, offset 0, flags [DF], proto TCP (6), length 122)
192.168.80.1.65494 > 192.168.80.187.7432: Flags [P.], cksum 0x038e (correct), seq 3125362069:3125362151, ack 843695582, win 2053, length 82
0x0000: 4500 007a 52ff 4000 4006 c571 c0a8 5001
0x0010: c0a8 50bb ffd6 1d08 ba49 3d95 3249 c5de
0x0020: 5018 0805 038e 0000 0000 0052 0003 0000
0x0030: 7573 6572 0070 6731 3262 7432 0064 6174
0x0040: 6162 6173 6500 706f 7374 6772 6573 0061
0x0050: 7070 6c69 6361 7469 6f6e 5f6e 616d 6500
0x0060: 7073 716c 0063 6c69 656e 745f 656e 636f
0x0070: 6469 6e67 0047 424b 0000 0000 0000 0000
0x0080: 0000 0000 0000 0000 0000
18:31:15.784492 Out 00:0c:29:49:ce:3c ethertype IPv4 (0x0800), length 69: (tos 0x0, ttl 64, id 45831, offset 0, flags [DF], proto TCP (6), length 53)
192.168.80.187.7432 > 192.168.80.1.65494: Flags [P.], cksum 0x2235 (incorrect -> 0xf45c), seq 843695582:843695595, ack 3125362151, win 58, length 13
0x0000: 4500 0035 b307 4000 4006 65ae c0a8 50bb
0x0010: c0a8 5001 1d08 ffd6 3249 c5de ba49 3de7
0x0020: 5018 003a 2235 0000 5200 0000 0c00 0000
0x0030: 0538 02ab 2600 0000 0000 0000 0000 0000
0x0040: 0000 0000 00
18:31:15.795540 In 00:50:56:c0:00:00 ethertype IPv4 (0x0800), length 62: (tos 0x0, ttl 64, id 21249, offset 0, flags [DF], proto TCP (6), length 40)
192.168.80.1.65494 > 192.168.80.187.7432: Flags [F.], cksum 0x787c (correct), seq 3125362151, ack 843695595, win 2053, length 0
0x0000: 4500 0028 5301 4000 4006 c5c1 c0a8 5001
0x0010: c0a8 50bb ffd6 1d08 ba49 3de7 3249 c5eb
0x0020: 5011 0805 787c 0000 0000 0000 0000 0000
0x0030: 0000 0000 0000 0000 0000 0000 0000
18:31:15.805767 Out 00:0c:29:49:ce:3c ethertype IPv4 (0x0800), length 56: (tos 0x0, ttl 64, id 45832, offset 0, flags [DF], proto TCP (6), length 40)
192.168.80.187.7432 > 192.168.80.1.65494: Flags [F.], cksum 0x2228 (incorrect -> 0x8046), seq 843695595, ack 3125362152, win 58, length 0
0x0000: 4500 0028 b308 4000 4006 65ba c0a8 50bb
0x0010: c0a8 5001 1d08 ffd6 3249 c5eb ba49 3de8
0x0020: 5011 003a 2228 0000 0000 0000 0000 0000
0x0030: 0000 0000 0000 0000
18:31:15.806840 In 00:50:56:c0:00:00 ethertype IPv4 (0x0800), length 62: (tos 0x0, ttl 64, id 21251, offset 0, flags [DF], proto TCP (6), length 40)
192.168.80.1.65494 > 192.168.80.187.7432: Flags [.], cksum 0x787b (correct), seq 3125362152, ack 843695596, win 2053, length 0
0x0000: 4500 0028 5303 4000 4006 c5bf c0a8 5001
0x0010: c0a8 50bb ffd6 1d08 ba49 3de8 3249 c5ec
0x0020: 5010 0805 787b 0000 0000 0000 0000 0000
0x0030: 0000 0000 0000 0000 0000 0000 0000
18:31:43.513374 In 00:50:56:c0:00:00 ethertype IPv4 (0x0800), length 68: (tos 0x0, ttl 64, id 21256, offset 0, flags [DF], proto TCP (6), length 52)
192.168.80.1.65502 > 192.168.80.187.7432: Flags [S], cksum 0xb36d (correct), seq 2078606808, win 64240, options [mss 1460,nop,wscale 8,nop,nop,sackOK], length 0
0x0000: 4500 0034 5308 4000 4006 c5ae c0a8 5001
0x0010: c0a8 50bb ffde 1d08 7be5 05d8 0000 0000
0x0020: 8002 faf0 b36d 0000 0204 05b4 0103 0308
0x0030: 0101 0402 0000 0000 0000 0000 0000 0000
0x0040: 0000 0000
18:31:43.513422 Out 00:0c:29:49:ce:3c ethertype IPv4 (0x0800), length 68: (tos 0x0, ttl 64, id 0, offset 0, flags [DF], proto TCP (6), length 52)
192.168.80.187.7432 > 192.168.80.1.65502: Flags [S.], cksum 0x2234 (incorrect -> 0xb736), seq 3778520013, ack 2078606809, win 29200, options [mss 1460,nop,nop,sackOK,nop,wscale 9], length 0
0x0000: 4500 0034 0000 4000 4006 18b7 c0a8 50bb
0x0010: c0a8 5001 1d08 ffde e137 a3cd 7be5 05d9
0x0020: 8012 7210 2234 0000 0204 05b4 0101 0402
0x0030: 0103 0309 0000 0000 0000 0000 0000 0000
0x0040: 0000 0000
18:31:43.513871 In 00:50:56:c0:00:00 ethertype IPv4 (0x0800), length 62: (tos 0x0, ttl 64, id 21257, offset 0, flags [DF], proto TCP (6), length 40)
192.168.80.1.65502 > 192.168.80.187.7432: Flags [.], cksum 0x6216 (correct), seq 2078606809, ack 3778520014, win 2053, length 0
0x0000: 4500 0028 5309 4000 4006 c5b9 c0a8 5001
0x0010: c0a8 50bb ffde 1d08 7be5 05d9 e137 a3ce
0x0020: 5010 0805 6216 0000 0000 0000 0000 0000
0x0030: 0000 0000 0000 0000 0000 0000 0000
18:31:43.526510 In 00:50:56:c0:00:00 ethertype IPv4 (0x0800), length 64: (tos 0x0, ttl 64, id 21259, offset 0, flags [DF], proto TCP (6), length 48)
192.168.80.1.65502 > 192.168.80.187.7432: Flags [P.], cksum 0x46fd (correct), seq 2078606809:2078606817, ack 3778520014, win 2053, length 8
0x0000: 4500 0030 530b 4000 4006 c5af c0a8 5001
0x0010: c0a8 50bb ffde 1d08 7be5 05d9 e137 a3ce
0x0020: 5018 0805 46fd 0000 0000 0008 04d2 162f
0x0030: 0000 0000 0000 0000 0000 0000 0000 0000
18:31:43.526628 Out 00:0c:29:49:ce:3c ethertype IPv4 (0x0800), length 56: (tos 0x0, ttl 64, id 16550, offset 0, flags [DF], proto TCP (6), length 40)
192.168.80.187.7432 > 192.168.80.1.65502: Flags [.], cksum 0x2228 (incorrect -> 0x69d9), seq 3778520014, ack 2078606817, win 58, length 0
0x0000: 4500 0028 40a6 4000 4006 d81c c0a8 50bb
0x0010: c0a8 5001 1d08 ffde e137 a3ce 7be5 05e1
0x0020: 5010 003a 2228 0000 0204 05b4 0103 0308
0x0030: 0101 0402 0000 0000
18:31:43.527832 Out 00:0c:29:49:ce:3c ethertype IPv4 (0x0800), length 57: (tos 0x0, ttl 64, id 16551, offset 0, flags [DF], proto TCP (6), length 41)
192.168.80.187.7432 > 192.168.80.1.65502: Flags [P.], cksum 0x2229 (incorrect -> 0x1bd0), seq 3778520014:3778520015, ack 2078606817, win 58, length 1
0x0000: 4500 0029 40a7 4000 4006 d81a c0a8 50bb
0x0010: c0a8 5001 1d08 ffde e137 a3ce 7be5 05e1
0x0020: 5018 003a 2229 0000 4e04 05b4 0101 0402
0x0030: 0103 0309 0000 0000 00
18:31:43.530708 In 00:50:56:c0:00:00 ethertype IPv4 (0x0800), length 138: (tos 0x0, ttl 64, id 21261, offset 0, flags [DF], proto TCP (6), length 122)
192.168.80.1.65502 > 192.168.80.187.7432: Flags [P.], cksum 0xecbe (correct), seq 2078606817:2078606899, ack 3778520015, win 2053, length 82
0x0000: 4500 007a 530d 4000 4006 c563 c0a8 5001
0x0010: c0a8 50bb ffde 1d08 7be5 05e1 e137 a3cf
0x0020: 5018 0805 ecbe 0000 0000 0052 0003 0000
0x0030: 7573 6572 0070 6731 3262 7432 0064 6174
0x0040: 6162 6173 6500 706f 7374 6772 6573 0061
0x0050: 7070 6c69 6361 7469 6f6e 5f6e 616d 6500
0x0060: 7073 716c 0063 6c69 656e 745f 656e 636f
0x0070: 6469 6e67 0047 424b 0000 0000 0000 0000
0x0080: 0000 0000 0000 0000 0000
18:31:43.532789 Out 00:0c:29:49:ce:3c ethertype IPv4 (0x0800), length 69: (tos 0x0, ttl 64, id 16552, offset 0, flags [DF], proto TCP (6), length 53)
192.168.80.187.7432 > 192.168.80.1.65502: Flags [P.], cksum 0x2235 (incorrect -> 0xf5e1), seq 3778520015:3778520028, ack 2078606899, win 58, length 13
0x0000: 4500 0035 40a8 4000 4006 d80d c0a8 50bb
0x0010: c0a8 5001 1d08 ffde e137 a3cf 7be5 0633
0x0020: 5018 003a 2235 0000 5200 0000 0c00 0000
0x0030: 0559 5d35 b300 0000 0000 0000 0000 0000
0x0040: 0000 0000 00
18:31:43.540651 In 00:50:56:c0:00:00 ethertype IPv4 (0x0800), length 97: (tos 0x0, ttl 64, id 21263, offset 0, flags [DF], proto TCP (6), length 81)
192.168.80.1.65502 > 192.168.80.187.7432: Flags [P.], cksum 0x5e43 (correct), seq 2078606899:2078606940, ack 3778520028, win 2053, length 41
0x0000: 4500 0051 530f 4000 4006 c58a c0a8 5001
0x0010: c0a8 50bb ffde 1d08 7be5 0633 e137 a3dc
0x0020: 5018 0805 5e43 0000 7000 0000 286d 6435
0x0030: 3636 6464 3764 6234 3163 6434 3439 3333
0x0040: 3835 3131 3963 3665 3538 6263 3261 3234
0x0050: 0000 0000 0000 0000 0000 0000 0000 0000
0x0060: 00
18:31:43.544295 Out 00:0c:29:49:ce:3c ethertype IPv4 (0x0800), length 391: (tos 0x0, ttl 64, id 16553, offset 0, flags [DF], proto TCP (6), length 375)
192.168.80.187.7432 > 192.168.80.1.65502: Flags [P.], cksum 0x2377 (incorrect -> 0x4c7c), seq 3778520028:3778520363, ack 2078606940, win 58, length 335
0x0000: 4500 0177 40a9 4000 4006 d6ca c0a8 50bb
0x0010: c0a8 5001 1d08 ffde e137 a3dc 7be5 065c
0x0020: 5018 003a 2377 0000 5200 0000 0800 0000
0x0030: 0053 0000 001a 6170 706c 6963 6174 696f
0x0040: 6e5f 6e61 6d65 0070 7371 6c00 5300 0000
0x0050: 1863 6c69 656e 745f 656e 636f 6469 6e67
0x0060: 0047 424b 0053 0000 0017 4461 7465 5374
0x0070: 796c 6500 4953 4f2c 204d 4459 0053 0000
0x0080: 0019 696e 7465 6765 725f 6461 7465 7469
0x0090: 6d65 7300 6f6e 0053 0000 001b 496e 7465
0x00a0: 7276 616c 5374 796c 6500 706f 7374 6772
0x00b0: 6573 0053 0000 0014 6973 5f73 7570 6572
0x00c0: 7573 6572 006f 6e00 5300 0000 1973 6572
0x00d0: 7665 725f 656e 636f 6469 6e67 0055 5446
0x00e0: 3800 5300 0000 1b73 6572 7665 725f 7665
0x00f0: 7273 696f 6e00 3132 6265 7461 3200 5300
0x0100: 0000 2273 6573 7369 6f6e 5f61 7574 686f
0x0110: 7269 7a61 7469 6f6e 0070 6731 3262 7432
0x0120: 0053 0000 0023 7374 616e 6461 7264 5f63
0x0130: 6f6e 666f 726d 696e 675f 7374 7269 6e67
0x0140: 7300 6f6e 0053 0000 001e 5469 6d65 5a6f
0x0150: 6e65 0041 6d65 7269 6361 2f4e 6577 5f59
0x0160: 6f72 6b00 4b00 0000 0c00 010d 1237 75a7
0x0170: cc5a 0000 0005 4900 0000 0000 0000 0000
0x0180: 0000 0000 0000 00
18:31:43.585568 In 00:50:56:c0:00:00 ethertype IPv4 (0x0800), length 62: (tos 0x0, ttl 64, id 21265, offset 0, flags [DF], proto TCP (6), length 40)
192.168.80.1.65502 > 192.168.80.187.7432: Flags [.], cksum 0x6038 (correct), seq 2078606940, ack 3778520363, win 2051, length 0
0x0000: 4500 0028 5311 4000 4006 c5b1 c0a8 5001
0x0010: c0a8 50bb ffde 1d08 7be5 065c e137 a52b
0x0020: 5010 0803 6038 0000 0000 0000 0000 0000
0x0030: 7573 6572 0070 6731 3262 7432 0064
第二种方式:
https://access.redhat.com/solutions/442393
[root@dbserver ~]# netstat -tulanp |grep postgre
tcp 0 0 0.0.0.0:7432 0.0.0.0:* LISTEN 64287/postgres
tcp 0 0 192.168.80.187:7432 192.168.80.1:63856 ESTABLISHED 66816/postgres: pg1 --->此行
tcp 0 0 192.168.80.187:7432 192.168.80.1:65502 ESTABLISHED 68882/postgres: pg1 --->此行
tcp6 0 0 :::7432 :::* LISTEN 64287/postgres
udp6 0 0 ::1:61020 ::1:61020 ESTABLISHED 64287/postgres
[root@dbserver ~]# ps -ef |grep postg
pg12bt2 56606 56562 0 15:13 pts/4 00:00:00 psql -d postgres
pg12bt2 57054 57012 0 15:19 pts/5 00:00:00 psql -d postgres
pg12bt2 64287 1 0 17:19 ? 00:00:00 /home/pg12bt2/pg12bt2bin/bin/postgres
pg12bt2 64292 64287 0 17:19 ? 00:00:00 postgres: checkpointer
pg12bt2 64293 64287 0 17:19 ? 00:00:00 postgres: background writer
pg12bt2 64294 64287 0 17:19 ? 00:00:00 postgres: walwriter
pg12bt2 64295 64287 0 17:19 ? 00:00:00 postgres: autovacuum launcher
pg12bt2 64296 64287 0 17:19 ? 00:00:00 postgres: stats collector
pg12bt2 64297 64287 0 17:19 ? 00:00:00 postgres: logical replication launcher
pg12bt2 65386 64287 0 17:36 ? 00:00:00 postgres: pg12bt2 postgres [local] idle
pg12bt2 66816 64287 0 18:00 ? 00:00:00 postgres: pg12bt2 postgres 192.168.80.1(63856) idle --->此行
pg12bt2 68882 64287 0 18:31 ? 00:00:00 postgres: pg12bt2 postgres 192.168.80.1(65502) idle --->此行
pg12bt2 72878 72832 0 19:38 pts/3 00:00:00 psql -d postgres
pg12bt2 72879 64287 0 19:38 ? 00:00:00 postgres: pg12bt2 postgres [local] idle
root 73879 67845 0 19:55 pts/2 00:00:00 grep --color=auto postg
[root@dbserver ~]#
注意点:
1. netstat -tulanp |grep postgre的结果只是走tcp和udp连入PostgreSQL数据库的
a参数的含义是包括已建立的connections。
-a, --all
Show both listening and non-listening (for TCP this means established connections) sockets.
With the --interfaces option, show interfaces that are not up.
2. ps -ef |grep postg结果中的65386进程号并没有在netstat -tulanp |grep postgre中体现,
65386进程是PostgreSQL数据库服务器本地“psql -d postgres”登录PostgreSQL数据库之后产生的local进程。
个人估计:PostgreSQL数据库服务器本地“psql -d postgres”登录PostgreSQL数据库---此种方式应该是走的Unix-Domain Socket