log_lock_postgresql.sh

#!/bin/bash
for ((;;)) do psql kangaroo postgresql -1 -t -c "SELECT now() as at_date, client_addr, substring(regexp_replace(trim(current_query), E'\r|\n', '', 'g'),0,100) as query, count(*) as qtd, case when count(*) > 50 then 'CRITICAL' else '' END as level FROM pg_locks pl LEFT JOIN pg_stat_activity psa ON pl.pid = psa.procpid where client_port > 0 group by client_addr, substring(regexp_replace(trim(current_query), E'\r|\n', '', 'g'),0,100)" >> /tmp/log_lock.log; sleep 1m; done;