Show HN: pg_netstat, a Postgres extension to monitor database network traffic (opens in new tab)

(github.com)

158 pointsburmecia3y ago25 comments

pg_netstat is a Postgres extension to monitor database network traffic. It uses libpcap to capture packets and aggregates at user-specified interval.

25 comments

merpkz3y ago

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.

Self-Perfection3y ago

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.

burmeciaOP3y ago

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

burmeciaOP3y ago

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.

Dowwie3y ago

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?

burmeciaOP3y ago

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.

1 more reply

adamcharnock3y ago

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.

xani_3y ago

> 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 ?

burmeciaOP3y ago

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.

mrits3y ago

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

burmeciaOP3y ago

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

hyperman13y ago

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.

djbusby3y ago

I would like to see your magic please.

hyperman13y ago

Not much magic in there, to be honest:

Setup like this:

  CREATE EXTENSION file_fdw;

  CREATE SERVER pglog FOREIGN DATA WRAPPER file_fdw;

then do this for the easy interpretable proc files:

  CREATE FOREIGN TABLE IF NOT EXISTS proc_loadavg(
  load1 decimal,
  load5 decimal,
  load15 decimal,
  threads_runnable_total text,
  most_recent_pid integer

) SERVER pglog OPTIONS ( filename '/proc/loadavg', header 'false',delimiter ' ' );

For harder things, read the file as lines instead of fields, then create a view with some regexes to split it in fields:

  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;

Hardest part is creating semi-legible source code in HN ;-)

zoobab3y ago

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.

londons_explore3y ago

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.

jpgvm3y ago

Or just use TimescaleDB which is competitive on most axes but already PG.

LoriP3y ago

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...

_joel3y ago

+1 for Timescale. Used it for a few RNN projects and it's been great.

1 more reply

dengolius3y ago

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.

mritchie7123y ago

or clickhouse!

dengolius3y ago

+1 have a good experience with using it

j / k navigate · click thread line to collapse

25 comments

merpkz3y ago

  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
        }
  ...

Self-Perfection3y ago

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.

burmeciaOP3y ago

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

burmeciaOP3y ago

Hey HN,

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

Thanks.

Dowwie3y ago

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?

burmeciaOP3y ago

1 more reply

adamcharnock3y ago

xani_3y ago

> 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 ?

burmeciaOP3y ago

mrits3y ago

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

burmeciaOP3y ago

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

hyperman13y ago

djbusby3y ago

I would like to see your magic please.

hyperman13y ago

Not much magic in there, to be honest:

Setup like this:

  CREATE EXTENSION file_fdw;

  CREATE SERVER pglog FOREIGN DATA WRAPPER file_fdw;

then do this for the easy interpretable proc files:

  CREATE FOREIGN TABLE IF NOT EXISTS proc_loadavg(
  load1 decimal,
  load5 decimal,
  load15 decimal,
  threads_runnable_total text,
  most_recent_pid integer

) SERVER pglog OPTIONS ( filename '/proc/loadavg', header 'false',delimiter ' ' );

For harder things, read the file as lines instead of fields, then create a view with some regexes to split it in fields:

  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;

Hardest part is creating semi-legible source code in HN ;-)

zoobab3y ago

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.

londons_explore3y ago

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.

jpgvm3y ago

Or just use TimescaleDB which is competitive on most axes but already PG.

LoriP3y ago

Thanks for the mentions (and for using TimescaleDB).

If anyone's curious, the youtube channel may be a good place to start, especially this playlist https://www.youtube.com/playlist?list=PLsceB9ac9MHTtM1XWONMR...

_joel3y ago

+1 for Timescale. Used it for a few RNN projects and it's been great.

1 more reply

dengolius3y ago

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.

mritchie7123y ago

or clickhouse!

dengolius3y ago

+1 have a good experience with using it

j / k navigate · click thread line to collapse