Munin contributed plugins

#!/bin/bash

#

# Plugin to monitor PostgreSQL Database Ratios (blocks read X blocks hit)

#

# Author:

#    Guilherme Augusto da Rocha Silva <gars.dba@gmail.com>

#

# Created:

#    5th of november 2007

#

# Usage:

#    Place in /etc/munin/plugins/ (or link it there using ln -s)

#

# Parameters:

#    config   (required)

#

# General info:

#    Require permission for database access and read (no writes are processed).

#    Recommended user is PostgreSQL database owner (default: postgres).

#

# Log info:

# 2007/11/30 - Review on comments

#

dbserver='localhost'

dbuser='postgres'

if [ "$1" = "config" ]; then

   echo 'graph_args --base 1000 --lower-limit 0 --upper-limit 100'

   echo 'graph_category db'

   echo 'graph_info Shows each database read/hit (%) on the PostgreSQL Server.'

   echo 'graph_scale no'

   echo 'graph_title PostgreSQL Database Hit/Read Ratios'

   echo 'graph_vlabel % of blocks read and hits'

   psql -h ${dbserver} -U ${dbuser} -tc "SELECT datname FROM pg_stat_database WHERE datname != 'template0' ORDER BY 1;" | while read name

   do

      test -z "${name}" && continue

      echo ${name}'.label '${name}

      echo ${name}'.type GAUGE'

      echo ${name}'.min 0'

      echo ${name}'.max 100'

      if [ "${name}" == "template0" ]; then

         echo ${name}'.info PostgreSQL template database.'

      elif [ "${name}" == "template1" ]; then

         echo ${name}'.info PostgreSQL and/or user template database.'

      elif [ "${name}" == "postgres" ]; then

         echo ${name}'.info User postgres database.'

      else

         echo ${name}'.info User defined database.'

         echo ${name}'.critical 50:min'

         echo ${name}'.warning 75:min'

      fi

   done

   exit 0

fi

psql -h ${dbserver} -U ${dbuser} -tc "SELECT datname||'.value '||(CASE WHEN (blks_hit > 0) THEN ROUND((blks_hit::NUMERIC / (blks_hit + blks_read)::NUMERIC) * 100, 2) ELSE 0 END)::TEXT FROM pg_stat_database WHERE datname != 'template0' ORDER BY datname;"