A script to collect PostgreSQL's metrics by ganglia

pg_gmetric.sh

#!/bin/bash

DBNAME="$1"
export PGPASSWORD="$2"
CACHE_FILE="/tmp/pg_gmetric"
OLD_CACHE_FILE="$CACHE_FILE.old"

pg_query() {
    _NAME="$1"
    _COMMAND="$2"
    psql --no-password --expanded --dbname="$DBNAME" --command="$_COMMAND" |
	grep "$_NAME" |
	awk '{print $3}'
}

report() {
    OPTIND_OLD=$OPTIND
    OPTIND=1

    _NAME="$1"
    _TYPE="$2"
    shift 2
    while getopts cdq: OPT
    do
	case $OPT in
	    c) _VALUE=$(cat $CACHE_FILE | grep "$_NAME" | awk '{print $3}')
	       ;;
	    d) [ ! -f $OLD_CACHE_FILE ] && exit
	       _VALUE=$(paste $OLD_CACHE_FILE $CACHE_FILE | grep "$_NAME" | awk '{print $6 - $3}')
	       ;;
	    q) _VALUE=$(pg_query "$_NAME" "$OPTARG")
	       ;;
	esac
    done

    OPTIND=$OPTIND_OLD

    gmetric --name "pg_$_NAME" --value "$_VALUE" --type "$_TYPE"
}

if [ -f "$CACHE_FILE" ]; then
    mv $CACHE_FILE $OLD_CACHE_FILE
fi

psql --no-password --expanded --dbname="$DBNAME" --command="SELECT xact_commit AS commits, xact_rollback AS rollbacks, round(blks_hit*100/(blks_hit+blks_read), 2) AS cache_hit_ratio FROM pg_stat_database WHERE datname = '$DBNAME' AND blks_read > 0" > $CACHE_FILE

report "db_size" "float" -q "SELECT pg_database_size('$DBNAME') AS db_size"
report "connections" "int32" -q "SELECT count(*) AS connections FROM pg_stat_activity"

report "cache_hit_ratio" "float" -c
report "commits" "int32" -d
report "rollbacks" "int32" -d