Been trying to migrate to PostreSQL for weeks

Troubleshooting
1

I’ve been endlessly unable to migrate an ~70M record MariaDB db to Postgres. It has also failed when exported and imported to MySQL, to Postgres, with multiple possibly overlapping issues. Of ~40 attempts spanning multiple hours each run, I’ve (eventually) had a successful migration 3 times, only to repeat it the steps identically for it to fail next time around, on exactly the same hardware, the same database a day or two newer.

Tools

  • MM version: 10.11.22
  • Pgloader:
    • Mattermost Docker: pgloader version “3.6.3e06d1b”, compiled with SBCL 2.2.9.debian
    • Native (Debian package): pgloader version “3.6.10~devel”, compiled with SBCL 2.5.2.debian
  • MariaDB: 11.8.6
  • MySQL: 8.0.46
  • Migration-assist: latest, v0.8

Host

  • Host machine: 64G RAM
  • Intel i7-8700
  • Monitoring of RAM consumption shows it never quite hits bottom, nor leans on swap. But it has got close, at around 62GB consumption total.

NOTES: I also tried the Java implementation of pgloader, but could not get it to work with any load file. I’ve not tried a manual migration, as I figure it would not change much.

Load file

LOAD DATABASE
    FROM       mysql://mmuser:***********@localhost:3306/mattermost
    INTO       postgresql://mmuser:************@127.0.0.1:5432/mattermost
 
WITH data only,
    workers = 8, concurrency = 1,
    multiple readers per thread, rows per range = 5000,
    prefetch rows = 10000, batch rows = 2500,
    create no tables, create no indexes,
    preserve index names
 
SET PostgreSQL PARAMETERS
    maintenance_work_mem to '1024MB',
    work_mem to '2048MB'
 
SET MySQL PARAMETERS
    net_read_timeout  = '600',
    net_write_timeout = '600'
 
 CAST column Channels.Type to "channel_type" drop typemod,
    column Teams.Type to "team_type" drop typemod,
    column UploadSessions.Type to "upload_session_type" drop typemod,
    column ChannelBookmarks.Type to "channel_bookmark_type" drop typemod,
    column Drafts.Priority to text,
    type int when (= precision 11) to integer drop typemod,
    type bigint when (= precision 20) to bigint drop typemod,
    type text to varchar drop typemod using remove-null-characters,
    type tinyint when (<= precision 4) to boolean using tinyint-to-boolean,
    type json to jsonb drop typemod using remove-null-characters
 
EXCLUDING TABLE NAMES MATCHING ~<IR_>, ~<focalboard>, ~<calls>, 'schema_migrations', 'db_migrations', 'db_lock',
    'configurations', 'configurationfiles', 'db_config_migrations'
 
BEFORE LOAD DO
    $$ ALTER SCHEMA public RENAME TO mattermost; $$,
    $$ TRUNCATE TABLE mattermost.systems; $$,
    $$ DROP INDEX IF EXISTS mattermost.idx_posts_message_txt; $$,
    $$ DROP INDEX IF EXISTS mattermost.idx_fileinfo_content_txt; $$
 
AFTER LOAD DO
    $$ UPDATE mattermost.db_migrations set name='add_createat_to_teamembers' where version=92; $$,
    $$ ALTER SCHEMA mattermost RENAME TO public; $$,
    $$ SELECT pg_catalog.set_config('search_path', '"$user", public', false); $$,
    $$ ALTER USER mmuser SET search_path TO "$user", public; $$;

The two issues I’m most consistently having are as follows:

1. Loss of connection to socket ~5hrs in

NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL
NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL
NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL
NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL
NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL
NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL
NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL
NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL
NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL
NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL
NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL
NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL
NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL
NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL NIL) :START
1531 :COUNT 0) :LOCK #<SB-THREAD:MUTEX "Anonymous lock" free owner=0>
:NOTIFY-PUSH #<SB-THREAD:WAITQUEUE #1=Anonymous condition variable
{102DEEB0E3}> :NOTIFY-POP #<SB-THREAD:WAITQUEUE #1# {101E723C03}>) 1)
[fast-method] 16: ((FLET "BODY-FN0" :IN
LPARALLEL.KERNEL::MAKE-CHANNELED-TASK)) 17: ((LAMBDA NIL :IN
LPARALLEL.KERNEL::MAKE-CHANNELED-TASK)) 18:
(LPARALLEL.KERNEL::EXEC-TASK/WORKER #<unavailable argument>
#S(LPARALLEL.KERNEL::WORKER :HANDSHAKE/FROM-WORKER
#S(LPARALLEL.CONS-QUEUE:CONS-QUEUE :IMPL #S(LPARALLEL.RAW-QUEUE:RAW-QUEUE
:HEAD NIL :TAIL NIL) :LOCK #<SB-THREAD:MUTEX #1="Anonymous lock" free owner=0>
:CVAR NIL) :HANDSHAKE/TO-WORKER #S(LPARALLEL.CONS-QUEUE:CONS-QUEUE :IMPL
#S(LPARALLEL.RAW-QUEUE:RAW-QUEUE :HEAD NIL :TAIL NIL) :LOCK #<SB-THREAD:MUTEX
#1# free owner=0> :CVAR #<SB-THREAD:WAITQUEUE Anonymous condition variable
{100583E583}>) :EXIT-NOTIFICATION #S(LPARALLEL.CONS-QUEUE:CONS-QUEUE :IMPL
#S(LPARALLEL.RAW-QUEUE:RAW-QUEUE :HEAD NIL :TAIL NIL) :LOCK #<SB-THREAD:MUTEX
#1# free owner=0> :CVAR NIL) :THREAD #<SB-THREAD:THREAD "lparallel" RUNNING
{1005838263}> :RUNNING-CATEGORY :DEFAULT :INDEX 3 :TASKS
#S(LPARALLEL.SPIN-QUEUE:SPIN-QUEUE :HEAD (LPARALLEL.SPIN-QUEUE::DUMMY
(#<FUNCTION #2=(LAMBDA NIL :IN LPARALLEL.KERNEL::MAKE-CHANNELED-TASK)
{101305D57B}> . :DEFAULT) (#<FUNCTION #2# {101305D5AB}> . :DEFAULT)
(#<FUNCTION #2# {101305D5DB}> . :DEFAULT) (#<FUNCTION #2# {101305D60B}> .
:DEFAULT) (#<FUNCTION #2# {101305D63B}> . :DEFAULT) (#<FUNCTION #2#
{101305D66B}> . :DEFAULT) (#<FUNCTION #2# {101305D69B}> . :DEFAULT)
(#<FUNCTION #2# {101305D6CB}> . :DEFAULT) (#<FUNCTION #2# {101305D6FB}> .
:DEFAULT) (#<FUNCTION #2# {101305D72B}> . :DEFAULT) (#<FUNCTION #2#
{101305D75B}> . :DEFAULT) (#<FUNCTION #2# {101305D78B}> . :DEFAULT)
(#<FUNCTION #2# {101305D7BB}> . :DEFAULT) (#<FUNCTION #2# {101305D7EB}> .
:DEFAULT) (#<FUNCTION #2# {101305D81B}> . :DEFAULT) . #3=((#<FUNCTION #2#
{101305D84B}> . :DEFAULT))) :TAIL #3#))) 19: (LPARALLEL.KERNEL::WORKER-LOOP
#<LPARALLEL.KERNEL:KERNEL :NAME "lparallel" :WORKER-COUNT 8 :USE-CALLER NIL
:ALIVE T :SPIN-COUNT 2000 {10085C4723}> #S(LPARALLEL.KERNEL::WORKER
:HANDSHAKE/FROM-WORKER #S(LPARALLEL.CONS-QUEUE:CONS-QUEUE :IMPL
#S(LPARALLEL.RAW-QUEUE:RAW-QUEUE :HEAD NIL :TAIL NIL) :LOCK #<SB-THREAD:MUTEX
#1="Anonymous lock" free owner=0> :CVAR NIL) :HANDSHAKE/TO-WORKER
#S(LPARALLEL.CONS-QUEUE:CONS-QUEUE :IMPL #S(LPARALLEL.RAW-QUEUE:RAW-QUEUE
:HEAD NIL :TAIL NIL) :LOCK #<SB-THREAD:MUTEX #1# free owner=0> :CVAR
#<SB-THREAD:WAITQUEUE Anonymous condition variable {100583E583}>)
:EXIT-NOTIFICATION #S(LPARALLEL.CONS-QUEUE:CONS-QUEUE :IMPL
#S(LPARALLEL.RAW-QUEUE:RAW-QUEUE :HEAD NIL :TAIL NIL) :LOCK #<SB-THREAD:MUTEX
#1# free owner=0> :CVAR NIL) :THREAD #<SB-THREAD:THREAD "lparallel" RUNNING
{1005838263}> :RUNNING-CATEGORY :DEFAULT :INDEX 3 :TASKS
#S(LPARALLEL.SPIN-QUEUE:SPIN-QUEUE :HEAD (LPARALLEL.SPIN-QUEUE::DUMMY
(#<FUNCTION #2=(LAMBDA NIL :IN LPARALLEL.KERNEL::MAKE-CHANNELED-TASK)
{101305D57B}> . :DEFAULT) (#<FUNCTION #2# {101305D5AB}> . :DEFAULT)
(#<FUNCTION #2# {101305D5DB}> . :DEFAULT) (#<FUNCTION #2# {101305D60B}> .
:DEFAULT) (#<FUNCTION #2# {101305D63B}> . :DEFAULT) (#<FUNCTION #2#
{101305D66B}> . :DEFAULT) (#<FUNCTION #2# {101305D69B}> . :DEFAULT)
(#<FUNCTION #2# {101305D6CB}> . :DEFAULT) (#<FUNCTION #2# {101305D6FB}> .
:DEFAULT) (#<FUNCTION #2# {101305D72B}> . :DEFAULT) (#<FUNCTION #2#
{101305D75B}> . :DEFAULT) (#<FUNCTION #2# {101305D78B}> . :DEFAULT)
(#<FUNCTION #2# {101305D7BB}> . :DEFAULT) (#<FUNCTION #2# {101305D7EB}> .
:DEFAULT) (#<FUNCTION #2# {101305D81B}> . :DEFAULT) . #3=((#<FUNCTION #2#
{101305D84B}> . :DEFAULT))) :TAIL #3#))) 20:
(LPARALLEL.KERNEL::%CALL-WITH-TASK-HANDLER #<unavailable argument>) 21:
((LAMBDA NIL :IN LPARALLEL.KERNEL::CALL-WITH-WORKER-CONTEXT)) 22:
(LPARALLEL.KERNEL::CALL-WITH-WORKER-CONTEXT #<FUNCTION (LAMBDA NIL :IN
LPARALLEL.KERNEL::ENTER-WORKER-LOOP) {100609DC8B}> #<FUNCTION FUNCALL>
#<LPARALLEL.KERNEL:KERNEL :NAME "lparallel" :WORKER-COUNT 8 :USE-CALLER NIL
:ALIVE T :SPIN-COUNT 2000 {10085C4723}> #S(LPARALLEL.KERNEL::WORKER
:HANDSHAKE/FROM-WORKER #S(LPARALLEL.CONS-QUEUE:CONS-QUEUE :IMPL
#S(LPARALLEL.RAW-QUEUE:RAW-QUEUE :HEAD NIL :TAIL NIL) :LOCK #<SB-THREAD:MUTEX
#1="Anonymous lock" free owner=0> :CVAR NIL) :HANDSHAKE/TO-WORKER
#S(LPARALLEL.CONS-QUEUE:CONS-QUEUE :IMPL #S(LPARALLEL.RAW-QUEUE:RAW-QUEUE
:HEAD NIL :TAIL NIL) :LOCK #<SB-THREAD:MUTEX #1# free owner=0> :CVAR
#<SB-THREAD:WAITQUEUE Anonymous condition variable {100583E583}>)
:EXIT-NOTIFICATION #S(LPARALLEL.CONS-QUEUE:CONS-QUEUE :IMPL
#S(LPARALLEL.RAW-QUEUE:RAW-QUEUE :HEAD NIL :TAIL NIL) :LOCK #<SB-THREAD:MUTEX
#1# free owner=0> :CVAR NIL) :THREAD #<SB-THREAD:THREAD "lparallel" RUNNING
{1005838263}> :RUNNING-CATEGORY :DEFAULT :INDEX 3 :TASKS
#S(LPARALLEL.SPIN-QUEUE:SPIN-QUEUE :HEAD (LPARALLEL.SPIN-QUEUE::DUMMY
(#<FUNCTION #2=(LAMBDA NIL :IN LPARALLEL.KERNEL::MAKE-CHANNELED-TASK)
{101305D57B}> . :DEFAULT) (#<FUNCTION #2# {101305D5AB}> . :DEFAULT)
(#<FUNCTION #2# {101305D5DB}> . :DEFAULT) (#<FUNCTION #2# {101305D60B}> .
:DEFAULT) (#<FUNCTION #2# {101305D63B}> . :DEFAULT) (#<FUNCTION #2#
{101305D66B}> . :DEFAULT) (#<FUNCTION #2# {101305D69B}> . :DEFAULT)
(#<FUNCTION #2# {101305D6CB}> . :DEFAULT) (#<FUNCTION #2# {101305D6FB}> .
:DEFAULT) (#<FUNCTION #2# {101305D72B}> . :DEFAULT) (#<FUNCTION #2#
{101305D75B}> . :DEFAULT) (#<FUNCTION #2# {101305D78B}> . :DEFAULT)
(#<FUNCTION #2# {101305D7BB}> . :DEFAULT) (#<FUNCTION #2# {101305D7EB}> .
:DEFAULT) (#<FUNCTION #2# {101305D81B}> . :DEFAULT) . #3=((#<FUNCTION #2#
{101305D84B}> . :DEFAULT))) :TAIL #3#))) 23: ((LAMBDA NIL :IN
LPARALLEL.KERNEL::MAKE-WORKER-THREAD)) 24: ((LABELS
**REDACTED**-THREADS::%BINDING-DEFAULT-SPECIALS-WRAPPER :IN
**REDACTED**-THREADS::BINDING-DEFAULT-SPECIALS)) 25: ((FLET SB-UNIX::BODY :IN
SB-THREAD::RUN)) 26: ((FLET "WITHOUT-INTERRUPTS-BODY-11" :IN SB-THREAD::RUN))
27: ((FLET SB-UNIX::BODY :IN SB-THREAD::RUN)) 28: ((FLET
"WITHOUT-INTERRUPTS-BODY-4" :IN SB-THREAD::RUN)) 29: (SB-THREAD::RUN) 30:
("foreign function: call_into_lisp_") 31: ("foreign function: funcall1")
 
 
What I am doing here?
 
