Publish PostgreSQL metrics to Ganglia

postgres_gmetric.sh

#!/bin/bash

database=$1

if [ "$database" == "" ] ; then
  echo "Usage: $0 <database>" > /dev/stderr
  exit 1
fi
if [ ! -r /home/darwin/check_postgres/check_postgres.pl ] ; then
  exit 1
fi

function publish() {
  action=$1
  echo -n "$action: "
  value=`/home/darwin/check_postgres/check_postgres.pl --db $database --host localhost --action $action --output simple` || return
  echo "$value"
  type=$2
  units=$3
  if [ "units" != "" ] ; then
    units="--units $units"
  fi
  # Type is either string|int8|uint8|int16|uint16|int32|uint32|float|double
  gmetric --name "postgres_$action" --value $value --type $type $units --dmax=240
}

#publish autovac_freeze      # Checks how close databases are to autovacuum_freeze_max_age.
publish backends            float connections # Number of connections, compared to max_connections.
publish bloat               float bytes # Check for table and index bloat.
#publish checkpoint          # Checks how long since the last checkpoint
#publish custom_query        # Run a custom query.
publish database_size       float bytes # Report if a database is too big.
#publish dbstats             # Returns stats from pg_stat_database: Cacti output only
#publish disk_space          # Checks space of local disks Postgres is using.
#publish fsm_pages           uint16 pages # Checks percentage of pages used in free space map. (obsolete in 8.4?)
#publish fsm_relations       uint16 relations # Checks percentage of relations used in free space map. (obsolete in 8.4?)
publish index_size          float bytes # Checks the size of indexes only.
#publish last_analyze        # Check the maximum time in seconds since any one table has been analyzed.
#publish last_autoanalyze    # Check the maximum time in seconds since any one table has been autoanalyzed.
#publish last_autovacuum     # Check the maximum time in seconds since any one table has been autovacuumed.
#publish last_vacuum         # Check the maximum time in seconds since any one table has been vacuumed.
#publish listener            # Checks for specific listeners.
publish locks               float locks # Checks the number of locks.
#publish logfile             # Checks that the logfile is being written to correctly.
#publish query_runtime       # Check how long a specific query takes to run.
publish query_time          float seconds # Checks the maximum running time of current queries.
publish relation_size       float bytes # Checks the size of tables and indexes.
#publish replicate_row       # Verify a simple update gets replicated to another server.
#publish sequence            # Checks remaining calls left in sequences.
#publish settings_checksum   # Check that no settings have changed since the last check.
publish table_size          float bytes # Checks the size of tables only.
publish timesync            float seconds # Compare database time to local system time.
publish txn_idle            float transactions # Checks the maximum "idle in transaction" time.
publish txn_time            float seconds # Checks the maximum open transaction time.
publish txn_wraparound      float txn # See how close databases are getting to transaction ID wraparound.
#publish version             # Check for proper Postgres version.
publish wal_files           float files # Check the number of WAL files in the pg_xlog directory