MySQL to PostGreSQL Migration

1

Hello there,
We followed the steps to transfer data from MySQL to PostgreSQL and after transferring, we started facing issues.

  1. The preferences table in PostgreSQL has a unique constraint on “userid, category and name”
    while in MySQL there was no such checks and the Preferences table contains a lot of duplicates.
  2. If we only pick the distinct records, the mattermost stops working. And, if we let all the records enter by removing the constraint, the mattermost starts but fails to save the preferences and other stuff. The Direct Messages contacts disappears once clicked and the Favorites and other stuff do not work.
2

Can you share more about the steps you followed in your transition? (also more details such as MM version)

1 Like
3

Thank you Neil B to getting back:
Here is more detail:

Mattermost Version: 9.8.1

Database Schema Version: 120

Build Number: 9253470812

Database: postgres

I followed these steps:
https://docs.mattermost.com/deploy/postgres-migration.html

I created a PostgreSQL DB.
I changed the types of the listed columns from Text to Varchar in MySQL database.
Removed the Full Text indexes
Unsupported unicode sequences

DROP TABLE mattermost.schema_migrations;
ALTER TABLE SharedChannelRemotes DROP COLUMN description, DROP COLUMN nextsyncat;
SET @preparedStatement = (SELECT IF(
 (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE table_name = 'Threads'
    AND table_schema = DATABASE()
    AND column_name = 'TeamId'
 ) > 0,
 'ALTER TABLE Threads DROP COLUMN TeamId;',
 'SELECT 1'
));

PREPARE alterIfExists FROM @preparedStatement;
EXECUTE alterIfExists;
DEALLOCATE PREPARE alterIfExists;

And used the provided migration.Load by changing the DSNs.

After Migration:

CREATE INDEX IF NOT EXISTS idx_posts_message_txt ON public.posts USING gin(to_tsvector('english', message));
CREATE INDEX IF NOT EXISTS idx_fileinfo_content_txt ON public.fileinfo USING gin(to_tsvector('english', content));

After doing all of these stuff, I checked and found that the mattermost did not start because, while transferring data from MySQL to PostgreSQL, pgloader skipped all the duplicates from preferences table.

Error Messages

Blockquote
{“timestamp”:“2024-06-21 10:11:58.270 +05:00”,“level”:“info”,“msg”:“SimpleWorker: Job is complete”,“caller”:“jobs/base_workers.go:101”,“worker_name”:“ExpiryNotify”,“job_id”:“m1qooyfrp7brmyoq8gj7gqcbce”,“job_type”:“expiry_notify”,“job_create_at”:“Jun 21 05:11:48.646”}
{“timestamp”:“2024-06-21 10:19:08.050 +05:00”,“level”:“info”,“msg”:“Post.Message has size restrictions”,“caller”:“sqlstore/post_store.go:2615”,“max_characters”:16383,“max_bytes”:65535}

Kindly guide what change in other tables we need to do in order to run the mattermost with PostgreSQL?

Regards
Murtaza

4

@isacikgoz can you share some thoughts?

5

@murtaza Actually MySQL has the same constraint on Preferences table.

CREATE TABLE `Preferences` (
  `UserId` varchar(26) NOT NULL,
  `Category` varchar(32) NOT NULL,
  `Name` varchar(32) NOT NULL,
  `Value` text,
  PRIMARY KEY (`UserId`,`Category`,`Name`),
  KEY `idx_preferences_category` (`Category`),
  KEY `idx_preferences_name` (`Name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

As you can see, it’s the Primary Key. The reason that you might saw that error was due to a second attempt on running pgloader maybe? Because, if it failed for whatever reason, it may already migrated some tables and therefore on the subsequent attempt you will get a PK conflict error.

My recommendation is starting from scratch and sharing the logs from your first attempt. Meanwhile, we are working on a tool to help automate migration process called migration-assist. This is still in the works but you can follow this guide to use the tool: Migration guidelines from MySQL to PostgreSQL - Mattermost documentation