Re: DB responce during DB dump

Evgeny Gridasov wrote:
> Hi, everybody!
>
> I experience problems with backing up one of my Postgresql 8.1.2 installations.
> The problem is that when I do DB backup, all queries begin to run very slow =(
> The database only grows in its size (~20Gb today), and the number of transactions increases every month.
> A year ago such slow down was OK, but today it is unacceptable.
>
> I found out that pg_dump dramatically increases hdd I/O and because of this most of all
> queries begin to run slower. My application using this DB server is time-critical, so
> any kind of slow down is critical.
>
> I've written a perl script to limit pg_dump output bandwidth, a simple traffic shaper,
> which runs as: pg_dumpall -c -U postgres | limit_bandwidth.pl | bzip2 > pgsql_dump.bz2
> The limit_bandwidth.pl script limits pipe output at 4Mb/sec rate, which seems to be ok.
>
> Is there any other solution to avoid this problem?

That's an interesting solution, and I'd guess people might like to see
it posted to the list if it's not too big.

Also, there's no reason you have to dump from the same machine, you can
do so over the network which should reduce activity a little bit.

Basically though, it sounds like you either need more disk I/O or a
different approach.

Have you looked into using PITR log-shipping or replication (e.g. slony)
to have an off-machine backup?

--
Richard Huxton
Archonet Ltd