Re: replication/synchronisation

On 7/9/2006 12:40, "Glenn Davy" <glenn(at)tangelosoftware(dot)net> wrote:

> Hi All - now heres a stupid question...
> for better or worse a lot of project 'documentation' (wikis et.al.) and
> project administration occurs in web applications. A lot of my time is
> spent for example in either trac or media wiki - and in my case these
> nearly are always in a postgres backend.
>
> Unfortunately I can no longer just check out the latest documents and
> take em off line on my laptop, edit them and merge them back later any
> more.
>
> so obviously I want to take my laptop with a snap shot of the database,
> (and wiki software etc) work on it, then synchronise it back to the
> 'master'.
>
> My question (finally) is: are the existing tools for that purpose - are
> pgpool or pgcluster or slony suitable? I'm tempted to try and script it
> myself, but am apprehensive about manging id clashes primarily, and of
> course managing rows updated in >1 db instance - and whatever else I
> havent even though of
>
Not sure of your exact scenario but sounds like you may want to consider a
custom sync app. The ones you mentioned are designed for situations where
multiple servers are running and can 'talk' to each other all the time.

If you are the only one adding/changing the db it will be easy. If others
are adding/changing then new rows inserted from your laptop copy would need
to change the insert id of a new laptop row to null (assuming you use serial
for id) to get a new id from the server sequence (so you would need to know
the last id inserted when you get a copy to know which rows are added on the
laptop) and you would need a server mod timestamp and laptop mod timestamp
to know what rows you changed that you want back to the server - this would
also be a double check to see if the server copy has changed since you took
a copy - if the server has a new mod timestamp for a row you updated then
you decide how to handle the update.

The other thing to consider and probably the easiest way is that if you are
doing the mods through a web front end then why not connect to that from
your laptop and work on the server from wherever you are. If it is in an
internal network look at setting up a vpn or remote dial-in to the local
network if setting up port forwarding on the router isn't an option.

--

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

Get Sheeky @ http://Sheeky.Biz