bash script to find and terminate long running postgres queries, logging results to splunk

psql_long_queries.sh

#!/bin/bash
set -x
now=$( date '+%Y/%m/%d %H:%M:%S' )

TMPFILE1=$(mktemp /tmp/XXXXXXXXXX.json)

script_exit () {
    rm -rf ${TMPFILE1}
    exit 0
}

psql -t << EOF
\o ${TMPFILE1};
with t as (
    SELECT
        pid,
        client_addr,
        usename as username,
        application_name,
        datname as database_name,
        now() - pg_stat_activity.query_start AS duration,
        state,
        query
    FROM pg_stat_activity
    WHERE (now() - pg_stat_activity.query_start) > interval '10 minutes'
        AND application_name LIKE 'myapp'
        AND state = 'active'
        AND query LIKE 'SELECT %'
    ORDER BY duration DESC )
select json_agg(t) from t;
EOF

# if dont find any NON-EMPTY lines, exit
if [ $(grep -v '^\s*$' ${TMPFILE1} | wc -l) -eq 0 ]; then
    script_exit
fi

IFS=$'\n'

results=($(tr -d '+\n' <${TMPFILE1} | jq -r -c '.[]'))

for result in "${results[@]}"; do

    pid=$(jq .pid <(echo ${result}))
    #echo "SELECT pg_terminate_backend($pid)"
    psql -t -c "SELECT pg_terminate_backend($pid);"

    curl -k \
        -H "Authorization: Splunk XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX" \
        https://splunk.localdomain:8088/services/collector/event \
        -d "{\"index\":\"myindex\", \"sourcetype\": \"myapp:terminated_queries\",\"event\":${result}}"

done

script_exit