How to properly monitor the number of PostgreSQL database connections?

Question

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.

Answer 1

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'

Answer 2

The Postgres 14 release notes contain an information that suggests that the necessary information has been added to Posgres 14 (see section "E.8.3.1.7. 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:

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.

Answer 3

You can use extension with local_preload_libraries to do this.

Something like this:

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

#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif

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

/*
 * _PG_fini
 * Uninstall the hook.
 */
void _PG_fini(void)
{
}

Or instead UPDATE via NOTIFY