databases

// The size of each database of a PostgreSQL cluster, by SSH and psql on its server, and how fast it grows.
// 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('size', 'number', array('label' => 'Size', 'unit' => 'MiB', 'per' => 'database'));

watch.increase('growth', 'Database growing fast', 'size', array('warning' => 10240, 'window' => '1 d'));
watch.above('large', 'Database too large', 'size', array('enabled' => false));
watch.missing('missing', 'Database missing', 'size', 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']));
}

// {"postgres": 7623471, "app": 1073741824}, in bytes: each database one may connect to, the templates left
// out. The size of a database needs CONNECT on it, or pg_monitor (through pg_read_all_stats).
$sizes = query($port, $database, "SELECT COALESCE(json_object_agg(datname, pg_database_size(datname)), '{}'::json)
FROM pg_database WHERE datallowconn AND NOT datistemplate");

foreach ($sizes as $name => $bytes) {
    value('size', number.round($bytes / 1048576, 1), $name);
}