I’ve opened this as an issue in GitHub as well, given the nature of the problem, but the Troubleshooting forum seemed like a good place to discuss this as well. I have limited knowledge of MySQL, and especially PostgreSQl, but I’m in the process of learning as I go.
Please look at the below, and if you can help, please let me know what I need to look into, or if there is a fix. I’m quite interested in .load files which I can use with pgloader, to load in each affected area; if I have to go that far.
At the moment, the real show-stopper is the users panel of the mattermost system console will not show users, due to the affected areas not migrating. Boards are also non-functional, but unused (would still like those working.) Playbooks appears functional, but untested fully. If boards and playbooks can be dropped and rebuilt, I’d like to know the method.
Summary
I am in the process of running a test migration for a 9.6.1 installation from MySQL to PostgreSQL, which is resulting in the non-migration of data due to text vs jsonb formatting.
Note: Apologies about this post in advance, I’m very stuck.
Steps to reproduce
This is taking the currently existing installation, which has been upgraded over a fair number of years, following the official migration guide steps verbatim.
Expected behavior
I expect the conversion to proceed smoothly, and at the very least perform conversions of text to jsonb as needed.
Observed behavior (that appears unintentional)
Following the official guide, certain data is unable to be migrated as it is of type text, and not jsonb.
2024-04-09T02:13:01.344009Z WARNING Source column "mattermost"."ChannelMembers"."notifyprops" is casted to type "text" which is not the same as "jsonb", the type of current target database column "mattermost"."channelmembers"."notifyprops".
2024-04-09T02:13:01.344009Z 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-04-09T02:13:01.344009Z WARNING Source column "mattermost"."Jobs"."data" is casted to type "text" which is not the same as "jsonb", the type of current target database column "mattermost"."jobs"."data".
2024-04-09T02:13:01.344009Z WARNING Source column "mattermost"."LinkMetadata"."data" is casted to type "text" which is not the same as "jsonb", the type of current target database column "mattermost"."linkmetadata"."data".
2024-04-09T02:13:01.344009Z 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-04-09T02:13:01.344009Z 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-04-09T02:13:01.344009Z WARNING Source column "mattermost"."Posts"."props" is casted to type "text" which is not the same as "jsonb", the type of current target database column "mattermost"."posts"."props".
2024-04-09T02:13:01.344009Z WARNING Source column "mattermost"."RecentSearches"."query" is casted to type "text" which is not the same as "jsonb", the type of current target database column "mattermost"."recentsearches"."query".
2024-04-09T02:13:01.344009Z WARNING Source column "mattermost"."RetentionIdsForDeletion"."ids" is casted to type "text" which is not the same as "character varying[]", the type of current target database column "mattermost"."retentionidsfordeletion"."ids".
2024-04-09T02:13:01.344009Z WARNING Source column "mattermost"."Sessions"."props" is casted to type "text" which is not the same as "jsonb", the type of current target database column "mattermost"."sessions"."props".
2024-04-09T02:13:01.344009Z 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-04-09T02:13:01.344009Z WARNING Source column "mattermost"."Threads"."participants" is casted to type "text" which is not the same as "jsonb", the type of current target database column "mattermost"."threads"."participants".
2024-04-09T02:13:01.344009Z 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-04-09T02:13:01.344009Z WARNING Source column "mattermost"."Users"."props" is casted to type "text" which is not the same as "jsonb", the type of current target database column "mattermost"."users"."props".
2024-04-09T02:13:01.344009Z WARNING Source column "mattermost"."Users"."notifyprops" is casted to type "text" which is not the same as "jsonb", the type of current target database column "mattermost"."users"."notifyprops".
2024-04-09T02:13:01.344009Z WARNING Source column "mattermost"."Users"."timezone" is casted to type "text" which is not the same as "jsonb", the type of current target database column "mattermost"."users"."timezone".
Possible fixes
I need a method by which the problem areas can be fixed, this is well beyond my current knowledge level, and those with expertise should be able to help me. In turn, I believe if this can be fixed, it will provide a pathway for many users in the same situation; as well as improve the migration guide.
Note: I’m mainly interested in directions for performing such a conversion, preferably usable at a beginners level; I will learn, but I need proper direction to do so.
I was able to migrate the posts specifically using the following adjusted load file, found in another issue post, so that is working well enough [this may be of use to you for the official guide]:
FROM mysql://mmuser:password@localhost:3306/mattermost
INTO pgsql://mmuser:password@localhost:5432/mattermost
WITH
workers = 2,
concurrency = 1,
preserve index names
SET PostgreSQL PARAMETERS
maintenance_work_mem to '512MB',
work_mem to '48MB'
CAST type int when (= precision 11) to integer drop typemod,
type bigint when (= precision 20) to bigint drop typemod,
type text to varchar drop typemod,
type json to jsonb drop typemod
SET MySQL PARAMETERS
net_read_timeout = '1500',
net_write_timeout = '1500'
INCLUDING ONLY TABLE NAMES MATCHING 'Posts'
BEFORE LOAD DO
$$ ALTER SCHEMA public RENAME TO mattermost; $$,
$$ ALTER TABLE mattermost.Posts ALTER COLUMN message TYPE character varying(65535) COLLATE pg_catalog."default"; $$,
$$ ALTER TABLE mattermost.Posts ALTER COLUMN hashtags TYPE character varying(1000) COLLATE pg_catalog."default"; $$,
$$ ALTER TABLE mattermost.Posts ALTER COLUMN filenames TYPE character varying(4000) COLLATE pg_catalog."default"; $$,
$$ ALTER TABLE mattermost.Posts ALTER COLUMN fileids TYPE character varying(3000) COLLATE pg_catalog."default"; $$
AFTER LOAD DO
$$ ALTER SCHEMA mattermost RENAME TO public; $$;