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.