How to properly monitor the number of PostgreSQL database connections?

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.

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>

#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