I hope this is the right list. I have read through a few pg_dump slow posts but none of them seem to apply to our case. We have a nice big server running windows server 2008 and postgres 8.4. The machine does nothing else and every so often running the nightly backup take 10 to 12 hours to complete. Most nights it completes in 1 to 2 hours. Just for some context, 5 rather large DBs get backed up. All of them have many schema (100s) and many many tables.
A few things I cannot change. I cannot switch to Linux even though I want to. I cannot upgrade to a newer postgres just yet (though 9.0 should be installed before year end). One of the issues holding a newer postgres up is the fact that backups aren't running right. :-)
So my first thought was the machine or DB being over worked, but apart from a lone vacuum at 2am there is little else going on, the cpu seems fine. I thought maybe IO, but running the command as a user during peak working hours seems to run well (< hour per DB).
Something that is curios is that if a DB takes long, it really takes horribly long like some kind of a lock is holding it. It would sit at a few kb dump size for 20 minutes en then run a bit and get stuck again (as far as we can tell), what we do know is that it is way to slow for some IO or cpu starvation. We have seen a 5GB backup sitting at 1Gb after 12hours and then we stop it.
Any siggestions? Can autovacume or lack thereof cause this? It seems noone has been doing any maintenance on the DB (it does look like autovacuum is running), so any suggestions would be nice.
Thanks, Kobus
P.S. Would we see performance improvements on windows going from 8.4 to 9.0? Any comment on the difference between 32 and 64? Is it a safe migration?