Return on experience, migration from MySQL to Postgresql


I have migrated my nextcloud 22 intance from MySQL to Postgreql
While this activity could seem trivial using the built-in migration command

occ db:convert-type --all-apps --clear-schema --password <password> pgsql <user> <pg host> <db name>

it turns out in my case that the migration of data was real slow.
activity tables (~40Mo) would take 2hrs, filescache (1G) would take 4 days !

So I came with an alternative method that I describe here, for the sake of anyone needing this …

I first created the schema with the occ db:convert-type, hiting [n] when asking to start.
This made my pgsql database with a clean schema
I also noted the tables that would not be converted by the db:convert-type.

I then installed pgloader

create a loader.load file with bellow content

        FROM mysql://<mysqluser>:<mysqlpass>@<mysql host>/<mysql db name>
        INTO pgsql://<pg user>@<pg host>/<pg db name>
        WITH create no tables, disable triggers, preserve index names, include no drop, truncate, create indexes

        CAST type datetime to timestamptz drop default drop not null using zero-dates-to-null,
         type date drop not null drop default using zero-dates-to-null
EXCLUDING TABLE NAMES MATCHING  ~/^audioplayer_.+/,~/^contacts_cards_properties$/, ~/^fulltextsearch_.+$/,~/^news_.+$/,~/^ocsms_.+$/,~/^passman_.+$/,~/^phonetrack_.+$/,~/^privatedata$/,~/^radio_stations$/

the Exludeing table names regex I set to not migrate the tables that would not be migrated by the occ db:convert-type

the command execution took only 6 minutes

My setup is Nextcloud running docker, so:

  • I added a container for target pgsql
  • I added a container to run pgloader

Note: pgloader seems to have a bug with pgsql database password, so I had to edit pg_hba.conf to add the host where pgloader was running to trust mode so that it would work without a password

That seems like an absurdly long time. You sure it would have actually taken that long and that it wasn’t just a bad estimate?

And if you don’t mind my asking, what’s the motivation for switching to postgresql?

wouldn*t that make more sense to be filed under Howto and make it a Wiki?

Hi Larry,
No the estimates where rigth, Ieft the migration go on for a full day.
What bother me is the maintenance window beeing to long where I could not use the server

As for the motivation to switch to pg, I read several articles mentioning the better performance of pg

not to mention that I use pg for work on a daily basis …

yes this is what I was willing to do, but for some reason I was not allowed to post over there…

done :wink: :wink:

1 Like

I try you config but I got an error…

FATAL error: pgloader failed to find schema "wolke7" in target catalog.
An unhandled error condition has been signalled:
   pgloader failed to find schema "wolke7" in target catalog.

What I am doing here?

pgloader failed to find schema "wolke7" in target catalog.