How to Migrate a MySQL Database to PostgreSQL for Mattermost (Complete Guide)

Migrating Mattermost from MySQL to PostgreSQL can significantly improve performance, stability, and scalability. PostgreSQL is the recommended database backend for modern Mattermost deployments—especially for production environments and large teams.

In this guide, I’ll walk you step-by-step through installing PostgreSQL in a homelab, setting up the master database, using pgloader for migration, and completing the post-migration cleanup.
This is the exact process I used, including the commands.

📌 Why Move Mattermost from MySQL to PostgreSQL?

• Better performance with large datasets
• Reliable ACID compliance
• Native JSONB support
• Recommended officially by Mattermost
• Faster indexing and more robust replication options
• Mattermost versions 11.0 and above no longer support MySQL, making PostgreSQL the required database backend. Document

If your Mattermost is running on MySQL today and you want long-term stability, PostgreSQL is the right choice.

1. Installing PostgreSQL in a Homelab (Docker-Based Master Setup)

Use Docker to quickly spin up a PostgreSQL master instance.

Docker Command

docker run -d \
  --name postgres-master \
  --restart unless-stopped \
  -e TZ=Asia/Kolkata \
  -e POSTGRES_PASSWORD={DB_PASSWORD} \
  -v /mnt/docker/volume/postgres:/var/lib/postgresql \
  -p 5432:5432 \
  postgres:{POSTGRES_VERSION}

docker run -d \
  --name postgres-master \
  --restart unless-stopped \
  -e TZ=Asia/Kolkata \
  -e POSTGRES_PASSWORD={DB_PASSWORD} \
  -v /mnt/docker/volume/postgres:/var/lib/postgresql \
  -p 5432:5432 \
  postgres:{POSTGRES_VERSION}

Replace placeholders:

• {DB_PASSWORD} → PostgreSQL password
• {POSTGRES_VERSION} → Example: 15, 16, etc.

Notes:

• Database files are stored under:
• /mnt/docker/volume/postgres
• Make sure port 5432 is accessible from your Mattermost server.
• Replace passwords/IPs in production.

2. Installing pgloader (Migration Tool)

pgloader is the easiest and most reliable way to migrate MySQL → PostgreSQL.

debian packages

You can install pgloader directly from apt.postgresql.org and from official debian repositories, see packages.debian.org/pgloader.

Run the below command to compile the latest code, sudo apt install pgloader will install the old version of it.

sudo apt update
sudo apt install build-essential sbcl libsqlite3-dev gawk curl make freetds-dev libzip-dev git
git clone https://github.com/dimitri/pgloader.git
cd pgloader
make
sudo make install

sudo apt update
sudo apt install build-essential sbcl libsqlite3-dev gawk curl make freetds-dev libzip-dev git
git clone https://github.com/dimitri/pgloader.git
cd pgloader
make
sudo make install

RPM packages

The Postgres community repository for RPM packages is yum.postgresql.org and does include binary packages for pgloader.

3. Running the Database Migration

pgloader copies schema, tables, indexes, and data automatically.

Migration Command

If you installed pgloader by compiling from source using make, the binary will be available under your build directory—typically something like:

{CURRENT_WORKING_DIRECTORY}/build/bin/pgloader

{CURRENT_WORKING_DIRECTORY}/build/bin/pgloader

Use the following migration command:

{CURRENT_WORKING_DIRECTORY}/build/bin/pgloader \
  'mysql://{MYSQL_USER}:{MYSQL_PASSWORD}@{MYSQL_HOST}:{MYSQL_PORT}/{MYSQL_DATABASE}' \
  'postgresql://{DB_USER}:{DB_PASSWORD}@{DB_HOST}:5432/{POSTGRES_DATABASE}'

{CURRENT_WORKING_DIRECTORY}/build/bin/pgloader \
  'mysql://{MYSQL_USER}:{MYSQL_PASSWORD}@{MYSQL_HOST}:{MYSQL_PORT}/{MYSQL_DATABASE}' \
  'postgresql://{DB_USER}:{DB_PASSWORD}@{DB_HOST}:5432/{POSTGRES_DATABASE}'

