Migration Mattermost MYSQL to Postgresql

[Leo]

Hello, we are preparing a database migration from mysql to postgresql.

We have followed all the steps and read some workarounds and we have managed to migrate it apparently well. But I have some doubts:

1. Some WARNINGs appear in the main pgloader, are these acceptable?

2024-11-04T15:36:53.590000+01:00 WARNING Source column “mattermost”.“ChannelBookmarks”.“displayname” is casted to type “varchar” which is not the same as “text”, the type of current target database column “mattermost”.“channelbookmarks”.“displayname”.
2024-11-04T15:36:53.590000+01:00 WARNING Source column “mattermost”.“ChannelBookmarks”.“sortorder” is casted to type “bigint” which is not the same as “integer”, the type of current target database column “mattermost”.“channelbookmarks”.“sortorder”.
2024-11-04T15:36:53.591000+01:00 WARNING Source column “mattermost”.“ChannelBookmarks”.“linkurl” is casted to type “varchar” which is not the same as “text”, the type of current target database column “mattermost”.“channelbookmarks”.“linkurl”.
2024-11-04T15:36:53.591000+01:00 WARNING Source column “mattermost”.“ChannelBookmarks”.“imageurl” is casted to type “varchar” which is not the same as “text”, the type of current target database column “mattermost”.“channelbookmarks”.“imageurl”.
2024-11-04T15:36:53.591000+01:00 WARNING Source column “mattermost”.“ChannelBookmarks”.“type” is casted to type “channel_bookmark_type” which is not the same as “mattermost.channel_bookmark_type”, the type of current target database column “mattermost”.“channelbookmarks”.“type”.
2024-11-04T15:36:53.591000+01:00 WARNING Source column “mattermost”.“ChannelMembers”.“schemeuser” is casted to type “smallint” which is not the same as “boolean”, the type of current target database column “mattermost”.“channelmembers”.“schemeuser”.
2024-11-04T15:36:53.591000+01:00 WARNING Source column “mattermost”.“ChannelMembers”.“schemeadmin” is casted to type “smallint” which is not the same as “boolean”, the type of current target database column “mattermost”.“channelmembers”.“schemeadmin”.
2024-11-04T15:36:53.591000+01:00 WARNING Source column “mattermost”.“ChannelMembers”.“schemeguest” is casted to type “smallint” which is not the same as “boolean”, the type of current target database column “mattermost”.“channelmembers”.“schemeguest”.
2024-11-04T15:36:53.591000+01:00 WARNING Source column “mattermost”.“Channels”.“type” is casted to type “channel_type” which is not the same as “mattermost.channel_type”, the type of current target database column “mattermost”.“channels”.“type”.
2024-11-04T15:36:53.591000+01:00 WARNING Source column “mattermost”.“OutgoingOAuthConnections”.“oauthtokenurl” is casted to type “varchar” which is not the same as “text”, the type of current target database column “mattermost”.“outgoingoauthconnections”.“oauthtokenurl”.
2024-11-04T15:36:53.591000+01:00 WARNING Source column “mattermost”.“OutgoingOAuthConnections”.“granttype” is casted to type “varchar” which is not the same as “mattermost.outgoingoauthconnections_granttype”, the type of current target database column “mattermost”.“outgoingoauthconnections”.“granttype”.
2024-11-04T15:36:53.591000+01:00 WARNING Source column “mattermost”.“Preferences”.“value” is casted to type “varchar” which is not the same as “text”, the type of current target database column “mattermost”.“preferences”.“value”.
2024-11-04T15:36:53.591000+01:00 WARNING Source column “mattermost”.“RetentionIdsForDeletion”.“ids” is casted to type “jsonb” which is not the same as “character varying”, the type of current target database column “mattermost”.“retentionidsfordeletion”.“ids”.
2024-11-04T15:36:53.591000+01:00 WARNING Source column “mattermost”.“TeamMembers”.“schemeuser” is casted to type “smallint” which is not the same as “boolean”, the type of current target database column “mattermost”.“teammembers”.“schemeuser”.
2024-11-04T15:36:53.591000+01:00 WARNING Source column “mattermost”.“TeamMembers”.“schemeadmin” is casted to type “smallint” which is not the same as “boolean”, the type of current target database column “mattermost”.“teammembers”.“schemeadmin”.
2024-11-04T15:36:53.591000+01:00 WARNING Source column “mattermost”.“TeamMembers”.“schemeguest” is casted to type “smallint” which is not the same as “boolean”, the type of current target database column “mattermost”.“teammembers”.“schemeguest”.
2024-11-04T15:36:53.591000+01:00 WARNING Source column “mattermost”.“Teams”.“type” is casted to type “team_type” which is not the same as “mattermost.team_type”, the type of current target database column “mattermost”.“teams”.“type”.
2024-11-04T15:36:53.591000+01:00 WARNING Source column “mattermost”.“UploadSessions”.“type” is casted to type “upload_session_type” which is not the same as “mattermost.upload_session_type”, the type of current target database column “mattermost”.“uploadsessions”.“type”.
2024-11-04T15:36:53.600000+01:00 WARNING PostgreSQL warning: constraint “fk_retentionpolicieschannels_retentionpolicies” of relation “retentionpolicieschannels” does not exist, skipping
2024-11-04T15:36:53.600000+01:00 WARNING PostgreSQL warning: constraint “fk_retentionpoliciesteams_retentionpolicies” of relation “retentionpoliciesteams” does not exist, skipping

