Quick n' dirty script to monitor PostgreSQL connectivity and connect latency and post metrics to AWS Cloudwatch

psql_check.sh

#!/bin/bash

export PGPASSWORD='password'

IFS=':'

echo ''
date '+%F %T'

for HOST in www.{foo,bar}.com; do
  for PORT in 543{2,5}; do
    PUT_DATA=0
    NODE="${HOST}:${PORT}"

    echo ''
    echo "Checking $HOST:$PORT"

    /usr/bin/time --format 'code:%x\ntime:%e' \
          psql --host ${HOST} --port ${PORT} --username diagnostic --no-password \
            --tuples-only --command 'select version();' \
            postgres 2>&1 | while read key value; do

      case $key in
        code)
          if [[ $value -eq 0 ]]; then
            PUT_DATA=1
          else
            echo "psql exited with code ${value}"
          fi
          ;;
        time)
          if [[ $PUT_DATA -eq 1 ]]; then
            echo "psql connected to $NODE with latency $value"

            aws cloudwatch put-metric-data \
              --namespace="Org/Connection" \
              --metric-name="DBConnLatency" \
              --unit="Seconds" \
              --value="$value" \
              --dimensions="Location=Office,Host=$(hostname),Target=$NODE"

            echo "posted metric to cloudwatch"
          fi
          ;;
        *)
          if [[ -n $DEBUG ]]; then
            echo "$key:$value"
          fi
          ;;
      esac
    done
  done
done