Re: Replicating databases

On Wed, Nov 02, 2005 at 05:45:40PM -0500, Andrew Sullivan wrote:
> 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

Isn't Slony2 supposed to do just that?

> 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(at)crankycanuck(dot)ca
> The whole tendency of modern prose is away from concreteness.
> --George Orwell
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>

--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461