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 [EMAIL PROTECTED] (805) 686-2830
