# MySQL to PostGreSQL Migration

**URL:** <https://forum.mattermost.com/t/mysql-to-postgresql-migration/18857>\
**Category:** Uncategorized\
**Created:** [June 20, 2024, 2:28pm UTC](https://forum.mattermost.com/t/mysql-to-postgresql-migration/18857 "2024-06-20T14:28:10Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![murtaza](https://avatars.discourse-cdn.com/v4/letter/m/eb8c5e/32.png) [@murtaza](https://forum.mattermost.com/u/murtaza)\
**Post date:** [June 20, 2024, 2:28pm UTC](https://forum.mattermost.com/t/mysql-to-postgresql-migration/18857/1 "2024-06-20T14:28:10Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![nab-77](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.mattermost.com/nab-77/32/5256_2.png) [@nab-77](https://forum.mattermost.com/u/nab-77)\
**Post date:** [June 20, 2024, 3:28pm UTC](https://forum.mattermost.com/t/mysql-to-postgresql-migration/18857/2 "2024-06-20T15:28:42Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![murtaza](https://avatars.discourse-cdn.com/v4/letter/m/eb8c5e/32.png) [@murtaza](https://forum.mattermost.com/u/murtaza)\
**Post date:** [June 21, 2024, 4:42am UTC](https://forum.mattermost.com/t/mysql-to-postgresql-migration/18857/3 "2024-06-21T04:42:19Z")

</div>

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](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

```auto
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:**

```auto
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

---

<div class="post-metadata">

**Author:** ![nab-77](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.mattermost.com/nab-77/32/5256_2.png) [@nab-77](https://forum.mattermost.com/u/nab-77)\
**Post date:** [June 28, 2024, 9:03am UTC](https://forum.mattermost.com/t/mysql-to-postgresql-migration/18857/4 "2024-06-28T09:03:46Z")

</div>

@isacikgoz can you share some thoughts?

---

<div class="post-metadata">

**Author:** ![isacikgoz](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.mattermost.com/isacikgoz/32/4554_2.png) [@isacikgoz](https://forum.mattermost.com/u/isacikgoz)\
**Post date:** [June 28, 2024, 10:00am UTC](https://forum.mattermost.com/t/mysql-to-postgresql-migration/18857/5 "2024-06-28T10:00:28Z")

</div>

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

```sql
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](http://mattermost-docs-preview-pulls.s3-website-us-east-1.amazonaws.com/7237/deploy/postgres-migration.html#migration-assist)