Re: Replication - Mailing list pgsql-general

On Mon, 2009-06-22 at 21:29 -0400, Gerry Reno wrote:

> I don't know how it could guarantee that.  That's really why row-based
> is better.

Yep, especially in the face of things like user PL functions, C
functions, etc.

This page:

http://dev.mysql.com/doc/refman/5.0/en/replication-features-functions.html

is downright alarming, and (implicitly) says quite enough about how
statement-based replication is a really, REALLY bad idea.

Rather than replicating sets of changed rows, though, I suspect that
block-level replication using the WAL is probably more efficient.
Certainly it'll be easier on the slave in terms of the work required to
keep up with the master.

I guess block-level replication isn't much good for multi-master,
though, since you'd be spending half your time finding out what the
other masters were doing and what their state was, or telling them about
yours. (I guess that's the case anyway to some extent, though, any time
you have concurrent statements on different masters using the same data
and one or more of them is altering it).

> Sequences I deal with by setting up an offset and increment for each
> replica so that there are no conflicts.

Ah, so you don't actually care if the replicas are identical - you
expect things like different primary keys on master and replicas(s) ?

How do your applications cope if they switch from one replica to another
and suddenly primary key identifiers are different?

> > What limitations of master-slave replication with read-only slaves
> > present roadblocks for you?
> >
> failure of single master.

For that, read-only slave + heartbeat based failover with STONITH (shoot
the other node in the head) by something like IPMI remote-poweroff or a
USB-controlled power switch would be sufficient.

The only part of the requirements for this that PG can't already satisfy
is synchronous replication - the current WAL-based replication doesn't
guarantee that the slave has the changes before the client's commit
returns successfully, so recent changes that the client thinks are
committed might be lost on failover. Synchronous replication is, of
course, what's being worked on right now, partly to address just this
issue and partly to allow for read-only reporting slaves.

This technique is well established, very robust, and it's not hard to
implement in a way that ensures that the slave - when it takes over as
master - claims the master's MAC address and IP address so clients
barely notice a change.

With Pg it'd break any existing connections, but any database
application worth a damn must be able to handle re-issuing transactions
due to deadlocks, resource exhaustion, admin statement cancellation etc
anyway.

--
Craig Ringer