Re: replication/synchronisation

From: "roy simkes" <roysimkes(at)hotmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: replication/synchronisation
Date: 2006-09-14 09:48:45
Message-ID: BAY108-W2E30D279DBC4F4C6895F2A1290@phx.gbl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

> 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.
_________________________________________________________________
Search from any Web page with powerful protection. Get the FREE Windows Live Toolbar Today!
http://get.live.com/toolbar/overview

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Tom Lane 2006-09-14 13:01:51 Re: Problem with triggers and cursors
Previous Message Shane Ambler 2006-09-14 07:54:56 Re: replication/synchronisation