Switch to PostgreSQL database?

[JimboJoe]

Considering how PostgreSQL seems to have better performance than MySQL and to be more reliable than MySQL (especially on SD-based ARM devices), maybe that move (though complex to consider migration-wise!) should be discussed... https://docs.nextcloud.com/server/13/admin_manual/configuration_database/linux_database_configuration.html

[maniackcrudelis]

Is the helpers to manage Postgres are stable yet ?

[JimboJoe]

Except some warnings to prevent, they look stable (and are being used in an official app).

[maniackcrudelis]

So, let's try then. At least what sure is mysql isn't really reliable...

[Josue-T]

Hello, So, in this case we'll need to migrate the actual database from mysql to postresql ? Do you think that it's easily possible ? Except the migration I also think that I could be a good Idea.

[JimboJoe]

Fortunately, Nextcloud has taken care of that migration use case! https://www.techandme.se/we-migrated-to-postgresql/ https://docs.nextcloud.com/server/13/admin_manual/configuration_database/db_conversion.html

[autra]

FWIW, I've tested such a migration more or less following this post. For me it worked perfectly.

TL;DR I created a db nextclouddb and a user nextcloud in postgres and ran:

sudo -u nextcloud php ./occ db:convert-type --all-apps --password "nextcloud" pgsql nextcloud localhost nextclouddb

Apparently, to work correctly with the text app, you need Nextcloud 21.0.3.

Also, for some reason at first I had this error

An unhandled exception has been thrown:
OC\HintException: [0]: Memcache \OC\Memcache\APCu not available for local cache (Is the matching PHP module installed and enabled?)

I fixed it by replacing 'memcache.local' => '\\OC\\Memcache\\APCU', by 'memcache.local' => '\\OC\\Memcache\\Redis', in the config file. I followed this comment, but as the author I didn't dig why apcu was off there. As we are already using redis elsewhere for locking apparently, I didn't bother :-)

[autra]

It might be worthy to test with 2FA enabled, as someone reported having trouble with it when migrating.

[bill-mcgonigle]

FYI 'dbport' in config.php doesn't get updated by the migration script. 3306->5432 in my case.

The speed is so much better - if you're on the fence, do it (I was just trying to get away from the bizarre maintenance requirements of mysql).

[csolisr]

Just a quick question: do I need to modify any scripts to make the automated database backup keep working? As I suppose those will still be pointing to the MariaDB database?

[lapineige]

Hello, Any update on this ? Is this still planned ? pending ? wontfix ?

[alexAubin]

@lapineige : i guess it's still on the table, but considering the magnitude of the change, we have to be super-careful about it ... Maybe at least if we were in V2 packaging we could display a pre-upgrade disclaimer for this upgrade

[lapineige]

So V2 first :D #559

I suppose we have to make a lot of manual testing and so on... Am I right to say that as long as we have backups trying these migrations scripts could do no harm if it fails ? (we could just remove everything and restore the backup ?) At least as long as it doesn't work at all, instead of being left in a partially faulty state ?

I'm asking to know if I could be testing this in an environment that is not for 100% dev use...

[ericgaspar]

#597 should be functional.