In the family of open-source RDBMS MySQL and PostgreSQL are known as powerful systems coming with a lot of administration and development tools. Both have been adopted for all popular OS and have SAAS variations as well.However, when talking about large and complicated databases, PostgreSQL offers multiple advantages towards MySQL that may be reasons 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 supports spatial data types viaPost GIS extension

On the other hand, PostgreSQL requires more experienced staff for maintenance and development purposes compared to MySQL. So, PostgreSQL is not a good alternative to MySQL for simple database projects if there are no plans to scale it.

Procedure of MySQL to PostgreSQL database migration is similar for that class of RDBMS and it consists of few basic steps.

  1. Definition of every MySQL table is extracted in form of SQL statements along with indexes and constraints. phpMyAdmin required to highlight the table, navigate to ‘Export’> ‘Custom’, set format option to ‘SQL’ and make sure that radio-button ‘Structure’ is selected. In MySQL command prompt the same purpose may be achieved using this statement:

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

  1. These SQL statements must be transformed according to destination format and loaded to the PostgreSQL database. All MySQL types must be mapped into PostgreSQL based on the range of accepted values. 
  2. Next step is to export MySQL data into comma separated values(CSV)format. In phpMyAdmin it is required to highlight table, go to ‘Export’ tab, select ‘Custom’, set format option to ‘CSV’ and make sure that radio-button ‘Data’ is selected.In MySQL command prompt this can be done through the following 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 PostgreSQL database.
     
  2. The final step of the database is processing views, stored procedures and triggers.Those database objects are extracted in form of SQL statements and source code. The following queries are used for that purpose in both phpMyAdmin and MySQL command prompt: 

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. Finally, the statements and source codes must be transformed according to PostgreSQL syntax and loaded to the target database. 

This whitepaper proves that MySQL to PostgreSQL database migration it is a tedious and complicated task. Manual approach to the migration process may lead to data lost or corruption due the human factor. It is reasonable to consider using special software to automate MySQL to PostgreSQL database migration. 

One of such tools having all necessary features to handle large and complex projects is MySQL-to-PostgreSQL converter developed by Intelligent Converters,software company specializing in database migration and synchronization since 2001.