Big projet, please help

At 16:38 7/06/00 +0200, Olivier PRENANT wrote:

The 2 databases have to be synchronized both ways ! Huh How can I do
that???

Can you give me some pointers... I'd love to do it with postgresql
instead of going to Oracle just because they can replicate bases...

Two way replication has some serious issues. AFAIK, it is not possible to
replicate both ways without some serious limitations on who updates what,
and how they do it (ie. very careful, and quite limiting, design choices).
This may suit your application - eg. if the updates are only inserts on
non-uniquely indexed tables, and any record updates are only ever done at
the site that originated them (or just at one of the sites). You also will
have referential integrity issues to deal with.

The only commercial replication system that I am familiar with will go both
ways, but not for the same table. ie.

DB1 DB2
=== ===
Table1 ---> Table1
Table2 <--- Table2

If I were you, I'd be looking at updating only the clients database, and
letting the changes replicate to the read-only web database; possibly with
the option of an error being reported to the submitter of the update.

As to replication in PostgreSQL, I don't think it will be there until after
the WAL appears, and if it's WAL-based, my guess is that it will be one-way.

But you could implement a kind of replication by using triggers on the
tables to be replicated: write out the record key, and the operation
performed (add, change,delete) to another table. Then have an (hourly?)
replication process that sends the changes to the replicated database(s).
Pretty low-tech, but probably quite reliable.

Hope this helps.

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.C.N. 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/

Olivier PRENANT wrote:

Hi all,

I'm faced to a big problem!!

I have to do this for a customer:

create a database on my server; this database will be queried and updated
through the web --- this is easy

H!ave the same databse on my customer server. This databse will be queried
and updated by the customer . -esay too

The 2 databases have to be synchronized both ways ! Huh How can I do
that???

Just a thought:

have two sets of tables client_xxx and web_xxx and allow updates on
'local'
tables only.

for queries create views like
(select * from client_xxx union select * from web_xxx)

if client wants to modyify web tables have her do it over web.

to synchronize just copy over the tables

Can you give me some pointers... I'd love to do it with postgresql
instead of going to Oracle just because they can replicate bases...

A general info on _file_system_ replication can be found at

http://www.coda.cs.cmu.edu/

it probably won't help you much with db replication

A distributed db based on early postgreSQL versions is at

http://s2k-ftp.cs.berkeley.edu:8000/mariposa/

---------
Hannu

Hi all,

I'm faced to a big problem!!

I have to do this for a customer:

create a database on my server; this database will be queried
and updated
through the web --- this is easy

H!ave the same databse on my customer server. This databse
will be queried
and updated by the customer . -esay too

The 2 databases have to be synchronized both ways ! Huh How can I do
that???

Can you give me some pointers... I'd love to do it with postgresql
instead of going to Oracle just because they can replicate bases...

In an environment with moderate to low update activity at least on one side
the simplest and most reliable replication mechanism is usually done with
triggers
that work on the primary key.
They provide a synchronous replication on the basis of all or nothing,
and thus solve the problem of concurrent update to the same row
from both sides.
The tricky part is usually how to break the trigger chain. In Informix you
can
set global stored procedure variables to "local" and "remote" and only
trigger if that
variable is set to "local". (create trigger .... when myconn()="local" ...)

Andreas

The only commercial replication system that I am familiar
with will go both
ways, but not for the same table. ie.

DB1 DB2
=== ===
Table1 ---> Table1
Table2 <--- Table2

No. Informix has update everywhere replication in the standard IDS server.
Informix replication is configurable from sync to async repl (Laptops) with
several options of behavior in the case of conflict (network outage ...) .

But you could implement a kind of replication by using triggers on the
tables to be replicated: write out the record key, and the operation
performed (add, change,delete) to another table. Then have an
(hourly?)
replication process that sends the changes to the replicated
database(s).
Pretty low-tech, but probably quite reliable.

If you can, I would do the replication online in the trigger stored
procedure.
This of course implys an update everywhere or not at all. If connection
between
the two servers is lost no update is possible.

Andreas

At 11:58 8/06/00 +0200, Zeugswetter Andreas SB wrote:

The only commercial replication system that I am familiar
with will go both
ways, but not for the same table. ie.

No. Informix has update everywhere replication in the standard IDS server.
Informix replication is configurable from sync to async repl (Laptops) with
several options of behavior in the case of conflict (network outage ...) .

That's interesting. Out of curiosity, what choices does it provide for the
following:

a) Two inserts on a (unique) primary key

b) Two inserts on a unique index (slightly different to (a))

c) Two updates to different fields in the same record

d) Two updates to the same field in the same record

e) Deletion of a record and update of the same record

If connection between
the two servers is lost no update is possible.

Unfortunately this restriction removes *one* of the motivations behind
replication. It might be better to implement a queue of pending updates in
another table, where the master database applies or rejects the updates
according to rules of the application. Needless to say, the mechanics could
get pretty ugly. Hence my curiosity about the answers to the above questions.

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.C.N. 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/