How to migrate da data from Mo Mong ngoDB B to Postgres with h - - PowerPoint PPT Presentation

▶
how to migrate da data from mo mong ngodb b to postgres
SMART_READER_LITE
LIVE PREVIEW

How to migrate da data from Mo Mong ngoDB B to Postgres with h - - PowerPoint PPT Presentation

Dec 08, 2022 256 likes •668 views

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 >

slide-1
SLIDE 1

How to migrate da data from Mo Mong ngoDB B to Postgres with h ToroDB

slide-2
SLIDE 2

Wh Who we are

30/06/2017 Page 2 How to migrate data from MongoDB to Postgres with ToroDB

Ex Exper perts At Your ur Ser ervice

> Over 50 specialists in IT infrastructure > Certified, experienced, passionate

Ba Based In Switzerland

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

slide-3
SLIDE 3

Ab About me me

30/06/2017 Page 3 How to migrate data from MongoDB to Postgres with ToroDB

Me Mehdi Bada

Consultant +41 79 928 75 48 mehdi.bada[at]dbi-services.com

slide-4
SLIDE 4

Ag Agenda

1.Introduction 2.MongoDB 3.ToroDB 4.Migration: from MongoDB to PostgreSQL 5.Conclusion

30/06/2017 Page 4 How to migrate data from MongoDB to Postgres with ToroDB

slide-5
SLIDE 5

30/06/2017 Page 5 How to migrate data from MongoDB to Postgres with ToroDB

1 2 3 4 5

In Introd

  • duction

ion

slide-6
SLIDE 6

30/06/2017 How to migrate data from MongoDB to Postgres with ToroDB Page 6

In Intr trod

  • ducti

tion

  • n
slide-7
SLIDE 7

30/06/2017 Page 7 How to migrate data from MongoDB to Postgres with ToroDB

1 2 3 4 5

Mo MongoDB DB

> Overview > Data Model > High availability > Horizontal scalability > Limitations

slide-8
SLIDE 8

30/06/2017 How to migrate data from MongoDB to Postgres with ToroDB Page 8

Mo MongoDB

Overview

Open Source Horizontal scalability Schema-less Document

  • riented

High availability Indexes Rich queries Full CRUD support

slide-9
SLIDE 9

30/06/2017 How to migrate data from MongoDB to Postgres with ToroDB Page 9

Mo MongoDB

Overview Co Concept mapping

RDBMS MongoDB

Tables Collections Rows/records Documents Queries return rows Queries return cursor Join Embedded document Partition Shard

slide-10
SLIDE 10

30/06/2017 How to migrate data from MongoDB to Postgres with ToroDB Page 10

Mo MongoDB

Data Model

Da Data ar a are s stored as as d docu cuments Mon MongoD

  • DB s

stor

  • res B

