Re: Failover architecture

I can't help so much with the Pg replication specific parts, but this I
can answer:

On 17/08/2011 9:25 PM, Reuven M. Lerner wrote:
>     restart streaming, it goes back into read-write mode.  Is there a
>     way (other than Bucardo, which doesn't seem to fit the bill for this
>     project), is there any way for us to merge whatever diffs might be
>     on the two servers, and then reconnect them in master-slave
>     streaming mode when communication is re-established?

Nope.

Merging diffs between two "forked" database timelines is not possible
with PostgreSQL's built-in replication. Pg does replication at the block
level, so there's no meaningful way to merge the changes.

Even if replication were done at the tuple level, how would you merge
changes where both forks INSERTed into a table with a sequence-generated
primary key? Or used an aggregate like sum(...) when generating content
for a new record?

Statement-level replication has similar issues. An app may calculate a
value that it includes in a query based on the result of a prior query
or might issue a different query depending on prior queries. This makes
it impossible to interleave and replay recorded statements when contact
is resumed and still get consistent, correct results.

It's a lot like the SERIALIZABLE transaction problem on a larger scale.
Often you can run two transactions in parallel and have them produce the
same results as they would've done when run serially. It's not possible
to guarantee this (without predicate locking and communication between
the transactions) though, which is why apps must be prepared for
serializable transactions to fail. Same deal when merging timelines,
except that you're dealing with long-committed transactions the app
_trusts_ the database to have successfully recorded.

The only way to do this sort of thing seems to be at the application
level. You can insert new keys with UUIDs to work around sequence
issues, etc, but you'll still have to handle delete collisions and
numerous other issues yourself. No-SQL folks may chime in with "<my-db>
magically fixes this" here, but all the cases I've seen so far just push
the problem back to the application to deal with rather than finding a
true solution for seamlessly merging forked timelines.

I suspect the only sane way to cope with these issues _reliably_ will be
to have your app _always_ run with the assumption that the other server
is unreachable, and always be synchronizing with the other server as it
goes. Otherwise you'll find that everything works great until your link
goes down, then it'll turn out that your clever merge-and-sync logic has
bugs that eat your data. Of course, you'll probably find that your DB
access logic becomes cumbersome and painful...

I can't help thinking that there must be some easy solution to this, but
I've never seen anyone solve the DB change merging problem properly.
Everyone who claims to turns out to have a "solution" with numerous
caveats and limitations - or numerous obvious flaws. Once you fork a
timeline where events may depend on the outcome of prior events, you
cannot guarantee that you can seamlessly merge them into a single
timeline where every event happens (or doesn't happen) in the same order
as it would've without the fork.

--
Craig Ringer