Date: Wed, 1 Mar 2006 16:15:18 -0600
From: "Jim C. Nasby" <jnasby@pervasive.com>
To: Andrew Sullivan <ajs@crankycanuck.ca>
Cc: pgsql-sql@postgresql.org
Subject: Re: Replication - state of the art?
Message-ID: <20060301221518.GW82012@pervasive.com>
References: <20060301133041.8EFFA19B797@smtp03l.fasthosts.co.uk>
	<4405DF32.4070307@obviously.com>
	<20060301182803.GK17356@phlogiston.dyndns.org>
	<4405F5C6.1040300@obviously.com>
	<20060301194918.GA5200@phlogiston.dyndns.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <20060301194918.GA5200@phlogiston.dyndns.org>
User-Agent: Mutt/1.5.11

You could also use WAL shipping and some PITR trickery to keep a 'warm
standby' database up to date. How far behind it falls is up to you,
since you'll be periodically syncing the current WAL file to the backup
machine. Do the sync once a minute, and at most you lose 60 seconds of
data.

On Wed, Mar 01, 2006 at 02:49:18PM -0500, Andrew Sullivan wrote:
> On Wed, Mar 01, 2006 at 11:28:06AM -0800, Bryce Nesbitt wrote:
> > Actually let me loosen that a bit:  we don't need two phase commit.  We
> > can loose the most recent transaction, or even the last few seconds of
> > transactions.  What we can't survive is -- on the day of the emergency
> > -- a long and complicated DB rebuild with mistakes and hard-to-debug
> > data issues.
> 
> Then I suggest you use Slony-I.  While it is not plug and play, the
> thing it _is_ designed to handle reasonably well is failover and
> (better) switchover.  Most systems plan to solve that piece of
> functionality later, with a script or something, at which point it is
> apparent that setting up failover or swichover to be anything
> approaching safe is actually very tricky.  (Log shipping is probably
> not in this category, but AFAIK the promote-to-live support for a
> standby database copy is still not all built by anyone.  If you like
> rolling your own, however, it might be your answer.)
> 
> > There's no fire creating demand for replication, so there is little time
> > budget.
> > So is there a sort of padded, no-sharp-corners, playroom that gets us
> > 90% of the way there?
> 
> The "no budget" remark here is what makes me strike CMD's Mammoth
> Replicator off the list.  But I'm sure their administration tools are
> far sweeter than the admittedly hackish ones that Slony currently
> delivers out of the box.  
> 
> > nightly) into something more reasonable (like 500 milliseconds).  But
> > risk -- of data corruption --
> > and time --too much-- will can the project.
> 
> Another big reason to use a live-standby system like Slony is that
> once you have the extra database online, you suddenly think of all
> sorts of nifty queries you can move there without destroying your
> production performance.  Be careful not to get addicted, is all.
> 
> A
> 
> -- 
> Andrew Sullivan  | ajs@crankycanuck.ca
> Information security isn't a technological problem.  It's an economics
> problem.
> 		--Bruce Schneier
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match
> 

-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461