banshee/services-collection/services/postgresql/ssh/databases

PostgreSQL databases, by SSH

Returns the size of each database of a PostgreSQL cluster, read by psql on the database server over SSH, and watches how fast it grows. Three watches are offered, each one set on the probe. No password leaves banshee.

Versions

PostgreSQL 10 and later (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:

SELECT COALESCE(json_object_agg(datname, pg_database_size(datname)), '{}'::json)
FROM pg_database WHERE datallowconn AND NOT datistemplate

{ "postgres" : 7623471, "app" : 1073741824 }

A database is named by its name; the templates, and the databases nobody may connect to, are left out.
size is the size on disk of the database, in MiB, its indexes and TOAST included.
"Database growing fast" raises when a database grows by more than the threshold within the window: by default 10 GiB in a day. A database that shrinks (a VACUUM FULL, a purge) starts the window again.
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 stops the collector misconfigured, with what psql said.
Nothing is written: pg_database_size only adds up the size of the files.

Prerequisites

The same as postgresql/ssh/connections: an SSH account without write rights, psql in its path, a PostgreSQL role of its name member of pg_monitor, allowed in by peer authentication over the local socket (or by its own ~/.pgpass), no sudo, and the fingerprint of the server's host key. pg_database_size needs CONNECT on each database, or pg_monitor; without either, the query fails and the collector stops misconfigured.

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
`size`	Size	MiB	yes, per database

Watches

Name	Title	Kind	Defaults	Checked by default
`growth`	Database growing fast	increase, on `size`	warning beyond 10240 MiB in 1 d	yes
`large`	Database too large	above a threshold, on `size`	no threshold: set one on the probe	no
`missing`	Database missing	expected instances, on `size`	no name given: it raises nothing until names are set on the probe	no

Limits

The space left on the disk is not read here: the collectors of the file systems watch it (linux/ssh/filesystems).
pg_database_size walks the files of each database: on a cluster of many thousands of tables, the query takes a moment.
Not checked against a PostgreSQL: the function and the catalog are those of the PostgreSQL documentation ("Database Object Management Functions", pg_database).