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