
Ask HN: How did you migrate from Mongo to a relational database? - vosper
My team owns a Mongo database that&#x27;s a big mess, and since we have fundamentally relational data in there we&#x27;re looking to move to an RDBMS like MySQL or Postgres.<p>Have you managed to move from Mongo (or some other NoSQL &#x2F; NoSchema database) to an RDBMS? What considerations did you have to make? Any traps or pitfalls?<p>It seems to me there are two basic approaches:<p>1. Create migrations for the data, and rewrite all queries. Test thoroughly, then flip the switch and move everything to the new DB<p>2. Create migrations, then keep the two databases in sync while rewriting queries, until all queries are migrated and Mongo can be turned off.<p>Approach (1) seems both difficult and risky, but means I don&#x27;t have to keep data in sync, which seems to me to be the big risk of approach (2).<p>I&#x27;d love to hear your thoughts!
======
nishantvyas
1) start with dual-write to both the DBs for all the writes.

2) all reads from Mongo

3) in the background start data migration, one key/document at a time;
transform Mongo document into RDBMS table/rows and insert... have mongo
document id as primary key for main table... for every document
migration/insert, lock the corresponding RDBMS row, this will ensure data
consistency...

4) create code path to read from RDBMS.

5) test it in stage for different fail-case scenarios...

6) ideally, do canary type push to single server etc in prod to see if read
from RDBMS seems fine (this assumes #3 is done)

actual steps may vary... but these are high-level steps....hope it helps...

~~~
vosper
Thanks, that's helpful!

Regarding locking the database rows, I'm a little unclear about how that
helps? Is the idea that once a record has been migrated from mongo to RDBMS
you want to lock the data in the latter so that no-one accidentally updates
the record in the RDBMS and not in Mongo? And therefore Mongo remains the
definite source of truth?

~~~
nishantvyas
basically locking will create the serializability for that document... so if
there are concurrent updates on mongo side it will have to wait... also for
step-1 in dual write it should be automic/two-phase between mongo and RDBMS.

