Migrating MySQL to PostgreSQL with pgloader

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.

  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

    maintenance_work_mem to '128MB',
    work_mem to '12MB',
    search_path to 'database, public, "$user"'

    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.

Comments (0)

There are no comments to display.

Leave A Comment