Failed to migrate from Mysql 8 to postgres using pgloader

Question

I'm trying to migrate from MySQL to PostgreSQL database.

Is there any way to migrate from MySQL to PostgreSQL without modifying the my.cnf file?

pgloader mysql://root:[email protected]/mysql_database postgresql://postgres_user:[email protected]/postgres_database

I tried creating this:

create user 'pgloader'@'localhost' identified with mysql_native_password by 'pass'

But when I try the migration, I always get this error:

Failed to connect to mysql at "localhost" (port 3306) as user "pgloader": Condition QMYND:MYSQL-UNSUPPORTED-AUTHENTICATION was signalled.

Answer 1

As of April 2021, pgloader lacks support for the newer SHA2 authentication mode in MySQL v8. (It's a fair bit of work to upgrade.)

I ran into this issue 3 times over the last year.

1st Approach

For the 1st client I was able to downgrade MySQL to v5.6 following comments here.

2nd Approach

For the 2nd client with a small-ish database I hacked some shell scripts together.

1. SSH to grab mysqldump (definitely needed to play with the options.)
2. Reformatting SQL syntax to postgres.
3. Then exec against postgres target.

3rd Approach

My 3rd client had much more data, 4-5TB in hundreds of tables.

This time I found NMIG, a Node-based configurable data migration tool. After a bit of tuning & config, it seems to be working pretty well.

I'd definitely reach for NMIG in the future.