#!/bin/bash
# ======================================================================
#
# Check PSQL SERVER
#
# requirements:
# - psql client
#
# installation:
# - execute check_psqlserver -i
# 
# ----------------------------------------------------------------------
# Postgresql docs:
# https://www.postgresql.org/docs/current/monitoring-stats.html
# ----------------------------------------------------------------------
# 2023-06-07  v0.2  <axel.hahn@unibe.ch>
# ======================================================================


. $(dirname $0)/inc_pluginfunctions
self_APPNAME=$( basename $0 | tr [:lower:] [:upper:] )
self_APPVERSION=0.2

# --- other vars...
cfgfile=/etc/icingaclient/.psql.conf
myuser=icingamonitor

# new line
NL="
"

out=" "

# ----------------------------------------------------------------------
# FUNCTIONS
# ----------------------------------------------------------------------

# uninstall database user
function _uninstall(){
    echo UNINSTALL ...
    su - postgres -c "psql -c \"DROP USER ${myuser};\""
    unset PGHOST
    unset PGUSER
    unset PGPASSWORD
    unset PGDATABASE
}

# (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

# set default database because a user db won't be created
export PGDATABASE=postgres
EOF
    ls -l $cfgfile
    if [ $? -ne 0 ]; then
        echo "ERROR: creation of config file failed."
        exit 1
    fi
}

# show help
function _usage(){
    local _self=$( basename $0 )
    cat <<EOH
______________________________________________________________________

${self_APPNAME} :: v${self_APPVERSION}

(c) Institute for Medical Education - University of Bern
Licence: GNU GPL 3
______________________________________________________________________

USAGE:
  $_self [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:
      activity        running processes and queries
      dbrows          Count of database row actions
      conflicts       Detected conflicts from pg_stat_database_conflicts
      diskblock       Count of diskblocks physically read or coming from cache
      problems        Problems and troublemakers
      replication     Replication status (table output only)
      transactions    Count of transactions over all databases

EXAMPLES:
  $_self -i
  $_self -m activity

EOH
}

# render incremental counters from integer results of a given sql query
# global string  out  output of check
# param  string       database query
function renderCounters(){
  local _query;      _query="$1"
  local _out;        _out=$( psql -c "${_query}")
  local _iCounter;   typeset -i _iCounter=0
  local _header;     _header=$( echo "${_out}" | head -1 | tr -d ' ')
  local _data;       _data=$( echo "${_out}" | head -3 | tail -1 | tr -d ' ')
  local _sDeltaUnit; _sDeltaUnit=sec
  local _iSpeed;     typeset -i _iSpeed=0
  local _sStoreid;   _sStoreid=$( md5sum <<< "${_query}" | awk '{ print $1 }' )
  local _iValue;     typeset -i _iValue

  # read psql result and put columns and values to an array
  IFS="|"
  read -ra aCols <<< "$_header"
  read -ra aVals <<< "$_data"

  for sColumn in ${aCols[*]}
  do
    _iValue=${aVals[$_iCounter]}
    _iSpeed=$( ph.perfdeltaspeed "psql-${sColumn}-${_sStoreid}" ${_iValue} sec $_sDeltaUnit)
    out=$out$(printf "%25s: %10s %s \n" "${sColumn}" "${_iValue}" "... delta = ${_iSpeed} per $_sDeltaUnit${NL}")
    ph.perfadd "${sColumn}"   "${_iSpeed}"
    _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

    _uninstall
    _install

    ph.status "Installation was done"
    ph.exit
fi

# --- uninstall
if [ $bOptUninstall -eq 1 -a "$( whoami )" = "root" ]; then

    _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
    "activity")
        _out=$( psql -c "select pid,usename,state,query,backend_type,backend_start from pg_stat_activity" )
        typeset -i iQTotal;  iQTotal=$(  tail -1 <<< "$_out"  | cut -f 1 -d ' ' | tr -d '(' )
        typeset -i iQActive; iQActive=$( awk '{ print $5 }' <<< "$_out" | grep -c "active"  )

        descr="Running total: $iQTotal ... active: $iQActive"
        out="${_out}${NL}"
        ph.perfadd "running-total"  "${iQTotal}"
        ph.perfadd "running-active" "${iQActive}"
        ;;
    "conflicts")
        descr="Detected conflicts (from pg_stat_database_conflicts)"
        renderCounters "select                          \
            sum(confl_tablespace)  as confltablespace,  \
            sum(confl_lock)        as confllock,        \
            sum(confl_snapshot)    as conflsnapshot,    \
            sum(confl_bufferpin)   as conflbufferpin,   \
            sum(confl_deadlock)    as confldeadlock     \
            from pg_stat_database_conflicts "
          ;;
    "dbrows")
        descr="Count of database row actions (from pg_stat_database)"
        renderCounters "select               \
            sum(tup_returned)     as return, \
            sum(tup_fetched)      as fetch,  \
            sum(tup_inserted)     as insert, \
            sum(tup_updated)      as update, \
            sum(tup_deleted)      as delete  \
            from pg_stat_database "
          ;;
    "diskblock")
        descr="Count of diskblocks physically read or coming from cache (from pg_stat_database)"
        renderCounters "select             \
            sum(blks_read)     as read,    \
            sum(blks_hit)      as cached   \
            from pg_stat_database "
          ;;
    "problems")
        descr="Problems and troublemakers (from pg_stat_database)"
        renderCounters "select                           \
            sum(conflicts)          as conflicts,        \
            sum(deadlocks)          as deadlocks,        \
            sum(checksum_failures)  as checksumfailures, \
            sum(temp_files)         as tmpfiles,         \
            sum(temp_bytes)         as tmpbytes          \
            from pg_stat_database "
          ;;
    "replication")
        _out=$( psql -c "select * from pg_stat_replication" )
        if tail -1 <<< "$_out"  | grep "(0 rows)" >/dev/null ; then
            descr="No data in pg_stat_replication - this is no slave."
            out=""
        else
            descr="status (from pg_stat_replication)"
            out="${_out}${NL}"
        fi
        ;;

    "transactions")
        descr="Count of transactions over all databases (from pg_stat_database)"
        renderCounters "select             \
            sum(xact_commit)   as commit,  \
            sum(xact_rollback) as rollback \
            from pg_stat_database "
          ;;
    *)
        echo ERRROR: [${sMode}] is an INVALID mode
        _usage
        ph.abort

esac

ph.status "Pgsql $sMode :: $descr"
echo "$out"

ph.exit

# ----------------------------------------------------------------------