NextCloud DB Migration to PostgreSQL (#100DaysToOffload Day 9)
So I'm getting todays post out early because i have a feeling the rest of the day will get away from me. It's also going to be a quick one.
(Late)Last night i decided to finally migrate my Nextcloud server's DB to PostgreSQL from Mysql. No specific reason other than I've heard many times that PostgreSQL performs/scales better. I've been wanting to do it for a while but figured it would be a long dangerous process that would almost require a reinstall of the server. Thanks to a toot on mastodon i found out that Nextcloud has an easy tool to do the migration. So after doing regular updates on the server i decided to do the migration. Seemed simply enough. Just run the following:
occ db:convert-type --all-apps pgsql nextcloud 127.0.0.1 nextcloud
That ended badly
It was so late when i was doing this and i was very tired. The install was not broken so i just turned off maintenance mode and went to bed with the idea that i would spend much time this morning figuring out what was going wrong and how to fix it. I quickly posted to the Nextcloud IRC chan before i went to bed.
There was no response when i got up. I had a closer look at the error message and realized it was referring to a specific app. One that i had installed but never actually used. The Following oc_ocdownloader_queue_ID_seq pointed me in that direction. So i disabled/removed that app(oc Downloader) and deleted the 3 related tables from Mysql DB then re-ran the migration as follows:
occ db:convert-type --clear-schema --all-apps pgsql nextcloud 127.0.0.1 nextcloud
Had to add the --clear-schema option since the schema had already been created in the PostgreSQL DB. All went well this time. For good measure i ran
occ maintenance:repair that also went well.
Turned off maintenance mode and opened up the app. I right away noticed that things where loading faster. I navigated over to the admin overview page and it had a few recommendations. Had to run the following:
occ db:add-missing-indices
This could be done without enabling maintenance mode. Also had to run:
occ db:convert-filecache-bigint
This one had to be done while in maintenance mode.
After that all was happy. I was able to reinstall the offending app. I actually set it up this time and might actually put it to use.
I must say that things do seem to load faster(placebo or not). Overall a great migration. I'm not sure why NextCloud officially recommends Mysql over PostgreSQL but i would recommend to use PostgreSQL.
The only thing on that server left using Mysql is this blog(Writefreely). I'm going to see if that can use PostgreSQL as well. That way i can have only one DB backend running.
That is all for today(Ok maybe not a quick one after all).
Until tomorrow be safe!
Until next time. Stay safe!