Postgresql monitor old transactions

From NailingJello.com
Jump to: navigation, search

This command is executed from cron for postgresql user. We executed it hourly, since the script gave us a yellow alert if any transaction took longer than 3 hours and red after 6. For our application we did not get many false positives.

/usr/bin/psql -q -f /var/lib/postgresql/scripts/monitor_old_transactions.sql >> /var/log/postgresql/moodle_transactions.log 2>&1 

We have our monitoring system monitor the moodle_transactions.log log file for any "WARNING" or "ERROR" log lines. If so, we get an alert.

\pset tuples_only

select 'WARNING transaction older than 3 hours',* from pg_stat_activity where xact_start < (now() - interval '3 hours');
select 'ERROR transaction older than 3 hours',* from pg_stat_activity where xact_start < (now() - interval '6 hours');