synchronizing databases

Hi.

Again, I'm posting to this list because none of the other postgres lists
seem quite right for the topic. Excuse me if I'm off topic.

I'm writing an application and I'd like to have the data that
application uses to be uninterrupted in case the database server fails
(fail over). Likewise, when I'm ready to bring the crashed server back
online I'll need to be able to synchronize it with the one that's been
working so that I can go back to having fail over capability.

I know that oracle has a fail safe DB server, MsSQL has some form of
replication, and I can do it on the hardware level with a mirrored drive
array. I'd like to avoid shelling out the cash for these solutions and
was exploring the potential for an application/database layer solution.
I'd also gladly accept open source solutions like linux software raid or
clustering from anyone who's used them in this context!

I've thought of a couple possible solutions. I'm sure that this problem
has been tackled before and I'd appreciate feedback from anyone with
experience.

In the first scenario, I make the update, and also write a log entry
that will allow me to re-create that update. I do this as a transaction
first on server x, then on server y. The log entry has a key field that
allows me to check server x's log against that of server y, and if
discrepancies exist, I can bring the server that is missing the
transaction up to date with a synchronization program that runs
periodically.

There are problems with this. I have to return success after x has been
updated even if the update to y fails, because the update HAS happened,
and under normal conditions y will get synchronized. But if x crashes
immediately afterwards, then y will never show that the update happened,
but the outside world will have seen it as a success.

A more complex solution is to open the transaction on x then update the
record in y with a flag that shows that an update MIGHT have occurred.
If that update to y succeeds, then I can commit the transaction on x (at
this point I must return success to the outside world) and start another
transaction on y. This time I synchronize y with x and remove the flag
in one transaction. If x crashes at any time, transactions are rejected
until the system fails over to y. If y hiccups during a transaction on
x so that y doesn't get flagged, then I return failure and don't commit
to x. If my application crashes before synchronizing y and x fails
right after that, then I have to freeze all records in y that have flags
until I can get their real status back from x (if that's recoverable) or
I'm left with orphans that I'll have to resolve using real world info.

Meanwhile I have scripts that can create a new y from an x, which I use
in case y goes down or in case x goes down and y has to BECOME x.

Is there a more elegant solution to this? Thanks in advance!

--------------->Nathan

--

¤º°`°º¤ø,¸¸,ø¤º°`°º¤ø,¸¸,ø¤º°`°º¤ø,¸¸,ø¤º°`°º¤ø,¸¸,ø¤º°`°º¤ø,¸¸,ø¤º°`°º¤

Nathan Young
nathan(at)ncyoung(dot)com
(805) 686-2830