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

thanks for any help
glenn


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

glenn@tangelosoftware.net (Glenn Davy) writes:
> 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

Unfortunately, the "usual suspects" are generally designed to work
when systems are connected more or less all the time.

Furthermore, what you're after seems to be one of the forms of
"multimaster" replication, which is the tougher problem that generally
isn't supported.

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.
--
let name="cbbrowne" and tld="acm.org" in String.concat "@" [name;tld];;
http://linuxfinances.info/info/wp.html
He who laughs last thinks slowest.

On 7/9/2006 12:40, "Glenn Davy" <glenn@tangelosoftware.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@007Marketing.com

Get Sheeky @ http://Sheeky.Biz



---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Hey Chris
On Thu, 2006-09-07 at 18:38 -0400, Chris Browne wrote:
> glenn@tangelosoftware.net (Glenn Davy) writes:
> > 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
>
Thanks for replying

> Unfortunately, the "usual suspects" are generally designed to work
> when systems are connected more or less all the time.
>
yep - thats true
> Furthermore, what you're after seems to be one of the forms of
> "multimaster" replication,
yes that would make sense

> which is the tougher problem that generally
> isn't supported.
>
just wondering what you mean by 'generally'?
> 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
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.
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?

glenn

---------------------------(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

On 14/9/2006 15:50, "Glenn Davy" <glenn@tangelosoftware.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
>
> ---------------------------(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
>

--

Shane Ambler
Postgres@007Marketing.com

Get Sheeky @ http://Sheeky.Biz


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

glenn@tangelosoftware.net (Glenn Davy) writes:
> Hey Chris
> Thanks for replying
>
>> Unfortunately, the "usual suspects" are generally designed to work
>> when systems are connected more or less all the time.
>>
> yep - thats true
>> Furthermore, what you're after seems to be one of the forms of
>> "multimaster" replication,
> yes that would make sense
>
>> which is the tougher problem that generally
>> isn't supported.
>>
> just wondering what you mean by 'generally'?

None of the "product-like" things like Slony-I, Mammoth Replicator,
and such support multimaster replication.

>> 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 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.
>
> 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?

Conflict resolution is the Big Problem with asynchronous multimaster
replication.

Nobody has a direct answer to it. People try to design schemas to
avoid conflicts as much as possible.

When conflicts occur, a DBA has to do something manual, figuring out
what the problem was and how to fix it.

Microsoft has spent a lot of developer time on tools to cover this
over.
--
output = reverse("ofni.sesabatadxunil" "@" "enworbbc")
http://linuxfinances.info/info/languages.html
"There are almost unlimited ways for making your programs more
complicated or bizarre" -- Arthur Norman