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.
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.
- 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:
- 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-pgloaderdefined we need to have a
Dockerfileinside that folder like
postgres-with-pgloader/Dockerfilewith following content
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: databasefrom 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
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 -din root to let Docker do it's magic. After both containers are online run
./pgloader_migrate.shthen 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