Mobile servers replication

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of
Carlos H. Reimer
Sent: 09 July 2006 12:17
To: pgsql-general@postgresql.org
Subject: [GENERAL] Mobile servers replication

Hi,

We´re looking for a replication solution that could address
the following
situation: every morning our sellers connect to the master
server and make a
copy of all tables they will need during the day to their
laptop database.
After a day of work, with a lot of work done at their local
database they
reconnect to the master server and resync their data.

In my opinion, to address that, this situation must be taken into
consideration when the application and database design are designed.

Or is there a magic solution, a product, that could do it?

I would like to hear some other opinions about this issue.

Slony's log shippping feature would probably work for your morning download, and if arranged such that data was uploaded to a different Slony cluster (and merged on the server with the other data later) could also handle the upload.

For true synchronisation however, you will need to build it in to the application I suspect. It can be done relatively easily by using a row versioning system in which every updated or inserted row gets an ever-increasing version number attached. At sync time, the client inserts or updates rows with a higher version than it has previously seen. Add an extra table to record deleted rows, and use a client identifier as part of the pkey, or have the server assign 'real' ID's during sync to replace temporary ones assigned on the clients. Also consider conflict resolution (ie. If the client and another user both update the same row). You may be able to merge the changes, or let the user choose to overwrite the previous users changes, or let an algorithm decide.

I have a couple of Pocket PC apps that sync SQL Server CE databases with PostgreSQL in this way, with tens of mobile users and it works very well.

Regards, Dave