MySQL to PostgreSQL Conversion: How and Why

WHY?

Like MySQL, PostgreSQL is an open-source RDBMS. Other similarities between the two include a broad array of development tools and administration tools, and due to their popularity, both have extensive communities of experts and compatibility with every popular OS. However, in a side by side comparison, PostgreSQL offers a few notable advantages. It features support for synchronous and asynchronous replication, can support full outer joins, CTE (Common Table Expressions), multiple indexing models, 100% compatibility with the ANSI SQL standard, and in addition, PostgreSQL does work with arrays. Despite these advantages though, its downfall is its complexity (for beginners). Therefore, before deciding to go with PostgreSQL, one should carefully consider whether your database project is large and complex enough to justify a migration from MySQL to PostgreSQL.

HOW?

If you do decide to migrate from MySQL to PostgreSQL, there are a number of steps you’ll need to follow.

1: To extract the table definitions from the soure database (in the form of a DDL SQL statement), this is what you should do:

phpMyAdmin: After highlighting the table, choose the “custom” option from the “export” tab, select the radio button “structure” and make sure the format is SQL.

MySQL console client: You can use the following statement: mysqldump -d -h (host) -u (user) –p(password) (databasename) > (dumpifle)

Make sure you replace the patterns in round parentheses with actual values.

2: Next you’ll need to get those DDL statements translated into PostgreSQL format. Once that’s done, you can load them into the database. Make sure that you have properly converted the column types from MySQL to PostgreSQL. You can check the article Types Mapping for more information about how to do that.

3: The data from MySQL tables needs to be exported into a CSV file, which is an intermediate format, and this is how to do that:

phpMyAdmin: Again, the table must be highlighted, and pick the “custom” option from the “export” tab, select the “data” radio button and set format to “CSV”.

MySQL console client: You need to use this statement, and again ensure that what is in the round parentheses is replaced with actual values.

SELECT * INTO OUTFILE (‘table.csv’)

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

LINES TERMINATED BY ‘\n’ FROM (table)

4: Once this is done, you’ll need to transform the CSV file data in accordance with PostgreSQL format, and after this, load the data to the database.

5: Once this is done, the triggers, stored procedures and also views will need to be pulled from the MySQL database. This must be done in the form of source code as well as SQL statements. You can use the following SQL statements to do this, using both MySQL console client and phpAdmin:

for the views:

SELECT table_name, view_definition FROM information_schema.views

WHERE table_schema=’(your database name)’

for the stored procedures:

SHOW PROCEDURE STATUS WHERE Db = ‘your database name’

for the triggers:

SHOW TRIGGERS

6: The source codes and statements that arise from this procedure will then be converted to PostgreSQL format. After this, they’ll be loaded to the appropriate database. For this you do need to have a very detailed understanding of both PostgreSQL and MySQL database development, and the SQL dialects of both programs.

As you can see, migrating from MySQL to PostgreSQL can prove to be quite a challenge, and there is a very real risk of data corruption or other technical mishaps, from both the human and computer side of things. Therefore, you’d be safest using a tool to automate the whole procedure – a tool like MySQL to Postgres converter. This tool, developed by Intelligent Converters, is able to easily handle all sorts of migration projects from the smallest to the largest and most complex.