Re: pg_dump and write locks

"David Parker" <dparker(at)tazznetworks(dot)com> writes:
> The observed behavior was that a pg_dump running with nothing else going
> on takes a couple of minutes, but when we are running some system tests
> that do heavy updates to a selection of application tables, it appears
> that pg_dump blocks until the update run is done.

Are you sure the other processes aren't taking any exclusive locks?
Are you sure your system isn't saturated to the point where pg_dump
just can't make progress very fast?

> We looked at pg_locks, and saw that the pg_dump process was acquiring
> locks like:
>
> 14764 | ExclusiveLock | 124576072 | COPY public.stats (id,
> description, lastsavedate, lastsaveuser) TO stdout;

It's impossible to tell what you are actually looking at here --- that's
not the raw output of pg_locks, and you've conveniently omitted any
column headers --- but I wonder whether that isn't just the
transaction's standard lock on its own XID.

If pg_dump is actually getting blocked, that will show as a row with
granted = false and pg_dump's PID.

regards, tom lane