September 10, 2024

Tech

Database migration from MySQL to PostgreSQL

by Enna Robbinson0596

MySQL and PostgreSQL are both leading RDBMS that are open source.  Both have rich set of tools for administering database and API for easy integration by programmers. However, PostgreSQL has an edge over MySQL due to some factors listed below:

  • It is in full compliance with ANSI SQL standard
  • Multiple Indexing model is supported
  • Asynchronous data commit feature compliant
  • Synchronous and Asynchronous replication is supported

Due to the aforementioned advantages, organizations do consider migrating their database from MySQL to PostgreSQL.

Database migration could be a bit complex. The steps described below are required to migrate data across database types.

  1. Table definition from the source database is extracted as DDL SQL statements. It can be done like this:
    1. phpMyAdmin – highlight the table, go to ‘Export’ tab, select ‘Custom’ option, set format to ‘SQL’ and make sure that radio-button ‘Structure’ is selected
    2. MySQL console client –use the statement (it is required to replace all patterns in round brackets by actual values)

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

databasename) > (dumpifle)

  1. Convert the statements into the format of the destination database and load into PostgreSQL, the main thing to check on this step is appropriate types conversion from MySQL to Postgres.
  2. Data from MySQL database is exported into an intermediate format such as CSV file. It can be done as follows:
    1. phpMyAdmin – highlight the table, go to ‘Export’ tab, select ‘Custom’ option, set format to ‘CSV’ and make sure that radio-button ‘Data’ is selected
    2. MySQL console client – use the statement (it is required to replace all patterns in round brackets by actual values)

SELECT * INTO OUTFILE (‘table.csv’)

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

LINES TERMINATED BY’\n’FROM(table)

  1. The data is transformed according to the destination format and consequently loaded into the Postgres database.
  2. Views, stored procedures and triggers are extracted from the MySQL database as SQL statements and source code.This is statement for listing all views belong to the database:

SELECT table_name, view_definition FROM information_schema.views

WHERE table_schema=’your database name’;

The following statement can extract source code of stored procedures in the specified database:

SHOW PROCEDURE STATUS WHERE Db =‘database name’

Finally, this is how triggers can be extracted:

SHOW TRIGGERS;

  1. The extracted statements and source codes are converted into the destination format and loaded into the PostgreSQL database. This step requires deep knowledges in both MySQL and PostgreSQL dialects of SQL and database programming.

The process of migrating database from MySQL to Postgres can be a harrowing task when done manually. Besides, the process is prone to error due to human factor which could lead to data loss or corruption of data. It is the best practice to use special software to automate the database conversion process.

MySQL to PostgreSQL designed by Intelligent Converters is one of such tools that can automate database conversion. Since 2001, Intelligent Converters has designed cutting edge programs specialized in database migration and synchronization.  MySQL to PostgreSQL is of exceptional high performance approximately 10000 records per second; this is an attribution of the software to reading and writing data directly without using proxy components or middleware libraries.  Furthermore, all versions of MySQL and PostgreSQL are supported including Cloud hosted solutions.  The best part of this software is that it goes way beyond simply migrating data to PostgreSQL database, it can also merge of synchronize with existing PostgreSQL database.

For those who require selective database migration, the software has feature for selective data filter through SELECT queries.  This feature empowers the database administrator to select specific columns and records or even transform the source data before converting to PostgreSQL format. Enumerated below are example scenarios where this feature can be used.

  • Filter records: SELECT * FROM TestTable WHERE IdColumn< 300
  • Select and rename individual columns: SELECT col1 AS FirsttName, col2 as Mobile FROM TestTable
  • Skip NULL values: SELECT * FROM TestTable WHERE Details IS NOT NULL

There are times when it is required to change the column type in the resulting database.  In MySQL to PostgreSQL, a feature called custom column mapping comes handy in accomplishing this task. A dialogue window opens which allow changing to Name, Type, Default value and NULL attribute for any column within the table or exclude selected columns from the conversion process.

In a scenario where the target PostgreSQL server declines remote connection, the program has an option to exclude the data into an SQL script.  In this method, the source database is exported as an SQL file containing all the statements required to create tables long side all the stored indexes and also to fill the tables with the required data. Thereafter, the database administrator can simply import the SQL script to PostgreSQL server with tools such as Psql of phpPgAdmin.