wraparound

// The transaction ID wraparound of a PostgreSQL cluster, by SSH and psql on its server: for each database, the
// age of its oldest unfrozen transaction ID, against the 2^31 that PostgreSQL may never reach. Near that
// limit, PostgreSQL refuses every new transaction until the database is vacuumed.
// 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('database', 'Databases');
output('age', 'number', array('label' => 'Age of the oldest unfrozen transaction ID', 'per' => 'database'));
output('wraparound-percent', 'number', array('label' => 'Way to wraparound', 'unit' => '%', 'per' => 'database'));

watch.above('wraparound', 'Transaction ID wraparound near', 'wraparound-percent', array('warning' => 60, 'critical' => 75));

// 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']));
}

// {"postgres": 52000318, "template0": 52000318, "app": 187654321}: age(datfrozenxid), in transactions, for
// every database, the templates included, which autovacuum keeps frozen too.
$ages = query($port, $database, "SELECT COALESCE(json_object_agg(datname, age(datfrozenxid)), '{}'::json) FROM pg_database");

// 2147483648, 2^31: the distance in transaction IDs at which an unfrozen row would turn from past to future.
foreach ($ages as $name => $age) {
    value('age', $age, $name);
    value('wraparound-percent', number.round(100 * $age / 2147483648, 1), $name);
}