Обсуждение: Merge replication with Postgresql on Windows?
Can anyone recommend a relatively simple merge replication package that would work well on windows and which relies on one of the current postgresql versions? 9 would be fine for my needs. I'm a fairly unsophisticated postgresql user; and not very experienced with replication on other databases. So the simplest to configure and maintain solution would be best. I am not sure but it sounds like the new replication features that come baked in to postgres 9 are not so oriented towards merge replication - is that right? My use case would involve a primary postgresql database and several postgresql databases on disconnected notebook computers. All dbs need to be able to support updates inserts etc; and then hopefully the replication software would help with things like conflict resolution. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Merge-replication-with-Postgresql-on-Windows-tp2856176p2856176.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 09/27/10 8:08 PM, novnovice wrote: > Can anyone recommend a relatively simple merge replication package that would > work well on windows and which relies on one of the current postgresql > versions? 9 would be fine for my needs. I'm a fairly unsophisticated > postgresql user; and not very experienced with replication on other > databases. So the simplest to configure and maintain solution would be best. > I am not sure but it sounds like the new replication features that come > baked in to postgres 9 are not so oriented towards merge replication - is > that right? > > My use case would involve a primary postgresql database and several > postgresql databases on disconnected notebook computers. All dbs need to be > able to support updates inserts etc; and then hopefully the replication > software would help with things like conflict resolution. One of the fundamental tenets of Relational Database is that there is One Version of the Truth. I don't think there's a transparent generic solution to offline replicant re-syncing that isn't full of thorny issues in all sorts of corner cases. I would instead design your app so it writes a logfile of database changes at an abstract application level, and has a method for replaying this log when the scout hooks back up with the mothership. Common practice is for this sync process to toss any conflicts into an exception log and notify the admins to deal with it. If you design it right, the administrator can add new processing rules and resubmit the exception log until its empty :) if there's any question about the veracity of the notebooks copy after synching its application change log, I'd consider loading it with a fresh copy of the master, unless this is really expensive because of Db size.
That's a surprising response. But it makes sense, at least as one perspective. I have written light duty sync systems but figured that there would be some battle tested postgresql solution that was more robust than I could cobble together. As in, if I invest 40 hours learning replication system X, I'd be further along than if I'd invested the same 40 hours writing my own system from scratch. It's not simple stuff. It would still be good to eval whatever canned solutions are out there. I have googled this topic of course; among the candidates none seemed to be a great match up with what I hoped to find. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Merge-replication-with-Postgresql-on-Windows-tp2856176p2856288.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 09/27/10 11:18 PM, novnovice wrote: > That's a surprising response. But it makes sense, at least as one > perspective. I have written light duty sync systems but figured that there > would be some battle tested postgresql solution that was more robust than I > could cobble together. As in, if I invest 40 hours learning replication > system X, I'd be further along than if I'd invested the same 40 hours > writing my own system from scratch. It's not simple stuff. It would still be > good to eval whatever canned solutions are out there. I have googled this > topic of course; among the candidates none seemed to be a great match up > with what I hoped to find. the general case of asynchronous offline replication fundamentally breaks one of the tenets of SQL, that COMMIT only returns true if the data is validly and reliably committed to the "Truth". multimaster databases create a lot of problems for which there are no good answers that don't compromise data integrity. delaying the synchronization by indeterminate intervals via offline updatable replicas aggravates this enormously btw, I don't speak for the 'postgresql community', i'm just s database user who happens to be on this list.
On Mon, 2010-09-27 at 20:08 -0700, novnovice wrote: > Can anyone recommend a relatively simple merge replication package that would > work well on windows and which relies on one of the current postgresql > versions? 9 would be fine for my needs. I'm a fairly unsophisticated > postgresql user; and not very experienced with replication on other > databases. So the simplest to configure and maintain solution would be best. > I am not sure but it sounds like the new replication features that come > baked in to postgres 9 are not so oriented towards merge replication - is > that right? > > My use case would involve a primary postgresql database and several > postgresql databases on disconnected notebook computers. All dbs need to be > able to support updates inserts etc; and then hopefully the replication > software would help with things like conflict resolution. As far as I know, this does not exist for Postgresql. Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt
Re: Merge replication with Postgresql on Windows?
От
merlyn@stonehenge.com (Randal L. Schwartz)
Дата:
>>>>> "novnovice" == novnovice <novnovice@gmail.com> writes: novnovice> That's a surprising response. But it makes sense, at least as novnovice> one perspective. I have written light duty sync systems but novnovice> figured that there would be some battle tested postgresql novnovice> solution that was more robust than I could cobble novnovice> together. As in, if I invest 40 hours learning replication novnovice> system X, I'd be further along than if I'd invested the same novnovice> 40 hours writing my own system from scratch. It's not simple novnovice> stuff. It would still be good to eval whatever canned novnovice> solutions are out there. I have googled this topic of course; novnovice> among the candidates none seemed to be a great match up with novnovice> what I hoped to find. CouchDB is open source, and was designed with this sort of disconnected syncing in mind. You can hear my interview with Jan Lehnardt at http://twit.tv/floss36. -- Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095 <merlyn@stonehenge.com> <URL:http://www.stonehenge.com/merlyn/> Smalltalk/Perl/Unix consulting, Technical writing, Comedy, etc. etc. See http://methodsandmessages.vox.com/ for Smalltalk and Seaside discussion
Joshua, you're with command prompt...you had/have a product called mammoth replicator which I looked at. It seemed approx what I was after but the project didn't seem very alive. Was my use case not what mammoth was about? Or is it just that mammoth is basically gone? -- View this message in context: http://postgresql.1045698.n5.nabble.com/Merge-replication-with-Postgresql-on-Windows-tp2856176p2857075.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On Tue, 2010-09-28 at 09:32 -0700, novnovice wrote: > Joshua, you're with command prompt...you had/have a product called mammoth > replicator which I looked at. It seemed approx what I was after but the > project didn't seem very alive. Was my use case not what mammoth was about? > Or is it just that mammoth is basically gone? Oh it's alive. We are down to the last feature of the 1.9 release before going into the wild again. That said, no -- what we offer would not help you. We offer Master->N-Slave replication. Sincerely, Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt
On Mon, 2010-09-27 at 20:08 -0700, novnovice wrote: > Can anyone recommend a relatively simple merge replication package that would > work well on windows and which relies on one of the current postgresql > versions? 9 would be fine for my needs. I'm a fairly unsophisticated > postgresql user; and not very experienced with replication on other > databases. So the simplest to configure and maintain solution would be best. > I am not sure but it sounds like the new replication features that come > baked in to postgres 9 are not so oriented towards merge replication - is > that right? > > My use case would involve a primary postgresql database and several > postgresql databases on disconnected notebook computers. All dbs need to be > able to support updates inserts etc; and then hopefully the replication > software would help with things like conflict resolution. As far as I know, this does not exist for Postgresql. Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt
On Tue, 2010-09-28 at 09:32 -0700, novnovice wrote: > Joshua, you're with command prompt...you had/have a product called mammoth > replicator which I looked at. It seemed approx what I was after but the > project didn't seem very alive. Was my use case not what mammoth was about? > Or is it just that mammoth is basically gone? Oh it's alive. We are down to the last feature of the 1.9 release before going into the wild again. That said, no -- what we offer would not help you. We offer Master->N-Slave replication. Sincerely, Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt
novnovice wrote: > My use case would involve a primary postgresql database and several > postgresql databases on disconnected notebook computers. All dbs need to be > able to support updates inserts etc; and then hopefully the replication > software would help with things like conflict resolution. > The main PostgreSQL compatible software that's good at this role is Bucardo: http://bucardo.org/ It allows having multiple masters, so each of your disconnected nodes would each be one. And then when they reconnect, they asynchronously replicate back to the big master. The exact way in which conflict resolution is done is something you can provide a handler for. So it handles most of the dirty work of figuring out what's changed via triggers, and you just plug in the application logic that makes sense for you in order to resolve any conflicts. You are correct that the built-in replication features in 9.0, and most of PostgreSQL replication software, it not really suitable for your situation. Bucardo probably is. -- Greg Smith, 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD PostgreSQL Training, Services and Support www.2ndQuadrant.us Author, "PostgreSQL 9.0 High Performance" Pre-ordering at: https://www.packtpub.com/postgresql-9-0-high-performance/book
Thanks Greg. I'd had a quick look at bucardo and while it seems capable of doing the job (or helping to do that job), I was hoping there was something simpler. But I will have a closer look. Have you ever evaluated rubyrep (http://www.rubyrep.org/)? It's also multi master. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Merge-replication-with-Postgresql-on-Windows-tp2856176p3072858.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On Fri, Oct 1, 2010 at 11:40 AM, novnovice <novnovice@gmail.com> wrote: > Thanks Greg. I'd had a quick look at bucardo and while it seems capable of > doing the job (or helping to do that job), I was hoping there was something > simpler. But I will have a closer look. > > Have you ever evaluated rubyrep (http://www.rubyrep.org/)? It's also multi > master. > I don't think one is simpler than the other.