Database Migration

Nov 2nd, 2022

MySQL to PostgreSQL

docker-compose.yml

version: '3.8'

services:
    database-server-mysql:
        image: mysql:8.0.30
        volumes:
            - ./data/database-data-mysql:/var/lib/mysql
            - ./conf/init-scripts/database-mysql:/docker-entrypoint-initdb.d
        restart: always
        environment:
            MYSQL_ROOT_PASSWORD: secret
            MYSQL_DATABASE: dbname
            MYSQL_USER: username
            MYSQL_PASSWORD: secret
        ports:
            - "3306:3306"

    database-gui-mysql:
        image: phpmyadmin/phpmyadmin:5.2.0
        depends_on:
            - database-server-mysql
        restart: always
        environment:
          PMA_HOST: database-server-mysql
          PMA_USER: root
          PMA_PASSWORD: secret
        ports:
          - "8081:80"

    database-server-postgresql:
        image: postgres
        volumes:
            - ./data/database-data-postgresql:/var/lib/postgresql/data
            - ./conf/init-scripts/database-postgresql:/docker-entrypoint-initdb.d
        restart: always
        environment:
            POSTGRES_DB: dbname
            POSTGRES_USER: username
            POSTGRES_PASSWORD: secret
        ports:
            - "5432:5432"
    
    database-gui-postgresql:
        image: dpage/pgadmin4
        volumes:
            - ./data/database-gui-data-postgresql:/var/lib/pgadmin
        depends_on:
            - database-server-postgresql
        restart: always
        environment:
          PGADMIN_DEFAULT_EMAIL: dev@aripd.com
          PGADMIN_DEFAULT_PASSWORD: secret
        ports:
            - "5050:80"

    debian:
        image: debian
        stdin_open: true # docker run -i
        tty: true        # docker run -t

volumes:
    data:

Run below commands inside the machine debian

apt-get update && apt-get upgrade
apt-get install pgloader sqlite3
pgloader --verbose --no-ssl-cert-verification mysql://username:secret@database-server-mysql:3306/dbname "postgresql://username:secret@database-server-postgresql:5432/dbname"

Errors

FATAL error: Failed to connect to pgsql at “database-server-postgresql” (port 5432) as user “username”: 10 fell through ECASE expression. Wanted one of (0 2 3 4 5 6 7 8).

The problem is that currently pgloader doesn’t support caching_sha2_password authentication plugin, which is default for MySQL 8, whereas older MySQL versions use mysql_native_password plugin. The corresponding issue is opened on Github.