Postgresql – Monitoring real-time statistics on PostgreSQL

monitoringpgbouncerpostgresql

I need to monitor the DB performance on a distributed system with multiple databases. Some of the database servers use pgbouncer to pool connections and in those servers I can connect to pgbouncer and execute a SHOW STATS; command to retrieve certain statistics, I'm interested in the following:

 avg_req | avg_recv | avg_sent | avg_query
------------------------------------------
     563 |  1080453 |  2060908 |     57718

The columns means:

  • avg_req
    Average requests per second in last stat period.
  • avg_recv
    Average received (from clients) bytes per second.
  • avg_sent
    Average sent (to clients) bytes per second.
  • avg_query
    Average query duration in microseconds.

Now my question is, is there a way of gathering this information without using pgbouncer on a postgresql server?

Best Answer

I believe the answer here is "not exactly". Those stats are coming from pgbouncer itself, so that don't map directly to the postgres backends. That said, you can track connection, data, and query statistics in postgres, but the information won't align exactly, and will probably be formatted differently (ie. you're more likely to get a counter than a gauge, so you'll need to do some tracking/math yourself)

Related Question