Plugin(database::postgres::plugin) - mode(backends): Add an option to take into account the sum of connections from all databasess.

[MickaelTheCat]

Hello

I dont understand this mode.

It calculates a percentage per database while max_connections is defined for all databases.

An option should be added to take into account the sum of connections from all databasess.

Regards

[lucie-tirand]

Hello :)

If I understand correctly what is returned by the query of this plugin, we should have an extract of this form? (I don't have a dataset on which to check the query)

current	mc	datname
5	100	app_db
10	100	other_db

If so, I have a plugin return looking like this with --verboseflag:

OK: All client database connections are ok | 'app_db#database.clients.connected.count'=5;;;0;100'other_db#database.clients.connected.count'=10;;;0;100
Database 'app_db': 5.00% client connections limit reached (5 of max. 100)
Database 'other_db': 10.00% client connections limit reached (10 of max. 100)

So if all this is correct, I'm not sure I understand what you mean by "It calculates a percentage per database while max_connections is defined for all databases."?
Is this related to the nature of the query being made or the format of the data?
This is the query made:

SELECT
    COUNT(datid) AS current,
    (SELECT setting AS mc FROM pg_settings WHERE name = 'max_connections') AS mc,
    d.datname
FROM pg_database d
LEFT JOIN pg_stat_activity s ON (s.datid = d.oid $noidle)
GROUP BY d.datname
ORDER BY d.datname

---

And for the enhancement suggestion "An option should be added to take into account the sum of connections from all databasess." Do you mean something that would give a "total connections" in this style of return?

OK: All client database connections are ok | 'database.client.total.count'=15;;;0;100 'app_db#database.clients.connected.count'=5;;;0;100'other_db#database.clients.connected.count'=10;;;0;100
Database 'app_db': 5.00% client connections limit reached (5 of max. 100)
Database 'other_db': 10.00% client connections limit reached (10 of max. 100)

[MickaelTheCat]

Hello

I have many databases :

current datname
20 db1
30 db2
50 db3
...

max_connections 100

Plugin :

warning 80
critical 90

In this situation all new connections are blocked (without critical alert) because :

sum(current) = max_connections

So, the only important return to avoid that is the percentage total of connections compared to the max_connections.

Regards

[Emanuel-Bourel]

Hello,

I'm interested in this development request. Indeed, the sum of connection would be a must have.
could you change the following query to

SELECT
COUNT(datid) AS current,
(SELECT setting AS mc FROM pg_settings WHERE name = 'max_connections') AS mc,
d.datname
FROM pg_database d
LEFT JOIN pg_stat_activity s ON (s.datid = d.oid $noidle)
GROUP BY d.datname
ORDER BY d.datname

by

SELECT
COUNT(datid) AS current,
(SELECT setting AS mc FROM pg_settings WHERE name = 'max_connections') AS mc,
coalesce(d.datname,'Total')
FROM pg_database d
LEFT JOIN pg_stat_activity s ON (s.datid = d.oid)
GROUP BY rollup(d.datname)
ORDER BY d.datname;

this is used to output the total number of connections, on all bases, even if excluded.

/usr/lib/centreon/plugins/centreon_postgresql.pl --plugin=database::postgres::plugin --mode=backends --host=XXX.XXX.XXX.XXX --username='postgres' --password='' --port='5432' --database='' --warning='90' --critical='95' --exclude='^((postgres|template1|template0))' --verbose

OK: All client database connections are ok | 'app1#database.clients.connected.count'=10;0:450;0:475;0;500 'app2#database.clients.connected.count'=10;0:450;0:475;0;500 'Total#database.clients.connected.count'=23;0:450;0:475;0;500
Database 'app1': 2.00% client connections limit reached (10 of max. 500)
Skipping database 'postgres"
Database 'app2': 2.00% client connections limit reached (10 of max. 500)
Skipping database 'template0"
Skipping database 'template1"
Database 'Total': 4.60% client connections limit reached (23 of max. 500)

Regards

[lucie-tirand]

Hello :)

@Emanuel-Bourel thank you for your contribution 🙏 , we'll take a look at your solution (maybe the inclusion of inactive connections would be set using an option) and validate if it solve this mix of thresholds bug and enhancement request.