Migration issue from mysql to postgres mattermost 10.4.1

Troubleshooting Upgrade
1

I hit a problem with my mattermost upgrade that is not listed in the migration-assist errors. I had to build my own pgloader due to mysql server having sha2 authentication but after I figured that out and the heap error issues it finally finished the migration. Though there are several warnings about columns. Once I hit Step 5 - Restore full-text indexes & create all indexes I get the following error output which doesnt make sense

@SERVER/mattermost"
2025-11-15 21:07:14 could not find the default schema “public” in search_path, consider setting it from the postgresql console
2025-11-15 21:07:14 search_path is set to “public” for the currrent session
2025-11-15 21:07:14 running migrations..
2025-11-15 21:07:14 applying create_idx_fileinfo_content_txt.sql
2025-11-15 21:07:16 Index creation failed due to content being too long for tsvector.
This is expected if you have a large amount of data.

Please run the migration manually and refer to the documentation page below:

migration log warnings

2025-11-15T21:03:58.025011Z WARNING Source column “mattermost”.“ChannelBookmarks”.“displayname” is casted to type “varchar” which is not the same as “text”, >
2025-11-15T21:03:58.025011Z WARNING Source column “mattermost”.“ChannelBookmarks”.“sortorder” is casted to type “bigint” which is not the same as “integer”, >
2025-11-15T21:03:58.025011Z WARNING Source column “mattermost”.“ChannelBookmarks”.“linkurl” is casted to type “varchar” which is not the same as “text”, the >
2025-11-15T21:03:58.025011Z WARNING Source column “mattermost”.“ChannelBookmarks”.“imageurl” is casted to type “varchar” which is not the same as “text”, the>
2025-11-15T21:03:58.025011Z WARNING Source column “mattermost”.“ChannelBookmarks”.“type” is casted to type “channel_bookmark_type” which is not the same as ">
2025-11-15T21:03:58.025011Z WARNING Source column “mattermost”.“ChannelMembers”.“schemeuser” is casted to type “smallint” which is not the same as “boolean”,>
2025-11-15T21:03:58.025011Z WARNING Source column “mattermost”.“ChannelMembers”.“schemeadmin” is casted to type “smallint” which is not the same as “boolean”>
2025-11-15T21:03:58.025011Z WARNING Source column “mattermost”.“ChannelMembers”.“schemeguest” is casted to type “smallint” which is not the same as “boolean”>
2025-11-15T21:03:58.025011Z WARNING Source column “mattermost”.“Channels”.“type” is casted to type “channel_type” which is not the same as "mattermost.channe>
2025-11-15T21:03:58.025011Z WARNING Source column “mattermost”.“OutgoingOAuthConnections”.“oauthtokenurl” is casted to type “varchar” which is not the same a>
2025-11-15T21:03:58.025011Z WARNING Source column “mattermost”.“OutgoingOAuthConnections”.“granttype” is casted to type “varchar” which is not the same as "m>
2025-11-15T21:03:58.025011Z WARNING Source column “mattermost”.“Preferences”.“value” is casted to type “varchar” which is not the same as “text”, the type of>
2025-11-15T21:03:58.026011Z WARNING Source column “mattermost”.“RetentionIdsForDeletion”.“ids” is casted to type “jsonb” which is not the same as "character >
2025-11-15T21:03:58.026011Z WARNING Source column “mattermost”.“ScheduledPosts”.“priority” is casted to type “varchar” which is not the same as “text”, the t>
2025-11-15T21:03:58.026011Z WARNING Source column “mattermost”.“TeamMembers”.“schemeuser” is casted to type “smallint” which is not the same as “boolean”, th>
2025-11-15T21:03:58.026011Z WARNING Source column “mattermost”.“TeamMembers”.“schemeadmin” is casted to type “smallint” which is not the same as “boolean”, t>
2025-11-15T21:03:58.026011Z WARNING Source column “mattermost”.“TeamMembers”.“schemeguest” is casted to type “smallint” which is not the same as “boolean”, t>
2025-11-15T21:03:58.026011Z WARNING Source column “mattermost”.“Teams”.“type” is casted to type “team_type” which is not the same as “mattermost.team_type”, >
2025-11-15T21:03:58.026011Z WARNING Source column “mattermost”.“UploadSessions”.“type” is casted to type “upload_session_type” which is not the same as "matt>
2025-11-15T21:03:58.034012Z WARNING PostgreSQL warning: constraint “fk_retentionpolicieschannels_retentionpolicies” of relation “retentionpolicieschannels” d>
2025-11-15T21:03:58.034012Z WARNING PostgreSQL warning: constraint “fk_retentionpoliciesteams_retentionpolicies” of relation “retentionpoliciesteams” does no>
2025-11-15T21:04:34.559417Z LOG report summary reset

