Get potentially blocking queries from postgresql log, cross ref with stat_activity and post to slack

get_blocking_queries.sh

#!/bin/bash

PGSQL_HOST="localhost"
PGSQL_PORT="5432"
PGSQL_DATABASE="postgres"
PGSQL_USERNAME="postgres"
export PGPASSWORD="passwd"
xmins=()
queries=""

echo "Extracting info from logs..."
LOGS=`grep "removable, oldest xmin:" /var/log/postgresql/postgresql-12.log | grep -oEi '[0-9]*$'`

echo "Parsing out xmins..."
while IFS= read -r line; do
        if [[ ! " ${xmins[*]} " =~ " ${line} " ]]; then
                xmins+=($line)
        fi
done <<< $LOGS

echo "Checking psql if queries still running..."
for i in "${xmins[@]}"
do
        Q=`/usr/bin/psql -P pager=off -t -c "SELECT pid, usename, backend_xmin, replace(query,E'\n',' '), round(cast(float8 (extract(epoch from (clock_timestamp() - query_start))/3600) as numeric),2) AS query_age FROM pg_stat_activity WHERE backend_xmin = '$i'"`
        if [ -z "$Q" ]
        then
                continue
        fi
        echo "Found $i in running queries..."
        query=`echo "$Q" | awk 'BEGIN {FS=OFS="|"} {print "*Query:* "$4"\n    *Process*: "$1"\n    *Runtime:* "$5" hours running\n    *Xmin:* "$3"\n"}'
        queries+="$query"
done

if [ -z "$queries" ]
then
        exit 0
fi

echo "Found potential threats! Messaging Slack!"
curl -H "Authorization: Bearer {token}" \
-H "Content-type: application/json; charset=utf-8" \
--data "{\"channel\":\"{channel}\",\"blocks\":[{\"type\":\"section\",\"text\":{\"type\":\"mrkdwn\",\"text\":\"*Found queries holding up xmin!*\n$queries\"}}]}" \
-X POST https://slack.com/api/chat.postMessage > /dev/null