MySQL to Postgres Migration using Docker with pgloader for Hasura
Tags: mysql, postgres, hasura, docker
Published: 2020-05-20 10:12:43

Follow me on [Twitter](https://twitter.com/@narayandreamer), 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.

![Hasura](https://dev-to-uploads.s3.amazonaws.com/i/hzckskf2erp2o2hbfthe.png)

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](https://twitter.com/@rajoshighosh) 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](https://twitter.com/GavinRayDev) from [Hasura](https://twitter.com/HasuraHQ) 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](https://www.docker.com/products/docker-desktop)
- 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

```shell
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

![mysql2pgsql-docker-folder](https://i.imgur.com/tooHQs7.png)

- 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 

![mysql2pgsql-docker-shell](https://i.imgur.com/7xZpf3h.png)

- 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](https://hasura.io)
[GavinRay](https://twitter.com/GavinRayDev)
[pgloader](https://pgloader.io/)


#Source Code

{% github https://github.com/narayandreamer/mysql2pgsql-docker  no-readme %}