Transform MySQL to PostgreSQL

Why should you Move to PostgreSQL?

Both MySQL and PostgreSQL are well-known open-source RDBMS rich with great deal of administration and development tools. Both systems are ported on every popular OS and also have large groups of professionals. Yet, PostgreSQL offers several benefits that could be essential for specific projects:

  • full compliance with ANSI SQL standard
  • support for multiple indexing models
  • synchronous and asynchronous replication are supported
  • support for Common Table Expressions (CTE)
  • full outer joins are supported
  • unlike MySQL, Postgres works with arrays

Alternatively, PostgreSQL is a lot more complicated than MySQL which can be hard for newbies. So, in case you work with very simple database project that’s protected by MySQL features and don’t intend to scale it, there’s no much sense to move it from MySQL to Postgres.

Migration Techniques

The procedures explained here are a few important process for moving a database manually from MySQL to Postgres.

  1. All table definitions are obtained from the source database as DDL SQL statements. You can accomplish it in this way:

phpMyAdmin – highlight the table, go to ‘Export’ tab, select ‘Custom’ option, set format to ‘SQL’ and make sure that radio-button ‘Structure’ is selected

MySQL console client – use the statement

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

All patterns in round brackets should be substituted with actual values

  1. Translate those DDL statements in accordance with PostgreSQL format and load inside the destination database. The primary problem of this specific step is to effectively convert column types from MySQL to Postgres. Go through article on Types Mapping for more information about types conversion.
  2. Data of every MySQL table is exported into an intermediate format such as CSV file. You can accomplish it in the following way:

phpMyAdmin – highlight the table, go to ‘Export’ tab, select ‘Custom’ option, set format to ‘CSV’ and ensure that radio-button ‘Data’ is chosen

MySQL console client – use the statement

SELECT * INTO OUTFILE (‘table.csv’)

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

LINES TERMINATED BY ‘\n’ FROM (table)

All patterns in round brackets must be replaced by actual values

  1. The data in CSV files has to be transformed according to PostgreSQL format (if it is necessary) and then loaded into the destination database.
  2. Finally views, stored procedures and triggers are extracted from MySQL database in form of SQL statements and source code. In both phpMyAdmin and MySQL console client it can be achieved by using these SQL-statements:

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 resulting statements and source codes are converted into PostgreSQL format and loaded into the target database. Note that this step requires deep knowledges in MySQL and PostgreSQL dialects of SQL and database development.

The procedures above are indicates that the database migration from MySQL to Postgres is a very challenging task. Carrying it out manually can lead to loss of data or corruption as a result of the human factor. This is basically the best process to work with, by making use of  suitable software programs to automate the database conversion process. MySQL to PostgreSQL database migration tool by Intelligent Converters is among such tools that has all required capabilities to manage large and complicated migration projects.