table inheritance and db syncs

From: Oleg Lebedev <oleg(dot)lebedev(at)waterford(dot)org>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: table inheritance and db syncs
Date: 2003-03-03 19:46:30
Message-ID: 993DBE5B4D02194382EC8DF8554A5273033592@postoffice.waterford.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I would like to receive anyone's feedback or alternative solutions on
the following. Thanks in advance.

We have two identical databases in different countries A and B.
Both databases have the same schema, and we would like to syncronize
them periodically. In other words, we would like to propagate changes
done to one database into another database daily. I've implemented a
syncing algorithm that does this based on record versions.
However, I think that using table inheritance would be a better way to
go about this.

Suppose, in each database I create two schemas: local and remote.
Each of the schemas will contain the same tables.
Tables in remote schema will inherit from tables in local schema.
Local to the database users will be able to modify tables in local
schema, but view data from tables in ALL schemas.
This will allow local users to view ALL the data, but modify only local
data.
When I need to synchronize database in country A with a database in
country B, I will just overwrite remote schema in database A with the
content of local schema in database B. There is no need for checking
versions or anything like this.

It seems very straightforward and wonder if I am missing smth here.
Looks like I'll still be able to preserve constraints between tables.
Will this impact database performance, since data from each table will
be stored in two (parent and child) tables?

Thanks.

Oleg

*************************************

This email may contain privileged or confidential material intended for the named recipient only.
If you are not the named recipient, delete this message and all attachments.
Any review, copying, printing, disclosure or other use is prohibited.
We reserve the right to monitor email sent through our network.

*************************************

Browse pgsql-general by date

  From Date Subject
Next Message Oleg Lebedev 2003-03-03 20:19:14 inheritance and FK
Previous Message scott.marlowe 2003-03-03 19:30:56 Re: versioning and transactions