How to Convert MySQL to PostgreSQL?

£ 1 WP Hosting: Free SSL Certificates
Click Here To Check NOW

Why You Should Migrate to PostgreSQL?

MySQL and PostgreSQL are equally popular open-source RDBMS because of their powerful administration and development features. The two systems are ported almost on all popular OS and they have huge number of experts. However, PostgreSQL provides additional advantages for certain projects:

  • Complete compliance with ANSI SQL standard
  • It supports multiple indexing models
  • It also supports both synchronous and asynchronous replications
  • It supports CTE – Common Table Expressions
  • It also supports full outer joins
  • Unlike MySQL platform, PostgresSQL is compatible with arrays

On the other side, PostgreSQL is much more complex if you compare with MySQL and it can become pretty tough for beginners. If you are working on simple projects that can be handled by MySQL there is no point in migrating Postgres, unless you plan to upgrade it.

Migration Methods

These are the steps you should follow in order to migrate your database from MySQL to PostgresSQL – manually.

1. Every table definitions should be extracted from a source database as DDL SQL statements. This is the way to do it:

phpMyAdmin: highlight a table, select ‘Export’ tab, select ‘Custom’, now set the format to ‘SQL’. Remember to pick the radio-button ‘Structure’.

MySQL console client: simply use the statement

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

Note this: Each pattern in the round brackets should be replaced by actual values.

2. Convert every DDL statements into PostgreSQL format and then upload to the database destination. However, there is a challenging step in this method. You must properly convert column types from MySQL to PostgresSQL. You can get more details about types conversion at https://www.convert-in.com/mysql-to-postgres-types-mapping.htm.

3. Export data from every MySQL table into CSV file format. This can be done as like this:

phpMyAdmin: highlight data table, select ‘Export’, select ‘Custom’, set the format to CSV. After that don’t forget to select radio-button ‘Data’

MySQL console client:  simply use the statement

SELECT * INTO OUTFILE (‘table.csv’)

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

LINES TERMINATED BY ‘\n’ FROM (table)

Note this: Replace every pattern in round brackets with actual values

4. Now, data you exported to CSV format should be converted into PostgreSQL format (if only required). After that load to the destination database

5. Now to the final step. Views, stored procedures and triggers should be extracted from MySQL database in the form of SQL statements and as source code. This can be done both on phpMyAdmin and MySQL console client by using the following 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

6. Now the resulting statements and the source codes are converted into a PostgreSQL format and loaded to the target database.

Please note that the steps for migration of database from MySQL to PostgresSQL can be complicated and overwhelming. A human error is very common when it’s done manually and this could result in data loss or file corruption. The best practice is to use good software that can automate database conversion process. You can check out MySQL to Postgres conversion tool created by Intelligent Converters. This tool has all the features to handle migration of large and complex databases.