Initial commit

6 files changed, 422 insertions, 0 deletions

diff --git a/prometheus_pgbouncer_exporter/__init__.py b/prometheus_pgbouncer_exporter/__init__.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/prometheus_pgbouncer_exporter/__init__.py
diff --git a/prometheus_pgbouncer_exporter/__main__.py b/prometheus_pgbouncer_exporter/__main__.py
new file mode 100644
index 0000000..4e28416
--- /dev/null
+++ b/prometheus_pgbouncer_exporter/__main__.py
@@ -0,0 +1,3 @@
+from .cli import main
+
+main()
diff --git a/prometheus_pgbouncer_exporter/cli.py b/prometheus_pgbouncer_exporter/cli.py
new file mode 100644
index 0000000..7284baa
--- /dev/null
+++ b/prometheus_pgbouncer_exporter/cli.py
@@ -0,0 +1,103 @@
+#!/usr/bin/python3
+
+# Copyright (C) 2015  Christopher Baines <mail@cbaines.net>
+#
+# This program is free software: you can redistribute it and/or modify
+# it under the terms of the GNU Affero General Public License as published by
+# the Free Software Foundation, either version 3 of the License, or
+# (at your option) any later version.
+#
+# This program is distributed in the hope that it will be useful,
+# but WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+# GNU Affero General Public License for more details.
+#
+# You should have received a copy of the GNU Affero General Public License
+# along with this program.  If not, see <http://www.gnu.org/licenses/>.
+
+import logging
+from http.server import HTTPServer
+
+import configargparse
+from prometheus_client.core import REGISTRY
+
+from .utils import get_connection
+from .exposition import RequestHandler
+from .collectors import StatsCollector, ListsCollector, PoolsCollector, \
+    DatabasesCollector
+
+
+def main():
+    p = configargparse.ArgParser(
+        default_config_files=[
+            '/etc/prometheus-pgbouncer-exporter/config',
+        ],
+    )
+
+    p.add(
+        '-c',
+        '--config',
+        is_config_file=True,
+        help='config file path',
+    )
+
+    p.add(
+        '--port',
+        default='6432',
+        help="Port to connect to pgbouncer",
+        env_var='PGBOUNCER_PORT',
+    )
+    p.add(
+        '--user',
+        default='pgbouncer',
+        help="User to connect to pgbouncer with",
+        env_var='PGBOUNCER_USER',
+    )
+
+    p.add(
+        '--database',
+        action='append',
+        help="Databases to report metrics for, if this is not specified, all metrics will be reported",
+        env_var='PGBOUNCER_DATABASES',
+    )
+
+    options = p.parse_args()
+
+    logging.basicConfig(level=logging.DEBUG)
+
+    logging.info(p.format_values())
+
+    connection = get_connection(options.user, options.port)
+
+    REGISTRY.register(StatsCollector(
+        connection=connection,
+        databases=options.database,
+    ))
+
+    REGISTRY.register(PoolsCollector(
+        connection=connection,
+        databases=options.database,
+    ))
+
+    REGISTRY.register(DatabasesCollector(
+        connection=connection,
+        databases=options.database,
+    ))
+
+    REGISTRY.register(ListsCollector(
+        connection=connection,
+    ))
+
+    host = '0.0.0.0'
+    port = 9127
+
+    httpd = HTTPServer((host, port), RequestHandler)
+
+    logging.info("Listing on port %s:%d" % (host, port))
+
+    try:
+        httpd.serve_forever()
+    except KeyboardInterrupt:
+        pass
+    finally:
+        connection.close()
diff --git a/prometheus_pgbouncer_exporter/collectors.py b/prometheus_pgbouncer_exporter/collectors.py
new file mode 100644
index 0000000..75aef90
--- /dev/null
+++ b/prometheus_pgbouncer_exporter/collectors.py
@@ -0,0 +1,210 @@
+#!/usr/bin/python3
+
+# This program is free software: you can redistribute it and/or modify
+# it under the terms of the GNU Affero General Public License as published by
+# the Free Software Foundation, either version 3 of the License, or
+# (at your option) any later version.
+#
+# This program is distributed in the hope that it will be useful,
+# but WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+# GNU Affero General Public License for more details.
+#
+# You should have received a copy of the GNU Affero General Public License
+# along with this program.  If not, see <http://www.gnu.org/licenses/>.
+
+from prometheus_client.core import GaugeMetricFamily
+
+from .utils import get_data_by_named_column, get_data_by_named_row
+
+
+class PgBouncerCollector(object):
+    def __init__(self, connection, namespace='pgbouncer'):
+        self.connection = connection
+        self.namespace = namespace
+
+
+class NamedColumnCollector(PgBouncerCollector):
+    def get_labels_for_row(self, row):
+        raise NotImplementedError()
+
+    def collect(self):
+        # Each of the metrics is recorded per database, by calling add_metric
+        # with the database as the label on the appropriate GaugeMetricFamily.
+
+        # First, create the GaugeMetricFamily objects for each metric
+        gauges = {
+            key: GaugeMetricFamily(
+                name="%s_%s" % (self.namespace, name),
+                documentation="%s (%s)" % (documentation, key),
+                labels=self.labels,
+            )
+            for key, (name, documentation) in self.metrics.items()
+        }
+
+        rows = get_data_by_named_column(self.connection, self.query_parameter)
+
+        # Each row coresponds to the metrics for a particular database
+        for row in rows:
+            database = row['database']
+
+            if self.databases is not None and database not in self.databases:
+                continue
+
+            # Sort for deterministic ordering in the output
+            for key in sorted(self.metrics.keys()):
+                value = row[key]
+                label_values = self.get_labels_for_row(row)
+
+                gauges[key].add_metric(label_values, value)
+
+        for gauge in gauges.values():
+            yield gauge
+
+
+class ListsCollector(PgBouncerCollector):
+    metrics = {
+        'databases': (
+            'database_count',
+            "Number of databases",
+        ),
+        'users': (
+            'user_count',
+            "Number of users",
+        ),
+        'pools': (
+            'pool_count',
+            "Number of pools",
+        ),
+        'free_clients': (
+            'free_client_count',
+            "Number of free clients",
+        ),
+        'used_clients': (
+            'used_client_count',
+            "Number of used clients",
+        ),
+        'login_clients': (
+            'login_client_count',
+            "Number of clients in the login stats",
+        ),
+        'free_servers': (
+            'free_server_count',
+            "Number of free servers",
+        ),
+        'used_servers': (
+            'used_server_count',
+            "Number of used servers",
+        ),
+        'dns_names': (
+            'dns_name_count',
+            "",
+        ),
+        'dns_zones': (
+            'dns_zone_count',
+            "",
+        ),
+        'dns_queries': (
+            'dns_querie_count',
+            "",
+        ),
+        'dns_pending': (
+            'dns_pending_count',
+            "",
+        ),
+    }
+
+    def collect(self):
+        data = get_data_by_named_row(self.connection, 'LISTS')
+
+        for key, (name, documentation) in sorted(
+            self.metrics.items(), key=lambda x: x[0]
+        ):
+            yield GaugeMetricFamily(
+                name="%s_%s" % (self.namespace, name),
+                documentation="%s (%s)" % (documentation, key),
+                value=data[key],
+            )
+
+
+class StatsCollector(NamedColumnCollector):
+    query_parameter = 'STATS'
+
+    labels = ['database']
+
+    metrics = {
+        'total_requests': (
+            'requests_total',
+            "Total number of SQL requests pooled by pgbouncer",
+        ),
+        'total_received': (
+            'received_bytes_total',
+            "Total volume in bytes of network traffic received by pgbouncer",
+        ),
+        'total_sent': (
+            'sent_bytes_total',
+            "Total volume in bytes of network traffic sent by pgbouncer",
+        ),
+        'total_query_time': (
+            'query_microseconds_total',
+            "Total number of microseconds spent by pgbouncer when actively connected to PostgreSQL",
+        ),
+    }
+
+    def __init__(self, databases, *args, **kwargs):
+        super().__init__(*args, **kwargs)
+
+        self.databases = databases
+
+    def get_labels_for_row(self, row):
+        return [row['database']]
+
+
+class PoolsCollector(NamedColumnCollector):
+    query_parameter = 'POOLS'
+
+    labels = ['database', 'user']
+
+    metrics = {
+        'cl_active': (
+            'active_client_count',
+            "Client connections that are linked to server connection and can process queries",
+        ),
+        'cl_waiting': (
+            'waiting_client_count',
+            "Client connections have sent queries but have not yet got a server connection",
+        ),
+    }
+
+    def __init__(self, databases, *args, **kwargs):
+        super().__init__(*args, **kwargs)
+
+        self.databases = databases
+
+    def get_labels_for_row(self, row):
+        return [row['database'], row['user']]
+
+
+class DatabasesCollector(NamedColumnCollector):
+    query_parameter = 'DATABASES'
+
+    labels = ['database']
+
+    metrics = {
+        'pool_size': (
+            'pool_size',
+            "",
+        ),
+        'reserve_pool': (
+            'reserve_pool_count',
+            "",
+        ),
+    }
+
+    def __init__(self, databases, *args, **kwargs):
+        super().__init__(*args, **kwargs)
+
+        self.databases = databases
+
+    def get_labels_for_row(self, row):
+        return [row['database']]
diff --git a/prometheus_pgbouncer_exporter/exposition.py b/prometheus_pgbouncer_exporter/exposition.py
new file mode 100644
index 0000000..4396b73
--- /dev/null
+++ b/prometheus_pgbouncer_exporter/exposition.py
@@ -0,0 +1,56 @@
+#!/usr/bin/python3
+
+# Copyright (C) 2015  Christopher Baines <mail@cbaines.net>
+#
+# This program is free software: you can redistribute it and/or modify
+# it under the terms of the GNU Affero General Public License as published by
+# the Free Software Foundation, either version 3 of the License, or
+# (at your option) any later version.
+#
+# This program is distributed in the hope that it will be useful,
+# but WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+# GNU Affero General Public License for more details.
+#
+# You should have received a copy of the GNU Affero General Public License
+# along with this program.  If not, see <http://www.gnu.org/licenses/>.
+
+from prometheus_client.exposition import MetricsHandler
+
+index_page = """
+<!doctype html>
+
+<html lang="en">
+<head>
+  <meta charset="utf-8">
+
+  <title>Prometheus PgBouncer Exporter</title>
+</head>
+
+<body>
+  <h1>PgBouncer exporter for Prometheus</h1>
+
+  <p>
+    This is a simple exporter for PgBouncer that makes several metrics
+    available to Prometheus.
+  </p>
+
+  <p>
+    Metrics are exported from the SHOW LISTS, STATS, POOLS and DATABASE comand
+    output.
+  </p>
+
+  <a href="/metrics">View metrics</a>
+</body>
+</html>
+"""
+
+
+class RequestHandler(MetricsHandler):
+    def do_GET(self):
+        if self.path == "/metrics":
+            return super().do_GET()
+        else:
+            self.send_response(200)
+            self.end_headers()
+            self.wfile.write(index_page.encode('UTF-8'))
diff --git a/prometheus_pgbouncer_exporter/utils.py b/prometheus_pgbouncer_exporter/utils.py
new file mode 100644
index 0000000..6789290
--- /dev/null
+++ b/prometheus_pgbouncer_exporter/utils.py
@@ -0,0 +1,50 @@
+#!/usr/bin/python3
+
+# This program is free software: you can redistribute it and/or modify
+# it under the terms of the GNU Affero General Public License as published by
+# the Free Software Foundation, either version 3 of the License, or
+# (at your option) any later version.
+#
+# This program is distributed in the hope that it will be useful,
+# but WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+# GNU Affero General Public License for more details.
+#
+# You should have received a copy of the GNU Affero General Public License
+# along with this program.  If not, see <http://www.gnu.org/licenses/>.
+
+import psycopg2
+
+
+def get_connection(user, port):
+    connection = psycopg2.connect(
+        database='pgbouncer',
+        user=user,
+        port=port,
+    )
+
+    # pgbouncer does not support transactions (as it does not make sense to),
+    # so don't start a transaction when connecting
+    connection.set_session(autocommit=True)
+
+    return connection
+
+
+def get_data_by_named_column(connection, key):
+    with connection.cursor() as cursor:
+        cursor.execute('SHOW %s;' % key)
+
+        rows = cursor.fetchall()
+        column_names = list(column.name for column in cursor.description)
+
+    return [
+        dict(zip(column_names, row))
+        for row in rows
+    ]
+
+
+def get_data_by_named_row(connection, key):
+    with connection.cursor() as cursor:
+        cursor.execute('SHOW %s;' % key)
+
+        return dict(cursor.fetchall())