#!/bin/sh
#
# Plugin to monitor postgresql queries
# 
#
# Parameters:
# 	
# 	config
# 	autoconf
#
# Configuration variables
#
#	PGHOST
#	PGPORT
#	PGDATABASE
#	PGUSER
#
#	(See libpq documentation for more.)
#	Note that PGDATABASE will default to 'template1' in this plugin, and
#	without PGHOST it will try ident authentication with the local server,
#	as the user that the plugin is running as.
#
# Configuration example:
#
#	# Use local server, ident authentication with the 'postgres' user.
#	[postgres_*]
#	user postgres
#
#	# Use local server, TCP authentication with a username and password.
#	[postgres_*]
#	env.PGHOST localhost
#	env.PGUSER someuser
#	env.PGPASSWORD somepassword
#
# Revision history:
#
# 2005-10-03 v0.0001  Vajtsz
#	this is my first munin plugin sorry for dummy code
# 2007-12-14 v0.0002  Tim Retout <tim.retout@credativ.co.uk>
#	Use libpq environment variables for configuration. Document the two
#	most common cases.
#
#
#
#%# family=auto
#%# capabilities=autoconf

# It is not guaranteed that a database exists with the same name as the user,
# but template1 should generally be there.
if [ -z "$PGDATABASE" ]; then
	PGDATABASE="template1"
fi

psql_comm='psql -c'

if [ "$1" = "autoconf" ]; then
        psql  --version 2>/dev/null >/dev/null
        if [ $? -eq 0 ]
        then
                $psql_comm  2>/dev/null >/dev/null
                if [ $? -eq 0 ]
                then
                        echo yes
                        exit 0
                else
                        echo "no (could not connect to psql)"
                fi
        else
                echo "no (psql not found)"
        fi
        exit 1
fi

if [ "$1" = "config" ]; then
	echo 'graph_title PostgreSQL queries 1.'
	echo 'graph_vlabel Count'
	echo 'graph_category PostgreSQL '
	echo 'graph_args --base 1000'

	echo 'seqsel.label  sequential select count'
	echo 'seqsel.draw LINE2'	
	echo 'seqsel.type COUNTER'			

	echo 'sqlselrows.label Sequential select rows returned'
	echo 'sqlselrows.draw LINE2'	
	echo 'sqlselrows.type COUNTER'			

	echo 'idxsel.label Index select count'
	echo 'idxsel.draw LINE2'	
	echo 'idxsel.type COUNTER'			

	echo 'idxselrows.label Index select rows returned'
	echo 'idxselrows.draw LINE2'	
	echo 'idxselrows.type COUNTER'			


	echo 'update.label Update count'
	echo 'update.draw LINE2'	
	echo 'update.type COUNTER'			


	exit 0
fi

ebbe=`$psql_comm 'select sum(seq_scan),sum(seq_tup_read),sum(idx_scan),sum(idx_tup_fetch)  from pg_stat_all_tables'`
ebbe1=`echo $ebbe | awk '{print($9)}'`
ebbe2=`echo $ebbe | awk '{print($11)}'`
ebbe3=`echo $ebbe | awk '{print($13)}'`
ebbe4=`echo $ebbe | awk '{print($15)}'`
/usr/bin/printf "seqsel.value $ebbe1\n"
/usr/bin/printf "sqlselrows.value $ebbe2\n"
/usr/bin/printf "idxsel.value $ebbe3\n"
/usr/bin/printf "idxselrows.value $ebbe4\n"

ebbe=`$psql_comm 'select sum(n_tup_ins),sum(n_tup_upd),sum(n_tup_del) from pg_stat_all_tables'`
ebbe1=`echo $ebbe | awk '{print($7)}'`
ebbe2=`echo $ebbe | awk '{print($9)}'`
ebbe3=`echo $ebbe | awk '{print($11)}'`

/usr/bin/printf "update.value $ebbe2\n"


