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. Commented Feb 11, 2014 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
    Commented Feb 11, 2014 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). Commented Feb 11, 2014 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?
    – user1822
    Commented Feb 11, 2014 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... Commented Feb 11, 2014 at 12:33

3 Answers 3


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'


The Postgres 14 release notes contain an information that suggests that the necessary information has been added to Posgres 14 (see section "E. System Views"):

Add session statistics to the pg_stat_database system view (Laurenz Albe)

The corresponding documentation on monitoring stats mentions a few new columns for the pg_stat_database table:

enter image description here

If my understanding of the documentation is correct, these should contain cumulative statistics of the database connections. This would mean that it is possible to monitor a database based on these aggregates now. One could query the database in intervals, and determine e.g. the number of connections within the interval by taking the difference of the sessions value. Unfortunately this also wouldn't deliver the true peak of max(connection) within the interval, but at least it can narrow it down.


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

  • 6
    Please, try to improve your answer with some explanation on how to proceed.
    – McNets
    Commented Jan 31, 2017 at 15:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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