{"id":9431,"date":"2016-12-06T17:24:24","date_gmt":"2016-12-06T16:24:24","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/from-mysql-oracle-to-postgres-using-the-edb-migration-toolkit\/"},"modified":"2016-12-06T17:24:24","modified_gmt":"2016-12-06T16:24:24","slug":"from-mysql-oracle-to-postgres-using-the-edb-migration-toolkit","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/from-mysql-oracle-to-postgres-using-the-edb-migration-toolkit\/","title":{"rendered":"From MySQL (Oracle) to Postgres using the EDB Migration Toolkit"},"content":{"rendered":"

Why should you migrate?<\/strong>
\nIf your current MySQL database does not offer some needed functionnalities according to your business as:
\n– more security
\n– more high availibilty options (hot standby)
\n– Strong Data Warehouse capabilities
\nIf you want to consolidate the number of different instances (Postgres, MySQL, MS-SQL,…)
\nIf you want to reduce administrative costs by using fewer database platforms
\nWhich tool should you use?<\/strong>
\nthe migration Toolkit command-line from EnterpriseDB that can be found below
\nhttp:\/\/www.enterprisedb.com\/products-services-training\/products-overview\/postgres-plus-solution-pack\/migration-toolkit<\/code>
\nWhy ?<\/strong>
\nReally easy to use
\nWhich MySQL Objects are supported for the migration?<\/strong>
\n– Schemas
\n– Tables
\n– Constraints
\n– Indexes
\n– Table Data
\nWhat about partitionned table?<\/strong>
\nYou have to remove the partitions before the migration
\nmysql> ALTER TABLE Table_name REMOVE PARTITIONING;<\/code>
\nMy environment:<\/strong>
\nMySQL: 5.7.14 on Oracle Linux Server 7.1
\nPostgreSQL: 9.6.1.4 on Oracle Linux Server 7.1
\nWhat are the prerequisites?<\/strong>
\n– download the migration toolkit from EnterpriseDB
\nNote that it can be only installed by registered users but the registration is free and can be done directly on the EnterpriseDB website.
\n– Install it and follow the instructions
\n .\/edb-migrationtoolkit-50.0.1-2-linux-x64.run<\/code>
\n– download the MySQL JDBC driver: mysql-connector-java-5.1.40-bin.jar
\n http:\/\/www.enterprisedb.com\/downloads\/third-party-jdbc-drivers<\/code>
\n– Install the driver by moving it to the right directory:
\n\/usr\/lib\/jvm\/java-1.8.0-openjdk-1.8.0.111-2.b15.el7_3.x86_64\/jre\/lib\/ext<\/code>
\n– To facilitate\u00a0 the migration, you have to prepare the configuration file: toolkit.properties located in your installation directory
\nthe most important is to associate the right JDBC URL to the SRC_DB_URL parameter
\nSRC_DB_URL=jdbc:mysql:\/\/hostname[:port]\/database<\/code>
\nFollowing is the content of the config file
\nSRC_DB_URL=jdbc:mysql:\/\/192.168.56.200:33001\/employees
\nSRC_DB_USER=root
\nSRC_DB_PASSWORD=manager
\nTARGET_DB_URL=jdbc:edb:\/\/192.168.56.200:5433\/employees # the database must be created in Postgres before
\nTARGET_DB_USER=postgres
\nTARGET_DB_PASSWORD=manager<\/code>
\nIn case you get MySQL connection problems (SSL), modify the parameter SRC_DB_URL
\nSRC_DB_URL=jdbc:mysql:\/\/192.168.56.200:33001\/employees?autoReconnect=true&useSSL=false<\/code>
\nThis will disable SSL and also suppress SSL errors.
\nBefore starting the Migration, it is mandatory to create a blank target database in the Postgres instance<\/strong>
\nWhat options for the migration ?<\/strong>
\n-sourcedbtype is mysql
\n-targetdbtype is enterprisedb
\n-fetchsize is 1\u00a0 to avoid\u00a0 an ‘out of heap space’ error and force the toolkit to load data one row at a time
\nHow to start the migration?<\/strong>
\n[root@pg_essentials_p1 mtk]# bin\/runMTK.sh -sourcedbtype mysql -targetdbtype enterprisedb -fetchSize 1 employees
\nRunning EnterpriseDB Migration Toolkit (Build 50.0.1) ...
\nSource database connectivity info...
\nconn =jdbc:mysql:\/\/192.168.56.200:33001\/employees?autoReconnect=true&useSSL=false
\nuser =root
\npassword=******
\nTarget database connectivity info...
\nconn =jdbc:edb:\/\/192.168.56.200:5433\/employees
\nuser =postgres
\npassword=******
\nConnecting with source MySQL database server...
\nConnected to MySQL, version '5.7.14-enterprise-commercial-advanced-log'
\nConnecting with target EDB Postgres database server...
\nConnected to EnterpriseDB, version '9.6.1.4'
\nImporting mysql schema employees...
\nCreating Schema...employees
\nCreating Tables...
\nCreating Table: departments
\n..........................
\nCreated 6 tables.
\nLoading Table Data in 8 MB batches...
\nLoading Table: departments ...
\n[departments] Migrated 9 rows.
\n..............................
\nLoading Table: salaries ...
\n[salaries] Migrated 246480 rows.
\n................................
\n[salaries] Migrated 2844047 rows.
\n[salaries] Table Data Load Summary: Total Time(s): 20.143 Total Rows: 2844047 Total Size(MB): 94.1943359375
\nLoading Table: titles ...
\n[titles] Migrated 211577 rows.
\n[titles] Migrated 419928 rows.
\n[titles] Migrated 443308 rows.
\n[titles] Table Data Load Summary: Total Time(s): 3.898 Total Rows: 443308 Total Size(MB): 16.8955078125
\nData Load Summary: Total Time (sec): 33.393 Total Rows: 3919015 Total Size(MB): 138.165
\nCreating Constraint: PRIMARY
\nCreating Constraint: dept_name
\n................................
\nCreating Index: dept_no1
\nSchema employees imported successfully.
\nMigration process completed successfully.
\nMigration logs have been saved to \/root\/.enterprisedb\/migration-toolkit\/logs
\n******************** Migration Summary ********************
\nTables: 6 out of 6
\nConstraints: 11 out of 11
\nIndexes: 2 out of 2
\nTotal objects: 19
\nSuccessful count: 19
\nFailed count: 0
\nInvalid count: 0
\n************************************************************<\/code>
\nSo as you can see, this migration process is really easy and you can take immediately benefits of all the standard features.<\/p>\n","protected":false},"excerpt":{"rendered":"

Why should you migrate? If your current MySQL database does not offer some needed functionnalities according to your business as: – more security – more high availibilty options (hot standby) – Strong Data Warehouse capabilities If you want to consolidate the number of different instances (Postgres, MySQL, MS-SQL,…) If you want to reduce administrative costs […]<\/p>\n","protected":false},"author":28,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[],"type_dbi":[],"class_list":["post-9431","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring"],"acf":[],"yoast_head":"\nFrom MySQL (Oracle) to Postgres using the EDB Migration Toolkit - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/from-mysql-oracle-to-postgres-using-the-edb-migration-toolkit\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"From MySQL (Oracle) to Postgres using the EDB Migration Toolkit\" \/>\n<meta property=\"og:description\" content=\"Why should you migrate? If your current MySQL database does not offer some needed functionnalities according to your business as: – more security – more high availibilty options (hot standby) – Strong Data Warehouse capabilities If you want to consolidate the number of different instances (Postgres, MySQL, MS-SQL,…) If you want to reduce administrative costs […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/from-mysql-oracle-to-postgres-using-the-edb-migration-toolkit\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2016-12-06T16:24:24+00:00\" \/>\n<meta name=\"author\" content=\"Open source Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Open source Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/from-mysql-oracle-to-postgres-using-the-edb-migration-toolkit\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/from-mysql-oracle-to-postgres-using-the-edb-migration-toolkit\\\/\"},\"author\":{\"name\":\"Open source Team\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/59554f0d99383431eb6ed427e338952b\"},\"headline\":\"From MySQL (Oracle) to Postgres using the EDB Migration Toolkit\",\"datePublished\":\"2016-12-06T16:24:24+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/from-mysql-oracle-to-postgres-using-the-edb-migration-toolkit\\\/\"},\"wordCount\":340,\"commentCount\":0,\"articleSection\":[\"Database Administration & Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/from-mysql-oracle-to-postgres-using-the-edb-migration-toolkit\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/from-mysql-oracle-to-postgres-using-the-edb-migration-toolkit\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/from-mysql-oracle-to-postgres-using-the-edb-migration-toolkit\\\/\",\"name\":\"From MySQL (Oracle) to Postgres using the EDB Migration Toolkit - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2016-12-06T16:24:24+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/59554f0d99383431eb6ed427e338952b\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/from-mysql-oracle-to-postgres-using-the-edb-migration-toolkit\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/from-mysql-oracle-to-postgres-using-the-edb-migration-toolkit\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/from-mysql-oracle-to-postgres-using-the-edb-migration-toolkit\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"From MySQL (Oracle) to Postgres using the EDB Migration Toolkit\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/59554f0d99383431eb6ed427e338952b\",\"name\":\"Open source Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"caption\":\"Open source Team\"},\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/open-source-team\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"From MySQL (Oracle) to Postgres using the EDB Migration Toolkit - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/from-mysql-oracle-to-postgres-using-the-edb-migration-toolkit\/","og_locale":"en_US","og_type":"article","og_title":"From MySQL (Oracle) to Postgres using the EDB Migration Toolkit","og_description":"Why should you migrate? If your current MySQL database does not offer some needed functionnalities according to your business as: – more security – more high availibilty options (hot standby) – Strong Data Warehouse capabilities If you want to consolidate the number of different instances (Postgres, MySQL, MS-SQL,…) If you want to reduce administrative costs […]","og_url":"https:\/\/www.dbi-services.com\/blog\/from-mysql-oracle-to-postgres-using-the-edb-migration-toolkit\/","og_site_name":"dbi Blog","article_published_time":"2016-12-06T16:24:24+00:00","author":"Open source Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Open source Team","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/from-mysql-oracle-to-postgres-using-the-edb-migration-toolkit\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/from-mysql-oracle-to-postgres-using-the-edb-migration-toolkit\/"},"author":{"name":"Open source Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/59554f0d99383431eb6ed427e338952b"},"headline":"From MySQL (Oracle) to Postgres using the EDB Migration Toolkit","datePublished":"2016-12-06T16:24:24+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/from-mysql-oracle-to-postgres-using-the-edb-migration-toolkit\/"},"wordCount":340,"commentCount":0,"articleSection":["Database Administration & Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/from-mysql-oracle-to-postgres-using-the-edb-migration-toolkit\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/from-mysql-oracle-to-postgres-using-the-edb-migration-toolkit\/","url":"https:\/\/www.dbi-services.com\/blog\/from-mysql-oracle-to-postgres-using-the-edb-migration-toolkit\/","name":"From MySQL (Oracle) to Postgres using the EDB Migration Toolkit - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2016-12-06T16:24:24+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/59554f0d99383431eb6ed427e338952b"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/from-mysql-oracle-to-postgres-using-the-edb-migration-toolkit\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/from-mysql-oracle-to-postgres-using-the-edb-migration-toolkit\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/from-mysql-oracle-to-postgres-using-the-edb-migration-toolkit\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"From MySQL (Oracle) to Postgres using the EDB Migration Toolkit"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/59554f0d99383431eb6ed427e338952b","name":"Open source Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","caption":"Open source Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/open-source-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/9431","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/28"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=9431"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/9431\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=9431"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=9431"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=9431"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=9431"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}