# Failed migrations from mysql to postgresql **URL:** \ **Category:** Uncategorized\ **Created:** [August 23, 2024, 6:41am UTC](https://forum.mattermost.com/t/failed-migrations-from-mysql-to-postgresql/19364 "2024-08-23T06:41:41Z")\ **Posts on this page:** 1\ **Page:** 1
**Author:** ![alamfalinwa](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.mattermost.com/alamfalinwa/32/8172_2.png) [@alamfalinwa](https://forum.mattermost.com/u/alamfalinwa)\ **Post date:** [August 23, 2024, 6:41am UTC](https://forum.mattermost.com/t/failed-migrations-from-mysql-to-postgresql/19364/1 "2024-08-23T06:41:41Z")
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 ~\, ~, ‘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 ~\, ~, ‘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 #\ 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 #\ 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 #\ 2024-08-23T06:32:01.100001Z LOG Migrating into #\ 2024-08-23T06:32:01.120001Z DEBUG CONNECTED TO #\ 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 #\ 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