6010 check pqsql

check_psqlserver

+#!/bin/bash
+# ======================================================================
+#
+# !!! WORK IN PROGRESS !!! DO NOT USE YET !!!
+#
+# Check PSQL SERVER
+#
+# requirements:
+# - psql client
+#
+# installation:
+# - execute check_psqlserver -i
+#
+# ----------------------------------------------------------------------
+# 2023-06-xx  v0.0  <axel.hahn@unibe.ch>
+# ======================================================================
+
+
+. `dirname $0`/inc_pluginfunctions
+_version="0.1"
+
+# --- set HOME
+HOME=/etc/icinga2-passive-client
+# HOME=/etc/icingaclient
+
+# --- other vars...
+cfgfile=$HOME/.psql.conf
+myuser=icingamonitor
+datafile=/tmp/psqlvars.out
+
+# new line
+NL="
+"
+
+lastvalue=
+out=" "
+
+# ----------------------------------------------------------------------
+# FUNCTIONS
+# ----------------------------------------------------------------------
+
+# uninstall database user
+function _uninstall(){
+    echo UNINSTALL ...
+    su - postgres -c "psql -c \"DROP USER ${myuser};\""
+    unset PGHOST
+    unset PGUSER
+    unset PGPASSWORD
+}
+
+# (re)install database user for monitoring
+function _install(){
+    echo INSTALLING ...
+    local pwlength=64
+    echo "- check psql connection..."
+    su postgres -c "psql -V postgres"
+    if [ $? -ne 0 ]; then
+        echo "ERROR: psql connect without password failed."
+        exit 1
+    fi
+
+    echo "- creating mysql user $myuser@localhost with random password ($pwlength chars)..."
+    mypw=$( head /dev/urandom | tr -dc A-Za-z0-9 | head -c $pwlength )
+
+    SQL1="CREATE USER ${myuser} WITH PASSWORD '${mypw}' CONNECTION LIMIT 5";
+    SQL2="GRANT pg_monitor TO ${myuser};"
+    # SQL2="GRANT MONITOR_QUERIES TO ${myuser};"
+    su - postgres -c "psql -c \"${SQL1}\""
+    if [ $? -ne 0 ]; then
+        echo "ERROR: psql command to create user failed."
+        exit 1
+    fi
+    echo "- grant ..."
+    su - postgres -c "psql -c \"${SQL2}\""
+    if [ $? -ne 0 ]; then
+        echo "ERROR: psql command to grant permissions failed."
+        # exit 1
+    fi
+
+    echo "- creating config file $cfgfile ... "
+    cat  >$cfgfile <<EOF
+#
+# generated on `date`
+#
+export PGUSER=${myuser}
+export PGPASSWORD=${mypw}
+export PGHOST=localhost
+export PGDATABASE=postgres
+EOF
+    ls -l $cfgfile
+    if [ $? -ne 0 ]; then
+        echo "ERROR: creation of config file failed."
+        exit 1
+    fi
+}
+
+function _usage(){
+    cat <<EOH
+______________________________________________________________________
+
+CHECK POSTGRESQL SERVER :: v${_version}
+
+(c) Institute for Medical Education - Univerity of Bern
+Licence: GNU GPL 3
+______________________________________________________________________
+
+USAGE:
+  `basename $0` [OPTIONS] -m METHOD
+
+OPTIONS:
+  -h  this help
+  -i  install monitoring user (must be executed as root)
+  -u  uninstall monitoring user (must be executed as root)
+
+PARAMETERS:
+  -m  method; valid methods are:
+      connections     current/ max connections
+      connects        connects per min and aborted connections/ clients
+      commands        current running statements insert, select, ...
+      qcache-blocks   query cache blocks total vs free
+      qcache-queries  cached / non cached queries; low memeory prunes 
+      qcache-hits     query cache efficiency: cached vs non cached and inserted
+
+EXAMPLES:
+  `basename $0` -i
+  `basename $0` -m commands
+
+EOH
+}
+
+function _mysqlreadvars(){
+    mysql -e "SHOW GLOBAL VARIABLES ;" --skip-column-names >$datafile
+    mysql -e "SHOW STATUS ;"           --skip-column-names >>$datafile
+}
+
+function _mysqlgetvar() {
+    local sVarname=$1
+    lastvalue=`grep "^$sVarname[^_a-z]" ${datafile} | awk '{ print $2 }'`
+    echo $lastvalue
+}
+# get a value from mysql status output
+# param  string  variable name
+# param  string  prefix to remove from beginning of variable
+# param  (set)   flag if it is a delta value
+function _mysqlrendervar() {
+    local sVarname=$1
+    local sRemove=$2
+    local sDeltaUnit=$3
+    local sFloat=$4
+
+    # local iValue=`grep "^$sVarname[^_a-z]" ${datafile} | awk '{ print $2 }'`
+    local iValue=`_mysqlgetvar "$sVarname"`
+    if [ "$iValue" = "" ]; then
+            ph.abort "no value for ${sVarname}"
+    fi
+
+    # get label for perfdata
+    local sLabel=`echo ${sVarname} | sed "s#^${sRemove}##g"`
+    if [ ! -z $sDeltaUnit ]; then
+        local iSpeed=` ph.perfdeltaspeed "mysql-${sVarname}" ${iValue} $sDeltaUnit $sFloat`
+        out=$out`printf "%25s: %10s %s \n" "${sLabel}" "${iValue}" "... delta = ${iSpeed} per $sDeltaUnit${NL}"`
+        ph.perfadd "${sLabel}"   "${iSpeed}"
+        lastvalue=$iSpeed
+    else
+        out=${out}`printf "%25s: %10s \n" "${sLabel}" "${iValue}${NL}"`
+        ph.perfadd "${sLabel}" "${iValue}"
+        lastvalue=$iValue
+    fi
+
+}
+
+function _mysqlrenderdelta() {
+    local deltaUnit=$3
+    test -z $deltaUnit && deltaUnit="sec"
+    _mysqlrendervar "$1" "$2" $deltaUnit $4
+}
+
+function renderCounts(){
+  local _query="$1"
+  local _out=$( psql -c "${_query}")
+  # out+=$( echo "DEBUG: _query=${_query}${NL}" )
+  # echo "DEBUG: _out="; echo "${_out}"
+
+  typeset -i local _iCounter=0
+  local _header=$( echo "${_out}" | head -1 | tr -d ' ')
+  local _data=$( echo "${_out}" | head -3 | tail -1 | tr -d ' ')
+  # echo "DEBUG: _header=${_header}"
+  IFS="|"
+  read -ra aCols <<< "$_header"
+  read -ra aVals <<< "$_data"
+  for sColumn in ${aCols[*]}
+  do
+    local value=${aVals[$_iCounter]}
+    # echo "DEBUG: $sColumn = $value"
+    out+=$( printf "%25s: %10s \n" "${sColumn}" "${value}${NL}")
+    ph.perfadd "${sColumn}" "${value}"
+    _iCounter+=1
+  done
+}
+# ----------------------------------------------------------------------
+# MAIN
+# ----------------------------------------------------------------------
+
+bOptInstall=`   ph.hasParamoption "i" "$@"`
+bOptUninstall=` ph.hasParamoption "u" "$@"`
+bOptHelp=`      ph.hasParamoption "h" "$@"`
+
+if [ $bOptHelp -eq 1 -o $# -lt 1 ]; then
+    _usage
+    exit 0
+fi
+
+# --- check required tools
+# ph.require mysql
+
+# --- install
+if [ $bOptInstall -eq 1 -a "$( whoami )" = "root" ]; then
+    if [ -f $cfgfile ]; then
+        ph.status "SKIP installation. config file already exists: $cfgfile."
+        ph.exit
+    fi
+    HOME=/root
+    export HOME
+
+    _uninstall
+    _install
+
+    ph.status "Installation was done"
+    ph.exit
+fi
+
+# --- uninstall
+if [ $bOptUninstall -eq 1 -a "$( whoami )" = "root" ]; then
+    HOME=/root
+
+    _uninstall
+    rm -f $cfgfile
+
+    ph.status "Uninstalled."
+    ph.exit
+fi
+
+
+# --- check installation
+grep $myuser $cfgfile >/dev/null 2>/dev/null
+if [ $? -ne 0 ]; then
+    ph.abort "PSQL access not possible yet. You need to install the monitoring user first: as root execute `basename $0` -i"
+fi
+
+# ----------------------------------------------------------------------
+
+sMode=`ph.getValueWithParam '' m "$@"`
+
+case "${sMode}" in
+
+    "dbstat")
+        descr="stat database"
+        renderCounts "select \
+            sum(xact_commit)   as commit,   \
+            sum(xact_rollback) as rollback, \
+            sum(blks_read)     as blkread,  \
+            sum(blks_hit)      as blkhit    \
+            from pg_stat_database "
+          ;;
+    "queries")
+        # psql -c "select * from pg_stat_activity "
+        # psql -c "select * from pg_stat_statements " -> av psql v11
+        ;;
+    "connections")
+        descr="current/ max connections"
+
+        _mysqlrendervar    max_connections
+        typeset -i iMaxConn=$lastvalue
+
+        _mysqlrendervar    Max_used_connections
+        typeset -i iMaxUsed=$lastvalue
+
+        typeset -i iMaxPercent=$iMaxUsed*100/$iMaxConn
+        out=$out"                                 --> $iMaxPercent % ${NL}${NL}"
+
+        _mysqlrendervar    Threads_connected
+        typeset -i iThreads=$lastvalue
+        typeset -i iUsage=$iThreads*100/$iMaxConn
+        out=$out"                                 --> $iUsage % ${NL}${NL}"
+        if [ $iWarnLimit -gt 0 -a $iCriticalLimit -gt 0 ]; then
+            ph.setStatusByLimit $iUsage $iWarnLimit $iCriticalLimit
+        fi
+        _mysqlrendervar    Questions
+
+        ;;
+
+    "connects")
+        descr="connects per min and aborted connections/ clients"
+        _mysqlrenderdelta  Connections            "" min float
+        _mysqlrenderdelta  Aborted_clients        "" min float
+        _mysqlrenderdelta  Aborted_connects       "" min float
+        ;;
+
+    "commands")
+        descr="currently executed commands"
+        _mysqlrendervar    Com_delete  "Com_"
+        _mysqlrendervar    Com_insert  "Com_"
+        _mysqlrendervar    Com_replace "Com_"
+        _mysqlrendervar    Com_select  "Com_"
+        _mysqlrendervar    Com_update  "Com_"
+        ;;
+
+    "qcache-blocks")
+        descr="query cache blocks"
+        _mysqlgetvar "have_query_cache" | grep "YES" >/dev/null || ph.abort "Query cache is not active"
+        _mysqlrendervar    Qcache_total_blocks "Qcache_"
+        _mysqlrendervar    Qcache_free_blocks  "Qcache_"
+        ;;
+
+    "qcache-queries")
+        descr="query cache data"
+        _mysqlgetvar "have_query_cache" | grep "YES" >/dev/null || ph.abort "Query cache is not active"
+        _mysqlrendervar    Qcache_queries_in_cache  "Qcache_"
+        _mysqlrenderdelta  Qcache_not_cached        "Qcache_"  min  float
+        _mysqlrenderdelta  Qcache_lowmem_prunes     "Qcache_"  min  float
+        ;;
+
+    "qcache-hits")
+        descr="query cache hits"
+        _mysqlgetvar "have_query_cache" | grep "YES" >/dev/null || ph.abort "Query cache is not active"
+        _mysqlrenderdelta  Qcache_hits        "Qcache_"  min
+        _mysqlrenderdelta  Qcache_inserts     "Qcache_"  min
+        _mysqlrenderdelta  Qcache_not_cached  "Qcache_"  min
+        ;;
+
+    *)
+        echo ERRROR: [${sMode}] is an INVALID mode
+        _usage
+        ph.abort
+
+esac
+
+ph.status "Pgsql $sMode :: $descr"
+echo "$out"
+
+rm -f $datafile
+ph.exit
+
+# ----------------------------------------------------------------------