MySQL and PostgreSQL are both known as extremely popular open-source RDBMS supplied with a lot of tools for administration and development purposes. These database management systems are ported on all popular OS and have large communities of fans. At the same time PostgreSQL provides multiple benefits compared to MySQL that may be important for some projects:

  • 100{96d91b5415478593143d8563ef204d636964c5863556d52af56fca50162c0ef5} compatibility with ANSI SQL standard
  • multiple indexing models are supported
  • synchronous and asynchronous replication
  • Common Table Expressions (CTE)
  • support for full outer joins

However, PostgreSQL is more complex than MySQL and requires more experienced staff for maintenance and development. That’s why it is not recommended to migrate simple database projects from MySQL to PostgreSQL in case there are no plans to scale it.

Approaches to Migration

There are few basic steps required to migrate MySQL database to PostgreSQL.

  1. Definition of every MySQL table is extracted in form of data definition language (DDL) SQL statements. To do this in phpMyAdmin highlight the appropriate 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 environment the following statement should be used:

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

All patterns in parenthesis must be replaced by actual values.

  1. Each of these DDL statements must be translated into PostgreSQL format and load to the target database. It is important to convert each MySQL column type into appropriate PostgreSQL equivalent.
  2. Then data of each MySQL table is exported into comma separated values (CSV) file. This task can be done in phpMyAdmin by highlighting table, navigating to ‘Export’ tab, selecting ‘Custom’ option, setting format to ‘CSV’ and activating radio-button ‘Data’. In MySQL console client the following statement must be used:

SELECT * INTO OUTFILE (‘table.csv’)

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

LINES TERMINATED BY ‘\n’ FROM (table)

All patterns in parenthesis must be replaced by actual values.

  1. If it is necessary the data from CSV files is transformed according to PostgreSQL format and then loaded into the target database.
  2. On the final step of the migration process all views, stored procedures and triggers are extracted from MySQL database in form of SQL statements and source code. It can be done using these SQL-statements 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. Those statements and source codes have to be transformed according to PostgreSQL format and loaded to the destination database.

The brief guide above illustrates that database migration from MySQL to PostgreSQL is a complicated task. Manual approach to this process may cause losing data integrity due the human factor. Fortunately, there are dedicated software tools to migrate MySQL to PostgreSQL available in the market. One of such tools having all necessary features to handle large and complex projects is provided by Intelligent Converters, software company specializing in database migration and synchronization since 2001.

Similar Posts