Migration MySQL to PostreSQL problem

Troubleshooting
1

Hello,
I’m asking for help migrating my Mattermost database from MySQL to PostgreSQL. I’m using the Automated PostgreSQL Migration guide at: Automated PostgreSQL migration - Mattermost documentation I followed the steps in the guide, but when I try to complete step 4 – Run PGloader – I get an ESRAP-PARSE-ERROR error every time. Below is the migration.load configuration file prepared by migratio-assist:

LOAD DATABASE
FROM       mysql://mmuser:PASSWORD@IP_ADDRESS:3306/mattermost
INTO       pgsql://mmuser:PASSWORD@IP_ADDRESS:5432/mattermost

WITH data only,
workers = 8, concurrency = 1,
multiple readers per thread, rows per range = 10000,
prefetch rows = 10000, batch rows = 2500,
create no tables, create no indexes,
preserve index names

SET PostgreSQL PARAMETERS
maintenance_work_mem to '128MB',
work_mem to '12MB'

SET MySQL PARAMETERS
net_read_timeout  = '120',
net_write_timeout = '120'

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_>, ~, ~, '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; $$;

I am using pgloader, according to the instructions in the docker version: mattermost/pgloader:latest, and I downloaded migration-assist from github in the highest available version -v0.8

pgloader returns an error:

 ESRAP-PARSE-ERROR: At
 WITH data only, workers = 8, concurrency = 1, 

                                     ^ (Line 6, Column 29, Position 209)
2

Thanks for sharing the full configuration - could you confirm the exact Docker command and pgloader --version output, and meanwhile try removing multiple readers per thread, rows per range = 10000, since that option requires concurrency greater than 1, then rerun Step 4 of the Automated PostgreSQL migration guide?

3

Hi, thank you in advance for your attention.

After removing the “multiple readers per thread, rows per range” parameter, as you suggested, pgloader stops with the error:

ESRAP-PARSE-ERROR: At 

SET MySQL PARAMETERS

^ (Line 16, Column 0, Position 404)

I’m using pgloader docker version:

Pgloader version "3.6.3e06d1b"
compiled with SBCL 2.2.9.debian