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 are 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 the ODBC format.
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.
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.
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.
Then in the Create New Data Source window, click Next to continue configuring the driver.
Next, select a driver for which you want to set up a data source. Select Devart ODBC Driver for PostgreSQL and click Next. Finally, click Finish.
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 click Test Connection to test the connection to the PostgreSQL server. Click OK to save your settings.
After that, you will see the Select Data Source window where the path for data migration to PostgreSQL has appeared. Click OK to finish.
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.
Step 5. Choose columns to be exported
On the Data formats tab, you need to check columns you want to migrate to PostgreSQL.
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.
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.
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.
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.
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.
For teams and individuals who work with databases on different DBMSs, another solution could be preferred – dbForge Edge which comprises the features and powers of all separate dbForge Studios.
With its support for SQL Server, MySQL and MariaDB, Oracle, and PostgreSQL, Edge lets you perform all database tasks without switching between various dedicated tools. The Free Trial of dbForge Edge is available for 30 days.
