Database migration from MySQL to PostgreSQL

When planning to convert MySQL to PostgreSQL, it is important to understand cons and pros of every DBMS as well as scope of database objects that have to be converted. Main advantage of MySQL against PostgreSQL is that it’s easy to install and to use. PostgreSQL has multiple advantages such as: full compliance with SQL standard, support for different indexing models, common table expressions, outer joins.

Database migration process involves migrating such objects as table definitions, data, indexes, constraints, views, stored procedures and triggers.

Basically, database conversion from MySQL to PostgreSQL includes the following stages:

• Definitions of all tables, indexes and constraints (DDL) are extracted from MySQL database in form of SQL CREATE or ALTER statements
• These DDL statements must be converted according to PostgreSQL format and loaded into the target database
• Data of each MySQL table is exported in comma separate values (CSV) files
• Then every CSV file must be transformed in order to comply with the destination format and loaded into PostgreSQL database.
• All of MySQL views, stored procedures and triggers are exported as SQL statements and source code
• These SQL statements and source code are converted into PostgreSQL format and imported into the target database

On large and complex databases manual conversion may require a lot of efforts and cause risk of data loss or corruption due to the human factor. That’s why database specialist who is in charge for converting MySQL to PostgreSQL may consider using special conversion tool.

What features are required for advanced database migration tools?

• The tool must support all modern versions of MySQL and PostgreSQL including forks and SAAS variations (such as Heroku, MariaDB, Percona)
• High performance is very important to migrate large volume of data. It must be not less than 10MB per second
• The tool should provide option to customize column name, type, default values for every table as well as exclude particular columns from migration
• There must be an opportunity to export MySQL data into PostgreSQL script file for those cases where direct connection to the target database server is not available
• The tool must convert indexes, foreign keys and constraints with all necessary attributes
• Option to synchronize MySQL and PostgreSQL should be considered as extra advantage
• Command line support allows to script and schedule the database migration

Many software vendors offer solutions to migrate MySQL to PostgreSQL having all features listed above. One of these vendors is Intelligent Converters, a company specializing in database migration and synchronization since 2001. Besides software products the company offers service to customize all available solutions according to customer requirements at affordable rates. Also, they provide turn-key migration service for large and complicated corporate warehouses that convers data, database logic (stored procedures and functions, triggers) and application layer. More information about Intelligent Converters and their software is available at https://www.convert-in.com