pgbouncer.py

from collections import defaultdict
import collectd
import psycopg2

config = {
  'connection_string': 'dbname=pgbouncer user=np_db_user port=6432'
}

def get_stats():
  conn, cur = None, None
  stats = {}
  try:
    conn = psycopg2.connect(config['connection_string'])
    conn.autocommit = True
    cur = conn.cursor()
    stats = _get_stats(conn, cur)
  finally:
    if cur is not None:
      cur.close()
      if conn is not None:
        conn.close()

  return stats

def _get_stats(conn, cur):
  stats = defaultdict(dict)
  cur.execute("SHOW POOLS;")
  for database, user, cl_active, cl_waiting, sv_active, sv_idle, sv_used, sv_testd, sv_login, maxwait, pool_mode in cur.fetchall():
    stats[database].update({
      'cl_active': cl_active,
      'cl_waiting': cl_waiting,
      'sv_active': sv_active,
      'sv_idle': sv_idle,
      'maxwait': maxwait
    })

  return stats

def pgbouncer_read(data=None):
  stats = get_stats()
  if not stats:
    collectd.error('pgbouncer plugin: No info received')
    return

  for metric, value in stats.iteritems():
    val = collectd.Values(plugin='pgbouncer_info')
    val.type = 'gauge'
    val.values = [value]
    val.dispatch()

def pgbouncer_config(c):
  for child in c.children:
    value = child.values[0]
    config.update({child.key: value})

collectd.register_read(pgbouncer_read)
collectd.register_config(pgbouncer_config)

THnk you for this code,it really helped! Here is a modified generic version that can handle as many DBs as show pools can show

from collections import defaultdict
import psycopg2

config = {
'connection_string': 'dbname=pgbouncer user=master port=6432'
}

def get_stats():
conn, cur = None, None
stats = {}
try:
conn = psycopg2.connect(config['connection_string'])
conn.autocommit = True
cur = conn.cursor()
stats = _get_stats(conn, cur)
finally:
if cur is not None:
cur.close()
if conn is not None:
conn.close()

return stats

def _get_stats(conn, cur):
stats = defaultdict(dict)
cur.execute("SHOW POOLS;")
for database, user, cl_active, cl_waiting, sv_active, sv_idle, sv_used, sv_testd, sv_login, maxwait, maxwait_us, pool_mode in cur.fetchall():
stats[database].update({
'cl_active': cl_active,
'cl_waiting': cl_waiting,
'sv_active': sv_active,
'sv_idle': sv_idle,
'maxwait': maxwait
})

return stats

def print_stats(data=None):
stats = get_stats()
if not stats:
print('pgbouncer plugin: No info received')
return

for db, dbmetrics in stats.iteritems():
for metric,value in dbmetrics.iteritems():
type = 'gauge'
values = [value]
instance= db + "-" + metric
print("%s %s"%(instance, value))

def pgbouncer_read(data=None):
stats = get_stats()
if not stats:
collectd.error('pgbouncer plugin: No info received')
return

for db, dbmetrics in stats.iteritems():
for metric,value in dbmetrics.iteritems():
val = collectd.Values()
val.plugin = 'pgbouncer'
val.type = 'gauge'
val.values = [value]
val.plugin_instance = db
val.type_instance = metric
val.dispatch()

def pgbouncer_config(c):
global config
for child in c.children:
value = child.values[0]
config.update({child.key: value})

if name == "main":
config.update({'connection_string': 'dbname=pgbouncer user=master port=6432 password=thisisappss' })
print_stats()
else:
import collectd
collectd.register_config(pgbouncer_config)
collectd.register_read(pgbouncer_read)