My experience with replication is primarily with Oracle in a non-web2py environment. Oracle's Multi-Master Replication has a ton of parameters that let you customize the replication window, but my systems are configured for a 5 minute delay target, but the databases are rarely out of sync more than a minute. However, in any asynchronous replication system, there is always the possibility that some part of the process will break, and the two will diverge. To Oracle's credit, there is a lot of database magic to help you get out of that situation, and I've had databases that were two days out of sync resync in about 15 minutes once the issue was fixed and replication restarted. These applications field about 10k-15k requests an hour, each request generating or modifying potentially 50-100 table rows at a time.
One issue that you'd run into with respect to using Oracle MMR with web2py would be handling primary key collisions. ID collisions arise when you have an auto-incremented primary key column on a table that can receive updates on both replicated databases which results in a single id number being assigned to two records. You would avoid these collisions by modifying the sequence objects that generate the id numbers so that one database in the pair would generate only even id numbers, and the other would generate only odd. In the Oracle DAL adapter, there is code to generate sequence objects and triggers to assign the sequence numbers to newly-created records. You would install your application on each local server connected to its respective database, and once the tables were built on both, you could find the sequence objects, drop them, and re-create them with something like On the first server CREATE SEQUENCE mytable_pk START WITH 1 INCREMENT BY 2; On the second server CREATE SEQUENCE mytable_pk START WITH 2 INCREMENT BY 2; Note, I'm fairly sure that the actual name that web2py uses is not "mytable_pk" so you'd have to look that up. Once the new sequence objects are in place, the triggers should automatically recompile. It is also a good idea to include a last update audit field on all replicated tables that can be used to set up conflict resolution in the Oracle MMR system. I would use auth.signature and the updated_on field. Of course, if you are really interested in Oracle's replication (and can afford their ridiculous licensing fees), I would recommend reading their Advanced Replication manual, which goes into a ton more detail on the architecture, components, and how-to of a setup like this. But TL;DR: Oracle Multi-Master Replication, once you get through the complexities of setting up the replication process itself, would have no problem running the setup you described. But it'd be more like using a 7 ton Mack truck to carry those groceries... -dan On Wednesday, May 27, 2015 at 2:05:20 PM UTC-5, Dave S wrote: > > I've done a quick scan of the archives about "synchronizing databases", > but there seem to have been few posts on it since > <URL:https://groups.google.com/d/msg/web2py/myTf5eedMc8/6xLmScVVH48J> in > 2011. > And that post, referencing > <URL: > http://www.web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#CSV-and-remote-database-synchronization > > > seems to be more concerned about occasional synchronization than > continuous synchronization. > > Has anybody done the latter? On StackOverflow, there's mentions of > SymmetricDS and MySQL master-master synchronization > > <URL:http://www.symmetricds.org/about/overview> > > By using database triggers, SymmetricDS guarantees that data changes are >> captured and atomicity is preserved. Support for database vendors is >> provided through a Database Dialect layer, with implementations for MySQL, >> Oracle, SQL Server, SQL Server Azure, PostgreSQL, DB2, Informix, Interbase, >> Firebird, HSQLDB, H2, Apache Derby, Greenplum, and SQLite included. >> > > and > > SymmetricDS is developed and maintained by JumpMind, Inc, a commercial >> open source company. JumpMind sponsors the open source project and >> dedicates its software developers to lead the development. The software is >> made available as both open source and the commercial SymmetricDS Pro >> product. >> > > and more info on MySQL M2M is at > <URL:https://www.howtoforge.com/mysql_master_master_replication> > > There's a hint here in W2P-Users that PostGRES also has replication or > synchronization. > > I see this as being primarily a fail-over need or supporting multiple > deployments (think in terms of a company with worksites in multiple cities, > and a local server for each), but while I'd like to synchronize properly, > honesty requires me to admit that in my own current usage I'm only talking > about tens of updates per week, so I'm sort of shopping for a 3/4 ton > pickup truck to make sure I can carry a couple of bags of groceries and the > occasional bag of catfood. > > That said, anyone here have experience in such synchronization? What's a > realistic expectation for the propagation delay? Is this subject to loads, > such that a flurry of activity would adversely impact synchronization? Is > this all hidden in the database plumbing, or does it have an effect on > controllers for W2P apps? > > Thanks for your thoughts and experiences. > > /dps > > -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout.
