Campbell, Lance wrote:
> We currently backup all of our database tables per schema using pg_dump
> every half hour. We have been noticing that the database performance
> has been very poor during the backup process. How can I improve the
> performance?
It sounds like the goal is to have frequent, near-real-time backups of your databases for recovery purposes. Maybe
insteadof looking at pg_dump's performance, a better solution would be a replication system such as Slony, or a "warm
backup"using Skype Tools.
Backing up the database every half hour puts a large load on the system during the dump, and means you are re-dumping
thesame data, 48 times per day. If you use a replication solution, the backup process is continuous (spread out
throughthe day), and you're not re-dumping static data; the only data that moves around is the new data.
I've used Slony with mixed success; depending on the complexity and size of your database, it can be quite effective.
I'veheard very good reports about Skype Tools, which has both a Slony-like replicator (not as configurable as Slony,
buteasier to set up and use), plus an entirely separate set of scripts that simplifies "warm standby" using WAL
logging.
Craig