replication-slots

// The replication slots of a PostgreSQL cluster, by SSH and psql on its server: for each slot, whether a
// consumer is attached to it, and the WAL it holds back. A slot nobody reads keeps every WAL file from its
// position on, until the disk is full.
// psql connects over the local socket as the PostgreSQL role named like the SSH account (peer authentication,
// or a ~/.pgpass of that account): no password leaves banshee, none is written on a command line.
need('protocol:ssh');
$port = variable('postgresql:port', 'integer', array(
    'label'   => 'Port',
    'help'    => 'The port of the PostgreSQL cluster, which names its local socket: 5432, or 5433 for a second cluster on the same server.',
    'default' => 5432,
));
$database = variable('postgresql:database', 'text', array(
    'label'   => 'Database',
    'help'    => 'The database psql connects to: any one the role may connect to.',
    'default' => 'postgres',
));

per('slot', 'Replication slots');
output('active', 'text', array('label' => 'Consumer attached', 'per' => 'slot'));
output('retained', 'number', array('label' => 'WAL held back', 'unit' => 'MiB', 'per' => 'slot'));

watch.state('inactive', 'Slot without consumer', 'active', array('warning' => array('no'), 'for' => '3 runs'));
watch.above('retained', 'Slot holding back WAL', 'retained', array('warning' => 1024, 'critical' => 10240));
watch.missing('missing', 'Slot missing', 'active', array('enabled' => false));

// One query through psql, which writes one line of JSON. LC_ALL=C keeps its messages in English. A cluster
// whose socket is absent, or that refuses the connection, is not running: critical.
function query($port, $database, $sql) {
    $read = ssh.run('LC_ALL=C psql -X -A -t -q -v ON_ERROR_STOP=1', arguments=array('-p', '' . $port, '-d', $database, '-c', $sql));
    if ($read['exit'] == 2 && list.count(text.match($read['errors'], '(?i)connection refused|no such file or directory')) > 0) {
        fail('critical', 'PostgreSQL is not running', $read['errors']);
    }
    if ($read['exit'] != 0) {
        fail('misconfigured', 'psql failed', $read['errors']);
    }
    return json.read(text.trim($read['output']));
}

// One object per slot: the WAL held back is the distance from the slot's restart_lsn to the WAL written here
// (received, on a standby); restart_lsn is null for a slot never used, which holds nothing back yet.
$slots = query($port, $database, "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");

foreach ($slots as $slot) {
    $active = 'no';
    if ($slot['active']) {
        $active = 'yes';
    }
    value('active', $active, $slot['name']);
    if ($slot['retained'] != '') {
        value('retained', number.round($slot['retained'] / 1048576, 1), $slot['name']);
    }
}