pgloader Docker: migrating from Docker & localhost MySQL to localhost PostgreSQL.

Using the latest dimitri/pgloader Docker image build, I've migrated a Docker MySQL server 8.0.30 database, and a locally installed MySQL server 5.5 database to a locally installed PostgreSQL server 14.3 databases. I am discussing how I did it in this post.

Table of contents

• Environments
• Migrating Commands
• Some Migration Observervations
• Detail Discussions

Environments

1. Windows 10 Pro -- version 10.0.19045 Build 19045.
2. Windows “docker” CLI ( Docker Engine ) -- version 20.10.17, build de40ad0.
3. Windows Docker Desktop -- version 4.11.0.
4. mysql:8.0.30-debian -- this is a MySQL Docker Official Image, version 8.0.30. It is running on the Windows 10 machine.
5. MySQL 5.5, server installed on the Windows 10 machine. This is an unsupported version of MySQL.
6. PostgreSQL 14.3, server installed on the Windows 10 machine, version compiled by Visual C++ build 1914, 64-bit.

On mysql:8.0.30-debian Docker image build, I've also written two related posts:

1. Docker on Windows 10: running mysql:8.0.30-debian with a custom config file.
2. Docker on Windows 10: mysql:8.0.30-debian log files.

Migrating Commands

Below are two ( 2 ) dimitri/pgloader commands I used successfully to migrate the two ( 2 ) MySQL databases to PostgreSQL. Please note that:

-- PostgreSQL target database must exist before migrating.

❶ Migrate Docker mysql:8.0.30-debian's database ompdev1 to localhost PostgreSQL's test_ompdev1 database:

F:\>docker run --rm -it dimitri/pgloader:latest pgloader mysql://root:secret-password@172.17.0.2/ompdev1 postgresql://postgres:secret-password@host.docker.internal/test_ompdev1

❷ Migrate localhost MySQL 5.5's employees database to localhost PostgreSQL's employees database:

F:\>docker run --rm -it dimitri/pgloader:latest pgloader mysql://root:secret-password@host.docker.internal/employees postgresql://postgres:secret-password@host.docker.internal/employees

Some Migration Observervations

Based on the two ( 2 ) migrations' experimentations, I've observed the followings:

1. Stored procedures and stored functions are not migrated.
2. Triggers are not migrated.
3. Auto increment integer primary keys migrated as integer primary keys; they lose the auto increment property, I have to fix these manually.

Detail Discussions

host.docker.internal and 172.17.0.2 hosts

Recall the two ( 2 ) commands used in the Migrating Commands section:

F:\>docker run --rm -it dimitri/pgloader:latest pgloader mysql://root:secret-password@172.17.0.2/ompdev1 postgresql://postgres:secret-password@host.docker.internal/test_ompdev1
F:\>docker run --rm -it dimitri/pgloader:latest pgloader mysql://root:secret-password@host.docker.internal/employees postgresql://postgres:secret-password@host.docker.internal/employees

When I started research MySQL to PostgreSQL migration tool, it seemed to me that dimitri/pgloader is the tool to use: it is not yet available as a stand-alone version for Windows, so the Docker image version is the next best thing. From the official page, and discussions on the net, this Docker image would just work out of the box. But I was not able to get it to work on the first go: I'd forgotten all about Docker networking!

Please note the host addresses, host.docker.internal and 172.17.0.2 in the above commands -- this is where I failed in the first place.

How to connect to docker host from container on Windows 10 (Docker for Windows) cites this official Docker document page Explore networking features:

The host has a changing IP address (or none if you have no network access). We recommend that you connect to the special DNS name host.docker.internal which resolves to the internal IP address used by the host. This is for development purpose and does not work in a production environment outside of Docker Desktop.

So this means dimitri/pgloader Docker container sees the host address for MySQL 5.5 and PostgreSQL servers installed on the Windows 10 as host.docker.internal.

For Docker mysql:8.0.30-debian, I need to use the Docker image container IP address. Recall from this post Docker on Windows 10: running mysql:8.0.30-debian with a custom config file, I run it with no network option:

E:\>docker run -d -it --rm --name mysql-docker --mount type=bind,source=//e/mysql-config,target=/etc/mysql/conf.d -p 3306:3306 -e MYSQL_ROOT_PASSWORD=pcb.2176310315865259 mysql:8.0.30-debian

That means its container uses the default bridge network. To list networks:

E:\>docker network ls

NETWORK ID     NAME            DRIVER    SCOPE
4fdfeff4bb4b   bridge          bridge    local
791ebddb8e24   host            host      local
cd3831cd0536   none            null      local

To see which containers are in the bridge network:

E:\>docker inspect bridge

I'm extracting out the relevant portion related to container mysql-docker:

...
        },
        "ConfigOnly": false,
        "Containers": {
            "02e57f7b22b358a6abaac1848ed0857b2ea9a9c63bc191b40061c15d770cdc2d": {
                "Name": "mysql-docker",
                "EndpointID": "e3cb0ed472f14da240416d828ebd368c4e734ca18f19cc779928106200ca8768",
                "MacAddress": "02:42:ac:11:00:02",
                "IPv4Address": "172.17.0.2/16",
                "IPv6Address": ""
            }
        },
        "Options": {
...

IPv4Address is the one we are interested in, which is 172.17.0.2.

The employees database

The employees database is a MySQL test data database released by Oracle Corporation. Downloadable from https://github.com/datacharmer/test_db. It is a simple database with only a few tables, easy to setup. The main tables have several hundreds thousand records, which is very good for testing purposes.

Saving dimitri/pgloader image to disk

I like to store Docker images I use to disk. Just in case I lost them, I can just reload, without having to pull them again.

D:\>docker images

REPOSITORY         TAG               IMAGE ID       CREATED        SIZE
...
dimitri/pgloader   latest            d548fdd654a5   2 months ago   194MB
...

D:\>docker save dimitri/pgloader > E:\docker-images\dimitri_pgloader_01.tar
D:\>docker save d548fdd654a5 --output E:\docker-images\dimitri_pgloader_02.tar

I've also done a post on this subject: Python: Docker image build — save to and load from *.tar files.

✿✿✿

I have done this for learning purposes. I have not applied this in production. I'm sure there are many more issues which I'm not aware of. During my entire working life so far, I have only done one production migration: we don't have that many opportunities, this is an expensive and often not a profitable exercise for any organisation. I do hope you find this useful. Thank you for reading and stay safe as always.