I tried to use a Nagios script for monitoring the number of database connections on a Postgres database and I reached this problem: these are counted as currently open-connections and measured every 5 minutes.

SELECT sum(numbackends) FROM pg_stat_database;

Still, this seems to miss a huge number of short-lived connections, so the statistics are far from the reality.

I tried to run the script manually and I observed big changes even between two connections made few seconds away one from another.

How could I get this information in a reliable way? like max(connectios) happened during a time interval.

  • 1
    Aggregate stats over time for connections would be nice, but I don't think PostgreSQL currently collects them. Look at the docs postgresql.org/docs/current/static/monitoring-stats.html for details. – Craig Ringer Feb 11 '14 at 11:58
  • @CraigRinger maybe I could configure Postgres or the clients to keep connections open for a longer period of time, so I could measure them. Due to the current setup I had one case when the postgres started to refuse connections. Monitoring was not able to detect this because this happened inside the 5 minute interval, and it went from below warning level to above critical in less then 5 minutes. And this was not a DoS attack. – sorin Feb 11 '14 at 12:12
  • 2
    Yeah, that's quite an interesting problem. I strongly recommend putting a PgBouncer in front of your PostgreSQL instance, it'll queue up connections when it's too busy instead of rejecting them. (Yes, it's stupid that PostgreSQL can't do that its self but it's not a simple fix; see the endless discussions on the mailing lists re built-in pooling). – Craig Ringer Feb 11 '14 at 12:26
  • 7
    What about logging connections (using log_connections and log_disconnections) into the logfile (e.g. csvlog) and then use pgBadger or something similar to extract that from the logfile? – a_horse_with_no_name Feb 11 '14 at 12:29
  • 2
    @a_horse_with_no_name Good point. You can even "tail" the logs with a client that just reads new log entries, integrating over disconnects and connects to get a near-real-time report of peak connections during a given timeframe. Frankly, it shouldn't be this complicated. One of my tasks for the AXLE project (axleproject.eu) is to implement some more auditing, and I might be able to fit this into it... – Craig Ringer Feb 11 '14 at 12:33

Its better to use Workload monitoring tools like pgbadger for checking database connections and overall load. It will help you understand which users are connecting for how much time and what queries are being fired by them. For information on installing and configuring pgbadger, refer this page.

If you just want to check the number of active connections, you can use select count(*) from pg_stat_activity where state='active'


You can use extension with local_preload_libraries to do this.

Something like this:

#include "postgres.h"
#include <string.h>


void _PG_init(void)
  int ret = SPI_execute("UPDATE logon_logs SET logged = logged + 1", false, 0);

 * _PG_fini
 * Uninstall the hook.
void _PG_fini(void)

Or instead UPDATE via NOTIFY

  • 5
    Please, try to improve your answer with some explanation on how to proceed. – McNets Jan 31 '17 at 15:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.