Search Postgresql Archives

Re: Postgresql replication

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



William Yu wrote:

Chris Travers wrote:

I guess I am thinking along different lines than you. I was thinking that the simplest solution would be to have master/slave replication for *approved* transactions only and no replication for initial commits prior to approval. This makes the assumption that a single transaction will be committed on a single server, and that a single transaction will not be split over multiple servers. In this way, you can commit a pending transaction to any single server, and when it is approved, it gets replicated via the master. See below for more.


This works if you don't care that multiple servers commit transactions that force a budget or bank account to be exceeded.

Ok. then lets look at possibility B. (Async Multimaster Replication is out).


Thinking about this.... The big issue is that you only want to replicate the deltas, not the entire account. I am still thinking master/slave, but something where the deltas are replicated in the background or where the user, in checking his account, is actually querying the home server. This second issue could be done via dblink or DBI-Link and would simply require that a master table linking the accounts with home servers be replicated (this should, I think, be fairly low-overhead).


Except what you know have is your system fails if any server fail or is inaccessible.

Ok. If you have a system where each location is authoritative for its customers and the server transparently queries that server where needed (via a view). then when any server becomes inaccessible then the customers whose accounts are on that server become inaccessible. This may not be accessible. But if this is the case, then you could treat this as a partitioned table, where each partition is authoritative on one location (see a previous post on how to do this) and then use Slony to replicate. Again this does not get around your objection above namely that it is possible to do duplicate transactions at multiple locations. For this you would need an explicit call to the authoritative server. I see no other way around that. This might allow people to view tentative balances from other branches if the home (or its connection) server is down, but they would not be able to withdraw funds.

But if you do this, you have a different problem. Namely that replication will be occuring over your slower than desired links. As the number of branches grow, so will the bandwidth demands on every branch. This may not therefore be scalable.

Unfortunately there is no free lunch here. And I think that at some point you are going to have to choose between:

1)  Efficiency of network throughput
2) Tolerance to attempts at repeat transactions before replication (emptying an account multiple times)
3)  Availability of a transaction.

You can pick any two. I think that this is the way it will work with any other database system as well.

Best Wishes,
Chris Travers
Metatron Technology Consulting

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

              http://www.postgresql.org/docs/faq




---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

              http://archives.postgresql.org

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux