Pgloader fails when migrating from mysql to Postgres

Troubleshooting
1

Summary
pgloader fails with connection error when din are correct

Steps to reproduce

  1. sudo -u postgres psql
    1.1. postgres=# CREATE DATABASE mattermost_db1;
    1.2. postgres=# CREATE USER mattermost_db1 WITH PASSWORD 'MYPASSWORD';
    1.3. postgres=# GRANT ALL PRIVILEGES ON DATABASE mattermost_db1 to mattermost_db1;
    1.4. postgres=ALTER DATABASE mattermost_db1 OWNER TO mattermost_db1;
    1.5. postgres=GRANT USAGE, CREATE ON SCHEMA PUBLIC TO mattermost_db1;
    1.6. postgres=\c mattermost_db1;
    1.7. postgres=SELECT current_database(); - this shows mattermost_db1
    1.8. postgres=ALTER SCHEMA public OWNER to mattermost_db1;
    1.9. postgres=GRANT ALL ON SCHEMA public to mattermost_db1;
  2. migration-assist mysql "DSN"
  3. migration-assist postgres "DSN" --run-migrations --mattermost-version="10.1.1"
  4. migration-assist pgloader --mysql="DSN" --postgres="DSN" > migration.load
  5. pgloader --verbose migration.load | tee migration.log

Expected behavior
I followed the above instructions. User and database name are identical: mattermost_db1.
When I start pgloader migration.load I get the following error:

Observed behavior

