Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Show HN: pg_netstat, a Postgres extension to monitor database network traffic

github.com

Post reply on HN
Print view View original

1–10 of 26 posts

Re: Show HN: pg_netstat, a Postgres extension to monitor database network traffic

#3

Post by burmecia »

Hey HN,

I have spent some time to search for a tool that can ingest realtime network traffic data to Postgres but have no luck, so I developed this extension and used it internally in our team. Thanks Rust, pgx and libpcap, the development journey is easy and enjoyable.

Would like to hear more feedbacks. Any contributions, feature requests, bug report or ideas are welcomed.

Thanks.

Re: Show HN: pg_netstat, a Postgres extension to monitor database network traffic

#4

Post by burmecia »

xani_ wrote: post #2

> Before install this extension, you need to give network packet capture permission to Postgres binary. At that point you have root and can use any of the dozen estabilished ways to do the same thing tho ?

Yes, you need root to grant capture permission to Postgres, but Postgres doesn't need to run as root. The main purpose is to easily ingest network traffic data into Postgres for further process. Yes we can use tcpdump to do the same thing, but that needs another tool to load data into Postgres.

Re: Show HN: pg_netstat, a Postgres extension to monitor database network traffic

#5

Post by mrits »

xani_ wrote: post #2

> Before install this extension, you need to give network packet capture permission to Postgres binary. At that point you have root and can use any of the dozen estabilished ways to do the same thing tho ?

Do you come to this site looking for old established tech to talk about?

Re: Show HN: pg_netstat, a Postgres extension to monitor database network traffic

#6

Post by merpkz »

could achieve something similar a bit different way - by using Linux nftables ( new iptables ) netfilter interface. I have setup like this for measuring used traffic by certain daemons running under specific user:

  table inet raw {
  ...
   counter postgre_tcp_traffic_out {
     packets 0 bytes 0
   }
  ...
   chain output {
    ...
    meta l4proto tcp skuid postgres counter name "postgre_tcp_traffic_out" notrack
    ...
   }
  }
and then view it like this:

  nft -j list counters | jq '.'
  ...
        "counter": {
          "family": "inet",
          "name": "postgre_tcp_traffic_out",
          "table": "raw",
          "handle": 20,
          "packets": 255,
          "bytes": 17694
        }
  ...
Since nft -j outputs JSON it can easily then be ingested back into Postgres and indexed. I personally use it together with zabbix to count per second differences in values. It needs some more work because netfilter can match packets by UID/GID only for output, input then has to be matched by destination port, 5432 in case of postgres.

Re: Show HN: pg_netstat, a Postgres extension to monitor database network traffic

#7

Post by burmecia »

merpkz wrote: post #6

could achieve something similar a bit different way - by using Linux nftables ( new iptables ) netfilter interface. I have setup like this for measuring used traffic by certain daemons running under specific user: table inet raw { ... counter postgre_tcp_traffic_out { packets 0 bytes 0 } ... chain output { ... meta l4proto tcp skuid postgres counter name "postgre_tcp_traffic_out" notrack ... } } and then view it like…

That's a nice approach, thanks for sharing with us.

Re: Show HN: pg_netstat, a Postgres extension to monitor database network traffic

#8

Post by burmecia »

mrits wrote: post #5
xani_ wrote: post #2

> Before install this extension, you need to give network packet capture permission to Postgres binary. At that point you have root and can use any of the dozen estabilished ways to do the same thing tho ?

Do you come to this site looking for old established tech to talk about?

I like old solid tech like Postgres, libpcap and etc. Putting them together with new tech like Rust and pgx is also fascinating.

Re: Show HN: pg_netstat, a Postgres extension to monitor database network traffic

#9

Post by Dowwie »

burmecia wrote: post #3

Hey HN, I have spent some time to search for a tool that can ingest realtime network traffic data to Postgres but have no luck, so I developed this extension and used it internally in our team. Thanks Rust, pgx and libpcap, the development journey is easy and enjoyable. Would like to hear more feedbacks. Any contributions, feature requests, bug report or ideas are welcomed. Thanks.

I'm curious as to how you're using these stats for monitoring. What kind of insights are you gaining with this, and in what context is it used?

Re: Show HN: pg_netstat, a Postgres extension to monitor database network traffic

#10

Post by burmecia »

Dowwie wrote: post #9
burmecia wrote: post #3

Hey HN, I have spent some time to search for a tool that can ingest realtime network traffic data to Postgres but have no luck, so I developed this extension and used it internally in our team. Thanks Rust, pgx and libpcap, the development journey is easy and enjoyable. Would like to hear more feedbacks. Any contributions, feature requests, bug report or ideas are welcomed. Thanks.

I'm curious as to how you're using these stats for monitoring. What kind of insights are you gaining with this, and in what context is it used?

Our price model based on database usage which includes db egress, so we use it to monitor db egress traffic just for now. We are all db fans and we like put things into db, so all the following processes, like searching, tracking, analysis and etc., are a lot easier.
Post reply on HN