Scaleway Documentationtutorials
migrate mysql databases postgresql pgloader

Jump toUpdate content

Migrating MySQL databases to a Managed Database for PostgreSQL using pgLoader

Reviewed on 10 May 2021Published 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:

Setting up pgLoader

  1. Connect to your Virtual Instance using SSH.

  2. Update the apt packet cache and upgrade the software already installed on the instance:

    apt update && apt upgrade -y
  3. Install pgLoader using the apt packet manager:

    apt install pgloader
  4. 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.