Скрипт убивающий долгие запросы

/etc/cron.d/postgres

SHELL=/bin/bash
PATH=/sbin:/bin:/usr/sbin:/usr/bin:/usr/local/sbin:/usr/local/bin
MAILTO=root
#
00 */3  * * *  postgres  psql_killing_long_running_queries.sh

psql_killing_long_running_queries.sh

#!/bin/bash
set -eu
export LC_ALL="C"
export LANG="en_US.UTF-8"
#------------------------------------------------------------------------------------
 
PSQL_CMD="psql -U postgres"
MAX_EXEC_TIME=180 # in minutes
 
### Test connection
echo "SELECT 1" | $PSQL_CMD >/dev/null || exit 1
 
 
### Print long running queries
RESULT=$(cat << EOF | $PSQL_CMD --pset='format=wrapped'
SELECT
  datname,
  pid,
  pg_stat_activity.query_start,
  now() - pg_stat_activity.query_start AS duration,
  state,
  client_addr,
  query
FROM 
  pg_stat_activity
WHERE
  (now() - pg_stat_activity.query_start) > interval '${MAX_EXEC_TIME} minutes'
  AND query NOT ILIKE 'autovacuum%'
  AND query NOT ILIKE 'REINDEX%'
  AND query NOT ILIKE 'REFRESH%'
  AND state != 'idle'
  AND pid <> pg_backend_pid();
EOF
)
# Exit if empty
if echo "${RESULT}" | grep -q '(0 rows)'; then
  exit 0
fi
date
echo "--------------------------------------------------------------------------------------------------------------------------------"
echo "${RESULT}"
 
 
### Kill long running queries
cat << EOF | $PSQL_CMD
SELECT
  pg_terminate_backend(pid)
FROM
   pg_stat_activity
WHERE
  (now() - pg_stat_activity.query_start) > interval '${MAX_EXEC_TIME} minutes'
  AND query NOT ILIKE 'autovacuum%'
  AND query NOT ILIKE 'REINDEX%'
  AND query NOT ILIKE 'REFRESH%'  
  AND state != 'idle'
  AND pid <> pg_backend_pid();
EOF
echo