We have an issue with running pg_dump while a database is under reasonably heavy update load. This is 7.4.5 on Solaris 9/intel.
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. This didn't make sense because everything I can find about pg_dump indicates that it should only be taking read locks, so I don't see why it should be blocked.
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;
(we are using pg_dump with -Fc) If COPY is taking a ROW EXCLUSIVE lock, then that would explain why we are seeing contention, but I don't understand why COPY is locking at that level. Is there a better way to run pg_dump against a database with a lot of update activity?
Thanks.
- DAP
======================================================
David Parker Tazz Networks