Message-ID: <481F3168.8050600@emolecules.com>
Date: Mon, 05 May 2008 09:10:16 -0700
From: Craig James <craig_james@emolecules.com>
User-Agent: Thunderbird 2.0.0.14 (Macintosh/20080421)
MIME-Version: 1.0
To: "Campbell, Lance" <lance@uiuc.edu>
CC: pgsql-performance@postgresql.org
Subject: Re: Backup causing poor performance - suggestions
References: <B10E6810AC2A2F4EA7550D072CDE8760CDDD2C@SAB-FENWICK.sab.uiuc.edu>
In-Reply-To: <B10E6810AC2A2F4EA7550D072CDE8760CDDD2C@SAB-FENWICK.sab.uiuc.edu>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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