search

dimitri / pgloader

Migrate to PostgreSQL in a single command!
http://pgloader.io
Other
5.27k stars 536 forks source link

pgloader 10 fell through ECASE expression #1233

Open nalan2012 opened 3 years ago

nalan2012 commented 3 years ago

pgloader version: 3.6.3

I ran the command as:

pgloader mysql://root:{password}%mysql_addr:port/tmpdata postgresql://pgname:pgpasswod@pg_addr:port/tmpdata

It reports as FATAL error: Failed to connect pgsql at "x.x.x.x" (port: x) as user: 10 fell through ECASE expression. Wanted one of (0 2 3 4 5 6 7 8)

How to solve this problem? I see that some one has encountered the same problem before(https://github.com/dimitri/pgloader/issues/1183), but the solution is not universal.

rodo commented 3 years ago

This is due to SCRAM authentification, if possible revert to md5 auth and password encryption, it'll work.

nalan2012 commented 3 years ago

This is due to SCRAM authentification, if possible revert to md5 auth and password encryption, it'll work.

I found others have proposed ideas to add support scram sha-256 support for pgloader.(https://github.com/dimitri/pgloader/issues/1207). Maybe the community would support the addition authentification lately?

rodo commented 3 years ago

SCRAM is supported by Postmodern the library used by pgloader https://github.com/marijnh/Postmodern/issues/217 If you are able to built it, it will work. Hope it's help

mecampbellsoup commented 2 years ago

This is due to SCRAM authentification, if possible revert to md5 auth and password encryption, it'll work.

Yea it looks like at some point between PG 13.5 and 14.1, the default pg_hba.conf password auth strategy changed from md5 to scram-sha-256. For a workaround you can migrate your DB to PG 13 via pgloader and then after completing that migration upgrade from PG 13 to 14.