banshee/services-collection/services/postgresql/ssh/replication-slots

PostgreSQL replication slots, by SSH

Returns, for each replication slot of a PostgreSQL cluster, whether a consumer is attached to it, and the WAL it holds back, read by psql on the database server over SSH. A slot that nobody reads any more (a standby gone, a logical subscriber dropped) keeps every WAL file from its position on, until the disk is full. Three watches are offered, each one set on the probe. No password leaves banshee.

Versions

PostgreSQL 10 and later (pg_current_wal_lsn, 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_agg(json_build_object(
    'name', slot_name, 'active', active,
    'retained', pg_wal_lsn_diff(CASE WHEN pg_is_in_recovery() THEN pg_last_wal_receive_lsn() ELSE pg_current_wal_lsn() END, restart_lsn))), '[]'::json)
FROM pg_replication_slots

A slot is named by its name; physical and logical slots alike.
active is yes while a consumer (a standby, a logical subscriber, pg_receivewal) is attached to it, no otherwise.
retained is the WAL from the slot's restart_lsn to the WAL written on the server (received, on a standby), in MiB: what the server must keep for it. A slot never used has no restart_lsn, holds nothing back yet, and returns no retained.
A cluster without any slot returns nothing, and raises nothing.
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 view and WAL positions.

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.

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
`active`	Consumer attached	—	yes, per slot
`retained`	WAL held back	MiB	yes, per slot

Watches

Name	Title	Kind	Defaults	Checked by default
`inactive`	Slot without consumer	state to avoid, on `active`	warning on `no`, for 3 runs	yes
`retained`	Slot holding back WAL	above a threshold, on `retained`	warning above 1024 MiB, critical above 10240 MiB	yes
`missing`	Slot missing	expected instances, on `active`	no name given: it raises nothing until names are set on the probe	no

Set the thresholds of "Slot holding back WAL" against the free space of the disk that holds pg_wal; max_slot_wal_keep_size (PostgreSQL 13 and later), when set, caps what a slot may hold back, at the price of losing the slot.

Limits

The free space of the disk is not read here: the collectors of the file systems watch it (linux/ssh/filesystems).
The column wal_status of PostgreSQL 13 and later (reserved, extended, unreserved, lost) is not read, so that the collector stays the same from version 10 on.
Not checked against a PostgreSQL: the view and functions are those of the PostgreSQL documentation (pg_replication_slots, "Backup Control Functions").