I've been fighting with getting a database moved from #MySQL to #Postgres for about a week now because of a dumb issue with Directus, and I finally fucking got it. The trick was, I think, to just make
pgloader run slower.
I tried using the default settings that the pgloader docs suggest, only to have it shit the bed over and over at different spots, and throw errors about not being able to connect to either the PG server or the MySQL server, or simply hanging until I
kill -9 sometimes -- it's a bastard) the fucking thing.
Anyway, here's the
load file I ended up using.
LOAD DATABASE from mysql://username:password@host:port/database into postgresql://username:password@host:post/database WITH include drop, create tables, create indexes, reset sequences, workers = 1, concurrency = 1, multiple readers per thread, rows per range = 1 SET PostgreSQL PARAMETERS maintenance_work_mem to '128MB', work_mem to '12MB', search_path to 'database, public, "$user"' SET MySQL PARAMETERS net_read_timeout = '120', net_write_timeout = '120' CAST type char when (= precision 36) to uuid drop typemod
Slap that shit in a file (obviously replacing all the necessary bits with your own stuff), and the fire it up with pgloader like:
$ pgloader data-loader.load
Then sit back and wait for your database to migrate :-)
I hope this helps someone in the future, because the documentation, and the
--debug output of
pgloader didn't do me a damn bit of good. It was by pure luck and chance that I thought "let's try cranking that
concurrency down, and that
rows per range, too, and see what happens. Well la-dee-fucking-dah, it worked.
Fun side note: Since this was for Directus, of course it presented another set of challenges once the database was migrated. Starting the server resulted in a
Database doesn't have Directus tables installed error with zero other details. Turning the
trace shed a little more light, which led me to this comment on GitHub. A tweak to my
.env, like so:
DB_SCHEMA="my database name" DB_CUSTOM_SCHEMA="my database name" DB_SEARCH_PATH="array:my database name,public"
And 💥 the server runs!!
Fuck you, computers. Fuck you to death.