banshee/services-collection/services/postgresql/ssh/transactions

PostgreSQL long transactions, by SSH

Returns the age of the oldest open transaction of a PostgreSQL cluster, of the oldest session idle inside a transaction, of the longest query running, and of the oldest prepared transaction, read by psql on the database server over SSH. A long transaction holds back the vacuum, which lets tables bloat; a forgotten prepared transaction does the same and holds its locks. Four 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 its own session left out, and pg_prepared_xacts:

SELECT json_build_object(
    'transaction', COALESCE(EXTRACT(EPOCH FROM max(now() - xact_start)), 0),
    'idle_in_transaction', COALESCE(EXTRACT(EPOCH FROM max(now() - state_change) FILTER (WHERE state LIKE 'idle in transaction%')), 0),
    'query', COALESCE(EXTRACT(EPOCH FROM max(now() - query_start) FILTER (WHERE state = 'active')), 0),
    'prepared', (SELECT COALESCE(EXTRACT(EPOCH FROM max(now() - prepared)), 0) FROM pg_prepared_xacts))
FROM pg_stat_activity WHERE backend_type = 'client backend' AND pid <> pg_backend_pid()

Each age is in seconds; 0 when there is no such transaction, session or query.
A session idle in a transaction is counted from its last change of state: the time it has waited, doing nothing, with its transaction open.
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 statistics views.

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. Without pg_monitor, the sessions of the other roles are not seen, and the ages read too young.

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
`transaction`	Oldest open transaction	s	no
`idle-in-transaction`	Oldest session idle in a transaction	s	no
`query`	Longest query running	s	no
`prepared`	Oldest prepared transaction	s	no

Watches

Name	Title	Kind	Defaults	Checked by default
`transaction`	Long transaction	above a threshold, on `transaction`	warning above 3600 s, critical above 14400 s	yes
`idle-in-transaction`	Session idle in a transaction	above a threshold, on `idle-in-transaction`	warning above 300 s, critical above 1800 s	yes
`query`	Long query	above a threshold, on `query`	warning above 600 s	no
`prepared`	Forgotten prepared transaction	above a threshold, on `prepared`	warning above 3600 s	yes

A nightly pg_dump holds one transaction open while it runs: raise "Long transaction" above its usual length on the probe.

Limits

The ages are counted in the cluster's own clock, from now(), the start of the collector's own transaction.
One cluster per probe; the sessions of every database count together.
Not checked against a PostgreSQL: the views are those of the PostgreSQL documentation (pg_stat_activity, pg_prepared_xacts).