How to migrate da data from Mo Mong ngoDB B to Postgres with h ToroDB
Wh Who we are Ex Exper perts At Your ur Ser ervice > Over 50 specialists in IT infrastructure > Certified, experienced, passionate Based In Switzerland Ba > 100% self-financed Swiss company > Over CHF8 mio. Turnover Le Lead adin ing I In I Infr fras astructure S Servic vices > More than 150 customers in CH, D & F > Over 50 SLAs dbi FlexService contracted dbi services is hiring (career@dbi-services.com) How to migrate data from MongoDB to Postgres with ToroDB 30/06/2017 Page 2
Ab About me me Me Mehdi Bada Consultant +41 79 928 75 48 mehdi.bada[at]dbi-services.com How to migrate data from MongoDB to Postgres with ToroDB 30/06/2017 Page 3
Ag Agenda 1.Introduction 2.MongoDB 3.ToroDB 4.Migration: from MongoDB to PostgreSQL 5.Conclusion How to migrate data from MongoDB to Postgres with ToroDB 30/06/2017 Page 4
In Introd oduction ion 1 2 3 4 5 How to migrate data from MongoDB to Postgres with ToroDB 30/06/2017 Page 5
In Intr trod oducti tion on How to migrate data from MongoDB to Postgres with ToroDB 30/06/2017 Page 6
Mo MongoDB DB 1 2 > Overview > Data Model 3 > High availability > Horizontal scalability 4 > Limitations 5 How to migrate data from MongoDB to Postgres with ToroDB 30/06/2017 Page 7
Mo MongoDB Overview Open Source Full CRUD Horizontal support scalability Rich queries Schema-less Document Indexes oriented High availability How to migrate data from MongoDB to Postgres with ToroDB 30/06/2017 Page 8
Mo MongoDB Overview Co Concept mapping RDBMS MongoDB Tables Collections Rows/records Documents Queries return rows Queries return cursor Join Embedded document Partition Shard How to migrate data from MongoDB to Postgres with ToroDB 30/06/2017 Page 9
Mo MongoDB Data Model Da Data ar a are s stored as as d docu cuments Mon MongoD oDB s stor ores B BSO SON d doc ocuments ( (Binary J JSO SON) { “_id” : ObjectId(“56e92b9cfdf7bc92bbb3b51f”), Analogous An s to a database se row “first_name” : “Mike”, “surname” : “Brody”, Ke Keys and Values “city” : ”New-York”, > Key : String “year” : 1987, > Values types: } > String, number, Boolean, null, array, object Date MAC @ PID Counter Speci Sp cial k key: _i _id > Unique identifier > Object id: 12 byte Hex String How to migrate data from MongoDB to Postgres with ToroDB 30/06/2017 Page 10
Mo MongoDB Data Model Re Relational MongoDB sc Mo schema Pers_Id Surname First_name City { “f “first_name” : : “Duran and”, ”, 0 Mike Durand Geneva “s “surname” : : “Mik ike", 1 Pat Millner London “city” : “c : “Geneva" a", “c “country” : : “Swit itzerlan land”, 2 Ortega Alvaro New-York “cars”:[ “c :[ { “m { “model”: : “Ferrar ari”, ”, Car_Id Model Year Pers_Id “year”: “y : 2013 0 Ferrari 2013 0 } 1 Peugeot 2005 0 } 2 BMW 2016 1 How to migrate data from MongoDB to Postgres with ToroDB 30/06/2017 Page 11
Mo MongoDB High availability Wh Which mechanism ensure high availability ty of your data? > MongoDB Replication HA HA is achi hieved ed thr hroug ugh h aut utoma matic failover er Mo MongoDB replicati tion allows: s: > High availability (HA) > Disaster Recovery (DR) > Data duplication across multiple database servers / storages > Functional Segregation > Topology of replica sets can be used for > Backups, Analytics, Reporting, DR, Read operations… How to migrate data from MongoDB to Postgres with ToroDB 30/06/2017 Page 12
Mo MongoDB High availability Re Replication architecture Application driver Writes Reads mongod Primary Replication mongod mongod Secondary Secondary How to migrate data from MongoDB to Postgres with ToroDB 30/06/2017 Page 13
Mo MongoDB High availability Au Autom omati tic failover proc ocess mongod Primary Election of a new primary mongod mongod Secondary Secondary Heartbeat New primary is elected Replication mongod mongod Primary Secondary Heartbeat How to migrate data from MongoDB to Postgres with ToroDB 30/06/2017 Page 14
Mo MongoDB Horizontal scalability Ve Vertical scalability Sc Scaling g with Mon ongoD oDB > MongoDB Sharding Increa Inc easing ng CPU, RAM, I/ I/O How to migrate data from MongoDB to Postgres with ToroDB 30/06/2017 Page 15
MongoDB Mo Horizontal scalability Sh Sharding g Architecture Application mongod ConfigSvr mongos mongod (query router) ConfigSvr mongod ConfigSvr Sharding Shard 0 Shard 1 mongod mongod Primary Primary mongod mongod Secondary Secondary … … How to migrate data from MongoDB to Postgres with ToroDB 30/06/2017 Page 16
Mo MongoDB Limitations No No ACID D transaction > Atomic transactions only work within the same document CART ORDERS [ ... { [ user: 567, . . . orders: [ { . . . user: 567, { products: [ orderId: 24658, { This operation is not Atomic!! products: [ id: 47, . . . units: 7, ] }, } . . . . . . ] ] } . . . ] How to migrate data from MongoDB to Postgres with ToroDB 30/06/2017 Page 17
Mo MongoDB Limitations Mo MongoDB High availability ty is s not t sa safe!! > Data loss depending the consistency level you choose MongoDB consi Mo sistency levels > Unacknowledged: Unsafe - 42% of data loss > Acknowledged: Unsafe > Journaled: Unsafe > Fsynced: Unsafe > Replica Acknowledged: Unsafe > Only majority is safe ht https://ap aphyr.com/posts/322 322-je jepsen-mo mong ngodb db-st stale-re reads How to migrate data from MongoDB to Postgres with ToroDB 30/06/2017 Page 18
Mo MongoDB Limitations BI BI query y performances issues Mo MongoDB aggregati tion framework rk is s very sl slow > Need to scan multiple documents > Lots of I/O required to answer the query Ag Aggr gregati tion on on on a relati tion onal design gn is “100x faster” Sol Solution on for or Mon ongoD oDB BI queries? > Implement a relational schema!! How to migrate data from MongoDB to Postgres with ToroDB 30/06/2017 Page 19
To ToroDB 1 2 > What is ToroDB? > How it works? 3 > Why ToroDB? 4 5 How to migrate data from MongoDB to Postgres with ToroDB 30/06/2017 Page 20
To ToroDB What is ToroDB? The fir Th first datab abas ase that merges the scalab alabilit ility of f a a NoSQL wi with t the r reliability o y of SQ SQL How to migrate data from MongoDB to Postgres with ToroDB 30/06/2017 Page 21
To ToroDB What is ToroDB? Op Open source ce, docu cument-or oriented, , JSO JSON database that runs on on top op of of Pos ostgr greSQ SQL BI connector for MongoDB BI MongoDB, JS JSON doc ocuments ts are st stored relationally Protocols Couchbase, Cassandra... > Significant storage > I/O savings Abstract Doc Transforming Docs into Tables and Rows Data2Relational MongoDB data is Mo s persi sisti ting in tables s and rows s with wi thin a SQL database PostgreSQL, DB2, Backends Oracle, Greenplum... How to migrate data from MongoDB to Postgres with ToroDB 30/06/2017 Page 22
To ToroDB How it works? To ToroDB transforms documents to relational tables > Data is stored in tables > ToroDB analyzes every incoming document and separates metadata (schema) form data (tuples) > 1+ tables per MongoDB collections > ToroDB creates a RDBMS catalog schema per MongoDB database > Dynamic and implicit schema generation Fu Full c compatibility w with Mo MongoDB > API programs, clients > CRUD operations including UPDATE How to migrate data from MongoDB to Postgres with ToroDB 30/06/2017 Page 23
To ToroDB How it works? To ToroDB can work as as a s a secondar ary n node o on a M a MongoDB r replic lica s a set How to migrate data from MongoDB to Postgres with ToroDB 30/06/2017 Page 24
To ToroDB Why ToroDB? Native SQL BI Connector Data Integration Apps: Write data Platform: SQL and with Mongo API, NoSQL apps in the query with SQL! same RDBMS How to migrate data from MongoDB to Postgres with ToroDB 30/06/2017 Page 25
Mi Migration: n: from Mo Mong ngoDB B to PostgreSQL 1 2 > Overview > Prerequisites 3 > Configuration > Demo 4 5 How to migrate data from MongoDB to Postgres with ToroDB 30/06/2017 Page 26
Mi Migration: from Mo MongoDB to PostgreSQL Overview How to migrate data from MongoDB to Postgres with ToroDB 30/06/2017 Page 27
Mi Migration: from Mo MongoDB to Po PostgreSQL Overview To ToroDB Stampeded uses MongoDB re replica set oplog to to keep track of the modifications in Mo MongoDB How to migrate data from MongoDB to Postgres with ToroDB 30/06/2017 Page 28
Mi Migration: from Mo MongoDB to PostgreSQL Overview Du During the replication To ToroDB Stampeded transforms JSON documents into a relational sc schema Column Type Encoding How to migrate data from MongoDB to Postgres with ToroDB 30/06/2017 Page 29
Recommend
More recommend