Thread: Looking for way to replicate master db to multiple mobile databases

Looking for way to replicate master db to multiple mobile databases

From
Bryan Montgomery
Date:
Hi,
I'm looking for a way to replicate am master database to multiple (100+) databases that are taken in to the field. Currently for each laptop we dump and load the tables. However,there is only a small percentage of data that changes on a frequent basis.
 
I've been looking around and come across pyerplica, londiste and bucardo - the documentation on most of these is fairly sparse. It seems that Bucardo may be the best bet - at least initially.
 
However, I thought I'd see if anyone is doing something similar and what thoughts there might be out there as to a better way to accomplish this.
 
Thanks,
Bryan. 

Re: Looking for way to replicate master db to multiple mobile databases

From
Scott Marlowe
Date:
On Wed, Sep 23, 2009 at 11:11 AM, Bryan Montgomery <monty@english.net> wrote:
> Hi,
> I'm looking for a way to replicate am master database to multiple (100+)
> databases that are taken in to the field. Currently for each laptop we dump
> and load the tables. However,there is only a small percentage of data that
> changes on a frequent basis.
>
> I've been looking around and come across pyerplica, londiste and bucardo -
> the documentation on most of these is fairly sparse. It seems that Bucardo
> may be the best bet - at least initially.
>
> However, I thought I'd see if anyone is doing something similar and what
> thoughts there might be out there as to a better way to accomplish this.

The problem domain you're working on is a bit different from regular
replication.  Most replication solutions are made to keep two machines
that talk to each other all the time in sync.  Disconnect one machine
and maybe replication will resume properly, and maybe it wont.

So, do you need the slave databases to be updatable?  Do you need the
changes to go back into the master?  Do you need conflict resolution?
Depending on the full fleshed out requirements, you may be stuck
writing your own solution, or re-writing one somebody already wrote.

Re: Looking for way to replicate master db to multiple mobile databases

From
Bryan Montgomery
Date:
Thanks for the reply. This is a one way push to the slaves. In theory, there shouldn't be any conflicts .... although I wouldn't swear to that. If there's a conflict, the master db should win. At the moment we just drop the tables, recreate the schema and reload the tables. However, some of the large tables literally take hours across the network, for maybe a few dozen changes.

On Wed, Sep 23, 2009 at 4:58 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
On Wed, Sep 23, 2009 at 11:11 AM, Bryan Montgomery <monty@english.net> wrote:
> Hi,
> I'm looking for a way to replicate am master database to multiple (100+)
> databases that are taken in to the field. Currently for each laptop we dump
> and load the tables. However,there is only a small percentage of data that
> changes on a frequent basis.
>
> I've been looking around and come across pyerplica, londiste and bucardo -
> the documentation on most of these is fairly sparse. It seems that Bucardo
> may be the best bet - at least initially.
>
> However, I thought I'd see if anyone is doing something similar and what
> thoughts there might be out there as to a better way to accomplish this.

The problem domain you're working on is a bit different from regular
replication.  Most replication solutions are made to keep two machines
that talk to each other all the time in sync.  Disconnect one machine
and maybe replication will resume properly, and maybe it wont.

So, do you need the slave databases to be updatable?  Do you need the
changes to go back into the master?  Do you need conflict resolution?
Depending on the full fleshed out requirements, you may be stuck
writing your own solution, or re-writing one somebody already wrote.

Re: Looking for way to replicate master db to multiple mobile databases

From
Sam Mason
Date:
On Wed, Sep 23, 2009 at 06:00:03PM -0400, Bryan Montgomery wrote:
> Thanks for the reply. This is a one way push to the slaves. In theory, there
> shouldn't be any conflicts .... although I wouldn't swear to that. If
> there's a conflict, the master db should win. At the moment we just drop the
> tables, recreate the schema and reload the tables. However, some of the
> large tables literally take hours across the network, for maybe a few dozen
> changes.

Could you just replay WAL updates?  I.e. have two copies of the database
on each device, one as the mirror of the "master" and one as the "live"
version.  When you need to push the changes out, just push out the WAL
updates, dump the "live" version, copy the "master" into a new "live"
version and then replay the new WAL records.

Depends on how much disk space you have I guess.

--
  Sam  http://samason.me.uk/

Re: Looking for way to replicate master db to multiple mobile databases

From
Alexey Klyukin
Date:
Hi,

