pgpool_connections

#!/usr/bin/env python

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

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

def processname(process):
    # Note that in psutil 2.0, various
    # properties were turned into methods
    if callable(process.cmdline):
        cmdline = process.cmdline()
    else:
        cmdline = process.cmdline
    if len(cmdline) == 0:
        if callable(process.name):
            return process.name()
        else:
            return process.name
    else:
        #return cmdline[0]
	return " ".join(cmdline)


# Get variables from config
host = os.environ['host']
port = os.environ['port']
user = os.environ['user']
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:
        procname = processname(item)
        if ((item.ppid() == parent_pid or parent_pid == 0) and "wait for connection request" in procname and not "PCP" in procname):
            waitCount += 1
        elif ((item.ppid() == parent_pid or parent_pid == 0) and "idle" in procname and not "PCP" in procname):
            idleCount += 1
    
    if sys.version_info < ( 2, 7, 0 ):
        pcpobject = subprocess.Popen( [ pcppath, "-h", host, "-p", port, "-U", user, "-w" ], stdout=subprocess.PIPE )
        pcpstatus = pcpobject.communicate()[0]
    else:
        pcpstatus = subprocess.check_output(["pcp_pool_status", "-h", host, "-p", port, "-U", user, "-w"])

    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)