monitoring-odoo-postgresql-lock.sh

#!/bin/bash
##################################################################################
##                                                                              ##
## To run this script by command line in background issue this command          ##
##   ./monitoring-odoo-postgresql-lock > /dev/null 2>&1 &                       ##
## To kill this script if running issue this command                            ##
##   pkill -f monitoring-odoo-postgresql-lock                                   ##
## To add this script to crontab:                                               ##
##   @reboot /path/to/monitoring-odoo-postgresql-lock > /dev/null 2>&1 || true  ##
##                                                                              ##
##################################################################################

NAP_BETWEEN_CHECK='5'  # Seconds between checks for locking
PGLOCK_AGE_TIME='15'   # Seconds that lock transaction needs to exist to be regarded as a locking event
NAP_BETWEEN_NOTIFICATION='600' # Seconds between sending email notifications about locking events
NOTIFICATION_EMAIL_SUBJECT='Monitoring '$(/bin/hostname -s)' Odoo PostgreSQL lock'

MAILTO=${MAILTO:-myself@example.com}
SYSTEMCUSTOMERUSER=root
PGHOST=localhost
PGUSER=ubuntu
PGDATABASE=database
PGPASSWORD=password

DOCKERCONTAINER=""  # to look inside Docker containers - specify the container name

################################################################
######                                                    ######
######       Don't change variables after this line       ######
######                                                    ######
################################################################

