Converting MySQL to PostgreSQL

Why Migrate to PostgreSQL?

If we concern these MySQL and PostgreSQL both are mostly known open-source RDBMS enriched by huge range of development and administration tools. MySQL and postgreSQL are ported on very modern OS and they have an enormous community of specialists, therefore postgreSQL provides different advantages that might be significant for individual projects:

• It is entirely compliance with ANSI SQL standard.
• It provides support for multiple indexing models.
• It shows the Replication of synchronous and asynchronous are supported.
• It Maintain for Common Table Expressions (CTE).
• It Supports of full outer joins.
• Postgres works with arrays unlike MySQL.

Alternatively, for beginners PostgreSQL is more complex than MySQL. So, working on simple database project that is covered by MySQL capabilities and do not plan to scale it, so that it makes no sense to migrate it from MySQL to Postgres.

Migration Strategies

Steps mentioned below are required to migrate database from MySQL to Postgres manually

1.   From the source database, all table definitions are extracted from DDL SQL statements.

This job can be done in one of the following ways:

phpMyAdmin – we can here emphasize the table, then go to ‘Export’ tab, select ‘Custom’ option over there, set format to ‘SQL’ and make sure that radio-button ‘Structure’ is to be chosen.

MySQL console – it uses the command line

mysqldump -d -h (host) -u (user) –p(password) (databasename) > (dumpifle)

All the above shown patterns in round brackets must be replaced by actual values

1.   According to PostgreSQL format and load into the destination database. We have to interpret those DDL statements. Conversion of column types from MySQL to Postgres properly is the main challenge of this. You can get further information by reading article Types of Mapping about type’s conversion.
2.   Data of every MySQL table is transformed into an intermediate format such as CSV file.

It can be done in such ways.

phpMyAdmin – emphasize the table, go to ‘Export’ tab, select the option ‘Custom’ option, put format to ‘CSV’ and make sure that radio-button ‘Data’ is been selected

MySQL console client – use the statement

SELECT * INTO OUTFILE (‘table.csv’)

FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”‘

LINES TERMINATED BY ‘\n’ FROM (table)

All above mentioned patterns in round brackets must be replaced by actual values

1.   According to PostgreSQL format the data in CSV files has to be transformed (if it is necessary) and then loaded into the destination database.
2.   Stored procedures and triggers are extracted from MySQL database in form of SQL statements and source code has been viewed finally. It can be done by using these SQL-statement in both phpMyAdmin and MySQL console client reports:

Views

SELECT table_name, view_definition FROM information_schema.views

WHERE table_schema=’(your database name)’;

Stored procedures

SHOW PROCEDURE STATUS WHERE Db = ‘your database name’;

Triggers

SHOW TRIGGERS;

1.   The concluding statements and source codes are transformed into PostgreSQL format and encumbered into the target database. Be careful with this step, it requires deep knowledges in MySQL and PostgreSQL dialects of SQL to make the conversion properly.

Steps mentioned above are proving the database migration from MySQL to Postgres is the relatively arduous task. While doing it manually it could lead to data loss, error or corruption due to human error. It is the best way to follow and use the appropriate software tools to automate the database conversion process. MySQL to PostgreSQL migration tool produced by Intelligent Converters is one of such solutions that have all necessary attributes to handle large and complex migration projects.