Re: Multimaster

[Moreno Andreo <moreno.andreo@xxxxxxxxxx>]

Il 14/04/2016 07:45, Craig Ringer ha scritto:

On 2 April 2016 at 02:15, Moreno Andreo <moreno.andreo@xxxxxxxxxx> wrote:

 

Actually we have to improve what our replicator is doing: it's only replicating the single user's database. The improvement should that we can put it on the "server" (in some cases there are groups of users sharing a dedicated server) and, given a configuration of what and how to replicate, it should replicate more than one DB a time.

That's a layer on top as far as pglogical is concerned. It's only interested in a single database at a time.

The same is true of BDR.

I'll try to explain.
We have a "cloud server" which contains all users DBs and to which all users connect to get replication and.
Some users have more than one work site, i.e. in one they're alone (and the PostgreSQL server is on their own work PC) and in another they're teamed up and they have a dedicated server (and everyone can write on teammates' databases).
In the first case it's easy: the only database is replicated with the corresponding one on the cloud server. Straightforward.
At the current state, in the team case, replication occurs only when the user connects to application, so if there's a user going to a particular site say, only once a week, in the remaining days all data input by team mates won't be replicated, and the user won't be aware on what's been done until he replicates again...
So we are trying to have a process, running on the server, that checks for any database to be replicated and performs the action on all of these.

Case 1
Local                         Remote
[User1:DB1]    -->    [DB1]

Case 2
[User1:DB1]    -->    [DB1]
[User2:DB2]    -->    [DB2]
...
[UserN:DBn]   -->    [DBn]

A tool that automatically configures newly found databases to be replicated should be pretty trivial to write, though.

All databases that have to be replicated have a flag in the app configuration, and the replication config is set up, for each database and each site, when the first synchronization is made, so we could spawn a process per database and replicate.
Not to mention we should be able, if necessary, to exclude one or more tables from the replication.

 

We were beginning to "translate" (and then improve) this program in c#, when I bumped into articles pointing to BDR, and I started taking a look. But it seems that is good to replicahe whole servers, and still hasn't the granularity we need.

Huh?

BDR is configured database-by-database. The only exception is with bdr_init_copy, for initial setup using binary base backups; in that case all databases are copied.

Hmmm... it seems I misunderstood something......

It sounds like you actually *want* to replicate all databases at once. Presumably the reason you're not just using physical streaming replication for that  is that different hosts have a different set of writeable databases? E.g.

[Node A]   [Node B]

[DB-1]   ->  [DB-1]

[DB-2]   ->  [DB-2]

[DB-3]   <-  [DB-3]

[DB-4]   <-  [DB-4]

so each DB is written from only one node at a time, but both nodes have writeable DBs. Right?

Yes, I tried to explain this in the first part of the message.

Thanks
Moreno

--

 Craig Ringer                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services