banshee/services-collection/services/postgresql/ssh/connections

PostgreSQL connections, by SSH

Returns the client connections of a PostgreSQL cluster against its max_connections, and how many run a query, sit idle inside a transaction or wait for a lock, read by psql on the database server over SSH. Three watches are offered, each one set on the probe. No password leaves banshee.

Versions

PostgreSQL 10 and later (backend_type, pg_monitor). Generic: no folder of version.

How it works

The collector runs, on the server, LC_ALL=C psql -X -A -t -q -v ON_ERROR_STOP=1 -p <port> -d <database> -c '<query>', each argument quoted apart, over the local socket. The query reads pg_stat_activity, the client backends only, and writes one line of JSON:

SELECT json_build_object(
    'max', current_setting('max_connections')::int,
    'total', count(*),
    'active', count(*) FILTER (WHERE state = 'active'),
    'idle_in_transaction', count(*) FILTER (WHERE state LIKE 'idle in transaction%'),
    'lock_waits', count(*) FILTER (WHERE wait_event_type = 'Lock'))
FROM pg_stat_activity WHERE backend_type = 'client backend'

{"max" : 100, "total" : 23, "active" : 2, "idle_in_transaction" : 1, "lock_waits" : 0}

connections-percent is the client connections against max_connections. Part of max_connections is kept for superusers (superuser_reserved_connections, 3 by default): an ordinary role is refused a little before 100 %.
A cluster whose socket is absent, or that refuses the connection (psql exits 2), is critical: "PostgreSQL is not running". Any other failure of psql (the role refused, psql missing) stops the collector misconfigured, with what psql said.
Nothing is written: the query only reads statistics views.

Prerequisites

An SSH account for monitoring, without write rights (monitoring below; put the account's name in its place), on the database server, with psql in its path (installed with the server; postgresql-client on Debian and Ubuntu).
A PostgreSQL role of the same name, member of pg_monitor, which reads the statistics of every session without any other right:

sudo -u postgres psql -c 'CREATE ROLE monitoring LOGIN' -c 'GRANT pg_monitor TO monitoring'

That role allowed in over the local socket by peer authentication, which checks that the system account has the role's name, without any password: a line local all monitoring peer in pg_hba.conf, above the other local lines (Debian and Ubuntu allow local all all peer already), then SELECT pg_reload_conf(). Where peer authentication is not wanted, a password in the account's own ~/.pgpass (mode 0600), one line localhost:5432:*:monitoring:<the password>, does it instead: the password stays on the server.
No sudo.
The fingerprint of the server's host key, set in protocol:ssh:fingerprint (not needed when the Security settings trust the first SSH fingerprint presented).

Variables

Name	Label	Says	Default
`postgresql:port`	Port	the port of the cluster, which names its local socket	`5432`
`postgresql:database`	Database	the database psql connects to	`postgres`

Outputs

Name	Label	Unit	Per instance
`connections`	Client connections	—	no
`connections-percent`	Connections against max_connections	%	no
`active`	Connections running a query	—	no
`idle-in-transaction`	Connections idle in a transaction	—	no
`lock-waits`	Connections waiting for a lock	—	no

Watches

Name	Title	Kind	Defaults	Checked by default
`saturation`	Connections near max_connections	above a threshold, on `connections-percent`	warning above 80 %, critical above 90 %	yes
`idle-in-transaction`	Connections idle in a transaction	above a threshold, on `idle-in-transaction`	warning above 10, for 3 runs	no
`lock-waits`	Connections waiting for a lock	above a threshold, on `lock-waits`	warning above 5, for 3 runs	no

Limits

One cluster per probe: a server running two clusters takes two probes, each with its port.
A connection pooler (PgBouncer) in front of the cluster is not seen: only the connections it opens to PostgreSQL count.
The messages of psql are read in English (LC_ALL=C) to tell a cluster down from a refused role.
Not checked against a PostgreSQL: the views and functions are those of the PostgreSQL documentation, chapter "The Cumulative Statistics System" and "System Administration Functions".