Recommend PostgreSQL instead of MariaDB/MySQL

[enoch85]

I wish PostgreSQL in general was better supported. In MariaDB/MySQL you have to mess around with UTF8mb4 if you want 4-byte support, which is default in PostgreSQL UTF8.

PostgreSQL feels like a better DB in general. Would be nice if Nextcloud decided to go down that path instead of recommending a less advanced (and IMHO) worse DB.

I'm writing this after I have been messing with MariaDB for hours to activate 4 byte support, which only took a few minutes in PostgreSQL (aka apt install postgresql). According to the IRC channel (#posgresql) UTF8mb4 is just something that MariaDB/MySQL came up with becuase they messed up the original code and had to make a quick fix to solve it. That doesn't sound good to me. Also UTFmb4 is considered "experimental" according to docs which isn't the case with PostgreSQL as it just works out of the box.

@MorrisJobke said that MariaDB/MySQL was performing better when tested in a clustered environment, but my guess is that that test was some time ago. Maybe you should consider making PostgreSQL the recommended DB after all?

Also, this is not just about UTF8mb4, I'm talking generally. There are a lot to gain in using PSQL any other DB IMHO. According to several hours of reading I did comparing and testing the alternatives.

Taken from here: #5909 (comment)

[MorrisJobke]

PostgreSQL feels like a better DB in general. Would be nice if Nextcloud decided to go down that path instead of recommending a less advanced (and IMHO) worse DB.

This is not about feeling. This is the recommendation, because in bigger setups we had better experience with it and we know how to handle.

Nevertheless Postgres and Mysql are supported and you should be free to use any of them.

And we will not recommend something were we have less detailed expertise. So I will close this for now. If there is a bug with Postgres: report it.

[jospoortvliet]

Yeah, I get advocacy for PGSQL - and you can do that, this is an open project. Organize and execute testing, write blogs about how to install and optimize it and so on! I'm happy to promote it. Heck, write a blog post "making the case for PostgreSQL with Nextcloud" and I'll publish it on our blog. But I'll allow any MySQL or MariaDB supporter to write one about MySQL/MariaDB of course!

[enoch85]

I was worth a try. :)

[enoch85]

Also @MorrisJobke

and we know how to handle.

were we have less detailed expertise

That proves to me that the choice is quite personal for you as well. Of course you tend to recommend something that you are more familiar with and can support in a better way than the alternative. I do the same, all the time. Like I recommend Nextcloud instead of ownCloud because I think that Nextcloud is the better choice. That's also why I now, after I tested it myself, will recommend PostgreSQL rather than something else.

It's quite obvious to me that MySQL MariaDB is recommended because most of the Nextcloud devs are familiar with it, and because of that also have better knowledge in how to set it up. That might explain why it worked better than PostgreSQL.

I will not fight you about this and it's not my intention to waste your time or anything like that, but again I've done a lot of reading and testing since I now also maintain a PostgreSQL VM and I can say from my own experience that have noticed that PostgreSQL is faster, easier to setup (at least I think as it's fewer lines of bash commands), more secure, and a more advanced database in general. Maybe "feel" was the wrong word, maybe I should say --^ instead.

Nevertheless, I'm grateful that Nextcloud supports PostgreSQL but recommending a database also mean that app developers won't bother to support the alternative if they don't have to. Most of the apps work with PostgreSQL but some don't and a warning pops up that it's only working with... yeah exactly, MySQL or MariaDB. So that's why I made this post, to encourage you to do some more tests with PostgreSQL and maybe, just maybe notice the same things as I did.

Disclaimer; I have not tested in clustered or bigger environments than 320 users.

@jospoortvliet Writing a blog post as we speak. Feel free to retweet or even post it on the Nextcloud blog if you don't feel it's to harsh or something like that. I'm also open for suggestions if you want me to improve it before you post it.

[enoch85]

And here it is: https://www.techandme.se/we-migrated-to-postgresql/

[jteib]

Is there somewhere I can find a list of unsupported apps on PostgreSQL?

[enoch85]

@jteib Download the VM and try them, then maybe you can publish the list in the Nextcloud documentation?

I could give you the PostgreSQL VM for free ($16) with a promise that you do that. :)

