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 36CBAE61.9A927A19@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:
>
> But what to do, if there are several local masters?

I may have interpreted the problem incorrectly, but here is
a similar problem I had...

Say I have 5 plants in a large company.  Each of these
plants has a variety of machinery and tools which have
detailed maintance records.

It dosn't make sence to have one centralized database,
so you have 5 seperate ones.  Each database with it's own
copy of the machines deployed at the plant.

Now comes the kicker.  About 3-5 times a year there is
a major migration of equipment between the plants.  Plant A
no longer needs such-and-such a tool, so the tool moves to
plant B.  The problem is, the history for that peice of
equipment is tied to several, some times a hundred or
more different production processes, and/or related machinery.
Also, primary key conflicts cause hudge problems.

To solve this problem, I switched from a numeric key to
an alpha numeric key.  I assigned a "birthplace" to
each machine... i.e., where it was first deployed.  Each
birthplace had it's alpha key, e.g., plant 'A', 'B', etc.
and the new object would be entered using the birth place
key followed by a unique sequence or 'birth number' at that
particular 'birth place'.  The result of concatination, is
a globally unique key that can move with the equipment
from place to place.

Each peice of equipment also has it's current location.
If when you query the peice of equipment in the local
database, and it's not the current location, then you
are not allowed to update that 'archive' copy of the data.
To make any changes, you must go over via remote database
link to the database which 'owns' the equipment.

This seemed to solve the problem.

Hope this helps.

Clark

pgsql-general by date:

Previous
From: Thomas Antepoth
Date:
Subject: RE: [GENERAL] Replication of databases (esp. postgres)
Next
From: Clark Evans
Date:
Subject: Re: [GENERAL] Replication of databases (esp. postgres)