SCRIPT_NAME=${0##*/}
SCRIPT_DIRECTORY=$(cd "$(dirname "$0")" && pwd)
WORK_DIRECTORY=$SCRIPT_DIRECTORY/000$SCRIPT_NAME
SVG_DIRECTORY=$WORK_DIRECTORY/svg-files
SVG_LIST_FILE=$WORK_DIRECTORY/svg-list
SCRIPT_LOG_FOLDER=$WORK_DIRECTORY/log-folder
SCRIPT_LOG_FILE=$SCRIPT_LOG_FOLDER/$SCRIPT_NAME.log
EMAIL_BODY_FILE=$WORK_DIRECTORY/email-body

# Check prerequisites
if [ $(id -u) -ne 0 ]; then
  echo "You should be root"
  exit 1
fi

if [ -e $(command -v /usr/bin/mutt ) ]; then
    /bin/echo 'mutt is required to send mails'
    exit 1
fi

if [ -e $(command -v $SCRIPT_DIRECTORY/py-spy ) ]; then
    /bin/echo 'py-spy is required in this folder'
    exit 1
fi

if [ "$PGLOCK_AGE_TIME" -le "$NAP_BETWEEN_CHECK" ] ; then
  /bin/echo 'PGLOCK_AGE_TIME='$PGLOCK_AGE_TIME' variable should be greater than NAP_BETWEEN_CHECK='$NAP_BETWEEN_CHECK' variable'
  exit 1
fi

if [ "$NAP_BETWEEN_NOTIFICATION" -le "$PGLOCK_AGE_TIME" ] ; then
  /bin/echo 'NAP_BETWEEN_NOTIFICATION='$NAP_BETWEEN_NOTIFICATION' variable should be greater than PGLOCK_AGE_TIME='$PGLOCK_AGE_TIME' variable'
  exit 1
fi

# Initialize empty files and SVG folder
/bin/mkdir -p $WORK_DIRECTORY
/bin/mkdir -p $SCRIPT_LOG_FOLDER
/usr/bin/touch $SCRIPT_LOG_FILE
[ ! -e $SCRIPT_LOG_FILE.1 ] && /usr/bin/touch $SCRIPT_LOG_FILE.1
/bin/mkdir -p $SVG_DIRECTORY

function cleanup {
  /bin/rm -fr $SVG_LIST_FILE
  /bin/rm -fr $EMAIL_BODY_FILE
  /bin/rm -fr $TEMPORARY_SCRIPT_FOLDER
}
cleanup
trap cleanup EXIT

while :
do
  START_LOOP_EPOCH_DATE=$(/bin/date +%s)
  END_LOOP_EPOCH_DATE=$((10#$START_LOOP_EPOCH_DATE + 10#$NAP_BETWEEN_NOTIFICATION))
  until [[ $(/bin/date +%s) -gt $END_LOOP_EPOCH_DATE ]] ; do
    PGLOCKQUERY=$(PGPASSWORD=$PGPASSWORD /usr/bin/psql -qtAX -d $PGDATABASE -U $PGUSER -h $PGHOST -p 5432 << EOF
    SELECT pid, client_port, extract(epoch from age(now(), xact_start)), extract(epoch from xact_start)
    FROM pg_stat_activity
    WHERE pid IN (
        select unnest(pg_blocking_pids(pid)) as blocked_by
        from pg_stat_activity
        where cardinality(pg_blocking_pids(pid)) > 0
    ) AND cardinality(pg_blocking_pids(pid)) = 0
    order by xact_start asc
EOF
    )
    if [ "$PGLOCKQUERY" != "" ] ; then
      /bin/echo -e "\n\n\n\n\n$(/bin/date)" >> $SCRIPT_LOG_FILE
      PGPASSWORD=$PGPASSWORD /usr/bin/psql -qtAX -d $PGDATABASE -U $PGUSER -h $PGHOST -p 5432 << EOF >> $SCRIPT_LOG_FILE
      SELECT pid, xact_start, now(), pg_blocking_pids(pid), client_port, query
      FROM pg_stat_activity
      WHERE pid IN (
          select unnest(pg_blocking_pids(pid)) as blocked_by
          from pg_stat_activity
          where cardinality(pg_blocking_pids(pid)) > 0
      )
      order by xact_start asc;
EOF
      OLDIFS="$IFS"
      IFS=$'\n'
      for lock in $PGLOCKQUERY ; do
        PGPID=$(/bin/echo $lock | /usr/bin/cut -d'|' -f1)
        PGCLIENTPORT=$(/bin/echo $lock | /usr/bin/cut -d'|' -f2)
        PGAGE=$(/bin/echo $lock | /usr/bin/cut -d'|' -f3 | /usr/bin/cut -d'.' -f1)
        XACTSTART=$(/bin/echo $lock | /usr/bin/cut -d'|' -f4 | /usr/bin/cut -d'.' -f1)
        if [ $PGAGE -ge $PGLOCK_AGE_TIME ] ; then
          ODOOPID=''
          if [ "$PGCLIENTPORT" != "-1" ] ; then
            if [ "$DOCKERCONTAINER" != "" ] ; then
              ODOOPID=$(/usr/bin/nsenter -t $(/usr/bin/docker inspect -f '{{.State.Pid}}' $DOCKERCONTAINER) -n /bin/netstat \
                -na -p | /usr/bin/awk '{print $4 " " $7}' | /bin/grep $PGCLIENTPORT | /bin/grep -v '127.0.0.1:' \
                | /usr/bin/head -1 | /usr/bin/awk '{print $2}' | /usr/bin/cut -d '/' -f1  | /bin/grep -Po "[0-9]+")
            else
              ODOOPID=$(/bin/netstat -na -p | /usr/bin/awk '{print $4 " " $7}' | /bin/grep $PGCLIENTPORT \
              | /bin/grep -v '127.0.0.1:' | /usr/bin/head -1 | /usr/bin/awk '{print $2}' | /usr/bin/cut -d '/' -f1  | /bin/grep -Po "[0-9]+")
            fi
# TODO: code in case postgres socket connections are used instead of ports
#          else
#            PGINODE=$(/bin/netstat -na -p | /bin/grep $PGPID | /usr/bin/awk '{print $7}')
#            ODOOPID=$( /bin/ss -a --unix -xp | /usr/bin/awk -v pginode=$PGINODE '$8 == pginode' | /usr/bin/awk '{print $9}' | /bin/grep -Po "pid=\K[0-9]+")
          fi
          if [ "$ODOOPID" != "" ] ; then
            SPYFILENAME=$(/bin/date -d @$XACTSTART "+%Y-%m-%d-%H-%M-%S-%Z")-${PGPID}_${ODOOPID}.svg
            if [ ! -f $SVG_DIRECTORY/$SPYFILENAME ] ; then
              $SCRIPT_DIRECTORY/py-spy record --idle --nonblocking -p $ODOOPID -o $SVG_DIRECTORY/$SPYFILENAME -d 1
              /bin/echo "$(/bin/date) ## ODOOPID: $ODOOPID PGPID: $PGPID PGCLIENTPORT: $PGCLIENTPORT PGAGE: $PGAGE XACTSTART: $XACTSTART SPYFILENAME: $SPYFILENAME" >> $SCRIPT_LOG_FILE
              PGPASSWORD=$PGPASSWORD /usr/bin/psql -qtAX -d $PGDATABASE -U $PGUSER -h $PGHOST -p 5432 << EOF | /usr/bin/tee -a $EMAIL_BODY_FILE $SCRIPT_LOG_FILE
              SELECT query from pg_stat_activity where pid = ${PGPID}
EOF
              /bin/echo $SPYFILENAME | /usr/bin/tee -a $EMAIL_BODY_FILE $SVG_LIST_FILE
              /bin/echo -e "\n\n\n\n\n"  >> $SCRIPT_LOG_FILE
            fi
          fi
        fi
      done
      IFS="$OLDIFS"
    fi
    /bin/sleep $NAP_BETWEEN_CHECK
  done
  if [ -s "$SVG_LIST_FILE" -a -s "$EMAIL_BODY_FILE" ] ; then
    SVG_FILES=$(/bin/cat $SVG_LIST_FILE | /bin/sed '/^$/d' | /usr/bin/awk -v svgdir="$SVG_DIRECTORY" '{print(svgdir "/" $0)}' | /usr/bin/tr '\r\n' ' ')

    # Move files to temporary directory for processing
    TEMPORARY_SCRIPT_FOLDER=$(/bin/mktemp -d -t 000XXXXXXXXXXXXXXXXXXXXXX)
    TEMPORARY_SVG_DIRECTORY=$TEMPORARY_SCRIPT_FOLDER/svg-files
    EMAIL_TEMPORARY_BODY_FILE=$TEMPORARY_SCRIPT_FOLDER/$email-body

    /bin/mkdir $TEMPORARY_SVG_DIRECTORY
    /bin/cp $SVG_FILES $TEMPORARY_SVG_DIRECTORY/

    ATTACH=$(/bin/cat $SVG_LIST_FILE | /bin/sed '/^$/d' | /usr/bin/awk -v svgdir="$TEMPORARY_SVG_DIRECTORY" '{print(svgdir "/" $0)}' | /usr/bin/tr '\r\n' ' ')

    # Generate mail text
    /bin/cat << EOF > "$EMAIL_TEMPORARY_BODY_FILE"
    This email generated by monitoring-odoo-postgresql-lock script with these variables:
        PGLOCK_AGE_TIME: $PGLOCK_AGE_TIME seconds
        NAP_BETWEEN_CHECK: $NAP_BETWEEN_CHECK seconds
        NAP_BETWEEN_NOTIFICATION: $NAP_BETWEEN_NOTIFICATION seconds
        The list of svg files:

EOF
    /bin/cat $EMAIL_BODY_FILE >> "$EMAIL_TEMPORARY_BODY_FILE"

    /bin/chown -R $SYSTEMCUSTOMERUSER:$SYSTEMCUSTOMERUSER $TEMPORARY_SCRIPT_FOLDER
    /bin/su - $SYSTEMCUSTOMERUSER << EOF
    /bin/cat $EMAIL_TEMPORARY_BODY_FILE | /usr/bin/mutt -a $ATTACH -s "$NOTIFICATION_EMAIL_SUBJECT" -- $MAILTO
EOF
    cleanup
  fi
  if [[ $(/bin/date +%s) -ge $((10#$(/usr/bin/stat -c '%Y' $SCRIPT_LOG_FILE.1) + 10#86400)) ]]; then
    if [ -f $SCRIPT_LOG_FILE.2 ] ; then
      if [ -f $SCRIPT_LOG_FILE.3 ] ; then
        if [ -f $SCRIPT_LOG_FILE.4 ] ; then
          if [ -f $SCRIPT_LOG_FILE.5 ] ; then
            if [ -f $SCRIPT_LOG_FILE.6 ] ; then
              /bin/rm -fr $SCRIPT_LOG_FILE.6
            fi
            /bin/mv $SCRIPT_LOG_FILE.5 $SCRIPT_LOG_FILE.6
          fi
          /bin/mv $SCRIPT_LOG_FILE.4 $SCRIPT_LOG_FILE.5
        fi
        /bin/mv $SCRIPT_LOG_FILE.3 $SCRIPT_LOG_FILE.4
      fi
      /bin/mv $SCRIPT_LOG_FILE.2 $SCRIPT_LOG_FILE.3
    fi
    /bin/mv $SCRIPT_LOG_FILE.1 $SCRIPT_LOG_FILE.2
    /bin/mv $SCRIPT_LOG_FILE $SCRIPT_LOG_FILE.1
    /usr/bin/touch $SCRIPT_LOG_FILE
  fi
done