pgloader repeatedly disconnecting from db during migration attempt about pgloader HOT 3 OPEN

Something worth noting is that if I use the INCLUDING ONLY TABLES parameters I can move a singe table at a time, but if I remove that and drop workers to 1 the migration seems to hang, and at 2 the migration behaves as described above.

from pgloader.

I'm Getting the same error for Mattermost Database migration, My setup is as follows,

You can find the details here: https://forum.mattermost.com/t/migration-from-mysql-8-to-postgres-database/17166

I've 2 docker containers one for MySQL 8 and for Psql15, running on my VM (2 core, 4 GB), I've tried tweaking the WITH configurations like

WITH data only,
     workers = 4, concurrency = 1,
     multiple readers per thread, rows per range = 5000,
     create no tables, create no indexes,
     batch size = 50MB, prefetch rows = 500,
     preserve index names

And the pgloader command to.

pgloader --dynamic-space-size 1564 migration.load > migration.log

I have the original MySQL database sizes about 6 GB (MySQL data-directory size)…

But after running some time the pgloader CPU usage reaches very high and it stops with error…

Database error: Connection to database server lost.

It is happening for me after 2GB of data transferred to postgress data directory, I’ve tried tuning the values in the WITH, reducing --dynamic-space-size also even tried increasing the server capacity,

But getting the connection lost error when reaching the 1.8 GB - 2 GB mark.

from pgloader.

@Akshaychdev Let me know if you've had any luck. I found a way around this (no thanks to my weeks long case with MM support) for my instance and I'm happy to share to see if it will work for more folks.

I found that only the posts table was causing crashes, so I did a 2 part move and some data transforms after the fact.

1. Create a .load file for the posts table migration and run the migration against your instance. This should work without crashing

`
LOAD DATABASE
FROM mysql://user:PASSWORD@localhost:3306/mattermost
INTO pgsql://user:PASSWORd@localhost:5432/mattermost

				WITH
				  workers = 2,
				  concurrency = 1,
				  preserve index names
				
				SET PostgreSQL PARAMETERS
				  maintenance_work_mem to '512MB',
				  work_mem to '48MB'
				
				CAST type int when (= precision 11) to integer drop typemod,
				     type bigint when (= precision 20) to bigint drop typemod,
				     type text to varchar drop typemod,
				     type json to jsonb drop typemod
				
				SET MySQL PARAMETERS
				  net_read_timeout  = '1500',
				  net_write_timeout = '1500'
				
				INCLUDING ONLY TABLE NAMES MATCHING 'posts'
				
				BEFORE LOAD DO
				  $$ ALTER SCHEMA public RENAME TO mattermost; $$,
				  $$ ALTER TABLE public.posts ALTER COLUMN message   TYPE character varying(65535) COLLATE pg_catalog."default"; $$,
				  $$ ALTER TABLE public.posts ALTER COLUMN hashtags  TYPE character varying(1000)  COLLATE pg_catalog."default"; $$,
				  $$ ALTER TABLE public.posts ALTER COLUMN filenames TYPE character varying(4000)  COLLATE pg_catalog."default"; $$,
				  $$ ALTER TABLE public.posts ALTER COLUMN fileids   TYPE character varying(3000)  COLLATE pg_catalog."default"; $$
				
				AFTER LOAD DO
				        $$ ALTER SCHEMA mattermost RENAME TO public; $$

;
`

2. Then update the postgres tables constraint to match the what the morph command sets. I also took some time to compare the schema set by the above to the schema the morph command sets to make sure they match. You can do this and make adjustments, but it will probably work either way
   ALTER TABLE public.posts RENAME CONSTRAINT random name TO posts_pkey;
3. Use your previous pgloader command to move all other tables besides posts (either with an include only tables or exclude tables command'
4. Update your matttermost config driver/dsn to the new database
5. profit?

from pgloader.