migration.load:

WITH data only,
prefetch rows = 500,
workers = 8, concurrency = 1,
multiple readers per thread, rows per range = 10000,
prefetch rows = 10000, batch rows = 2500,
create no tables, create no indexes,
preserve index names

SET PostgreSQL PARAMETERS
maintenance_work_mem to ‘128MB’,
work_mem to ‘12MB’

SET MySQL PARAMETERS
net_read_timeout  = ‘120’,
net_write_timeout = ‘120’

CAST column Channels.Type to “channel_type” drop typemod,
column Teams.Type to “team_type” drop typemod,
column UploadSessions.Type to “upload_session_type” drop typemod,
column ChannelBookmarks.Type to “channel_bookmark_type” drop typemod,
column Drafts.Priority to text,
type int when (= precision 11) to integer drop typemod,
type bigint when (= precision 20) to bigint drop typemod,
type text to varchar drop typemod using remove-null-characters,
type tinyint when (<= precision 4) to boolean using tinyint-to-boolean,
type json to jsonb drop typemod using remove-null-characters

EXCLUDING TABLE NAMES MATCHING ~<IR_>, ~, ~, ‘schema_migrations’, ‘db_migrations’, ‘db_lock’,
‘configurations’, ‘configurationfiles’, ‘db_config_migrations’

BEFORE LOAD DO
$$ ALTER SCHEMA public RENAME TO mattermost; $$,
$$ TRUNCATE TABLE mattermost.systems; $$,
$$ DROP INDEX IF EXISTS mattermost.idx_posts_message_txt; $$,
$$ DROP INDEX IF EXISTS mattermost.idx_fileinfo_content_txt; $$

AFTER LOAD DO
$$ UPDATE mattermost.db_migrations set name=‘add_createat_to_teamembers’ where version=92; $$,
$$ ALTER SCHEMA mattermost RENAME TO public; $$,
$$ SELECT pg_catalog.set_config(‘search_path’, ‘“$user”, “$user”, public’, false); $$,
$$ ALTER USER mmuser SET SEARCH_PATH TO ‘“$user”, public’; $$;

I also encountered an issue with database ownership that was not covered in the target database setup section
I did not apply the following step due to my postgres being v14 unless im mistaken.

  1. If using PostgreSQL v15.x or later, additional grants are required:

    ALTER DATABASE mattermost OWNER TO mmuser;
ALTER SCHEMA public OWNER TO mmuser;
GRANT USAGE, CREATE ON SCHEMA public TO mmuser;

which is weird since even if I would apply this step it would still error out.

I did have to apply the mmuser to own the mattermost server rather than the postgres user which allowed the migration to continue.

Any help would be great.

2

Hey @Riley.B did you check this step: Manually migrate from MySQL to PostgreSQL - Mattermost documentation You need to update the index creation to take a part of the string to index.

Per the warnings, they are okay because we have custom types in Postgres and pgloader just can’t understand that. We do have proper casting rules, so it should be okay.

I also encountered an issue with database ownership that was not covered in the target database setup section

I think it’s best to keep using same user for the entire process, from database creation to migration.

1 Like
3

I have glipsed the manual method I was hoping I could avoid it. however it seems Ill end up having to do it manually.