2. In the post-migrate step we have errors in the creation of indexes that I have seen are common

2024-10-25 10:20:27 applying create_idx_fileinfo_content_txt.sql
2024-10-25 10:20:32 Index creation failed due to content being too long for tsvector.
This is expected if you have a large amount of data.

I try this

mattermost=> CREATE INDEX IF NOT EXISTS idx_fileinfo_content_txt ON public.fileinfo USING gin(to_tsvector(‘english’, substring(content,0,1000000)));
NOTICE: word is too long to be indexed
DETAIL: Words longer than 2047 characters are ignored.
NOTICE: word is too long to be indexed
DETAIL: Words longer than 2047 characters are ignored.
NOTICE: word is too long to be indexed
DETAIL: Words longer than 2047 characters are ignored.
NOTICE: word is too long to be indexed
DETAIL: Words longer than 2047 characters are ignored.
NOTICE: word is too long to be indexed
DETAIL: Words longer than 2047 characters are ignored.
NOTICE: word is too long to be indexed
DETAIL: Words longer than 2047 characters are ignored.
NOTICE: word is too long to be indexed
DETAIL: Words longer than 2047 characters are ignored.
NOTICE: word is too long to be indexed
DETAIL: Words longer than 2047 characters are ignored.
NOTICE: word is too long to be indexed
DETAIL: Words longer than 2047 characters are ignored.
NOTICE: word is too long to be indexed
DETAIL: Words longer than 2047 characters are ignored.
NOTICE: word is too long to be indexed
DETAIL: Words longer than 2047 characters are ignored.
NOTICE: word is too long to be indexed
DETAIL: Words longer than 2047 characters are ignored.
NOTICE: word is too long to be indexed
DETAIL: Words longer than 2047 characters are ignored.
NOTICE: word is too long to be indexed
DETAIL: Words longer than 2047 characters are ignored.
NOTICE: word is too long to be indexed
DETAIL: Words longer than 2047 characters are ignored.
NOTICE: word is too long to be indexed
DETAIL: Words longer than 2047 characters are ignored.
NOTICE: word is too long to be indexed
DETAIL: Words longer than 2047 characters are ignored.
ERROR: string is too long for tsvector (1164936 bytes, max 1048575 bytes)

Is it necessary to do something about it?

3. In the migration of playbooks, calls and boards, we get non-existent table errors, I imagine because we do not have those elements. Is that so?

thanks in advance for your contributions

[john.oliver]

What version of Mattermost? If it’s an older version, I’d recommend upgrading Mattermost first, to get the latest migration tools.

[Leo]

Version: 10.1.1
Build Number: 11162711545

[john.oliver]

What version of MySQL are you trying to migrate to what version of PostGres?

[Leo]

Hi,

$ mysql -V
mysql Ver 8.0.33 for Linux on x86_64 (MySQL Community Server - GPL)

$ psql --version
psql (PostgreSQL) 11.22 (Debian 11.22-0+deb10u2)

[Leo]

Apparently the migration is done correctly but we want to avoid future problems

2024-11-04T15:50:46.453000+01:00 LOG report summary reset
table name errors rows bytes total time

                     before load          0          4                     0.027s
                 fetch meta data          0         67                     0.124s
               Drop Foreign Keys          0          4                     0.009s

                mattermost.posts          0    4078104   832.2 MB      13m52.595s
    mattermost.threadmemberships          0     159517    13.1 MB         33.922s
             mattermost.fileinfo          0     104650   477.9 MB         31.744s
               mattermost.audits          0     143625    18.4 MB          3.314s
              mattermost.threads          0      76281     8.5 MB         26.793s
            mattermost.reactions          0     118434    13.7 MB          2.895s
 mattermost.channelmemberhistory          0      17979     1.2 MB          1.723s
        mattermost.postspriority          0       6206   370.0 kB          0.304s
             mattermost.sessions          0        278   101.8 kB          0.317s
  mattermost.pluginkeyvaluestore          0        208    45.1 kB          0.307s
       mattermost.publicchannels          0        113    16.1 kB          0.410s
                 mattermost.bots          0         13     1.4 kB          0.436s
        mattermost.postreminders          0          1     0.1 kB          0.459s
     mattermost.clusterdiscovery          0          0                     0.478s
        mattermost.groupchannels          0          0                     0.492s
          mattermost.notifyadmin          0          0                     0.510s

