PostgreSQL deadlocks detection

pg_dealocks.sh

#!/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;"


    SQL2=" SELECT
        waiting.locktype           AS waiting_locktype,
        waiting.relation::regclass AS waiting_table,
        waiting_stm.query          AS waiting_query,
        waiting.mode               AS waiting_mode,
        waiting.pid                AS waiting_pid,
        other.locktype             AS other_locktype,
        other.relation::regclass   AS other_table,
        other_stm.query            AS other_query,
        other.mode                 AS other_mode,
        other.pid                  AS other_pid,
        other.granted              AS other_granted
    FROM
        pg_catalog.pg_locks AS waiting
    JOIN
        pg_catalog.pg_stat_activity AS waiting_stm
        ON (
            waiting_stm.pid = waiting.pid
        )
    JOIN
        pg_catalog.pg_locks AS other
        ON (
            (
                waiting."database" = other."database"
            AND waiting.relation  = other.relation
            )
            OR waiting.transactionid = other.transactionid
        )
    JOIN
        pg_catalog.pg_stat_activity AS other_stm
        ON (
            other_stm.pid = other.pid
        )
    WHERE
        NOT waiting.granted
    AND
        waiting.pid <> other.pid;"

    #execute query
    echo $(date) | tee -a $LOGFILE
    echo $SQL | su - postgres -c psql | tee -a $LOGFILE
    echo $SQL2 | 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;"


    SQL2="SELECT
    waiting.locktype           AS waiting_locktype,
    waiting.relation::regclass AS waiting_table,
    waiting_stm.current_query  AS waiting_query,
    waiting.mode               AS waiting_mode,
    waiting.pid                AS waiting_pid,
    other.locktype             AS other_locktype,
    other.relation::regclass   AS other_table,
    other_stm.current_query    AS other_query,
    other.mode                 AS other_mode,
    other.pid                  AS other_pid,
    other.granted              AS other_granted
    FROM
        pg_catalog.pg_locks AS waiting
    JOIN
        pg_catalog.pg_stat_activity AS waiting_stm
        ON (
            waiting_stm.procpid = waiting.pid
        )
    JOIN
        pg_catalog.pg_locks AS other
        ON (
            (
                waiting."database" = other."database"
            AND waiting.relation  = other.relation
            )
            OR waiting.transactionid = other.transactionid
        )
    JOIN
        pg_catalog.pg_stat_activity AS other_stm
        ON (
            other_stm.procpid = other.pid
        )
    WHERE
        NOT waiting.granted
    AND
        waiting.pid <> other.pid;"

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

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