Database Migration
Migrating a MySQL database to PostgreSQL with pgloader, step by step.
1 min read
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:15.1 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:6.18 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:bookworm stdin_open: true # docker run -i tty: true # docker run -t
volumes: data:Run docker compose
docker compose up -dThen open debian machine in terminal and run below commands inside the machine debian
apt-get update && apt-get upgradeapt-get install pgloader sqlite3pgloader --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.
ERROR: type “longtext” does not exist
Postgres doesn’t have a LONGTEXT datatype, so keep using TEXT.