BSO SON d doc

  • cuments (

(Binary J JSO SON) An Analogous s to a database se row Ke Keys and Values

> Key : String > Values types:

> String, number, Boolean, null, array, object

Sp Speci cial k key: _i _id

> Unique identifier > Object id:

{ “_id” : ObjectId(“56e92b9cfdf7bc92bbb3b51f”), “first_name” : “Mike”, “surname” : “Brody”, “city” : ”New-York”, “year” : 1987, }

Date MAC @ PID Counter 12 byte Hex String

slide-11
SLIDE 11

30/06/2017 How to migrate data from MongoDB to Postgres with ToroDB Page 11

Mo MongoDB

Data Model Re Relational Mo MongoDB sc schema { “f “first_name” : : “Duran and”, ”, “s “surname” : : “Mik ike", “c “city” : : “Geneva" a", “c “country” : : “Swit itzerlan land”, “c “cars”:[ :[ { { “m “model”: : “Ferrar ari”, ”, “y “year”: : 2013 } }

Pers_Id Surname First_name City Mike Durand Geneva 1 Pat Millner London 2 Ortega Alvaro New-York Car_Id Model Year Pers_Id Ferrari 2013 1 Peugeot 2005 2 BMW 2016 1

slide-12
SLIDE 12

30/06/2017 How to migrate data from MongoDB to Postgres with ToroDB Page 12

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…

slide-13
SLIDE 13

30/06/2017 How to migrate data from MongoDB to Postgres with ToroDB Page 13

Mo MongoDB

High availability Re Replication architecture

Application mongod Secondary mongod Secondary mongod Primary

driver

Replication

Reads Writes

slide-14
SLIDE 14

30/06/2017 How to migrate data from MongoDB to Postgres with ToroDB Page 14

Mo MongoDB

High availability Au Autom

  • mati

tic failover proc

  • cess

mongod Primary mongod Secondary mongod Secondary Election of a new primary Heartbeat mongod Secondary mongod Primary Heartbeat Replication New primary is elected

slide-15
SLIDE 15

30/06/2017 How to migrate data from MongoDB to Postgres with ToroDB Page 15

Mo MongoDB

Horizontal scalability Ve Vertical scalability Inc Increa easing ng CPU, RAM, I/ I/O Sc Scaling g with Mon

  • ngoD
  • DB

> MongoDB Sharding

slide-16
SLIDE 16

mongod ConfigSvr

Shard 0 …

30/06/2017 How to migrate data from MongoDB to Postgres with ToroDB Page 16

Mo MongoDB

Horizontal scalability Sh Sharding g Architecture

Application mongod Primary mongod Secondary

mongos (query router)

Shard 1 … mongod Primary mongod Secondary

mongod ConfigSvr mongod ConfigSvr

Sharding

slide-17
SLIDE 17

30/06/2017 How to migrate data from MongoDB to Postgres with ToroDB Page 17

Mo MongoDB

Limitations No No ACID D transaction

> Atomic transactions only work within the same document

[ . . . { user: 567, products: [ { id: 47, units: 7, }, . . . ] [ ... { user: 567,

  • rders:

[ . . . {

  • rderId: 24658,

products: [ . . . ] } . . . ] } . . . ]

CART ORDERS This operation is not Atomic!!

slide-18
SLIDE 18

30/06/2017 How to migrate data from MongoDB to Postgres with ToroDB Page 18

Mo MongoDB

Limitations Mo MongoDB High availability ty is s not t sa safe!!

> Data loss depending the consistency level you choose

Mo MongoDB consi 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

slide-19
SLIDE 19

30/06/2017 How to migrate data from MongoDB to Postgres with ToroDB Page 19

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

  • n on
  • n a relati

tion

  • nal design

gn is “100x faster” Sol Solution

  • n for
  • r Mon
  • ngoD
  • DB BI queries?

> Implement a relational schema!!

slide-20
SLIDE 20

30/06/2017 Page 20 How to migrate data from MongoDB to Postgres with ToroDB

1 2 3 4 5

To ToroDB

> What is ToroDB? > How it works? > Why ToroDB?

slide-21
SLIDE 21

30/06/2017 How to migrate data from MongoDB to Postgres with ToroDB Page 21

To ToroDB

What is ToroDB?

Th The fir 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

slide-22
SLIDE 22

30/06/2017 How to migrate data from MongoDB to Postgres with ToroDB Page 22

To ToroDB

What is ToroDB? Op Open source ce, docu cument-or

  • riented,

, JSO JSON database that runs on

  • n top
  • p of
  • f Pos
  • stgr

greSQ SQL BI BI connector for MongoDB JS JSON doc

  • cuments

ts are st stored relationally

> Significant storage > I/O savings

Mo MongoDB data is s persi sisti ting in tables s and rows s wi with thin a SQL database

Protocols Abstract Doc Data2Relational Backends

MongoDB, Couchbase, Cassandra... Transforming Docs into Tables and Rows PostgreSQL, DB2, Oracle, Greenplum...

slide-23
SLIDE 23

30/06/2017 How to migrate data from MongoDB to Postgres with ToroDB Page 23

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

slide-24
SLIDE 24

30/06/2017 How to migrate data from MongoDB to Postgres with ToroDB Page 24

To ToroDB

How it works? To ToroDB can work as as a s a secondar ary n node o

  • n a M

a MongoDB r replic lica s a set

slide-25
SLIDE 25

30/06/2017 How to migrate data from MongoDB to Postgres with ToroDB Page 25

To ToroDB

Why ToroDB? Native SQL BI Connector Apps: Write data with Mongo API, query with SQL! Data Integration Platform: SQL and NoSQL apps in the same RDBMS

slide-26
SLIDE 26

30/06/2017 Page 26 How to migrate data from MongoDB to Postgres with ToroDB

1 2 3 4 5

Mi Migration: n: from Mo Mong ngoDB B to PostgreSQL

> Overview > Prerequisites > Configuration > Demo

slide-27
SLIDE 27

30/06/2017 How to migrate data from MongoDB to Postgres with ToroDB Page 27

Mi Migration: from Mo MongoDB to PostgreSQL

Overview

slide-28
SLIDE 28

30/06/2017 How to migrate data from MongoDB to Postgres with ToroDB Page 28

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

slide-29
SLIDE 29

30/06/2017 How to migrate data from MongoDB to Postgres with ToroDB Page 29

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

slide-30
SLIDE 30

30/06/2017 How to migrate data from MongoDB to Postgres with ToroDB Page 30

Mi Migration: from Mo MongoDB to PostgreSQL

Prerequisites Ru Runti time dependencies

Technology Description

MongoDB Install and start MongoDB instances with the replication features (dbi services best practices installation) Replica sets configuration ToroDB Stampede receives data from a MongoDB replica set. A single-node replica set is sufficient. PostgreSQL Install and start a PostgreSQL instance. Create a dedicated user and database. (dbi services best practices) Java ToroDB Stampede is written in Java so a Java Runtime Environment (JRE) required to run it. Java 8 is recommended.

slide-31
SLIDE 31

30/06/2017 How to migrate data from MongoDB to Postgres with ToroDB Page 31

Mi Migration: from Mo MongoDB to PostgreSQL

Configuration Ar Architectu ture

Mongo Env Postgres Env ToroDB Stampeded schema generation PostgreSQL instance with DMK installed MongoDB instances with DMK installed 192.168.56.102 192.168.56.140

slide-32
SLIDE 32

30/06/2017 How to migrate data from MongoDB to Postgres with ToroDB Page 32

Mi Migration: from Mo MongoDB to PostgreSQL

Configuration Af After installati tion

  • n,

, expor

  • rt

t $TOROHOME va variable Cr Create and adapt the ToroDB B configuration file (YAML)

mehdi@MacBook-Pro:/u00/app/torodb/ export TOROHOME /u00/app/torodb/torodb-stampede-1.0.0- beta2” mehdi@MacBook-Pro:/u00/app/torodb/ echo $TOROHOME /u00/app/torodb/torodb-stampede-1.0.0-beta2 mehdi@MacBook-Pro:/u00/app/torodb/ /u00/app/torodb/torodb-stampede-1.0.0-beta2/bin/torodb- stampede -l > ../conf/torodb.yaml mehdi@MacBook-Pro:/u00/app/torodb/ vi ../conf/torodb.yaml

slide-33
SLIDE 33

30/06/2017 How to migrate data from MongoDB to Postgres with ToroDB Page 33

Mi Migration: from Mo MongoDB to PostgreSQL

Configuration To ToroDB Stampeded reads databases credentials from the .t .toropass file file Cr Create the .toropass file in the home directory

mehdi@MacBook-Pro:/u00/app/torodb/ echo ”<host>:<port>:<database>:<user>:<PASSWD>" > "$HOME/.toropass" mehdi@MacBook-Pro:/u00/app/torodb/ chmod 0400 ”~/.toropass”

slide-34
SLIDE 34

30/06/2017 How to migrate data from MongoDB to Postgres with ToroDB Page 34

Mi Migration: from Mo MongoDB to PostgreSQL

Configuration Cu Custom configuration for ToroDB B Stampeded on startup

> Command line options > Configuration file

Re Recommended to use a configuration file Pr Print the current co configuration (YAML)

mehdi@MacBook-Pro:/u00/app/torodb/ ./torodb-stampede -c myconfiguration.yml mehdi@MacBook-Pro:/u00/app/torodb/ ./torodb-stampede -l

slide-35
SLIDE 35

30/06/2017 How to migrate data from MongoDB to Postgres with ToroDB Page 35

Mi Migration: from Mo MongoDB to PostgreSQL

Demo

Steps Description

1 Ja Java 8 (J (JRE) ) In Installa allatio ion an and Config figuratio ion: http://download.oracle.com/otn-pub/java/jdk/8u131-b11/d54c1d3a095b4ff2b6607d096fa80163/jre-8u131- linux-x64.tar.gz 2 Po PostgreSQL In Installa allatio ion an and Config figuratio ion:

  • Modify, if needed, PostgreSQL instance configuration: /u02/pgdata/PG1/postgresql.conf

https://www.torodb.com/stampede/docs/1.0.0-beta2/configuration/postgresql-configuration-tips/

  • Create user torodb and database torod (with password)
  • Test the connection with new user and databases
  • Adapt pg_hba.conf for new connections

3 Mo MongoDB Installati tion and Configurati tion:

  • Configure and initialize MongoDB replication: replset = torodb
  • Check replication config. (primary and secondary)
  • Import data into MongoDB

4 ToroDB Stampede Installation and Configuration:

  • torodb.yaml configuration file creation
  • .toropass file creation for creation
  • Start ToroDB Stampeded
slide-36
SLIDE 36

30/06/2017 How to migrate data from MongoDB to Postgres with ToroDB Page 36

Mi Migration: from Mo MongoDB to PostgreSQL

Demo

slide-37
SLIDE 37

30/06/2017 Page 37 How to migrate data from MongoDB to Postgres with ToroDB

1 2 3 4 5

Co Conclusion

> Advantages vs Drawbacks

slide-38
SLIDE 38

30/06/2017 How to migrate data from MongoDB to Postgres with ToroDB Page 38

Co Conclusion

Advantages vs Drawbacks

Fast and Powerful Dynamic schema generation Only supports PostgreSQL as RDBMS backend Dynamic changes in the schema Open source and free!! Cross-platform: Linux, Windows Need improvements for different schema in the same mongo collection

slide-39
SLIDE 39

Any y questions?

Please do ask!

We would love to boost your IT-Infrastructure Ho How a about yo you?

Zürich Basel Delémont Nyon 30/06/2017 Page 39 How to migrate data from MongoDB to Postgres with ToroDB