Message-ID: <4CB67C03.2010300@postnewspapers.com.au>
Date: Thu, 14 Oct 2010 11:41:55 +0800
From: Craig Ringer <craig@postnewspapers.com.au>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.9.1.9) Gecko/20100423 Thunderbird/3.0.4
MIME-Version: 1.0
To: "Evan D. Hoffman" <evandhoffman@gmail.com>
CC: Brian Hirt <bhirt@me.com>,
	Postgresql Mailing List <pgsql-general@postgresql.org>
Subject: Re: Copying data files to new hardware?
References: 
 <7dc101b1-6596-4850-b0ce-cb3f01f51f14@n40g2000vbb.googlegroups.com>
	<F55203CF-D9A5-4AEB-BAC4-511767133926@me.com>
	<AANLkTimATVkVuvK=VJV3o7FWYg_emapxs=T0FxXHm1DJ@mail.gmail.com>
In-Reply-To: <AANLkTimATVkVuvK=VJV3o7FWYg_emapxs=T0FxXHm1DJ@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

On 14/10/10 00:59, Evan D. Hoffman wrote:
> Thanks, Brian & Jaime.  Regarding Slony, would that allow for
> migration to a new version as well - i.e. moving from 8.2 on the old
> machine to 8.4 on the new machine via Slony with minimal downtime?
> 
> The Slony method is one I hadn't considered.  Since our database is so
> large, even a direct file copy would require some downtime (since we'd
> need to stop the DB before beginning the copy).

You don't have to stop the DB before doing a file-level database copy,
though. If you set up WAL archiving to the destination server, you can
then use pg_start_backup(), copy the data directory while Pg is writing
to it, disconnect clients, pg_stop_backup(), and only then shut the old
Pg down.

You will face some WAL replay time bringing the new machine up, though
if you set it up as a warm spare with continuous replay that'll minimize
the replay time. Unless your master is under heavy continuous write load
it won't be too bad anyway.

The handy thing about this approach is that you can *test* it by doing
the whole migration as if you meant it for real, just not disconnecting
clients from the original server or shutting it down at the end. That
gives you a chance to verify that the DB that's been replicated to the
new server has come up cleanly and is working well before you do the
whole thing again for real. Even better, if you've done such a test you
can use rsync to update your base backup after pg_start_backup() instead
of copying the whole thing again, which can be a *LOT* faster.

If you take this approach, be VERY sure to read the documentation on
PITR and warm standby, and test your migration before doing it for real.


-- 
Craig Ringer

Tech-related writing: http://soapyfrogs.blogspot.com/