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 ctrl-c
(or 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 workers
and 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 LOG_LEVEL
to 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.