when migrate data from mysql to postgresql using pgloader show fell through ECASE expression

Question

when I using this command to migrate data from mysql 5.17.1 to postgresql 13 in my macOS Catalina 10.15.7:

pgloader mysql://root:[email protected]:3309/common postgresql://postgres:[email protected]:5432/dolphin

shows this error:

2021-02-02T14:35:18.015000Z LOG pgloader version "3.6.2"
2021-02-02T14:35:18.017000Z LOG Data errors in '/private/tmp/pgloader/'
KABOOM!
FATAL error: Failed to connect to pgsql at "postgres.dolphin.com" (port 5432) as user "postgres": 10 fell through ECASE expression. Wanted one of (0 2 3 4 5 6 7 8).
An unhandled error condition has been signalled:
   Failed to connect to pgsql at "postgres.dolphin.com" (port 5432) as user "postgres": 10 fell through ECASE expression. Wanted one of (0 2 3 4 5 6 7 8).




What I am doing here?

Failed to connect to pgsql at "postgres.dolphin.com" (port 5432) as user "postgres": 10 fell through ECASE expression. Wanted one of (0 2 3 4 5 6 7 8).

(base)

I read the issue has the same problem but still not figure out where is the problem. What should I do to fix this problem?

Answer 1

since version 14

Change the default of the password_encryption server parameter to scram-sha-256 (Peter Eisentraut) §

Previously it was md5

feature is requested, yet at the moment you need to use a workaround suggsted in comments on github.

I would advise though to avoid editting postgresql.conf (would affect all users) for that and making changes globaly. Instead do it on session level. E.g.

pgloader_user:postgres_db> set password_encryption to 'md5';
SET
pgloader_user:postgres_db> alter user pgloader_user password 'JUST_RETYPE_YOUR_EXISTING_PASSWORD';
ALTER ROLE

After reentering the password, server will save it with md5 hash and you can pgloader to it immediately. no need to reset anything back - the default encryption will remain scram-sha-256 for all users. (including yours, so if you change password again, it will be hashed with scram-sha-256, thus to make it possible for pgloader to use, you will need set password_encryption to 'md5'; on session level before changing the password)