Database migration from MySQL to PostgreSQL

Although MySQL and PostgreSQL are both very popular open source RDBMS having rich set of administration tools and programming APIs, the second DBMS has multiple important advantages over MySQL:

  • PostgreSQL is fully compatible with ANSI SQL standard
  • PostgreSQL supports several index types (B-tree, Hash, GiST and others) that used different algorithms
  • PostgreSQL provides asynchronous commit capabilities
  • PostgreSQL supports both synchronous and asynchronous models of replication

That is the main reason of why many organizations migrates their databases from MySQL to Postgres.

Database migration from MySQL to PostgreSQL is usually implemented according to extract-transform-load (ETL) concept that consists of the following steps:

  • Table definitions are extract from MySQL database in form of CREATE-statements
  • Those statements are converted into the target dialect of SQL and loaded into PostgreSQL database
  • MySQL data is imported from the source database into comma separate files (one file per table) as an intermediate storage
  • The data is transformed to comply with PostgreSQL format and load it into the target database
  • MySQL views, stored procedures and triggers are exported in form of SQL statements and source code
  • Those statements and source code are converted into PostgreSQL format and imported in the destination database
  • The most important step of the entire migration procedure: validate all entries from PostgreSQL database to check that everything has been migrated properly

The brief description of MySQL to PostgreSQL migration listed above indicates this tedious procedure requires huge efforts when doing it manually. Also, it leads to high risk of data loss or corruption due to the human errors. That is why many database professionals use special software to simplify the migration process.

One of such tools is MySQL to PostgreSQL converter developed by Intelligent Converters, a software vendor specializing in database conversion, migration and synchronization since 2001. The database migration tool has extremely high performance (about 20,000 records per second of average modern system) due to direct reading and writing data without any middleware libraries or components. It supports all versions of MySQL and PostgreSQL including software-as-service variations. Command line support can be used to automate and to script database migration. Also, MySQL to PostgreSQL converter allows to merge and synchronize MySQL data with existing PostgreSQL database.

Besides the specified basic features, the tool allows to filter MySQL data via SELECT queries. With this option it is possible to migrate particular columns and rows only or transform the data during the migration.

When it is necessary to change column type, name or attributes in the resulting database as well as exclude some columns from migration, MySQL to PostgreSQL converter provides a capability called “custom column mapping”.

If PostgreSQL server does not allow remote connection, MySQL to PostgreSQL converter provides option of exporting into SQL script. According to this scenario, MySQL tables are converted into a local file containing PostgreSQL statements to create tables with all indexes and constraints and fill them with the data. The script file can be loaded into PostgreSQL database via the standard tools like psql or phpPgAdmin.