Migrate from MySQL to PostgreSQL

From powering e-commerce to financial services, relational database management systems (or RDBMS in short) have been around for several decades. MySQL and PostgreSQL, two well-known open-source database options, rank among the top 5 DBMS systems in the DB-Engines rankings. These databases have been tested to their limits over several years and are trusted by many organizations, including Uber, Netflix, and Facebook, for their production deployments.

If you’re wondering about the use-cases of these databases — typically, MySQL has been commonly used as a small web database, powering millions of websites. On the other hand, PostgreSQL has found its usefulness in many use-cases requiring more scale and having complex querying needs. If migration from MySQL to PostgreSQL is what you are after, you will find this blog helpful. We will discuss some of the critical challenges faced when migrating from MySQL to PostgreSQL and how you can make your life easy by effortlessly sync data between MySQL and PostgreSQL using Airbyte.

Pre-requisites  

1. You’ll need MySQL and PostgreSQL. If you aren’t using a pre-existing setup, check the installation steps for MySQL and PostgreSQL for your particular operating system. In this case, we are using Ubuntu 20.04 LTS. 
   

2. You’ll need data to be loaded into MySQL. We will use the Classic Models Inc. example database developed as part of the Eclipse BIRT (Business Intelligence Reporting Tools) project. 
   

3. You’ll need to get Airbyte to do the migration for you. To deploy Airbyte, follow the simple instructions in our documentation here. 
   

Well, the answer to this boils down to the needs of your application. Here are a few key reasons why you might want to migrate to PostgreSQL:

1. You need an easy way to model complex data: The object-oriented design of PostgreSQL, combined with support for various data types, and features like table inheritance and function overloading, makes modeling of complex data easy.
   
   
2. Your application has advanced query needs, including analytics: If your app has complex querying requirements including joins, subqueries, window, and statistical functions, filtered results, and group-bys, then PostgreSQL might be a better option compared to MySQL.
   
   
3. Your app has strong consistency requirements ACID:  In many use-cases, your app might have ACID-consistent data requirements from the start — ensuring that data modifications are accurate, reliable, and resistant to change in the event of a failure. PostgreSQL is an ACID-compliant database, while MySQL is only compliant when it uses the InnoDB storage engine.
   
   

Sometimes the reasons to migrate are beyond pure technical stuff. It could be that your fellow developers just love PostgreSQL because they are most familiar with it and want to move. 

What migration challenges? 

Now, migrating from MySQL to PostgreSQL is not a walk in the park, and there are several factors you might need to consider before you make the switch. For example, just imagine if your source data in MySQL has a complex schema like the one below?  

In many cases, mapping these relations from MySQL (source) to PostgreSQL (destination) is a manual task and can be pretty time-consuming. Your source data might also use data types that do not have equivalent mappings in the destination, or you might just not know which destination data types to use for the data at hand.

If you don’t have pre-existing data, Create a database called classicmodels and load data into MySQL. To create the database and load data, you can follow the steps here.

Connect to the MySQL database and list databases to check for your database, or in our case, classicmodels.

Open Airbyte by navigating to http://localhost:8000 in your web browser. Opening the URL should take you to the page below to set up the source database (MySQL in this case).

Provide a name for the source. In this case, we have called it ‘MySQL_Source’. Set the source type as MySQL, and type the name of the server where MySQL is installed. In this case, since we have installed MySQL on the local machine, we are using ‘localhost’. If the port has not been changed, the default port (3306) can be used. The name of the database should be ‘classicmodels’ if you are following along with our guide, or the specific database you are interested in migrating. The username and password should be as per the credentials setup during MySQL installation. 

If you’ve installed MySQL for the first time, and the timezone of your MySQL server does not match your correct regional timezone, you might run into errors such as the one below: 

To work around this issue, ensure that your MySQL database’s globaltime_zone is set correctly using the SET GLOBAL time_zone command.

‍

Hit the “Set up source” button, and if everything goes well, you should see a message telling you that all your connection tests have passed!

Next, you will be taken to the “Destination Setup” page. Fill out the details as follows.

By default, PostgreSQL sets no password for the Postgres user. Thus, it is up to us to set up a password for the Postgres user that we will be using to establish a connection, or else we will face an error like the one below.

To set a password for the postgres user account, you can run the following command - 

postgres=# ALTER USER postgres password 'Postgres_pa$$word1';

Hit the “Setup the destination” button, and if everything goes well, you'll get the green checkmark.

Once the destination is set up, let’s go and set up the connection. In this step, you will notice that Airbyte has already detected the tables and schemas to migrate. By default, all tables are selected for migration. If you only want a subset of the data to be migrated, you can un-select the tables you wish to skip. In this step, you can also specify details such as sync frequency between source and destination. There are several interval options as shown in the figure — from 5 minutes to every hour. The granularity of the sync operation can also be set by selecting the correct sync mode for your use case. Read sync mode in the Airbyte docs for more details.

After we have specified all our customizations, we can click on “Set up Connection” to start the data migration task and move data from MySQL to PostgreSQL. 

At a glance, you’ll be able to see the last sync status of your connection and how frequently the sync is taking place. That’s how easy it is to move your data from MySQL to PostgreSQL using Airbyte. You can now go into PostgreSQL and query your data tables to ensure that your data has been synced.

Migration should be a planned activity. As with any database migration operation, you should not run a migration during a production peak. Migration latency also depends on several factors, including the size of data to be moved, geographic location of the source and destination, and other parameters. Testing is essential before production deployments.‍

Here’s what we’ve accomplishedduring this recipe:

• Configure a MySQL Airbyte source
• Configure a Postgres Airbyte destination
• Create a connection that will automatically migrate data from MySQL to Postgres
  

We know that development and operations teams working on fast-moving projects with tight timelines need quick answers to their questions from developers who are actively developing Airbyte. They also want to share their learnings with experienced community members who have “been there and done that.”

Join the conversation at Airbyte’s community Slack Channel to share your ideas with over 1000 data engineers and help make everyone’s project a success. 

Good luck, and we wish you all the best using Airbyte!