On Wed, Sep 23, 2009 at 8:11 PM, Bryan Montgomery <monty@english.net> wrote:
> Hi,
> I'm looking for a way to replicate am master database to multiple (100+)
> databases that are taken in to the field. Currently for each laptop we dump
> and load the tables. However,there is only a small percentage of data that
> changes on a frequent basis.
>
> I've been looking around and come across pyerplica, londiste and bucardo -
> the documentation on most of these is fairly sparse. It seems that Bucardo
> may be the best bet - at least initially.

You might have a look at Mammoth Replicator (I'm a developer of it).
Although we haven't checked whether it works with hundreds of slaves,
in theory it should. The feature that can be useful to your setup is
'batched' updates, designed for slaves that are not constantly
connected to the master server. These slaves connect to the
replication server for some configurable period of time, get the new
data, and disconnect until the next attempt.

>
> However, I thought I'd see if anyone is doing something similar and what
> thoughts there might be out there as to a better way to accomplish this.
>
> Thanks,
> Bryan.



--
Alexey Klyukin   wwww.commandprompt.com
The PostgreSQL Company - Command Prompt, Inc

Re: Looking for way to replicate master db to multiple mobile databases

From
Selena Deckelmann
Date:
Hi!

On Wed, Sep 23, 2009 at 10:11 AM, Bryan Montgomery <monty@english.net> wrote:
> Hi,
> I'm looking for a way to replicate am master database to multiple (100+)
> databases that are taken in to the field. Currently for each laptop we dump
> and load the tables. However,there is only a small percentage of data that
> changes on a frequent basis.
>
> I've been looking around and come across pyerplica, londiste and bucardo -
> the documentation on most of these is fairly sparse. It seems that Bucardo
> may be the best bet - at least initially.

Bucardo is a good choice for this usage model because it was
originally designed to work over a lossy network connections.

You could issue 'kicks' for each laptop sync when you know for sure
that a laptop has got an active network connection to your master.
It's also pretty efficient with updates, only copying the current row
(that's changed) a single time, rather than multiple times if there
have been multiple changes to that row since the last time a sync
occurred.

-selena

--
http://chesnok.com/daily - me
http://endpoint.com - work

Re: Looking for way to replicate master db to multiple mobile databases

From
Cédric Villemain
Date:
Le jeudi 24 septembre 2009, Selena Deckelmann a écrit :
> Hi!
>
> On Wed, Sep 23, 2009 at 10:11 AM, Bryan Montgomery <monty@english.net>
wrote:
> > Hi,
> > I'm looking for a way to replicate am master database to multiple (100+)
> > databases that are taken in to the field. Currently for each laptop we
> > dump and load the tables. However,there is only a small percentage of
> > data that changes on a frequent basis.
> >
> > I've been looking around and come across pyerplica, londiste and bucardo
> > - the documentation on most of these is fairly sparse. It seems that
> > Bucardo may be the best bet - at least initially.
>
> Bucardo is a good choice for this usage model because it was
> originally designed to work over a lossy network connections.

yes, but isn't bucardo designed to 2 nodes only ?

>
> You could issue 'kicks' for each laptop sync when you know for sure
> that a laptop has got an active network connection to your master.
> It's also pretty efficient with updates, only copying the current row
> (that's changed) a single time, rather than multiple times if there
> have been multiple changes to that row since the last time a sync
> occurred.
>
> -selena
>


--
----
Cédric Villemain
Administrateur de Base de Données
Cel: +33 (0)6 74 15 56 53
http://dalibo.com - http://dalibo.org

Attachment

Re: Looking for way to replicate master db to multiple mobile databases

From
Joshua Tolley
Date:
On Thu, Sep 24, 2009 at 05:09:26PM +0200, Cédric Villemain wrote:
> > Bucardo is a good choice for this usage model because it was
> > originally designed to work over a lossy network connections.
>
> yes, but isn't bucardo designed to 2 nodes only ?

Bucardo's multi-master replication works only between two hosts, unless
perhaps you can assure that only certain primary keys will be updated on
certain hosts, or do some other trickery. Syncing from one master to multiple
slaves is straightforward, if all you need is master->slave.

--
Joshua Tolley / eggyknap
End Point Corporation
http://www.endpoint.com

Attachment

Re: Looking for way to replicate master db to multiple mobile databases

From
Selena Deckelmann
Date:
On Thu, Sep 24, 2009 at 8:09 AM, Cédric Villemain
<cedric.villemain@dalibo.com> wrote:
> Le jeudi 24 septembre 2009, Selena Deckelmann a écrit :
>> Hi!
>>
>> On Wed, Sep 23, 2009 at 10:11 AM, Bryan Montgomery <monty@english.net>
> wrote:
>> > Hi,
>> > I'm looking for a way to replicate am master database to multiple (100+)
>> > databases that are taken in to the field. Currently for each laptop we
>> > dump and load the tables. However,there is only a small percentage of
>> > data that changes on a frequent basis.
>> >
>> > I've been looking around and come across pyerplica, londiste and bucardo
>> > - the documentation on most of these is fairly sparse. It seems that
>> > Bucardo may be the best bet - at least initially.
>>
>> Bucardo is a good choice for this usage model because it was
>> originally designed to work over a lossy network connections.
>
> yes, but isn't bucardo designed to 2 nodes only ?

No, definitely not!  You can replicate to any number of systems. And
you can group them in whatever groups you'd like.  Multi-master (as
Joshua said) only works between two nodes, but master->slave can be
from a master, to any number of slaves.

--
http://chesnok.com/daily - me
http://endpoint.com - work

Re: Looking for way to replicate master db to multiple mobile databases

From
Grant Maxwell
Date:
On 25/09/2009, at 12:50 PM, Selena Deckelmann wrote:

> On Thu, Sep 24, 2009 at 8:09 AM, Cédric Villemain
> <cedric.villemain@dalibo.com> wrote:
>> Le jeudi 24 septembre 2009, Selena Deckelmann a écrit :
>>> Hi!
>>>
>>> On Wed, Sep 23, 2009 at 10:11 AM, Bryan Montgomery <monty@english.net
>>> >
>> wrote:
>>>> Hi,
>>>> I'm looking for a way to replicate am master database to multiple
>>>> (100+)
>>>> databases that are taken in to the field. Currently for each
>>>> laptop we
>>>> dump and load the tables. However,there is only a small
>>>> percentage of
>>>> data that changes on a frequent basis.
>>>>
>>>> I've been looking around and come across pyerplica, londiste and
>>>> bucardo
>>>> - the documentation on most of these is fairly sparse. It seems
>>>> that
>>>> Bucardo may be the best bet - at least initially.
>>>
>>> Bucardo is a good choice for this usage model because it was
>>> originally designed to work over a lossy network connections.
>>
>> yes, but isn't bucardo designed to 2 nodes only ?
>
> No, definitely not!  You can replicate to any number of systems. And
> you can group them in whatever groups you'd like.  Multi-master (as
> Joshua said) only works between two nodes, but master->slave can be
> from a master, to any number of slaves.
>
>
I use bucardo extensively across multiple sites and with complex
replication requirements. It does a great job.
regards
Grant

Re: Looking for way to replicate master db to multiple mobile databases

From
Cédric Villemain
Date:
Le vendredi 25 septembre 2009, Selena Deckelmann a écrit :
> On Thu, Sep 24, 2009 at 8:09 AM, Cédric Villemain
>
> <cedric.villemain@dalibo.com> wrote:
> > Le jeudi 24 septembre 2009, Selena Deckelmann a écrit :
> >> Hi!
> >>
> >> On Wed, Sep 23, 2009 at 10:11 AM, Bryan Montgomery <monty@english.net>
> >
> > wrote:
> >> > Hi,
> >> > I'm looking for a way to replicate am master database to multiple
> >> > (100+) databases that are taken in to the field. Currently for each
> >> > laptop we dump and load the tables. However,there is only a small
> >> > percentage of data that changes on a frequent basis.
> >> >
> >> > I've been looking around and come across pyerplica, londiste and
> >> > bucardo - the documentation on most of these is fairly sparse. It
> >> > seems that Bucardo may be the best bet - at least initially.
> >>
> >> Bucardo is a good choice for this usage model because it was
> >> originally designed to work over a lossy network connections.
> >
> > yes, but isn't bucardo designed to 2 nodes only ?
>
> No, definitely not!  You can replicate to any number of systems. And
> you can group them in whatever groups you'd like.  Multi-master (as
> Joshua said) only works between two nodes, but master->slave can be
> from a master, to any number of slaves.
>

Ah! thank you for clarifying that.

----
Cédric Villemain
Administrateur de Base de Données
Cel: +33 (0)6 74 15 56 53
http://dalibo.com - http://dalibo.org

Attachment