I’ve just been looking into some issues with locking in Postgres and the documentation as ever has been excellent.
A closer look at the queries provided suggests they don’t show table level locks. At the very least if you do a
pg_dump of your database while checking those queries you see nothing, despite there definitely being some locks going on. This query probably isn’t perfect, and is simply based on a quick practical test of running
pg_dump against a test db but it may help spot the table locks which could be blocking things.
select pid, usename, datname, current_query from pg_catalog.pg_locks l inner join pg_catalog.pg_stat_activity a on a.procpid = pid where mode like '%ExclusiveLock%';