Re: 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   |/