Re: Backup causing poor performance - suggestions

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 instead of 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 the same data, 48 times per day. If you use a replication solution, the backup process is continuous (spread out through the 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've heard very good reports about Skype Tools, which has both a Slony-like replicator (not as configurable as Slony, but easier to set up and use), plus an entirely separate set of scripts that simplifies "warm standby" using WAL logging.

Craig