MySQL to PostgreSQL Database Migration

Database specialists recognize MySQL and PostgreSQL as powerful open-source RDBMS coming with a lot of administration and development tools. Both systems have been adopted for all popular OS and have SAAS variations as well.Despite of these facts, PostgreSQL has multiple benefits towards MySQL that may be particular reason for migration:

• It is 100% compatible with ANSI SQL standard
• It supports multiple indexing models
• It provides both synchronous and asynchronous replication
• It supports full outer joins
• It is supplied with number of extensions, for example spatial module Post GIS

At the same time, PostgreSQL is more complicated than MySQL and consequently requires more experienced staff for maintenance and development purposes. So, PostgreSQL is not a good alternative to MySQL for simple database projects if there are no plans to scale it.

Database migration from MySQL to PostgreSQL consists of few basic steps.

1. Definition of every MySQL table is extracted in form of SQL statements along with indexes and constraints. In phpMyAdmin highlight the table, go to ‘Export’ tab, select ‘Custom’, set format option to ‘SQL’ and make sure that radio-button ‘Structure’ is selected. In MySQL command line client use the following statement:

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

1. These SQL statements must be transformed according to destination format and loadedto the PostgreSQL database. Each MySQL type must be converted into PostgreSQL equivalent based on range of acceptable values.
2. Next step is to export MySQL data into comma separated values(CSV)format. In phpMyAdmin highlight the table, go to ‘Export’ tab, select ‘Custom’, set format option to ‘CSV’ and make sure that radio-button ‘Data’ is selected.In MySQL commandline processor use this statement:

SELECT * INTO OUTFILE (‘table.csv’)

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

LINES TERMINATED BY ‘\n’ FROM (table)

1. The data in CSV files must be transformed according to the target format if it is necessary and loaded into the PostgreSQLdatabase.
2. The final step of the database is processing views, stored procedures and triggers.All of these objects and entities must be extracted from MySQL database in form of SQL statements and source code. It can be done using the following queries in both phpMyAdmin and MySQL console environments:

views

SELECT table_name, view_definition FROM information_schema.views

WHERE table_schema=’(your database name)’

stored procedures

SHOW PROCEDURE STATUS WHERE Db = ‘<database_name>’

triggers

SHOW TRIGGERS

1. Finally, the statements and source codes must be transformed according to PostgreSQL syntax and loaded to the target database.

This brief guide to database migration from MySQL to PostgreSQL proves that it is a quite tedious and complicated task. Manual implementation of the process may cause data lost or corruption due the human factor. It is reasonable to consider using special software to automate MySQL to PostgreSQLdatabase migration.

One of such tools having all necessary features to handle large and complex projects has been developed by Intelligent Converters,software company specializing in database migration and synchronization since 2001. More information about the product may be found on the official page: https://www.convert-in.com/mysql-to-postgres.htm