Couldn't write to #<SB-SYS:FD-STREAM for "socket 127.0.0.1:40234, peer:
127.0.0.1:3306" {100C29B933}>: Broken pipe

This will occur often, perhaps 3 times in 5, and about 5hrs into the migration. It occurs whether importing from a MariaDB, or from a MySQL service.

2. Value too long

                                                                           :EXTENSION NIL) which is not the same as "mattermost.property_field_type", the type of current target database column "mattermost"."propertyfields"."type".
2026-08-24T06:07:18.375998Z WARNING Source column "mattermost"."PropertyFields"."attrs" is casted to type "text" which is not the same as "jsonb", the type of current target database column "mattermost"."propertyfields"."attrs".
2026-08-24T06:07:18.375998Z WARNING Source column "mattermost"."PropertyValues"."value" is casted to type "text" which is not the same as "jsonb", the type of current target database column "mattermost"."propertyvalues"."value".
2026-08-24T06:07:18.375998Z WARNING Source column "mattermost"."RecentSearches"."query" is casted to type "text" which is not the same as "jsonb", the type of current target database column "mattermost"."recentsearches"."query".
2026-08-24T06:07:18.375998Z WARNING Source column "mattermost"."RetentionIdsForDeletion"."ids" is casted to type "text" which is not the same as "character varying[]", the type of current target database column "mattermost"."retentionidsfordeletion"."ids".
2026-08-24T06:07:18.375998Z WARNING Source column "mattermost"."ScheduledPosts"."priority" is casted to type "varchar" which is not the same as "text", the type of current target database column "mattermost"."scheduledposts"."priority".
2026-08-24T06:07:18.375998Z WARNING Source column "mattermost"."Sessions"."props" is casted to type "text" which is not the same as "jsonb", the type of current target database column "mattermost"."sessions"."props".
2026-08-24T06:07:18.375998Z WARNING Source column "mattermost"."Teams"."type" is casted to type "team_type" which is not the same as "mattermost.team_type", the type of current target database column "mattermost"."teams"."type".
2026-08-24T06:07:18.375998Z WARNING Source column "mattermost"."Threads"."participants" is casted to type "text" which is not the same as "jsonb", the type of current target database column "mattermost"."threads"."participants".
2026-08-24T06:07:18.375998Z WARNING Source column "mattermost"."UploadSessions"."type" is casted to type "upload_session_type" which is not the same as "mattermost.upload_session_type", the type of current target database column "mattermost"."uploadsessions"."type".
2026-08-24T06:07:18.375998Z WARNING Source column "mattermost"."Users"."props" is casted to type "text" which is not the same as "jsonb", the type of current target database column "mattermost"."users"."props".
2026-08-24T06:07:18.375998Z WARNING Source column "mattermost"."Users"."notifyprops" is casted to type "text" which is not the same as "jsonb", the type of current target database column "mattermost"."users"."notifyprops".
2026-08-24T06:07:18.375998Z WARNING Source column "mattermost"."Users"."timezone" is casted to type "text" which is not the same as "jsonb", the type of current target database column "mattermost"."users"."timezone".
2026-08-24T06:07:18.375998Z WARNING Source column "mattermost"."Users"."mfausedtimestamps" is casted to type "text" which is not the same as "jsonb", the type of current target database column "mattermost"."users"."mfausedtimestamps".
2026-08-24T06:07:18.379998Z WARNING PostgreSQL warning: constraint "fk_retentionpolicieschannels_retentionpolicies" of relation "retentionpolicieschannels" does not exist, skipping
2026-08-24T06:07:18.379998Z WARNING PostgreSQL warning: constraint "fk_retentionpoliciesteams_retentionpolicies" of relation "retentionpoliciesteams" does not exist, skipping
2026-08-24T10:59:20.402872Z ERROR Database error 22001: value too long for type character varying(1024)
CONTEXT: COPY audits, line 16336011, column extrainfo: "failure - login_id=a very long collection of characters someone has chosen to use as their handle..."