2024-11-29T22:25:47.004001Z LOG pgloader version "3.6.3~devel"
2024-11-29T22:25:47.008001Z LOG Data errors in '/tmp/pgloader/'
2024-11-29T22:25:47.008001Z LOG Parsing commands from file #P"/root/migration.load"
2024-11-29T22:25:47.116005Z LOG Migrating from #<MYSQL-CONNECTION mysql://mattermost_db1@localhost:3306/mattermost_db1 {10083CC293}>
2024-11-29T22:25:47.116005Z LOG Migrating into #<PGSQL-CONNECTION pgsql://mattermost_db1@localhost:5432/mattermost_db1 {10083CD273}>
2024-11-29T22:25:47.584022Z WARNING Source column "mattermost_db1"."ChannelBookmarks"."displayname" is casted to type "varchar" which is not the same as "text", the type of current target database column "mattermost_db1"."channelbookmarks"."displayname".
2024-11-29T22:25:47.584022Z WARNING Source column "mattermost_db1"."ChannelBookmarks"."sortorder" is casted to type "bigint" which is not the same as "integer", the type of current target database column "mattermost_db1"."channelbookmarks"."sortorder".
2024-11-29T22:25:47.584022Z WARNING Source column "mattermost_db1"."ChannelBookmarks"."linkurl" is casted to type "varchar" which is not the same as "text", the type of current target database column "mattermost_db1"."channelbookmarks"."linkurl".
2024-11-29T22:25:47.584022Z WARNING Source column "mattermost_db1"."ChannelBookmarks"."imageurl" is casted to type "varchar" which is not the same as "text", the type of current target database column "mattermost_db1"."channelbookmarks"."imageurl".
2024-11-29T22:25:47.584022Z WARNING Source column "mattermost_db1"."ChannelMembers"."notifyprops" is casted to type "text" which is not the same as "jsonb", the type of current target database column "mattermost_db1"."channelmembers"."notifyprops".
2024-11-29T22:25:47.584022Z WARNING Source column "mattermost_db1"."Jobs"."data" is casted to type "text" which is not the same as "jsonb", the type of current target database column "mattermost_db1"."jobs"."data".
2024-11-29T22:25:47.584022Z WARNING Source column "mattermost_db1"."LinkMetadata"."data" is casted to type "text" which is not the same as "jsonb", the type of current target database column "mattermost_db1"."linkmetadata"."data".
2024-11-29T22:25:47.584022Z WARNING Source column "mattermost_db1"."OutgoingOAuthConnections"."oauthtokenurl" is casted to type "varchar" which is not the same as "text", the type of current target database column "mattermost_db1"."outgoingoauthconnections"."oauthtokenurl".
2024-11-29T22:25:47.584022Z WARNING Source column "mattermost_db1"."OutgoingOAuthConnections"."granttype" is casted to type "varchar" which is not the same as "outgoingoauthconnections_granttype", the type of current target database column "mattermost_db1"."outgoingoauthconnections"."granttype".
2024-11-29T22:25:47.584022Z WARNING Source column "mattermost_db1"."Posts"."props" is casted to type "text" which is not the same as "jsonb", the type of current target database column "mattermost_db1"."posts"."props".
2024-11-29T22:25:47.584022Z WARNING Source column "mattermost_db1"."Preferences"."value" is casted to type "varchar" which is not the same as "text", the type of current target database column "mattermost_db1"."preferences"."value".
2024-11-29T22:25:47.584022Z WARNING Source column "mattermost_db1"."RecentSearches"."query" is casted to type "text" which is not the same as "jsonb", the type of current target database column "mattermost_db1"."recentsearches"."query".
2024-11-29T22:25:47.584022Z WARNING Source column "mattermost_db1"."RetentionIdsForDeletion"."ids" is casted to type "text" which is not the same as "character varying[]", the type of current target database column "mattermost_db1"."retentionidsfordeletion"."ids".
2024-11-29T22:25:47.584022Z WARNING Source column "mattermost_db1"."Sessions"."props" is casted to type "text" which is not the same as "jsonb", the type of current target database column "mattermost_db1"."sessions"."props".
2024-11-29T22:25:47.584022Z WARNING Source column "mattermost_db1"."Threads"."participants" is casted to type "text" which is not the same as "jsonb", the type of current target database column "mattermost_db1"."threads"."participants".
2024-11-29T22:25:47.584022Z WARNING Source column "mattermost_db1"."Users"."props" is casted to type "text" which is not the same as "jsonb", the type of current target database column "mattermost_db1"."users"."props".
2024-11-29T22:25:47.584022Z WARNING Source column "mattermost_db1"."Users"."notifyprops" is casted to type "text" which is not the same as "jsonb", the type of current target database column "mattermost_db1"."users"."notifyprops".
2024-11-29T22:25:47.584022Z WARNING Source column "mattermost_db1"."Users"."timezone" is casted to type "text" which is not the same as "jsonb", the type of current target database column "mattermost_db1"."users"."timezone".
2024-11-29T22:25:47.584022Z WARNING Source column "mattermost_db1"."Users"."mfausedtimestamps" is casted to type "text" which is not the same as "jsonb", the type of current target database column "mattermost_db1"."users"."mfausedtimestamps".
2024-11-29T22:25:47.592022Z WARNING PostgreSQL warning: constraint "fk_retentionpolicieschannels_retentionpolicies" of relation "retentionpolicieschannels" does not exist, skipping
2024-11-29T22:25:47.592022Z WARNING PostgreSQL warning: constraint "fk_retentionpoliciesteams_retentionpolicies" of relation "retentionpoliciesteams" does not exist, skipping
2024-11-29T22:25:47.700025Z ERROR A thread failed with error: internal error 222: NIL; args=NIL
2024-11-29T22:25:47.700025Z ERROR A thread failed with error: Failed to connect to pgsql at "localhost" (port 5432) as user "mattermost_db1": internal error 222: NIL; args=NIL
2024-11-29T22:25:47.784028Z ERROR Date/time: 2024-11-29-23:25!
An unhandled error condition has been signalled:
   internal error 222: NIL; args=NIL

Can someone tell me what’s going wrong here?

2

Hi @autoflow, it looks like you’ve set up your PostgreSQL database correctly, but the connection error might stem from a misconfigured DSN or permissions issue. I recommend double-checking the PostgreSQL DSN format and ensuring that the mattermost_db1 user has full access to the database. You can find more details in our MySQL → PostgreSQL migration guide. Let us know if you need further assistance! :blush: