Re: Database replication method

From: Keith <keith(at)keithf4(dot)com>
To: Yuksel Pinarbasi <yukselp(at)gmail(dot)com>
Cc: PGSQL-Novice <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Database replication method
Date: 2014-05-02 21:11:34
Message-ID: CAHw75vuSRVatHbKA8XHon_W7mv7jsYPLEwSPaxfuswtQ+p+puA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

If they both have independent data, that's not quite so bad. I'd just
caution you that at some people a request may come in that they want to
edit each other's data. I'd make sure and establish a firm plan for that
before implementing a solution. Either clearly state it won't be allowed or
figure out exactly how it will be handled. Bucardo has some conflict
resolution management built in, but you have to be sure that's the method
of resolution you want to implement.

If this is a relatively small database, and they will be kept as
independent databases, bucardo can be a bit of a chore to set up and
maintain. I've been working on a smaller (hopefully easier to use) logical
table replication extension.

https://github.com/omniti-labs/mimeo

You could set up replication of the tables that both sites need to some
secondary tables made just for replicating the other site's data (perhaps
keep it in its own schema). Then you can resolve getting the data in synch
yourself whenever it's needed.

Still, I'd definitely recommend going for the single database solution
first and then if it really can't work, then go the multi-master route.

On Fri, May 2, 2014 at 5:04 PM, Yuksel Pinarbasi <yukselp(at)gmail(dot)com> wrote:

> Thanks Keith.
> Those two branches will mostly be handling independent data.
> Only time to time they will need to see a record that the other branch has
> been created.
> I don't think there will be ant conflict. Because they will not read or
> edit the same row in a close time frame.
> It seems a Multi Master- Asynchronous replication will be the right
> solution in the end.
> Anyway, I will start with a single database and work my way around later.
>
> Yuksel
>
>
>
> You're going down a rather complex path, especially for someone new to
> full RDBMS systems. Multi-master (as this is frequently called) is one of
> the most difficult problems to solve in database administration. A poor
> network between the two locations is going to make it even more difficult
> to manage. The conflict resolution for both sites editing the same data is
> more complex of a problem than I think you realize right now. If there's
> any chance you can stick with a single, more reliable offsite location that
> both locations can access, I'd highly recommend starting there, perhaps in
> Amazon EC2 or RDS. Then one site being down isn't a bottleneck to the
> other.
>
> Keith
> http://www.keithf4.com
>
>

In response to

Browse pgsql-novice by date

  From Date Subject
Next Message Jonatan Malaver 2014-05-05 14:21:44 FTS
Previous Message Yuksel Pinarbasi 2014-05-02 21:04:02 Re: Database replication method