tl;dr: Is there a way to see which process is holding a table lock without being able to log in to the database?
I have a CentOS 6.5 server with a Postgres 8.4 database. There's quite a few crons running maintenance sql scripts and they usually run fine but once in a while a process will hang and block other processes, which will cause an accumulation of processes until the process limit for the postgres user is reached and the database becomes unresponsive.
Reading my logs, I have identified one process that blocks other processes, and I even know which line of the sql script ran by that process gets stuck: LOCK TABLE tablename IN ACCESS EXCLUSIVE MODE. The lock table actually never happens and the process just gets stuck there, unable to lock the table, which tells me there is probably another SQL script holding the lock and never releasing it. I want to find that other script and fix it.
The problem is I cannot log in to the database to query it because it became unresponsive, but if I reboot to make the db available then I won't see the blocked processes anymore, so I don't know how to find out which process is holding the lock, or which script that process is running...
