Any way to analyze locks after the fact?

Question

Say I had a lock that lasted for a couple of hours and caused a lot of queries to wait for it, but it was released before I had the chance to investigate it. Is there any way to see where that lock was? Anything in particular to look for in the logs?

Answer 1

I had a similar problem in the past and wrote a simple bash script that checked on the database and saved a copy of the views I wanted with a timestamp on it every minute so I could later look at the data when the problem had passed. Here's the one I used, it grabs pg_stat_activity when there are more than 50 backends connected. Feel free to mangle it in whatever way works for you:

#!/bin/bash
threshold=50;
dt=`date +%Y%m%d%H%M%S`;
active=`/usr/bin/psql www -Atc "select count(*) from pg_stat_activity where current_query not ilike '%idle%';"`
if [[ active -gt threshold ]]; then
    echo "there are "$active" backends";
    echo "creating backup for pg_stat as pg_stat_bk_$dt"
    psql www -c "select * into monitoring.pg_stat_bk_$dt from pg_stat_activity where current_query not ilike '%idle%';"
fi