Placeholder explanation:

• {CURRENT_WORKING_DIRECTORY} → The directory where you cloned and built pgloader using make.
• {MYSQL_USER} → MySQL username
• {MYSQL_PASSWORD} → MySQL password
• {MYSQL_HOST} → MySQL server IP/hostname
• {MYSQL_PORT} → Usually 3306
• {MYSQL_DATABASE} → The Mattermost MySQL database
• {DB_USER} → PostgreSQL username
• {DB_PASSWORD} → PostgreSQL password
• {DB_HOST} → PostgreSQL server IP/hostname
• {POSTGRES_DATABASE} → PostgreSQL database created for Mattermost

Important Notes

• You must create the mattermost DB in PostgreSQL before running the command.
• pgloader automatically:
  • Creates tables
  • Migrates data
  • Converts datatypes
  • Builds indexes

This usually finishes in just a few minutes, depending on dataset size.

4. Post-Migration SQL Fixes

After migration, you must fix a few Mattermost-specific schema settings.

Run these SQL commands in PostgreSQL:

✔ Fix incorrect migration entry

UPDATE {YOUR_DB_NAME}.db_migrations
SET name = 'add_createat_to_teamembers'
WHERE version = 92;

UPDATE {YOUR_DB_NAME}.db_migrations
SET name = 'add_createat_to_teamembers'
WHERE version = 92;

✔ Cleanup: Drop the default ‘public’ schema

DROP SCHEMA IF EXISTS public CASCADE;

DROP SCHEMA IF EXISTS public CASCADE;

✔ Rename the Mattermost schema to public

ALTER SCHEMA {YOUR_MYSQL_DB_NAME} RENAME TO public;

ALTER SCHEMA {YOUR_MYSQL_DB_NAME} RENAME TO public;

✔ Set correct PostgreSQL search path

SELECT pg_catalog.set_config('search_path', '"$user", public', false);
ALTER USER postgres SET SEARCH_PATH TO 'public';

SELECT pg_catalog.set_config('search_path', '"$user", public', false);
ALTER USER postgres SET SEARCH_PATH TO 'public';

⚠️ If renaming fails

Use this alternative:

ALTER SCHEMA {YOUR_MYSQL_DB_NAME} RENAME TO public;

ALTER SCHEMA {YOUR_MYSQL_DB_NAME} RENAME TO public;

5. Update Your Mattermost Configuration

Mattermost uses a PostgreSQL connection string like this:

DB Connection String

postgres://{DB_USER}:{DP_PASSWORD}@{DP_HOST}:5432/{YOUR_MATTERMOST_DB}?sslmode=disable&connect_timeout=10

postgres://{DB_USER}:{DP_PASSWORD}@{DP_HOST}:5432/{YOUR_MATTERMOST_DB}?sslmode=disable&connect_timeout=10

Replace the placeholders as follows:

• Replace the placeholders as follows:
• {DB_USER} → Your PostgreSQL username
• {DB_PASSWORD} → Your PostgreSQL password
• {DB_HOST} → IP or hostname of your PostgreSQL server
• {YOUR_MATTERMOST_DB} → Mattermost database name

Update this in:

• config.json under SqlSettings.DataSource, or
• Environment variable:
  • MM_SQLSETTINGS_DATASOURCE="postgres://{DB_USER}:{DB_PASSWORD}@{DB_HOST}:5432/{POSTGRES_DATABASE}?sslmode=disable&connect_timeout=10"

Then restart Mattermost to apply the new configuration.

6. Validate Your Migration

After Mattermost starts:

Verify:

• ✔ Users load successfully
• ✔ Channels and messages load
• ✔ New messages can be posted
• ✔ Uploads & attachments work
• ✔ Integrations & bots function normally
• ✔ No SQL errors appear in logs

Check logs if needed:

docker logs mattermost

docker logs mattermost

If the UI loads and messages are visible, the migration is successful.

Conclusion

Migrating Mattermost from MySQL to PostgreSQL is straightforward when using pgloader and a few schema adjustments. PostgreSQL offers better performance, improved reliability, and long-term support—making it the ideal choice for production Mattermost systems.