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 dbMYSQL_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
Top comments (9)
You save my life
before above command do which database adapter need.
bro i am stuck around 20 days, can you guide me step wise what you done so it work for you.
I can help you my friend
can you tell how do you done! i have followed blog and we have done same like blog but not work for me
docker-entrypoint-initdb.d what mysql dump file name or something else?
@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)
No me funciona sigue funcionando ?
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.
Thanks.