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
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
