Monitoring real-time statistics on PostgreSQL

Question

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?

Answer 1

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)