Обсуждение: Replication for R/W offline use
Dear Gurus, I need some solution for a transaction-based system that may have offline clients. Something like CVS in version management, but SQL-based. I need this because we have a team of 6 people, concurrently updating shared XML files and such. When I leave the corporate network I can keep updating corporate data, along with my colleagues. When I join the corporate network again the data will then be published to a common place. I thought about PostgreSQL for several reasons. First of all, concurrent writes while online will not cause confusion. What I need: - If client is online the data should be stored in master DB with data integrity - if client is offline the data should be stored locally - If data is stored locally and client goes online data should be synced to master. - Way of handling conflicting records e.g. client A goes offline, client B modifies master, client A modifies locally, and when goes online again cannot commit the changes. i.e. I need to insert/update (maybe not delete) records even at times when I cannot access the DB (of course, will be committed later). What I DON'T need: - No need for performance (no massive data) - No need for automatic sync (client-initiated sync is sufficient; automatic is a bonus though) - No need to be generic, just some tables should be monitored. Even if I have to write triggers for each table it's OK. - Don't even need master, if it can be solved without master. Crudely speaking I can imagine it something like: - when client goes offline a "snapshot" is present locally, just like at the beginning of a transaction - when client goes online all the changes are "committed" to the master db, just like at end of transaction. The difference is that I do not know when will the client go offline, even client does not know (e.g. ISP goes down) and, of course, there may be a lot of connections while client is offline. Inserting is piece of cake but updates give me a headache. Is there some addon, contrib, whatever for PostgreSQL that can be used for such purpose? Is there some guide how to implement such a system? (my first guess is helper tables, similar in redo logs, but only for specific changes in the database) -- G.
-----BEGIN PGP SIGNED MESSAGE----- Hash: RIPEMD160 > - Way of handling conflicting records > e.g. client A goes offline, client B modifies master, client A > modifies locally, and when goes online again cannot commit the > changes. One possible solution you should look at is Bucardo, which does master-master replication. One of the tricky parts you might want to reconsider though is: > The difference is that I do not know when will the client go offline, > even client does not know (e.g. ISP goes down) and, of course, there > may be a lot of connections while client is offline. This says to me that you should simply make all writes go to the local box, and have Bucardo keep trying to keep things in sync. If it can connect, great. If not, it dies and tries again later. http://bucardo.org/ - -- Greg Sabino Mullane greg@turnstep.com End Point Corporation PGP Key: 0x14964AC8 200907311359 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iEYEAREDAAYFAkpzMSsACgkQvJuQZxSWSsgpoQCgrNa9PFxtVvKcljTguCCvmN6E HnkAn3OzYFCJZeR7vvhC9Hr/+DVs9tir =+Tr0 -----END PGP SIGNATURE-----
Hello Greg, Thanks, that sounds really reasonable. I'll have to get really familiar with it, though. First of all, I need Windows clients for offline use -- maybe cygwin will do. Second, I don't exactly know what is the effect of disabling triggers and rules: will it affect transactions running in parallel (a real problem), or will triggers be disabled for that transaction only (sounds like trouble)? On Fri, Jul 31, 2009 at 8:00 PM, Greg Sabino Mullane<greg@turnstep.com> wrote: > > -----BEGIN PGP SIGNED MESSAGE----- > Hash: RIPEMD160 > > >> - Way of handling conflicting records >> e.g. client A goes offline, client B modifies master, client A >> modifies locally, and when goes online again cannot commit the >> changes. > > One possible solution you should look at is Bucardo, which > does master-master replication. One of the tricky parts you > might want to reconsider though is: > >> The difference is that I do not know when will the client go offline, >> even client does not know (e.g. ISP goes down) and, of course, there >> may be a lot of connections while client is offline. > > This says to me that you should simply make all writes go to the > local box, and have Bucardo keep trying to keep things in sync. > If it can connect, great. If not, it dies and tries again later. > > http://bucardo.org/ > > - -- > Greg Sabino Mullane greg@turnstep.com > End Point Corporation > PGP Key: 0x14964AC8 200907311359 > http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 > -----BEGIN PGP SIGNATURE----- > > iEYEAREDAAYFAkpzMSsACgkQvJuQZxSWSsgpoQCgrNa9PFxtVvKcljTguCCvmN6E > HnkAn3OzYFCJZeR7vvhC9Hr/+DVs9tir > =+Tr0 > -----END PGP SIGNATURE----- > > > > -- > Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-admin > -- G.
-----BEGIN PGP SIGNED MESSAGE----- Hash: RIPEMD160 > First of all, I need Windows clients for offline use -- maybe cygwin will do. Perhaps. Keep in mind the Bucardo daemon(s) can live anywhere, doesn't have to be on the Windows boxes themselves. > Second, I don't exactly know what is the effect of disabling triggers > and rules: will it affect transactions running in parallel (a real > problem), or will triggers be disabled for that transaction only > (sounds like trouble)? No, it will not affect anything outside of that transaction, and thus cause no trouble. - -- Greg Sabino Mullane greg@turnstep.com End Point Corporation PGP Key: 0x14964AC8 200908131627 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iEYEAREDAAYFAkqEd1QACgkQvJuQZxSWSshQwQCg4MhCPyzx0zmNFN3uCY6C/YRB 6OcAoNss20HkPudynjm1jt/TNS+NSour =O0hG -----END PGP SIGNATURE-----