James Sewell <james.sewell@jirotech.com> writes:
>> I'm also wondering a bit about whether they're being blocked on a lock,
>> eg. due to something taking an exclusive lock on pg_authid or pg_database.
>> pg_locks might be interesting to check.
> postgres=# select * from pg_locks where not granted;
The hypothesis I'm thinking of is that incoming sessions are being blocked
somewhere before they can acquire a ProcArray entry; if so, they'd not
show up in either pg_stat_activity or pg_locks. What we have to look for
then is evidence of somebody holding a strong lock on a shared relation.
Try "select * from pg_locks where locktype = 'relation' and database = 0".
regards, tom lane