banshee/services-collection/services/postgresql/ssh/wraparound

PostgreSQL transaction ID wraparound, by SSH

Returns, for each database of a PostgreSQL cluster, the age of its oldest unfrozen transaction ID, and how far it has gone towards the wraparound limit, read by psql on the database server over SSH. Near that limit, PostgreSQL refuses every new transaction until the database is vacuumed: an outage that announces itself weeks ahead. One watch is offered, set on the probe. No password leaves banshee.

Versions

PostgreSQL 10 and later (pg_monitor); the query itself reads in every version. 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, age(datfrozenxid)), '{}'::json) FROM pg_database

{ "postgres" : 52000318, "template0" : 52000318, "app" : 187654321 }

A database is named by its name; every database is read, the templates included, which autovacuum keeps frozen too.
age is age(datfrozenxid), in transactions: how many transaction IDs ago the oldest row not yet frozen in the database was written.
wraparound-percent is that age against 2^31 (2,147,483,648), the distance at which such a row would pass from the past into the future. PostgreSQL stops accepting transactions a little before 100 %. Autovacuum starts freezing a database on its own when its age passes autovacuum_freeze_max_age (200 million by default, about 9 %): a percentage that keeps rising beyond that says autovacuum does not manage, a long transaction or an abandoned replication slot holding it back.
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: the query only reads a catalog.

Prerequisites

The same as postgresql/ssh/connections: an SSH account without write rights, psql in its path, a PostgreSQL role of its name 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 is readable by any role.

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
`age`	Age of the oldest unfrozen transaction ID	—	yes, per database
`wraparound-percent`	Way to wraparound	%	yes, per database

Watches

Name	Title	Kind	Defaults	Checked by default
`wraparound`	Transaction ID wraparound near	above a threshold, on `wraparound-percent`	warning above 60 %, critical above 75 %	yes

Limits

The wraparound of the multixact IDs (mxid_age(datminmxid)), rarer, is not read here.
Which table holds the database back is not said: SELECT relname, age(relfrozenxid) FROM pg_class ORDER BY 2 DESC in that database tells it.
Not checked against a PostgreSQL: the catalog and the function are those of the PostgreSQL documentation (pg_database, "Preventing Transaction ID Wraparound Failures").