Re: pg_dump / pg_dumpall / memory issues

Oh, as a followup on that last message I sent off, I ran the backup by
hand, which basically runs a PHP script on a postgresql 7.3 machine to
backup a postgresql 7.2 machine. Since 7.3's pg_dumpall isn't so good at
talking to the 7.2 machine, I had to write my own, that's ok, there were
other things to do as well, it's a backup script after all.

Anyway, the backup script gets a list of all the databases on the 7.2
server and initializes an empty place with 'initdb --locate=C' then
promptly fires off line after line like this:

createdb postgres;pg_dump -O -h mainbox postgres | psql postgres

In fact it fires it off 62 times for our system. With the backup script
running the load factor on the mainbox was about 1.09 while the % CPU for
the postmaster doing the backup was 50%. when I ran pgbench -c 4 -t
1000000 to provide some PTL (parallel thrash load :-) the usage of the
postmasters running the pgbench was about 24%, while the postmaster
running the backup was about 35 to 40%. Note that this was a dual
PIII-750, so the totals can add up to 200% in RH Linux.

The responsiveness of the main box is about the same during just the
backup, but the pgbench was a killer, with or without the backup, that
slows the machine down a lot more for me.

So I'm wondering if the simple solution might be to either use a slower
box / network connection / throttled port on the backup box, or just
backup into another database since the copies into the other machine
probably slow things down enough to render less of a load on the server
being backed up.

both the two servers in this test are identical, except the mainline box
is still running 7.2.4 while the backup / test box is running 7.3.2. both
have dual 750 MHz CPUs and 1.5 gig ram with a 10krpm USCSI one for system,
one for postgresql.