Migrating MySQL databases to a Managed Database for PostgreSQL using pgLoader

Reviewed on 10 May 2021 • Published on 19 November 2019

pgloader
postgresql
mysql
migration

pgLoader is an open-source database migration tool developed to simplify the process of migrating an existing database from one database engine to PostgreSQL.
The tool supports migrations from several file types and database engines like MySQL, MS SQL and SQLite.

In this tutorial you will learn how to migrate from an existing remote MySQL database to a Managed Database for PostgreSQL using an intermediate Development Instance running Ubuntu Bionic Beaver.

Requirements:

You have an account and are logged into the Scaleway Console
You have configured your SSH Key
You have a Database Instance
You have a Compute Instance running on Ubuntu Bionic Beaver (18.04).
You have sudo privileges or access to the root user.
You have an existing MySQL database.

Setting up pgLoader

Connect to your Virtual Instance using SSH.
Update the apt packet cache and upgrade the software already installed on the instance:
```
apt update && apt upgrade -y
```
Install pgLoader using the apt packet manager:
```
apt install pgloader
```
To check if the installation of pgLoader was successfully, run the following command:
```
pgloader --version
```
An output like the following is displayed:
```
pgloader version "3.4.1"
compiled with SBCL 1.3.3.debian
```

pgLoader is installed now, and you can begin the migration of your Database Instance.

Migrating your database

Note:

To complete the following steps, you need to have access to your current MySQL database and have created the target database on your Database Instance.

Database migration with pgLoader is designed to be very simple and one line of commands is all that is required to migrate a MySQL database to a PostgreSQL database:

pgloader mysql://mysqluser:password@<mysql-server>:<mysql-port>/<source-database> postgresql://<pgsql-role>:password@<pgsql_server>:<postgresql-port>/<target-database>

Run the command above to automatically migrate your current database to the managed Database Instance. Depending on the size of your database, the migration may take some time. Once it has completed, a summary is displayed:


2019-11-19T16:19:51.342000Z LOG report summary reset
                     table name       read   imported     errors      total time
-------------------------------  ---------  ---------  ---------  --------------
                fetch meta data         51         51          0          0.165s
                 Create Schemas          0          0          0          0.016s
               Create SQL Types          0          0          0          0.017s
                  Create tables         24         24          0          0.525s
                 Set Table OIDs         12         12          0          0.021s
-------------------------------  ---------  ---------  ---------  --------------
                    commentmeta          0          0          0          0.037s
                       comments          1          1          0          0.080s
                          links          0          0          0          0.033s
                       postmeta          1          1          0          0.123s
                       termmeta          0          0          0          0.119s
             term_relationships          1          1          0          0.192s
                        options        119        119          0          0.117s
                       usermeta         18         18          0          0.235s
                          posts          3          3          0          0.167s
                          terms          1          1          0          0.254s
                  term_taxonomy          1          1          0          0.272s
                          users          1          1          0          0.273s
-------------------------------  ---------  ---------  ---------  --------------
        COPY Threads Completion          4          4          0          0.328s
                 Create Indexes         39         39          0          1.656s
         Index Build Completion         39         39          0          0.466s
                Reset Sequences         11         11          0          0.085s
                   Primary Keys         12         12          0          0.132s
            Create Foreign Keys          0          0          0          0.000s
                Create Triggers          0          0          0          0.006s
               Install Comments          0          0          0          0.000s
-------------------------------  ---------  ---------  ---------  --------------
              Total import time        146        146          0          2.351s

Your data is migrated now, and update the database connection information in your software to communicate with your new managed PostgreSQL database.