Re: [GENERAL] Replication of databases (esp. postgres) - Mailing list pgsql-general

From Clark Evans
Subject Re: [GENERAL] Replication of databases (esp. postgres)
Date
Msg-id 36CBB0F6.75036A6D@manhattanproject.com
Whole thread Raw
In response to RE: [GENERAL] Replication of databases (esp. postgres)  (Thomas Antepoth <t_antepoth@hamm.netsurf.de>)
List pgsql-general
Thomas Antepoth wrote:
>
> Michael,
>
> As Dustin pointed out, he solved it by merging a unique database
> id with a sequence into a unique primary key.

<snip>

> If the newly inserted record gets the database id of database 2
> it will later not be recognized belonging to the relation by a
> "... where db_id=this_relations_host and recid=this_relations_sequence"
> on every host.

You concatinate them.  They become a 'single value'.

Do not treat them as seperate things, you merge them together
to generate the uniqueness.  You _never_ seperate them, i.e.,
you _never_ change the db_id.

> If the newly inserted record gets the database id of database 1
> it will be surely regocnized, but the sequence number of the
> record may collide with a pre existing record in database 1
> when replicated.

Nope.  This is never a problem.  This is *exactly* what
the concatination gives you.

> Multiple updates to a record of the same relation on different
> non connected databases are another problem not solved by
> this mechanism.

Correct.  If the data moves... you need a pointer
telling it where it moved to.  When something moves
(a rare event in my case), you go back and update all of
the pointers in the 'local-cashe' copy of the tuple.

> Every solution which i can imagine relies on a master, which
> receives temporary data on inserts, creates unique record ids
> and re exports the data to the slaves.

Nope.  You can only query the local-cashe.  If you want to
update it, you need to 'transfer' the ownership of the object
(tuple) from one database to the other.  _Then_ you can
update the local copy.

> A slave will create in his local database a record id which is
> known to be "not part of all databases". e.g. all numbers below
> 100000 are temporary.

YIKES!  I would not do this.... it won't scale, and besides
that it's messy!  *evil grin*

> Then it exports this data to the master and deletes the temporary
> data, when the master acknowledged his import.

Why delete it?  If you just edited it, chances are you may
want to query it.  Sure.. you might get 'old' data, but for
relatively static data this is not a problem.  A replication
service can go around propigating updates during off-peak times.

> The master creates unique record ids among all temporary data
> and reexports the data to all his slaves.
>
> But what to do, if there are several local masters?

You have a 'chain of responsibility', the Design Pattern book
will help with this one.

>
> t++

Best,

Clark Evans

pgsql-general by date:

Previous
From: Clark Evans
Date:
Subject: Re: [GENERAL] Replication of databases (esp. postgres)
Next
From: Adriaan Joubert
Date:
Subject: Error in querying view