General Blog

Migrate database from MySQL to PostgreSQL

370 0

Among all open source RDBMSMySQL and PostgreSQL are recognized as the most popular systems enrichedwith lot ofmanagementframeworks and developer APIs. When compare these two RDBMS,they provide similar capabilities although PostgreSQL overcomes MySQL in following advantages:

  • full compliance with ANSI SQL standard
  • support for several models of indexing
  • asynchronous commit
  • support for both synchronous and asynchronous replication

This is the primary reason of why many organizations migrate their MySQL databases to PostgreSQL.

The process of database migration from MySQL to PostgreSQL is arranges according to extract-transform-load (ETL) concept and it consists of the following steps:

  • MySQL table definitions are extracted in form of SQL CREATE-statementsalong with the corresponding indexes and constraints
  • those statements are converted to the PostgreSQLdata definition language (DDL) and then loaded into the target database
  • MySQL data is exported into an intermediate comma separate values (CSV) files
  • theseCSV filesare transformed according to PostgreSQL format and then loaded into the target database
  • MySQL views, stored procedures/functionsand triggers are extracted in form of SQL CREATE-statements and source code
  • These statements and source code are converted to PostgreSQL format and loaded into the target database
  • Finally, the most important step is to verify that all database objects were converted properly

The guide above illustrates that MySQL to PostgreSQL migration is quite complicated procedure demanding a lot of efforts when doing it manually. Also, it may cause data loss or corruption due to human errors. In view of these facts it is reasonable to use special software to automate the migration procedure.

MySQL to PostgreSQL converter is one of such tools provided by Intelligent Converters, a company specializing in database conversion, migration and synchronization since 2001. The tool has high performance due to direct reading and writing algorithms.

All versions of MySQL and PostgreSQL are supported including SAASvariations such as Amazon RDS and Heroku. MySQL to PostgreSQL convertersupports command line for scheduling andautomatingthe database migration.The product not only can migrate MySQL data into new database but also merge or synchronize it with existing PostgreSQL database.

Some migration projects require partial data transfer to the destination database.For this purpose,MySQL to PostgreSQL converterprovides filtering data via SELECT queries. Below examples of using this feature are given for different tasks.

  • Select particular rows: SELECT * FROM SomeTable WHERE KeyColumn>100
  • Rename columns: SELECT column123 AS LastName, column_2 as Company FROM SomeTable
  • Do not migrate NULL values: SELECT * FROM SomeTable WHERE Birthday IS NOT NULL

If it is required to modifydata type in the destinationtable, MySQL to PostgreSQL convertercan do customtype mapping. If PostgreSQL server forbids remote connections, the tool can export the data into SQL script. Then database administrator or other person responsible for migration can import the script file into the destination database usingthe standard tools like psql or phpPgAdmin.