Last active
May 17, 2024 14:18
-
-
Save thomaspaulb/f809740c36d337e01297a925cbb96477 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment