Hi, i want migrate my mattermost from mysql to postgresql, hut when i try migrate with pgloader its failed, need help
Here is my migration.load script
LOAD DATABASE
FROM mysql://root:root@localhost:3306/mattermost_test
INTO pgsql://mattermost:root@localhost: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_test; $$,
$$ TRUNCATE TABLE mattermost_test.systems; $$,
$$ DROP INDEX IF EXISTS mattermost_test.idx_posts_message_txt; $$,
$$ DROP INDEX IF EXISTS mattermost_test.idx_fileinfo_content_txt; $$
AFTER LOAD DO
$$ UPDATE mattermost_test.db_migrations set name=‘add_createat_to_teamembers’ where version=92; $$,
$$ ALTER SCHEMA mattermost_test RENAME TO public; $$,
$$ SELECT pg_catalog.set_config(‘search_path’, ‘“$user”, “”“$user”“, public”’, false); $$,
$$ ALTER USER mattermost SET SEARCH_PATH TO ‘“”“$user”“, public”’; $$;
Here is error message
pgloader version 3.6.3~devel
compiled with SBCL 2.1.11.debian
sb-impl::default-external-format :UTF-8
tmpdir: #P"/tmp/pgloader/"
2024-08-23T06:32:01.000000Z NOTICE Starting pgloader, log system is ready.
2024-08-23T06:32:01.010000Z INFO Starting monitor
2024-08-23T06:32:01.010000Z LOG pgloader version “3.6.3~devel”
2024-08-23T06:32:01.030001Z INFO Parsed command:
LOAD DATABASE
FROM mysql://root:root@localhost:3306/mattermost_test
INTO pgsql://mattermost:root@localhost: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_test; $$,
$$ TRUNCATE TABLE mattermost_test.systems; $$,
$$ DROP INDEX IF EXISTS mattermost_test.idx_posts_message_txt; $$,
$$ DROP INDEX IF EXISTS mattermost_test.idx_fileinfo_content_txt; $$
AFTER LOAD DO
$$ UPDATE mattermost_test.db_migrations set name=‘add_createat_to_teamembers’ where version=92; $$,
$$ ALTER SCHEMA mattermost_test RENAME TO public; $$,
$$ SELECT pg_catalog.set_config(‘search_path’, ‘“$user”, “”“$user”“, public”’, false); $$,
$$ ALTER USER mattermost SET SEARCH_PATH TO ‘“”“$user”“, public”’; $$;
2024-08-23T06:32:01.060001Z DEBUG CONNECTED TO #<PGLOADER.PGSQL:PGSQL-CONNECTION pgsql://mattermost@localhost:5432/mattermost {10083C9273}>
2024-08-23T06:32:01.060001Z DEBUG SET client_encoding TO ‘utf8’
2024-08-23T06:32:01.060001Z DEBUG SET maintenance_work_mem TO ‘128MB’
2024-08-23T06:32:01.060001Z DEBUG SET work_mem TO ‘12MB’
2024-08-23T06:32:01.060001Z DEBUG SET application_name TO ‘pgloader’
2024-08-23T06:32:01.070001Z NOTICE Executing SQL block for before load
2024-08-23T06:32:01.080001Z DEBUG CONNECTED TO #<PGLOADER.PGSQL:PGSQL-CONNECTION pgsql://mattermost@localhost:5432/mattermost {10083C9273}>
2024-08-23T06:32:01.080001Z DEBUG SET client_encoding TO ‘utf8’
2024-08-23T06:32:01.080001Z DEBUG SET maintenance_work_mem TO ‘128MB’
2024-08-23T06:32:01.080001Z DEBUG SET work_mem TO ‘12MB’
2024-08-23T06:32:01.080001Z DEBUG SET application_name TO ‘pgloader’
2024-08-23T06:32:01.080001Z DEBUG BEGIN
2024-08-23T06:32:01.080001Z SQL ALTER SCHEMA public RENAME TO mattermost_test;
2024-08-23T06:32:01.080001Z SQL TRUNCATE TABLE mattermost_test.systems;
2024-08-23T06:32:01.090001Z SQL DROP INDEX IF EXISTS mattermost_test.idx_posts_message_txt;
2024-08-23T06:32:01.090001Z SQL DROP INDEX IF EXISTS mattermost_test.idx_fileinfo_content_txt;
2024-08-23T06:32:01.100001Z LOG Migrating from #<MYSQL-CONNECTION mysql://root@localhost:3306/mattermost_test {10083C8503}>
2024-08-23T06:32:01.100001Z LOG Migrating into #<PGSQL-CONNECTION pgsql://mattermost@localhost:5432/mattermost {10083C9273}>
2024-08-23T06:32:01.120001Z DEBUG CONNECTED TO #<MYSQL-CONNECTION mysql://root@localhost:3306/mattermost_test {10083C8503}>
2024-08-23T06:32:01.130001Z SQL MySQL: sending query: set net_read_timeout = 120;
2024-08-23T06:32:01.130001Z SQL MySQL: sending query: set net_write_timeout = 120;
2024-08-23T06:32:01.130001Z SQL MySQL: sending query: – params: db-name
– table-type-name
– only-tables
– only-tables
– including
– filter-list-to-where-clause incuding
– excluding
– filter-list-to-where-clause excluding
select c.table_name, t.table_comment,
c.column_name, c.column_comment,
c.data_type, c.column_type, c.column_default,
c.is_nullable, c.extra
from information_schema.columns c
join information_schema.tables t using(table_schema, table_name)
where c.table_schema = ‘mattermost_test’ and t.table_type = ‘BASE TABLE’
and (table_name NOT REGEXP 'IR_' and table_name NOT REGEXP 'focalboard' and table_name != 'schema_migrations' and table_name != 'db_migrations' and table_name != 'db_lock' and table_name != 'configurations' and table_name != 'configurationfiles' and table_name != 'db_config_migrations')
order by table_name, ordinal_position;
2024-08-23T06:32:01.140001Z ERROR mysql: 76 fell through ECASE expression.
Wanted one of (2 3 4 5 6 8 9 10 11 14 15 17 20 21 23 27 28 30 31 32 33
35 41 42 45 46 47 48 49 50 51 52 54 55 56 60 61 62 63 64
65 69 72 77 78 79 82 83 87 90 92 93 94 95 96 97 98 101
102 103 104 105 106 107 108 109 110 111 112 113 114 115
116 117 118 119 120 121 122 123 124 128 129 130 131 132
133 134 135 136 137 138 139 140 141 142 143 144 145 146
147 148 149 150 151 159 160 161 162 163 164 165 166 167
168 169 170 171 172 173 174 175 176 177 178 179 180 181
182 183 192 193 194 195 196 197 198 199 200 201 202 203
204 205 206 207 208 209 210 211 212 213 214 215 223 224
225 226 227 228 229 230 231 232 233 234 235 236 237 238
239 240 241 242 243 244 245 246 247 254).
2024-08-23T06:32:01.140001Z NOTICE Executing SQL block for after load
2024-08-23T06:32:01.150001Z DEBUG CONNECTED TO #<PGLOADER.PGSQL:PGSQL-CONNECTION pgsql://mattermost@localhost:5432/mattermost {10083C9273}>
2024-08-23T06:32:01.150001Z DEBUG SET client_encoding TO ‘utf8’
2024-08-23T06:32:01.160001Z DEBUG SET maintenance_work_mem TO ‘128MB’
2024-08-23T06:32:01.160001Z DEBUG SET work_mem TO ‘12MB’
2024-08-23T06:32:01.160001Z DEBUG SET application_name TO ‘pgloader’
2024-08-23T06:32:01.160001Z DEBUG BEGIN
2024-08-23T06:32:01.160001Z SQL UPDATE mattermost_test.db_migrations set name=‘add_createat_to_teamembers’ where version=92;
2024-08-23T06:32:01.160001Z SQL ALTER SCHEMA mattermost_test RENAME TO public;
2024-08-23T06:32:01.160001Z SQL SELECT pg_catalog.set_config(‘search_path’, ‘“$user”, “”“$user”“, public”’, false);
2024-08-23T06:32:01.160001Z SQL ALTER USER mattermost SET SEARCH_PATH TO ‘“”“$user”“, public”’;
2024-08-23T06:32:01.160001Z LOG report summary reset
table name errors read imported bytes total time read write
before load 0 4 4 0.030s
fetch meta data 0 0 0 0.000s
after load 0 4 4 0.020s
Total import time ✓ 0 0 0.020s