connections

// The client connections of a PostgreSQL cluster, by SSH and psql on its server: how many against
// max_connections, how many run a query, sit idle inside a transaction, or wait for a lock.
// 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',
));

output('connections', 'number', array('label' => 'Client connections'));
output('connections-percent', 'number', array('label' => 'Connections against max_connections', 'unit' => '%'));
output('active', 'number', array('label' => 'Connections running a query'));
output('idle-in-transaction', 'number', array('label' => 'Connections idle in a transaction'));
output('lock-waits', 'number', array('label' => 'Connections waiting for a lock'));

watch.above('saturation', 'Connections near max_connections', 'connections-percent', array('warning' => 80, 'critical' => 90));
watch.above('idle-in-transaction', 'Connections idle in a transaction', 'idle-in-transaction', array('warning' => 10, 'for' => '3 runs', 'enabled' => false));
watch.above('lock-waits', 'Connections waiting for a lock', 'lock-waits', array('warning' => 5, 'for' => '3 runs', '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']));
}

// The client backends only: autovacuum, the WAL senders and the other background processes do not count
// against max_connections the same way. Reading the state of the sessions of other roles needs pg_monitor.
$found = query($port, $database, "SELECT json_build_object(
    'max', current_setting('max_connections')::int,
    'total', count(*),
    'active', count(*) FILTER (WHERE state = 'active'),
    'idle_in_transaction', count(*) FILTER (WHERE state LIKE 'idle in transaction%'),
    'lock_waits', count(*) FILTER (WHERE wait_event_type = 'Lock'))
FROM pg_stat_activity WHERE backend_type = 'client backend'");

value('connections', $found['total']);
value('connections-percent', number.round(100 * $found['total'] / $found['max'], 1));
value('active', $found['active']);
value('idle-in-transaction', $found['idle_in_transaction']);
value('lock-waits', $found['lock_waits']);