Re: Postgresql replication - Mailing list pgsql-general

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
>
>