The issue with this is that it’s changeable. From day to day it could be someone’s handle, or another some URL metadata, but it will fall over with the same outcome, at a different point in the process.

I’d be very grateful for some insights or guidance. At this stage we are considering at abandoning Mattermost altogether, as this imposed migration has very nearly incapacitated us.

2

@isacikgoz, have you seen any of these failure modes when migrating to Postgres?

3

Most recent attempt, with a runtime of ‘1 days 13h7m17.906s’ produced that link metadata error I mentioned above:

 mattermost.linkmetadata 1 0 4m10.855s

The next run it could just as easily be a long handle, or something else.

_Set Search Path_ also failed, but with no output. I didn’t use debug mode that run, to spare physical mem.

Thanks again for taking a look.

4

Just to confirm I am seeing two issues here: long running migrations seem to have an issue with network connection and failing without recovery. And the other problem is some data being too long and causing migration to fail.

To reduce long running migration, maybe you can try increasing row counts by 2x and observe memory but that would be an trial and error case and may end up costing too much time.

You can also individually migrate tables (exclude problematic ones to deal with them later on) but it requires changing EXCLUDING TABLE NAMES MATCHING clause in the load file to exclude and then remove that clause to migrate tables individually INCLUDING ONLY TABLE NAMES MATCHING ref.

For the error case in the link metadata, unfortunately there is a type diff between MySQL and Postgres schema detailed here. You can run migration-assist mysql <DSN> --fix-varchar ref. It should automatically trim the data that wouldn’t fit into Postgres schema.

I’d propose backing up before doing any destructive flags such as --fix-varchar to be on the safe side.

Let me know if these steps makes sense to you.

5

Many thanks, I will try this, starting from the last suggestion first. I’ll report back as I do. I will first wait for the current attempt to finish (in whatever state), by now some 12hrs in.