Show HN: pg_netstat, a Postgres extension to monitor database network traffic
github.compg_netstat is a Postgres extension to monitor database network traffic. It uses libpcap to capture packets and aggregates at user-specified interval. 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: Nice. I had another approach in mind but never actually implemented. Systemd seems to report.network traffic stats for managed units. It spawns cgroups for units which among other things track network traffic. That's a nice approach, thanks for sharing with us. 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. DB fan here who likes putting things into dbs. Are you guys hiring? Seems like they are: https://boards.greenhouse.io/supabase I've written a tool to do this not once but twice now! The first was for ingesting netflow data, the second from ingesting AWS VPC flow logs. In both cases this was storing 'flows' rather than raw packet information. I'd be super interested in tooling to help with this. I'm running – literally right now – a Python script that is writing about 100k flows per second into Postgres (TimescaleDB). It is batch rather than realtime, and it geocodes the flows on the way in. > 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. 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. For another alternative: I added some monitoring, by using file_fdw. This is a standard pg extension that presents files or program stdout as tables. So I file_fdw'd some files in /proc and some system utilities. I would like to see your magic please. Not much magic in there, to be honest: Setup like this: For harder things, read the file as lines instead of fields, then create a view with some regexes to split it in fields: It should explode once slave is out of sync, and is rating all the bandwidth to resync, putting the main DB down. Happened to me once. Nearly all usecases for this seem to be recreating a time series database... Postgres isn't a great TSDB - the indexes don't understand columns that will only have updates at one end, it doesn't do column compression, range queries are expensive, etc. Perhaps it's time to just set up a time series database... Like influxDB. Or just use TimescaleDB which is competitive on most axes but already PG. Thanks for the mentions (and for using TimescaleDB). If anyone's curious about TimescaleDB, it's packaged as an extension to Postgres, optimizing for performance, storage, and analysis of time series data. Implementing columnar compression algorithms is a big part of the secret sauce that makes TimescaleDB a popular choice with Postgres and SQL developers. You can read more about that on the Timescale blog (I'm Timescale's community manager btw). https://www.timescale.com/blog/search/?query=compression If anyone's curious, the youtube channel may be a good place to start, especially this playlist https://www.youtube.com/playlist?list=PLsceB9ac9MHTtM1XWONMR... +1 for Timescale. Used it for a few RNN projects and it's been great. And I +1 your +1, we've been using it in prod for 2 years or so. I recommend to use VictoriaMetrics instead of using InfluxDB for many reasons. PostgreSQL is good SQL database for different purposes but as TSDB on a big installations it works not really well. or clickhouse! +1 have a good experience with using it
and then view it like this: 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
...
}
}
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. nft -j list counters | jq '.'
...
"counter": {
"family": "inet",
"name": "postgre_tcp_traffic_out",
"table": "raw",
"handle": 20,
"packets": 255,
"bytes": 17694
}
...
then do this for the easy interpretable proc files: CREATE EXTENSION file_fdw;
CREATE SERVER pglog FOREIGN DATA WRAPPER file_fdw;
) SERVER pglog OPTIONS ( filename '/proc/loadavg', header 'false',delimiter ' ' ); CREATE FOREIGN TABLE IF NOT EXISTS proc_loadavg(
load1 decimal,
load5 decimal,
load15 decimal,
threads_runnable_total text,
most_recent_pid integer
Hardest part is creating semi-legible source code in HN ;-) CREATE FOREIGN TABLE IF NOT EXISTS proc_meminfo(
line text
) SERVER pglog OPTIONS ( filename '/proc/meminfo', header 'false',delimiter '$' );
CREATE OR REPLACE VIEW proc_meminfo_interpreted AS
WITH arr AS (SELECT regexp_split_to_array(line,':| +') a FROM proc_meminfo)
SELECT a[1] as name,a[3] as value FROM arr;