postgresql_connections

#!/usr/bin/python

import munin
import psycopg2.psycopg1 as psycopg

class PostgresConnections(munin.Plugin):

    env_vars = {
        "host": "",
        "dbname": "template1",
        "user": "",
        "port": "",
        "password": "",
    }

    def _connect(self):
        dsn = " ".join(
            "%s=%s" % (arg, self.env_vars[arg]) 
            for arg in self.env if self.env[arg]
        )
        return psycopg.connect(dsn)
    
    def fetch(self):
        c = self._connect().cursor()
        c.execute("SELECT COUNT(*) FROM pg_stat_activity")
        yield ("connections.value", int(c.fetchone()[0]) - 1)
    
    def config(self):
        c = self._connect().cursor()
        c.execute("SHOW max_connections")
        maxconns = int(c.fetchone()[0])
        return [
            ('graph_title', 'PostgreSQL connections'),
            ('graph_args', '-l 0 --base 1000'),
            ('graph_vlabel', 'Active connections'),
            ('graph_category', 'PostgreSQL'),
            ('graph_info', 'Shows active PostgreSQL connections'),
            ('connections.label', 'Active connections'),
            ('connections.info', 'Active connections'),
            ('connections.type', 'GAUGE'),
            ('connections.warning', maxconns*0.7),
            ('connections.critical', maxconns*0.8),
        ]
        
    def autoconf(self):
        try:
            self._connect()
        except psycopg.OperationalError, e:
            return False
        return True

if __name__ == '__main__':
    munin.run(PostgresConnections)