Re: Replicating databases

On Wed, Nov 02, 2005 at 12:06:36PM +0000, Carlos Benkendorf wrote:
> I would appreciate suggestions about how the best way to implement
> such soluction.
>
> Slony-1? SQL scripts?

Maybe a combination.  My natural inclination would be to try to do
this with some tricky views+rules so that each store could write into
its own table (then everybody could replicate, and in fact you could
have the other store data updated, but maybe not as fast as real
time).  The problem is that in the central database, this is going to
turn out to be a big, nasty UNION if there are more than a handful of
stores.

But, you could do this with some batch processing in the night at
each store, such that you pulled local data into a special local
table (into which you'd write, depending on your local store id) and
the non-local table.  Again, you could use a view with rules to allow
writing into these local tables.  Then during the batch processing at
night, you could merge all these changes together, and prepare
special sets to push out to the stores so that they could see
everyone else's day old data.

It seems kludgey this way, though.  What you really need is
multimaster with conflict resolution, because you can depend on your
application to cause no conflicts.  Slony is designed to prevent you
from writing into the replicated tables.  Some of the other
master-slave ones don't have that restriction, but they're sort of
dangerous for the same reason.

A

--
Andrew Sullivan  | ajs@crankycanuck.ca
The whole tendency of modern prose is away from concreteness.
        --George Orwell