How to convert MySQL data to PostgreSQL

June 25th, 2020

Transferring data between two different servers is not an easy task as it requires a great deal of effort and accuracy, especially when migrating from one database management system to another. In this article, we provide a visual walkthrough of MySQL data migration to PostgreSQL Server.

In this tutorial, we will move data from the address table in the sakila database that exists on MySQL server to the postgres database on PostgreSQL server. To migrate MySQL to PostgreSQL, we will need dbForge Studio for MySQL and ODBC Driver for PostgreSQL.

It should be pointed out that migration tasks to PostgreSQL are commonly performed with the help of pgloader which is an open-source database migration tool designed to move data from other RDBMSs and files to PostgreSQL. To succeed with pgloader, you will have to suffer a bit creating tweaky configurations which is not easy especially for beginners. It is also worth mentioning that pgloader is a command-line tool and thus doesn’t have a visual interface.

Data Export functionality of dbForge Studio for MySQL

dbForge Studio for MySQL and dbForge Fusion for MySQL products come with advanced Data Export Wizard. The tool is highly customizable and enables exporting data to the most popular formats quickly and easily. The undoubted advantage of it is a comprehensive GUI allowing non-professional users to use it effectively. Another benefit the Data Export tool can boast is the ability to export the result of a query which makes the process of data migration more flexible.

Note: If you need to transfer data between databases on one MySQL server or between different MySQL servers, a reliable Copy Database functionality built into dbForge Studio for MySQL would be of great help as it provides the fastest and easiest way to copy database structure and data from source to target within one DBMS.

Data migration procedure

Below are the steps you need to follow to migrate MySQL to PostgreSQL.  Each step is illustrated and explained in detail.

Please note, that dbForge Studio for MySQL allows transferring between servers not only table data but also query results. To move a query result, from MySQL to PostgreSQL, just execute the query, right-click its result, select the Export Data command from the context menu that appears, and follow the procedure below.

Step 1. Select ODBC export format

In the Database Explorer, right-click the table you want to migrate and select the Export Data command from the context menu. Next, in the Data Export Wizard that opens, select ODBC format.

Choose the table you want to migrate and select the Export Data command

Step 2. Select a source table for export

With the Data Export wizard, you can select a connection, a database, and a table and/or view to be moved.

Select a connection, a database, and a table and/or view to be moved

Step 3. Set options for ODBC data provider

In this step, the Data Export Wizard will offer you to set ODBC driver options.
We recommend you to test the connection specified before running an Export job.

ODBC driver options - Data Export Wizard

How to configure the ODBC driver

To convert MySQL data to PostgreSQL, you will need ODBC Driver for PostgreSQL from Devart. You need to configure its options before proceeding with the data export task.
That can be done right from the Data Export Wizard. First, click the Build button next to the Use a connection string field.

Then, in the Select Data Source window that opens, go to the Machine Data Source tab and press the New button.

MySQL to PostgreSQL migration - ODBC driver configuration

Then in the Create New Data Source window, press the Next button to continue configuring the driver.

Transfer MySQL to PostgreSQL - create new cource

Next, select a driver for which you want to set up a data source. Select Devart ODBC Driver for PostgreSQL and press the Next button. Finally, press Finish.

MySQL migrate to PostgreSQL - select driver from a list

After that driver configuration window opens where you need to specify:

  • Data source name
  • Description (optionally)
  • Server name and port
  • User ID and password
  • Database and schema.

    You can press the Test Connection button to test the connection to the PostgreSQL server. Press OK to save your settings. 
Convert MySQL data to PostgreSQL data- provider configuration

After that, you will see the Select Data Source window where the path for data migration to PostgreSQL has appeared. Press OK to finish.

Copy data from MySQL to PostgreSQL - Driver created

Step 4. Select a destination table for your export task

On the Table tab of the wizard, you need to choose a target table for export. You can select it from a list of existing tables in a database you specified when configuring the driver as well as create a new table and provide its name.

MySQL export to PostgreSQL - selecting target table

Step 5. Choose columns to be exported

On the Data formats tab, you will be offered to check columns you want to migrate to PostgreSQL.

Moving from MySQL to PostgreSQL - selecting columns

Step 6. Choose rows to be exported

You don’t need to migrate the whole table. On the Exported rows tab, you can select to:

  • export all rows
  • export the selected rows only
  • specify and export a range of rows.
    The selective export option significantly mitigates data migration and notably saves time.
Convert MySQL data to PostgreSQL - selecting rows

Step 7. Configure errors processing behavior

On the Errors handling tab of the Data Export wizard, you can configure the application behavior when an error occurs. The following options are available: prompt a user for an action, ignore all errors, and abort at the first error.
Also, in case you need to create a log file, you can set a path to it on this tab, too.

MySQL migration to PostgreSQL - errors behavior

Step 8. Finish and save the template

dbForge Studio for MySQL allows saving templates for repeating export scenarios. That eliminates the need to waste time setting up data export again and again, just use templates saved earlier to apply configurations to any number of migration jobs.

Templates for repeating export scenarios in dbForge Studio for  MySQL

Step 9. Check and enjoy the result

As a result of our MySQL to PostgreSQL migration efforts, the address table and the result of the query have appeared on the PostgreSQL server. 

To check the result, we will run dbForge Studio for PostgreSQL.

Mysql to postgresql migration results

Conclusion

dbForge Studio for MySQL is an advanced IDE possessing powerful data transfer functionality. A well-designed and intuitive interface of the tool makes it possible to easily move MySQL data between different databases, servers, and even DBMSs. It would definitely be of great assistance to teams in their data migration routines.

Leave a Comment