Re: pg_dump in a production environment

On Mon, 2005-05-23 at 16:54, Chris Kratz wrote:
> Hello Thomas,
>
> We've had and have the exact same issue and have been unable to find a
> satisfactory solution to the problem. Currently we "just live with it". We
> do periodic backups with pg_dump on an hourly basis. During the dump, other
> accesses to the db are incredibly slow making our web app feel somewhat
> sluggish for 5 to 10 minutes while the db is dumped.
>
> After a lot of research, it appears to be an i/o and memory contention issue.
> Basically, the dump procedure has to pull in all data and tables into memory
> to dump them which means any other requests have to pull the data they need
> back off of disk (because they got paged out to make room for the dump data)
> making them very slow. This is compounded by the fact that pg_dump usually
> saturates your I/O throughput. Since postgres doesn't manage the file system
> buffers (the os does), there appears to be no easy way to tell it to only use
> x amount of memory for the dump leaving all the other memory available for
> the running database. I have a hunch that the same thing happens with the
> shared buffers, though I haven't proven that. This wasn't a problem for us
> while the db fit into ram, but we've grown far past that point now.

Are you folks running 8.0 with its improved caching algorithms? Just
wondering if that helps or not.

> The only solution we have ever found is simply to use something like a slony
> slave and do dumps from the slave. The slave takes the performance hit and
> your primary db keeps on running at full speed. Once the dump is done, then
> the slave can "catch up" if it needs to. Unfortunately, I believe there are
> issues currently with restoring off of a dump from a slave.

Actually, there's a special dump program somewhere in the slony source
tree, one of the perl scripts. That should fix the issues with the
backups. I ran into it a while back and have to start using the same
file.