From: Craig James <craig_james@emolecules.com>
To: Campbell, Lance <lance@uiuc.edu>
Cc: pgsql-performance@postgresql.org
Subject: Re: Backup causing poor performance - suggestions
Date: Mon, 05 May 2008 09:10:16 -0700
Message-ID: <481F3168.8050600@emolecules.com> (raw)
In-Reply-To: <B10E6810AC2A2F4EA7550D072CDE8760CDDD2C@SAB-FENWICK.sab.uiuc.edu>
References: <B10E6810AC2A2F4EA7550D072CDE8760CDDD2C@SAB-FENWICK.sab.uiuc.edu>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: craig_james@emolecules.com, lance@uiuc.edu
Subject: Re: Backup causing poor performance - suggestions
In-Reply-To: <481F3168.8050600@emolecules.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox