Ask HN: How did you migrate from Mongo to a relational database? | Hacker News Reader

Ask HN: How did you migrate from Mongo to a relational database?

4 pointsvosper3 comments

My team owns a Mongo database that's a big mess, and since we have fundamentally relational data in there we're looking to move to an RDBMS like MySQL or Postgres.

Have you managed to move from Mongo (or some other NoSQL / NoSchema database) to an RDBMS? What considerations did you have to make? Any traps or pitfalls?

It seems to me there are two basic approaches:

1. Create migrations for the data, and rewrite all queries. Test thoroughly, then flip the switch and move everything to the new DB

2. Create migrations, then keep the two databases in sync while rewriting queries, until all queries are migrated and Mongo can be turned off.

Approach (1) seems both difficult and risky, but means I don't have to keep data in sync, which seems to me to be the big risk of approach (2).

I'd love to hear your thoughts!

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...

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?

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.

Reply on news.ycombinator.com