DEV Community

Cover image for MySQL to Postgres Migration using Docker with pgloader for Hasura
Narayan
Narayan

Posted on

MySQL to Postgres Migration using Docker with pgloader for Hasura

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.

Hasura

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)
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:
Enter fullscreen mode Exit fullscreen mode
  • 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
FROM postgres:latest
RUN apt-get update
RUN apt-get install -y wget sudo pgloader
Enter fullscreen mode Exit fullscreen mode
  • 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
Enter fullscreen mode Exit fullscreen mode

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

  • 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

  • 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

Source Code

GitHub logo narayanpromax / mysql2pgsql-docker

MySQL to PostgreSQL with pgloader and Docker

Top comments (9)

Collapse
 
snettah profile image
Safi Nettah

You save my life

Collapse
 
prajapatisantu profile image
prajapatisantu

before above command do which database adapter need.

Collapse
 
prajapatisantu profile image
prajapatisantu

bro i am stuck around 20 days, can you guide me step wise what you done so it work for you.

Collapse
 
snettah profile image
Safi Nettah

I can help you my friend

Thread Thread
 
prajapatisantu profile image
prajapatisantu

can you tell how do you done! i have followed blog and we have done same like blog but not work for me

Collapse
 
prajapatisantu profile image
prajapatisantu

docker-entrypoint-initdb.d what mysql dump file name or something else?

Collapse
 
abhijeet_vhotkar profile image
Abhijeet Vhotkar

@prajapatisantu You do not need to do anything extra.
If you are in windows, then run the following command after both the containers of databases are up (Make sure run Powershell as administrator)

docker-compose exec postgres pgloader mysql://user:password@mysql:3306/database postgresql://postgres:root@localhost:5432/postgres
Enter fullscreen mode Exit fullscreen mode
Collapse
 
jonas0819 profile image
Jonas0819

No me funciona sigue funcionando ?

Collapse
 
shakib448 profile image
Muktadir Ahamed • Edited

Thanks boss it's working for those using Mac if you face any Linux-based issues. You can try this with your docker-compose file.

platform: Linux/amd64
Enter fullscreen mode Exit fullscreen mode

Thanks.