Good afternoon, I’ve tried migrating from MySQL to PostgreSQL in every way possible and have only encountered one problem after another.
I made a backup
I downloaded the migration tool
I tried using pgloader. However, the backup isn’t in the correct format, and the version of pgloader that installs isn’t the correct version because it has a bug. The steps I followed were:
WITH include drop, create tables, create indexes, reset sequences
SET work_mem to ‘16MB’,
maintenance_work_mem to ‘512 MB’,
search_path to 'publi
CAST type datetime to timestamptz drop default drop not null,
type timestamp to timestamptz drop default drop not null;
pgloader migrate.load
However, the database won’t connect due to password incompleteness. It’s always one error after another. And since I have no experience, I’m completely lost. I’ve also tried installing DBeaver to migrate, but it gives me an error. Can you help me?
hello rrmfar, I just completed a migration and update of mattermost successfully (from mariadb via mysql to postgres-14) . The one thing that sticks out and that I don’t remember seeing in guides is importing the mysqldump into directly, .. you are installing pgloader, but not using it?
@Horned1
PostgreSQL 14 using port: 5433 for default. (not be as port: 5432 before)
Pls, check mattermost/config/config.json or /etc/postgresql/14/main/pg_hba.conf to matching listen port.
./migration-assist mysql “mmuser:XXX@tcp(localhost:3306)/mattermost” --fix-artifacts --fix-unicode --fix-varchar
in the guide there is a single command you are supposed to run, but after finfing this info on some forums I did all these commands separately and it solved my issues
[root@mattermost:/tmp]# sudo -u postgres psql
psql (14.19)
Type “help” for help.
postgres=# create database mattermost;
CREATE DATABASE
postgres=# CREATE USER mmuser WITH PASSWORD ‘XXX’;
CREATE ROLE
postgres=# GRANT ALL PRIVILEGES ON DATABASE mattermost to mmuser;
GRANT
postgres=# ALTER DATABASE mattermost OWNER TO mmuser;
ALTER DATABASE
postgres=# GRANT USAGE, CREATE ON SCHEMA PUBLIC TO mmuser;
GRANT
postgres=# \c mattermost;
You are now connected to database “mattermost” as user “postgres”.
mattermost=# SELECT current_database();
current_database
mattermost
(1 row)
mattermost=# ALTER SCHEMA public OWNER to mmuser;
ALTER SCHEMA
mattermost=# GRANT ALL ON SCHEMA public to mmuser;
GRANT
mattermost=# \q
the command below should have your own mattermost version of course
./migration-assist postgres “postgres://mmuser:XXX@localhost:5432/mattermost?sslmode=disable” --run-migrations --mattermost-version=“10.8.0”
./migration-assist pgloader --mysql=“mmuser:XXX@tcp(localhost:3306)/mattermost” --postgres=“postgres://mmuser:XXX@localhost:5432/mattermost?sslmode=disable” --remove-null-chars > migration.load
set the sql connector to PSQL and change the unit-file to point to PostgreSQL as in the guide
vi /opt/mattermost/config/config.json
vi /etc/systemd/system/mattermost.service
Start and check for errors
systemctl start mattermost
journalctl -xeu mattermost.service
Then update as in docs ( Upgrade Mattermost Server - Mattermost documentation )
wget https://releases.mattermost.com/11.1.0/mattermost-11.1.0-linux-amd64.tar.gz
tar -xvf mattermost-11.1.0-linux-amd64.tar.gz mattermost/
systemctl stop mattermost
cd /opt/
cp -ra mattermost/ mattermost-back-$(date +‘%F-%H-%M’)/
sudo find mattermost/ mattermost/client/ -mindepth 1 -maxdepth 1 ! ( -type d ( -path mattermost/client -o -path mattermost/client/plugins -o -path mattermost/config -o -path mattermost/logs -o -path mattermost/plugins -o -path mattermost/data ) -prune ) | sort | sudo xargs rm -r
I sincerely appreciate the steps you provided; however, I am running everything on Ubuntu 24. I installed the tool via sudo apt install pgloader, but it installed a version that reports compatibility issues with MySQL. From that point onward, the repositories are misconfigured, and I inevitably encounter the same problems again.
cd /tmp/
wget https://github.com/mattermost/migration-assist/releases/download/v0.5/migration-assist-Linux-x86_64.tar.gz
tar -xvf migration-assist-Linux-x86_64.tar.gz
sudo apt install pgloader
sudo sh -c 'wget -O /etc/apt/trusted.gpg.d/pgdg.asc https://www.postgresql.org/media/keys/ACCC4CF8.asc'
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
sudo apt update
sudo apt install postgresql-14
sudo systemctl status postgresql # Confirmed operational
systemctl stop mattermost
./migration-assist mysql "mmuser:rg22090801@tcp(localhost:3306)/mattermost" --fix-artifacts --fix-unicode --fix-varchar
The database creation and permission assignments executed flawlessly.
However, the following errors occurred during execution:
2025-11-25T18:21:31.032000Z LOG pgloader version "3.6.7~devel"
2025-11-25T18:21:31.317003Z LOG Migrating from #<MYSQL-CONNECTION mysql://mmuser@localhost:3306/mattermost {1006EA2D33}>
2025-11-25T18:21:31.317003Z LOG Migrating into #<PGSQL-CONNECTION pgsql://mmuser@localhost:5432/mattermost {1006EA2F23}>
2025-11-25T18:21:31.384004Z ERROR mysql: Failed to connect to mysql at "localhost" (port 3306) as user "mmuser": Condition QMYND:MYSQL-UNSUPPORTED-AUTHENTICATION >
2025-11-25T18:21:31.412004Z ERROR Database error 42P01: relation "mattermost.focalboard_blocks" does not exist
QUERY: UPDATE mattermost.focalboard_blocks SET "fields" = '{}'::json WHERE "fields"::text = '';
Thereafter, I was unable to proceed further—encountering persistent errors while attempting to restart the service. I even performed system updates and switched the database connection to PostgreSQL, yet the issues persisted.
That authentication error.. The problem is that currently pgloader doesn’t support caching_sha2_password authentication plugin, which is default for MySQL 8, whereas older MySQL versions use mysql_native_password plugin.