Home > Documentation > MySQL to PostgreSQL

Convert MySQL to PostgreSQL

[MySQL to Postgres Converter]  [How to Control Migration Results]  [Types Mapping]  [Converting Queries]

Why Migrate to PostgreSQL?

Both MySQL and PostgreSQL are popular open-source RDBMS enriched by wide range of administration and development tools. Both systems are ported on every popular OS and have large communities of experts. However, PostgreSQL provides multiple advantages that may be important for certain projects:

On the other hand, PostgreSQL is more complex than MySQL and it can be difficult for beginners. So, if you work on simple database project that is covered by MySQL capabilities and do not plan to scale it, there is no much sense to migrate it from MySQL to Postgres.

Migration Strategies

The steps described below are required to migrate database from MySQL to Postgres manually.

  1. All table definitions are extracted from the source database as DDL SQL statements. It can be done like this:

    phpMyAdmin - highlight the table, go to 'Export' tab, select 'Custom' option, set format to 'SQL' and make sure that radio-button 'Structure' is selected

    MySQL console client - use the statement

    mysqldump -d -h (host) -u (user) -p(password) (databasename) > (dumpifle)
    All patterns in round brackets must be replaced by actual values

  2. Translate those DDL statements according to PostgreSQL format and load into the destination database. The main challenge of this step is to properly convert column types from MySQL to Postgres. Read article Types Mapping for detailed information about types conversion.
     
  3. Data of every MySQL table is exported into an intermediate format such as CSV file. It can be done as follows:

    phpMyAdmin - highlight the table, go to 'Export' tab, select 'Custom' option, set format to 'CSV' and make sure that radio-button 'Data' is selected

    MySQL console client - use the statement

    SELECT * INTO OUTFILE ('table.csv')
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    LINES TERMINATED BY '\n' FROM (table)
    
    All patterns in round brackets must be replaced by actual values

  4. The data in CSV files has to be transformed according to PostgreSQL format (if it is necessary) and then loaded into the destination database.
     
  5. Finally views, stored procedures and triggers are extracted from MySQL database in form of SQL statements and source code. In both phpMyAdmin and MySQL console client it can be done using these 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
  6. The resulting statements and source codes are converted into PostgreSQL format and loaded into the target database. Note that this step requires deep knowledges in MySQL and PostgreSQL dialects of SQL and database development. This article explains how to convert views from MySQL to PostgreSQL.

The steps above are proving the database migration from MySQL to Postgres is quite complicated task. Doing it manually could lead to data loss or corruption due the human factor. It is the best practice to use appropriate software tools to automate the database conversion process. MySQL to Postgres by Intelligent Converters is one of such tools having all necessary features to handle large and complex migration projects.