Why Should You Migrate to PostgreSQL?
Every database professional knows MySQL and PostgreSQL as popular open-source relational DBMS providing powerful administration and development features. The two database management systems run on all popular OS have huge communities of fans. However, PostgreSQL provides additional advantages that may be vital for some 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.
- Every table definition 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.
- Convert every DDL statement 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.
- 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
- Now, data you exported to CSV format should be converted into PostgreSQL format (if only required). After that load to the destination database.
- 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
- 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 the migration of databases 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 a good software that can automate database conversion processes. You can check out MySQL to Postgres conversion tool created by Intelligent Converters. This tool has all the features to handle the migration of large and complex databases.
