On May 6, 9:07 am, Vic <vctr...@gmail.com> wrote:
> I'm looking for a way to have my DB replicated in REAL TIME to be used
> in case I lose my primary copy.
>
> I saw that the two phase commit exist but I'm not sure if that is the
> correct option. I have the feeling that it would be abusing a
> mechanism purposed for correlating to separate DBs and not creating
> replications.
The preferred way to replicate databases is to use ready-made database
replication tools. For postgres the easiest way to do synchronous
replication is currently pgpool-II. With mysql I'm not so sure, there
is the NDB cluster, but it has its own issues, possibly you can find
similar replication middleware for it.
If you must do the replication inside your application, then my advice
is to do it by subclassing sqlalchemy.engine.{Engine,Connection} to
handle distributing requests to multiple backends and managing
transactions across them. You still need 2 phase commits to achieve
consistency, and the corresponding separate transaction management
that goes with it (to rollback/commit prepared transactions in case of
crashes). Also to avoid inconsitencies you have to get sequence values
from one database, and cannot use volatile functions for inserts,
updates. Also, if you are doing this to get high availability, then
you need figure out, how to bring a replica up online. Don't expect
this to be anything near simple or transparent if you want to have any
kind of concurrency for write queries.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
