Migrating MySQL to PostgreSQL Using Docker and pgLoader

Introduction

Migrating from MySQL to PostgreSQL can be straightforward with the right tools and setup. This guide provides step-by-step instructions to help you use Docker, pgLoader, and Adminer for a smooth database migration process. We’ll set up the environment, import your MySQL dump using a web UI, and finally convert and manage your databases.

Prerequisites

Before starting the migration, ensure that you have the following:

• Docker: Installed on your machine. If not installed, follow the instructions from the Docker website.
• pgLoader: Installed on your system (see Step 5 for installation instructions).
• MySQL Dump File: A dump file (.sql file) of your MySQL database ready for import.

Step 1: Setting Up Docker Compose

Docker Compose is a tool for defining and running multi-container Docker applications. It allows you to use a YAML file to configure your application’s services and start them with a single command.

Create a docker-compose.yml file to define the MySQL, PostgreSQL, and Adminer services:

version: '3'
 
services:
  mysql:
    image: mysql:8.0.19
    command: mysqld --default-authentication-plugin=mysql_native_password
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: admin
      MYSQL_DATABASE: admin
      MYSQL_USER: root
      MYSQL_PASSWORD: admin
    volumes:
      - ./mysql_data:/var/lib/mysql
    ports:
      - "3306:3306"
 
  postgres:
    image: postgres:latest
    restart: always
    environment:
      POSTGRES_USER: admin
      POSTGRES_PASSWORD: admin
      POSTGRES_DB: admin
    volumes:
      - ./postgres_data:/var/lib/postgresql/data
    ports:
      - "5432:5432"
      
  adminer:
    image: adminer
    ports:
      - '8080:8080'

Explanation:

• MySQL Service: Uses the mysql:8.0.19 image and sets up MySQL with the specified root password, database, and user.
• PostgreSQL Service: Uses the latest PostgreSQL image and sets up the PostgreSQL database with the specified user and password.
• Adminer Service: Provides a web-based interface to manage MySQL and PostgreSQL databases.

Step 2: Running the Docker Containers

To start all the services defined in your docker-compose.yml file, navigate to the directory containing the file and run:

docker-compose up -d

This command will start the MySQL, PostgreSQL, and Adminer containers in detached mode (running in the background). You can verify that the containers are up and running by using:

docker ps

This command lists all running Docker containers. You should see entries for MySQL, PostgreSQL, and Adminer.

Step 3: Importing Your MySQL Dump Using Adminer

Instead of using the command line, you can easily import your MySQL dump using the Adminer web interface:

Access Adminer by navigating to http://localhost:8080 in your web browser.

Login to Adminer using the following details:

• System: MySQL
• Server: mysql
• Username: root
• Password: admin
• Database: admin

Import the MySQL dump:

• Once logged in, go to the “Import” tab.
• Choose the .sql file from your local machine.
• Click “Execute” to import the dump into the MySQL database.

This will populate the MySQL database with the data from your dump file.

Step 4: Installing pgLoader

pgLoader is an open-source tool that automates the conversion of databases from MySQL to PostgreSQL. It’s powerful because it handles schema migration, data type conversion, and data migration in one go.

Installing pgLoader:

• Ubuntu/Debian:

sudo apt-get install pgloader

• macOS:

brew install pgloader

For other operating systems or more detailed installation instructions, refer to the pgLoader documentation.

Step 5: Migrating the MySQL Database to PostgreSQL

With pgLoader installed and your MySQL data in place, you can now migrate your MySQL database to PostgreSQL. Execute the following command:

pgloader mysql://root:admin@localhost:3306/admin postgresql://admin:admin@localhost:5431/admin

Command Breakdown:

MySQL Source: mysql://root:admin@localhost:3306/admin

• root is the MySQL user.
• admin is the password.
• localhost:3306 is the host and port for MySQL.
• admin is the database name.

PostgreSQL Target: postgresql://admin:admin@localhost:5431/admin

• admin is the PostgreSQL user.
• admin is the password.
• localhost:5431 is the host and port for PostgreSQL.
• admin is the database name.

This command will convert the schema and migrate all data from the MySQL database to PostgreSQL.

Step 6: Accessing the Databases via Adminer

Adminer is a simple and lightweight tool for managing databases. It supports MySQL, PostgreSQL, and several other databases.

After the migration, you can access both MySQL and PostgreSQL databases using Adminer by navigating to http://localhost:8080 in your web browser.

Login Details:

MySQL:

• System: MySQL
• Server: mysql
• Username: root
• Password: admin
• Database: admin

PostgreSQL:

• System: PostgreSQL
• Server: postgres
• Username: admin
• Password: admin
• Database: admin

Adminer provides a web interface where you can easily verify that all data was successfully migrated and manage your databases.

Step 7: Localhost Configuration and Verification

For development purposes, you’ll often need to connect to your databases directly from your local machine. Here’s how to access them:

• MySQL: Access via port 3306 (as defined in Docker).
• PostgreSQL: Access via port 5431 (as defined in Docker).

You can connect to these databases using any database management tool such as pgAdmin, DBeaver, or directly from your application.

Conclusion

Migrating a MySQL database to PostgreSQL can be done efficiently using Docker, pgLoader, and Adminer. This setup allows you to manage and interact with your databases in a development environment, ensuring a smooth transition between MySQL and PostgreSQL. With these tools, the migration process is streamlined, making it accessible even for those new to database management.