Migration Toolkit Overview:
The EDB Postgres Migration Toolkit (MTK) is a powerful command-line tool that offers granular control of the process of migrating of database objects and data from other database management systems to EDB Postgres Advanced Server and PostgreSQL.
MTK supports migration of the following databases:
- Oracle® Database
- SQL Server
Learn more about the performance improvements in the EDB Migration Toolkit 52.0.3 below. For more information, see EDB Postgres™ Migration Toolkit Guide.
Migrating TEXT data types from MySQL to Advanced Server
Earlier this release, MySQL’s TEXT, TINYTEXT, MEDIUMTEXT, and LONGTEXT data types were mapped to Advanced Server’s CLOB data type. Migrating CLOB data is a time taking process as MTK migrates one row at a time, by default. As the TEXT family data types already exist in Postgres and Advanced Server, with this release, MySQL’s TEXT type columns are migrated to Advanced Server’s TEXT type columns. This helped in improving the overall data migration performance.
Example: Data migration from MySQL to Advanced Server
Migrate schema only
./runMTK.sh -sourcedbtype mysql -targetdbtype enterprisedb -tables data -schemaOnly testdb
Command used for migrating data using MTK 51.0.0 and MTK 52.0.3
./runMTK.sh -sourcedbtype mysql -targetdbtype enterprisedb -tables data -dataOnly testdb
After mapping the MySQL’s TEXT family data types to the respective Advanced Server TEXT family data types instead of CLOB, the performance has improved by 98% on an average.
Migrating data from Oracle to Advanced Server using fetchSize option
Starting this release, the overall data migration time from Oracle to Advanced Server improved substantially while fixing the fetchSize option related issue.
If you specify too large value for the fetchSize option, it may cause Out of Memory error.
Migrating table structure only
runMTK.sh -sourcedbtype oracle -targetdbtype enterprisedb -schemaOnly -tables EMPLOYEES HRM
Migrating data using -fetchSize option (with different values)
runMTK.sh -sourcedbtype oracle -targetdbtype enterprisedb -dataOnly -fetchSize 10 -fastCopy -tables EMPLOYEES HRM
Using JDBC’s default fetchSize (Value for fetchSize option is not specified):
While running MTK, if no value for fetchSize is specified, then MTK uses default JDBC fetchSize (10, in case of Oracle JDBC) and migrating hundred thousand rows takes around 40 minutes.
Using fetchSize option:
After using the appropriate fetchSize option, the performance has improved by 98% on an average. Note: You may not see further significant improvement post a certain fetchSize value.
Muhammad Ikram is a Postgres Professional working as Lead Quality Assurance Engineer and Scrum Master at EnterpriseDB.
He is an Oracle Certified Developer, Oracle Certified DBA, Professional Scrum Master with working experience in Database Replication, Migration from Oracle to EDB Postgres Advance Server, Business Process Management, and Oracle Database Administration.