pgpool_connections

#!/usr/bin/env python

import psutil, commands, os, sys, subprocess, re

"""
@author "Viktor Petersson"
@www "http://viktorpetersson.com"
"""

# Get variables from config
host = os.environ['host']
port = os.environ['port']
user = os.environ['user']
userpwd = os.environ['userpwd']
timeout = 5
pcppath = os.environ.get( 'pcppath', '/usr/bin/pcp_pool_status' )
pid_file = os.environ.get( 'pid_file', '' )
parent_pid = 0

if ( pid_file != '' ):
  from subprocess import call
  parent_pid = int(open(pid_file, 'r').read().split(b'\0',1)[0])

if len(sys.argv) == 2 and sys.argv[1] == "autoconf":
    print 'yes'

elif len(sys.argv) == 2 and sys.argv[1] == "config":
	print 'graph_title Pgpool-II'
	print 'graph_args --base 1000 -l 0'
	print 'graph_vlabel number of connections'
	print 'graph_category Postgresql'
	print 'graph_info This graph monitors the number of connections to Pgpool-II.'
	print 'wait.label Wait for connection'
	print 'wait.info The number of wait for connection request.'
	print 'idle.label Idle connections'
	print 'idle.info The number of idle in transaction.'
	print 'max.label num_init_children'
	print 'max.info Maximum number of connections (num_init_children).'

else:
    waitCount = 0
    idleCount = 0

    pl = psutil.process_iter()
    
    for item in pl:
        if ((item.ppid == parent_pid or parent_pid == 0) and "wait for connection request" in str(item) and not "PCP" in str(item)):
            waitCount += 1
        elif ((item.ppid == parent_pid or parent_pid == 0) and "idle in transaction" in str(item) and not "PCP" in str(item)):
            idleCount += 1
    
    if sys.version_info < ( 2, 7, 0 ):
        pcpobject = subprocess.Popen( [ pcppath, str(timeout), host, port, user, userpwd ], stdout=subprocess.PIPE )
        pcpstatus = pcpobject.communicate()[0]
    else:
        pcpstatus = subprocess.check_output(["pcp_pool_status", str(timeout), host, port, user, userpwd])

    match = re.search(r'(name\s+:\s+num_init_children)\nvalue:\s(\d+)', pcpstatus)
    if match:
        maxCount = match.group(2)
    else:
        maxCount = "NaN"

    print "wait.value " + str(waitCount)
    print "idle.value " + str(idleCount)
    print "max.value " + str(maxCount)