MySQL to Postgres Migration using Docker with pgloader for Hasura

Narayan — Wed, 20 May 2020 10:12:43 +0000

Follow me on Twitter, happy to help or take suggestions from you /Narayan

This is my first article so please ignore my mistakes.

TLDR; This article is about converting a MySQL database to Postgres using pgloader with Docker. If you want code directly then skip to end.

I recently made a decision to use #Hasura for backend on one of my products. Reason REST sucks. Although REST is still powering majority of internet but once you taste the blood, there's no going back. Same thing happened with SOAP when REST came. Problem with REST is development time and updating. #GraphQL was the answer for me. But since #Hasura only supports Postgres as of now I needed to migrate my MySQL to Postgres.

I asked Hasura team about MySQL Support and got a reply that its under development and will come out soon, possibly 2 months from now. The thing about open source software is that we want it to support our ecosystem, so i was literally asking every few days to Rajoshi about MySQL support landing. But any new thing takes time. They have to properly test everything and then initial beta comes out and then final release. We become greedy and I'm not gonna lie I too became greedy, the sooner it comes out the better for obvious reasons.

Setup

Then 1 day I thought of migrating my existing MySQL setup to Postgres. Since the product is not in production, now is a good time to migrate. I started reading about it and came to know about this tool called pgloader. I was happy i got the tool. Below is my experience with different local setups for using pgloader

❌ Standalone MySQL and PostgreSQL and pgloader: In this setup MySQL, PostgreSQL i installed from their official site on MacOSX and pgloader with homebrew brew install pgloader. Didn't work out for me
❌ Docker MySQL and PostgreSQL and Standalone pgloader: In this setup MySQL, PostgreSQL were installed via Docker and pgloader with homebrew brew install pgloader. Didn't work out for me
✅ Docker MySQL and PostgreSQL and pgloader: In this setup MySQL, PostgreSQL and pgloader were all installed via Docker and it worked. Reaching this stage i found out that problem was pgloader installed via Homebrew. When i ran pgloader via Docker then Standalone MySQL and Standalone PostgreSQL also worked.

Learning : Always use Docker for such tasks (or generally too) as we can isolate environment and dependencies. A Big Shoutout to GavinRay from Hasura who helped me reach 3rd setup and prevented me from going bald with all my hair i was pulling out of frustration with 1st and 2nd setup.

Let's get started

Install Docker
Take MySQL dump from your MySQL Client like phpmyadmin, Sequel Pro etc in .sql format
Now we need a docker-compose.yaml for our services. We require 3 services (MySQL, Postgres and pgloader) ```yaml

version: '3.6'
services:
postgres:
build: postgres-with-pgloader
container_name: postgres-db
ports:
- '5431:5432'
restart: always
volumes:
- db_data:/var/lib/postgresql/data
environment:
POSTGRES_PASSWORD: root

mysql:
image: mysql:5.7
container_name: mysql-db
ports:
- '3307:3306'
volumes:
- /var/lib/mysql
- ./migrations:/docker-entrypoint-initdb.d
environment:
MYSQL_DATABASE: database
MYSQL_USER: user
MYSQL_PASSWORD: password
MYSQL_ROOT_PASSWORD: root_password

volumes:
db_data:

- As seen in docker-compose.yaml file, postgres is 1 service which has postgres running with pgloader. Since it has build parameter refers to `postgres-with-pgloader` defined we need to have a `Dockerfile` inside that folder like `postgres-with-pgloader/Dockerfile` with following content
```yaml


FROM postgres:latest
RUN apt-get update
RUN apt-get install -y wget sudo pgloader

MySQL is another service where we don't require build parameter but we will directly build from image. But since we will use dump taken in 2nd step we need to initialize MySQL service with that dump via migrations. So we have specified - ./migrations:/docker-entrypoint-initdb.d. This will take any sql in migrations folder and make db MYSQL_DATABASE: database from that dump. Pretty cool ha! 🤓
If you see ports for Postgres and MySQL have been mapped externally to 5431 and 3307 respectively. This is to avoid clash with any existing service running standalone of these Databases. It's better to be safe.
Now in root make a shell script pgloader_migrate.sh like below



docker-compose exec postgres pgloader \
mysql://user:password@mysql:3306/database \
postgresql://postgres:root@localhost:5432/postgres

Since pgloader is running inside and that too inside postgres service, it can reference postgres as localhost, but it has to connect to MySQL via mysql service connector. Again ports will remain internal of docker as pgloader is running internally.

Folder structure should look like below

Now run docker-compose up -d in root to let Docker do it's magic. After both containers are online run ./pgloader_migrate.sh then you should get a screen like this

You can connect to postgres with pgAdmin, phppgadmin and see if all is well.

Do give a like ♥️ if this article helped you

Thanks

Hasura
GavinRay
pgloader

DEV Community: Narayan

MySQL to Postgres Migration using Docker with pgloader for Hasura

Setup

Let's get started

Thanks

Source Code

narayanpromax / mysql2pgsql-docker

MySQL to PostgreSQL with pgloader and Docker