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 beenadopted forall 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 supportsmultiple indexing models
• It provides both synchronous and asynchronous replication
• It supportsfull outer joins
• It is supplied with number of extensions, for example spatial module PostGIS

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 forsimple 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 tableis extracted in form of SQL statements along with indexes and constraints. In phpMyAdminhighlight 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 lineclient usethe following statement:

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

1. These SQL statements must be transformed according to destination format and loadedto the PostgreSQL database. Each MySQL typemust be converted into PostgreSQL equivalent based on range of acceptable values. 
2. Next step is to exportMySQL 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 commandlineprocessor use thisstatement:

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 targetformat 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 followingqueries 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 = ‘your database name’

triggers

SHOW TRIGGERS

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

This brief guide to database migration from MySQL to PostgreSQL proves thatit 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