Re: Postgresql replication

William Yu wrote:

>
> This system sounds ok for documents and general data that can always
> be revived via version control/history. But I can't see how this would
> work for financial transactions where you're dealing with money and
> bank accounts. Suppose I have $100 in my account. I decided to login
> to multiple servers and wire transfer $100 to another account on every
> server. And I hit submit exactly at the same time for every server so
> check. Sure they can resolve the conflict afterwards in terms of
> saying in terms of which transfer to kill off. But the fact is that my
> other account has that N x $100 already and I've just fleeced the bank.
>
Hmmm.......

I think you should realize by now that no multimaster async replication
solution is going to adequately work for something like these financial
transactions. You need either synchronous or single-master simply
because you need to have a concept that there is a *single*
authoritative source of the current status and balance on the account.
You can't really expect async-replicated servers to know about
transactions that haven't been replicated yet can you?

In other words this problem is inherent to multimaster async
replication. There is no way around it. If there was it would either
not be async or not be multimaster :-)

I have built my own async multimaster replication systems for some
applications. One of them is actually a system where you have two and
only two masters which replicate back and forth. The trick I used here
was for each master to use a different SERIAL field as its primary key
so there are no duplicates. Another option is to have a compound
primary key which is a serial and a server-hash (this would scale to a
larger number of masters).

Best Wishes,
Chris Travers
Metatron Technology Consulting