#!/bin/bash
## Monitor the PostgreSQL database for deadlock queries
### https://wiki.postgresql.org/wiki/Lock_Monitoring

export PATH="/bin:/sbin:/usr/bin:/usr/sbin:/opt/rh/postgresql92/root/usr/bin"

PG_VERSION=$(su - postgres -c "psql -c 'select version();'" |  grep "^ PostgreSQL" | cut -f 3 -d ' ' | cut -f1 -d'.')

LOGFILE="/var/log/pg_monitor_deadlocks.log"

if [ $PG_VERSION -eq 9 ]; then

    SQL="SELECT bl.pid                 AS blocked_pid,
                a.usename              AS blocked_user,
                ka.query               AS blocking_statement,
                now() - ka.query_start AS blocking_duration,
                kl.pid                 AS blocking_pid,
                ka.usename             AS blocking_user,
                a.query                AS blocked_statement,
                now() - a.query_start  AS blocked_duration
    FROM  pg_catalog.pg_locks         bl
        JOIN pg_catalog.pg_stat_activity a  ON a.pid = bl.pid
        JOIN pg_catalog.pg_locks         kl ON kl.transactionid = bl.transactionid AND kl.pid != bl.pid
        JOIN pg_catalog.pg_stat_activity ka ON ka.pid = kl.pid
    WHERE NOT bl.granted;"

    #execute query
    echo $(date) | tee -a $LOGFILE
    echo $SQL | su - postgres -c psql | tee -a $LOGFILE


elif [ $PG_VERSION -eq 8 ]; then

    SQL="SELECT bl.pid                 AS blocked_pid,
                a.usename              AS blocked_user,
                ka.current_query       AS blocking_statement,
                now() - ka.query_start AS blocking_duration,
                kl.pid                 AS blocking_pid,
                ka.usename             AS blocking_user,
                a.current_query        AS blocked_statement,
                now() - a.query_start  AS blocked_duration
    FROM  pg_catalog.pg_locks         bl
        JOIN pg_catalog.pg_stat_activity a  ON a.procpid = bl.pid
        JOIN pg_catalog.pg_locks         kl ON kl.transactionid = bl.transactionid AND kl.pid != bl.pid
        JOIN pg_catalog.pg_stat_activity ka ON ka.procpid = kl.pid
    WHERE NOT bl.granted;"

    #execute query
    echo $(date) | tee -a $LOGFILE
    echo $SQL | su - postgres -c psql | tee -a $LOGFILE

else
    echo "Sorry, I could not determine the PostgreSQL version"
fi