mattermost.outgoingoauthconnections 0 0 0.528s
mattermost.retentionidsfordeletion 0 0 0.527s
mattermost.schemes 0 0 0.528s
mattermost.sharedchannels 0 0 0.546s
mattermost.usertermsofservice 0 0 0.552s
mattermost.channelmembers 0 17430 4.0 MB 2.096s
mattermost.drafts 0 892 152.8 kB 0.819s
mattermost.recentsearches 0 380 45.8 kB 0.837s
mattermost.users 0 194 147.4 kB 0.860s
mattermost.systems 0 66 2.7 kB 0.867s
mattermost.teams 0 5 1.0 kB 0.876s
mattermost.tokens 0 0 0.880s
mattermost.commandwebhooks 0 0 0.887s
mattermost.groupmembers 0 0 0.891s
mattermost.oauthaccessdata 0 0 0.893s
mattermost.persistentnotifications 0 0 0.893s
mattermost.retentionpolicies 0 0 0.892s
mattermost.sharedchannelattachments 0 0 0.887s
mattermost.termsofservice 0 0 0.887s
mattermost.jobs 0 81370 12.3 MB 1.774s
mattermost.channels 0 6291 1.2 MB 0.968s
mattermost.sidebarcategories 0 1000 136.8 kB 0.902s
mattermost.emoji 0 293 28.2 kB 0.943s
mattermost.status 0 191 10.1 kB 0.979s
mattermost.roles 0 22 22.5 kB 1.038s
mattermost.commands 0 2 0.4 kB 1.043s
mattermost.useraccesstokens 0 1 0.1 kB 1.045s
mattermost.compliances 0 0 1.047s
mattermost.groupteams 0 0 1.048s
mattermost.oauthapps 0 0 1.052s
mattermost.postacknowledgements 0 0 1.053s
mattermost.retentionpolicieschannels 0 0 1.055s
mattermost.sharedchannelremotes 0 0 1.057s
mattermost.uploadsessions 0 0 1.059s
mattermost.preferences 0 35999 3.1 MB 0.616s
mattermost.sidebarchannels 0 6155 635.6 kB 0.111s
mattermost.productnoticeviewstate 0 814 49.2 kB 0.062s
mattermost.teammembers 0 245 17.4 kB 0.057s
mattermost.linkmetadata 0 174 27.2 kB 0.053s
mattermost.incomingwebhooks 0 13 2.4 kB 0.017s
mattermost.outgoingwebhooks 0 2 0.5 kB 0.029s
mattermost.channelbookmarks 0 0 0.027s
mattermost.desktoptokens 0 0 0.050s
mattermost.licenses 0 0 0.022s
mattermost.oauthauthdata 0 0 0.019s
mattermost.remoteclusters 0 0 0.035s
mattermost.retentionpoliciesteams 0 0 0.016s
mattermost.sharedchannelusers 0 0 0.018s
mattermost.usergroups 0 0 0.012s

         COPY Threads Completion          0          8                 13m52.578s
                 Reset Sequences          0          0                     0.203s
             Create Foreign Keys          0          2                     0.010s
                 Set Search Path          0          1                     0.001s
                Install Comments          0          0                     0.000s
                      after load          0          4                     0.008s

               Total import time          ✓    4856948     1.4 GB      13m52.800s

[john.oliver]

I’m glad the migration succeeded! I have a strong feeling that these are errors picked up and then handled by the migration process. All of those “casted” errors, if they weren’t handled I’d think the db would be unusable.

[Leo]

And about the creation of create_idx_fileinfo_content_txt? Is it critical? Would there be any more steps to take?
Do the steps for playbooks, calls and boards apply only if you have those elements?

[john.oliver]

@Leo

And about the creation of create_idx_fileinfo_content_txt? Is it critical? Would there be any more steps to take?[/quote]

I’m not sure. I believe that if the migrated instance of Mattermost is working, you’re probably fine!

[quote]Do the steps for playbooks, calls and boards apply only if you have those elements?

I believe so.

[iqoqi]

This is all very infuriating.
Not only do I have to install yet another service (postgres) just for Mattermost when every other vendor supports either MySQL or both. I have to wade through the migration process, which sic! “facilitates a smooth transition”. But there are also changes in the new postgres schema that make the migration another step more difficult.

And on top of that, OP gets an “I believe so” in response to his answer

Get your stuff together …

[john.combs]

Hi y’all! Just hopping into this thread to let you know that I completely understand how frustrating this process can feel, and we truly appreciate your patience as you work through it.

Regarding the index creation error, it’s typically not critical for general functionality but could impact full-text search performance. You might find the official documentation on Migration from MySQL to PostgreSQL helpful for further clarity.

The team always strives to improve our support and migration tools, and will ensure your feedback is shared to help make this process smoother in the future.