Re: pg_dump and server responsiveness

On Dec 5, 2007 9:49 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Only access-share locks, but that could still be an issue if anything in
> your system likes to take exclusive locks. Have you looked into
> pg_locks to see if anything's getting blocked?
>
> pg_dump is entirely capable of causing an unpleasant amount of I/O
> load, but that shouldn't result in "complete unresponsiveness",
> and anyway your iostat output doesn't look like you're saturated...

It does appear to be lock contention. I took a closer look this
morning, and I noticed our web site was consistently locking up on a
particular table, and there were a number of exclusive locks. I
started eliminating various jobs, and found the one that essentially
rewrites that particular table every 5 minutes to be the culprit
(create new table, drop old table, rename new table).

Is there a better way we can do this so that we won't causes lock
contention during a dump? I can disable the process, but if the
backup takes an hour that's an hour where all the data in this table
is stale. I believe we chose to do it this way, because we wanted to
minimize the amount of time the table wasn't available, which is why
we didn't use a truncate based strategy.

Thanks,
Bryan