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.