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.
PgBouncerin 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).
log_disconnections) into the logfile (e.g. csvlog) and then use pgBadger or something similar to extract that from the logfile?