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

PostgreSQL replication, by SSH

Returns the streaming replication of a PostgreSQL cluster, from either side, read by psql on the database server over SSH. On a primary, one link per standby connected: its state, its synchronous state, and how far its replay lags behind, in WAL and in time. On a standby, one link, upstream: the state of its WAL receiver, and the WAL received but not replayed yet. The role of the server, and the number of links, so that a standby that is gone says so. Seven watches are offered, each one set on the probe. No password leaves banshee.

Versions

PostgreSQL 10 and later (pg_current_wal_lsn, replay_lag, 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. One query reads pg_is_in_recovery(), pg_stat_replication, pg_stat_wal_receiver and the WAL positions, and writes one line of JSON:

{"standby" : false, "standbys" : [{"name" : "pg2", "address" : "192.0.2.21", "pid" : 4211, "state" : "streaming", "sync" : "async", "bytes" : 0, "seconds" : null}], "receiver" : null, "backlog" : 0}

On a primary, one link per row of pg_stat_replication, named by the standby's application_name, or by its address when it keeps the default name walreceiver. state is startup, catchup, streaming, backup or stopping; sync is async, potential, sync or quorum. lag-size is the WAL written on the primary and not yet replayed on the standby, in MiB; lag is replay_lag, in seconds, which PostgreSQL leaves empty once an idle standby has caught up: it is then not returned.
On a standby, the link upstream: state is the status of its WAL receiver (streaming, starting, waiting, restarting, stopping), or stopped when none runs; lag-size is the WAL received and not yet replayed. The standbys that cascade from it are returned beside it, as on a primary.
links is the number of links returned: on a primary, the standbys connected; on a standby, its upstream and its cascading standbys. A standby that disconnects disappears from pg_stat_replication: "No replication link" raises when none is left, and "Standby missing" names the one gone, once its name is set on the probe.
role is primary or standby: after a failover, "Role changed" raises once the expected role is set on the probe.
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 views 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. Without pg_monitor, pg_stat_replication and pg_stat_wal_receiver hide their details, and the states read empty. The role is created on the primary and reaches the standbys with the replication; pg_hba.conf is not replicated, and needs its line on each server.

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
`role`	Role	—	no
`links`	Replication links	—	no
`state`	State	—	yes, per link
`sync`	Synchronous state	—	yes, per standby, on a primary
`lag-size`	WAL not replayed	MiB	yes, per link
`lag`	Replay lag	s	yes, per standby, on a primary

Watches

Name	Title	Kind	Defaults	Checked by default
`no-link`	No replication link	below a threshold, on `links`	critical below 1	yes
`not-streaming`	Replication not streaming	expected value, on `state`	critical when anything but `streaming`, for 3 runs	yes
`lag-size`	Replication behind	above a threshold, on `lag-size`	warning above 64 MiB, critical above 1024 MiB	yes
`lag`	Replication late	above a threshold, on `lag`	warning above 60 s, critical above 300 s	yes
`role`	Role changed	expected value, on `role`	no role given: set `primary` or `standby` on the probe	no
`sync`	Synchronous state changed	expected value, on `sync`	no state given: set `sync` on the probe for a synchronous standby	no
`missing`	Standby missing	expected instances, on `state`	no name given: it raises nothing until names are set on the probe	no

Deploy the collector on each server of the cluster: the primary sees its standbys, each standby its own receiver. A server alone, without replication, has no link: "No replication link" raises there, as it should, since the probe was put on it to watch a replication.

Limits

The replication slots, and the WAL an inactive slot holds back, are read by postgresql/ssh/replication-slots.
Logical replication (publications, subscriptions) is not read here: its WAL senders show in pg_stat_replication as links like the others.
A standby fed only by WAL archives (no streaming) reads stopped.
A standby named upstream, or two of one name, keep their own, the second carrying its process id, which changes at each reconnection.
Not checked against a PostgreSQL: the views and functions are those of the PostgreSQL documentation (pg_stat_replication, pg_stat_wal_receiver, "Recovery Control Functions").