Hello,
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
LOAD DATABASE
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
