I recently completed full migration of our production Mattermost database from msqld to psql
Here is first short summary of the main steps and challenges i went throough- i will include versions of services , steps, configs,logs and fixes below for anyone.
This is how i fixed it to be usable.
short via steps:
Cloned and built pgloader (v3.6) directly from the GitHub source.
Installed and configured a PostgreSQL service for the target database.
Created a dump of the production MySQL database.
Set up a Docker environment with a virtual MySQL server (older version) to make it compatible with pgloader.
Imported the production dump into this virtual MySQL container.
Used pgloader to migrate data from the virtual MySQL instance to PostgreSQL.
- removed tinyint casting rules during migration.
For tables that didn’t transfer correctly or were empty, I migrated them manually using new loader file for every table where is that error
Moved all migrated tables and schema to the public schema in PostgreSQL.
Went on config.json ifle in /opt/mattermost/config and set sql connector
Started Mattermost using the new PostgreSQL database.
FIXING DATABASE ISSUES
After the initial migration, I ran into multiple constraint and JSON column errors.
i wass troubleshootingthem live via postrgesql logs - i had problem with reactions, plugins , group channels and table post in general
some steps i had done :
Adjusted table definitions where JSON fields were changed to jsnob - after that error for group cnahhels disappeard/
added to every table primary keys
Used alter table commands to fix constraint mismatches across several tables.
Setup enviromnmet for migration
CentOS Stream 10 (Coughlan)
Docekr 28.5.1 Docekr compose 2.40.2
*Mysql prod 8.4.6 *
PSQL 16.10 on 5432
Mysql Virtual Docker container 5.7.44 - on 3307 port 127.0.0.1
Pgloader 5.7.44 (used for pgloader compatibility) - github download - pgloader-bundle-3.8.2-linux-x64.tar.gz + sbcl-2.5.9-x86-64-linux-binary.tar.bz2 for full support mysql
Mattermost 10.12
Before migration stop mattermost service
Pgloader - .loader file main for migration:
LOAD DATABASE
FROM mysql://user:pass@127.0.0.1:3307/database – use simple passwords to avoid errors from pgloader
INTO postgresql://user:pass@localhost/database
WITH include no drop,
create tables,
create indexes,
batch rows = 1000,
batch size = 10MB
CAST
type datetime to timestamptz drop default drop not null,
type timestamp to timestamptz drop default drop not null,
type json to jsonb drop default,
type longtext to text drop default,
type text to text drop default,
type blob to bytea drop default,
type varbinary to bytea drop default,
type binary to bytea drop default
SET work_mem to ‘16MB’,
maintenance_work_mem to ‘512MB’,
client_encoding to ‘utf8’,
standard_conforming_strings to ‘on’; —
*and second onde .load if some data tables does nto tranfered fully
LOAD DATABASE
FROM mysql://user:pass@127.0.0.1:3307/database
INTO postgresql:/user:pass@localhost/database
INCLUDING ONLY TABLE NAMES MATCHING ‘Posts’
SET work_mem to ‘16MB’, maintenance_work_mem to ‘512 MB’; —
Mattermost config.json connector
Updated /opt/mattermost/config/config.json to set the PostgreSQL connection string.
- “SqlSettings”: {
“DriverName”: “postgres”,
“DataSource”: “postgres://user:pass@localhost:5432/database?sslmode=disable\u0026connect_timeout=10”,
Reload-restart mattermost service
ERROR LOGS AND FIXES
During the first Mattermost startup after migration, PostgreSQL logs showed several SQL errors.
I used the following command to monitor logs in real time:
sudo tail -f /var/lib/pgsql/data/log/postgresql-$(date +%a).log
Main errors:
Error 1 :
ERROR: could not identify an equality operator for type json
STATEMENT: WITH cte AS (SELECT * FROM Posts WHERE UpdateAt > $1 AND ChannelId = $2 LIMIT 1000)
(SELECT * FROM cte)
UNION
(SELECT * FROM Posts p1 WHERE id in (SELECT rootid FROM cte))
ORDER BY CreateAt DESC
FIx for error 1:
SELECT table_name, column_name, data_type
FROM information_schema.columns
WHERE data_type = ‘json’;
ALTER TABLE posts ALTER COLUMN props TYPE jsonb USING props::jsonb;
Error 2:
ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
STATEMENT: INSERT INTO Status (UserId,Status,Manual,LastActivityAt,DNDEndTime,PrevStatus)
VALUES ($1,$2,$3,$4,$5,$6)
ON CONFLICT (userid) DO UPDATE SET …
FIX for error 2
ALTER TABLE status ADD CONSTRAINT status_userid_unique UNIQUE (userid);
0ALTER TABLE preferences
ADD CONSTRAINT preferences_userid_category_name_unique
UNIQUE (UserId, Category, Name);
Same was for tables Reactions, IR_UserInfo, Reactions and same query fix it.Btw beofre adding constraints i checkhed for duplicate records to ensure data integity.