[jteib]

@enoch85 sounds like a deal to me! Also I'd be happy to contribute :)

[enoch85]

@jteib Please send me an email to daniel at techandme dot se.

[mcwhelan]

It would really be nice if there was more clarity on this issue. I installed Nextcloud from a prepackaged VM image and the database of choice was PostgreSQL. But for the last few versions I've been running into a problem w/ the 2-factor admin tool written by Christoph Wurst. It doesn't work unless you're using MySQL or SQL-Lite. Looking through the forum exchanges on this, since this is not an "Official" Nextcloud app (even though Christoph works for Nextcloud) it is a "low-priority" issue. I got really excited for NC17 when I saw one of the new features involved support for an admin generated single use 2FA code to help users who were locked out of their account. I upgraded our company to NC17 and that section of the "Security" settings just isn't there. NC17 release screenshots show a new section titled "Two-factor admin" between the "Password Policy" and "Oauth 2.0 Clients" sections. We just don't have that section at all. Is this because we're still on PostgreSQL? Who knows. I can't find any documentation about it. It seems if this is a new "Official" feature then it should work across all supported database platforms that NC supports. Searching the vast Interwebs, it seems I'm the only one with this issue. Grrr....

[kesselb]

What kind of clarification do you have in mind? How can we improve it?

Nextcloud is working with PostgreSQL, MariaDB/MySQL, Oracle Database and SQLite. Actually there is a nice table at https://docs.nextcloud.com/server/latest/admin_manual/installation/system_requirements.html with the system requirements. Unfortunately it's broken right now but I already send a patch 🤣

A list of supported apps is here: https://docs.nextcloud.com/server/latest/admin_manual/installation/apps_supported.html

We just don't have that section at all. Is this because we're still on PostgreSQL

I think so. Probably good to subscribe to: nextcloud/twofactor_admin#35

It seems if this is a new "Official" feature then it should work across all supported database platforms

@jospoortvliet I second that. Please don't announce such features if they are not available for everyone or not officially supported.

@mcwhelan please note that the best way to ask questions is https://help.nextcloud.com. It's unlikely that people react to closed issues. Also this topic was about something different (should we recommend PostgreSQL instead of MariaDB / MySQL).

[henrystrick]

Thank you for threshing some of this out. And I must say, what came out from various contributors to nextcloud did not really make me enthusiastic.

The link provided about system requirements by @kesselb has in the URL "latest". If you get there via google (as I did before finding this), you can find your way to the same link to the admin manual with "17" in it - that gives different info, i.e. names only PHP 7.3 and not PHP7.4. The "latest" link shows PHP7.4. The link with "18" in it does not show this table with specific recommendations at all. This does not help a person who is about to make a new install (as I am), and wants to make decisions. PHP versions for new lemp / lamp installations are notoriously tricky. And of course you can try out if it works. But the whole purpose of system requirements is that you can consult the manual, and get it "right first time". It's not a big thing, and it IS a big thing in terms of the coherence of the nextcloud documentation.

And by the way, if in the version 18 manual you go to "Linux installation", it only mentions PHP 7.3, not 7.4. I have now standardised on upgrading to PHP7.4 on all my servers. Having to make an exception for nextcloud is really unpleasant. Having to make an exception without the need for it is worse.

My guess is that NC18 definitely works with PHP7.4. And that NC17 probably also worked with it. But I can't be certain.

I very much love nextcloud, and recommend it frequently to others. But for speed and for production usage, it is so desirable that things like the System requirements are clear and up to date. And actually represent the correct "state of the art"...

And finally - YES, I know that this was not the topic of this question. But it was pulled into the answers. And there are similar questioins about being able to use up to date versions of Postgres and MariaDB too. Can you use Postgres 12 with NC18? Postgres 12.2? MariaDB is at 10.3 in one of my servers. That's two ahead of the 10.1 that you mention... And the nagging thing from above is, I don't know if it would be necessary to use an earlier version to accommodate nextcloud, or not....

[kesselb]

please note that the best way to ask questions is https://help.nextcloud.com. It's unlikely that people react to closed issues.