Re: replication/synchronisation

On 14/9/2006 19:18, "roy simkes" <roysimkes(at)hotmail(dot)com> wrote:

>
>
>> Date: Thu, 14 Sep 2006 17:24:56 +0930> Subject: Re: [NOVICE]
>> replication/synchronisation> From: pgsql(at)007Marketing(dot)com> To:
>> glenn(at)tangelosoftware(dot)com(dot)au> CC: pgsql-novice(at)postgresql(dot)org> > On 14/9/2006
>> 15:50, "Glenn Davy" <glenn(at)tangelosoftware(dot)net> wrote:> > >> In effect, what
>> you're trying to do is akin to what PalmOS and Lotus> >> Notes solve using
>> synchronization systems. There aren't any tools I> >> can readily point you
>> to to help do this with PostgreSQL, alas.> > ok thanks - Im wondering how
>> microsoft access and mssql server seem to> > achieve this so easily - it
>> seems to be tied up with that massively long> > Only seems so easy because
>> they have taken the time to develop the feature> > > alpha unique row id
>> (uid?) i wonder if adding similar fields (ensuring> > uniqueness could prove
>> interesting) to all tables in any given schema> > and triggers or rules to
>> maintain them would allow provide a basis for> > some sort of simple system?
>> Im clearly out of my depth here - just> > puzzled why it seems so doable in
>> some platforms and nigh impossible on> > others.> > You would use a unique
>> rowID to match rows between the two copies and two> timestamps one would be
>> the time the server was modified the other for when> the roaming copy was
>> modified - this will allow you to know which copy was> modified since the
>> last synchronisation and which way to copy changes.> > > I guess im wondering
>> if there is something intrinsic to postgres that> > makes this idea
>> prohibitive, or is it that developers already have hands> > full with other
>> features on their minds?> > No technical reason, just needs someone to take
>> the time to do it.> > > glenn
> MSSQL has a different name for this multimaster replication thing which is
> called peer to peer replication. In PgSQL and MySQL there are cluster
> architectures for it. Which basically they are doing what you want. Sychronous
> and asynchronous according to your choice. But the thing is, they require a
> fast connection and they are only recommended to the local area networks. I
> had searched this multi master replication for a while and find out that
> master/multi slave solution was far more better and less problematic.
> In this kind of replication you could encounter with uniqueID problems. Two
> different nodes can have entered the same ID which will cause a problem. To
> solve this if you have 2 nodes. Then increase the value of serial by two. Then
> odd numberrs will be used by node 1 and pair numbers by node 2. There are two
> issues in this kind of solution which quite good for a multimaster
> replication. First, what if you intend to add more nodes! Your whole
> architecture is gone and should be remade. To prevent it, if you have two
> nodes then increase your number by 5 which will give you the opportunity to
> add more nodes. Second issue is the max value of uniqueID. You can reach the
> max value while there is only 1/5 of the files entered because you increase by
> 5. So you could prefer to use something different then the serial then.
>
> It will be far more better if you execute all your queries in the master then
> replicate to the slaves then trying to manage a multi master multi slave
> system. There can be wrong things in my opinions, things I have been missing
> or misunderstood, but hope that helps.

The way I would consider tackling the problem would be having the client
distinguish between master and roaming server, maybe with an identifying
query or have a stored proc that knows which is running to enter the mod
timestamps, entries in the main server would fill in a server mod timestamp
and entries in the roaming server would enter a roaming mod timestamp.

When synchronising a null server timestamp in the roaming row would indicate
a row inserted while roaming and would need to consider renumbering the
rowID and necessary foreign keys to align with the master rowID.

Maybe a support table to track synchronising info could be used to track
last synch times and last rowID at that time to simplify getting new rows
from the master server, this could be the way to allow multiple roaming
servers.

A simple thing like blog updating wouldn't pose any problem with this
scenario but a more complex db such as one with billing/invoicing etc would
need other considerations. eg. An invoice number created by a travelling rep
would need to be unique and remain unchanged between synchronising if
printed away from the master server.

--

Shane Ambler
Postgres(at)007Marketing(dot)com

Get Sheeky @ http://Sheeky.Biz