For some time now we have developed different types of VMs, and our main focus have always been the Nextcloud VM. As the quality of the VM keeps evolving we also struggle sometimes to get things right. We want the end user to have the best possible Nextcloud experiance, and we are responsible for at least 100-200 downloads per day (usually around 150) so yeah, stuff must be 100%. One of those things were to enable UTF8mb4 on MySQL/MariaDB, which still is an experimental feature in Nextcloud. We tested, tested, and tested until we got the scripts stable enough to release it. We later decided to move from MySQL to MariaDB after a user requested it which is considered even more experimental when using UTF8mb4. We tested even more, and finally got it to such a level that we now think it’s stable and works great. Though we also noticed the downside…
Why?
Working with this got us thinking, why make 4-byte support a feature? But first and foremost – why didn’t the MySQL developers enable 4-byte support by default – without having to enable it afterwards. So we started to look around and it led us to PostgreSQL (PGSQL). We had previously heard some good things about it and some of the Nextcloud app developers made some hints about PGSQL being a better database in general, but as we were not familiar with it, we didn’t start to look at it for real until the MySQL stuff started to get annoying for real. Tweaking and fixing, and tweaking even more. It should just work from start without having to enable and disable features, right?
As we didn’t know so much about PGSQL we decided to ask the experts in #postgresql on IRC. They told us many things that surprised us. One of those were that the MySQL developers actually messed things up with UTF8 and had to add 4-byte support afterwards(!) and even according to the MySQL documentation they describe possible issues when *converting* (UTF8 should just be UTF8. Convert UTF8 to UTF8? Come on!) from UTF8 to UTF8mb4. It sounded bad, and almost not true. So we decided to read about it ourselves, and as it turns out the IRC guys were right. PGSQL is a more advanced database in general and handles stuff more efficiently than MySQL/MariaDB, which also is proven in different benchmark tests that you can find easily by Googling.
The more we read about PGSQL the more interested we got in making it happen for the Nextcloud VM. So we decided to give it a try, made a new branch, and started developing a PGSQL VM. We were kind of amazed since the end result was removing a lot if MariaDB lines and replace them with just a few PGSQL lines, this is a good example. So in the end we did more with less as we didn’t have to enable and tweak and tweak even more, everything is just perfect out of the box (thank you Ubuntu). Not only that, PGSQL is faster, it actually flies! Folders with many files loads faster, a gallery with many images loads faster, addressbooks with many contacts loads faster, and making our stress test with the desktop client were faster. Except that it also gives a feeling of being more secure. No need to save the root password in a separate file like we did with MariaDB, with PGSQL you authenticate with the UNIX user. Yeah you can do that with MariaDB as well, but again, it’s a feature, PGSQL does that by default. So what would you choose, a database which you have to enable support for basic functionality, or one that has everything out of the box with fewer commands? The choice for us was easy – we chose PGSQL and today we did a full migration of our own Nextcloud server. We are still no experts on the subject, but what we found was enough to convince us.
Are you convinced?
Even though MariaDB/MySQL is the recommended database for Nextcloud according to the Nextcloud documentation, to us PGSQL is the better choice. Thankfully PGSQL works very good with Nextcloud and the only downside is that not all apps are supported, but that’s just a few. All of the official apps are supported and the few that isn’t aren’t developed by Nextcloud or developers that works close with Nextcloud, so nothing to worry about.
If you are convinced to change then great! The process is very easy and Nextcloud even have built in support for converting SQLite / MySQL / MariaDB to PGSQL (or the other way around) with the occ command. To make the leap smaller and easier for you we made a script that we used ourselves to make the change. Remember to test it on a clone of your server before you do it in production, you never know if and when stuff go wrong, and it’s better to be safe than sorry. If you are running your server as a VM it will only take a little while, depending on the size of course. In our case it took 8 hours to clone, but our setup holds pretty much data :)
Before you start Apache2 after the script is run, be sure that you have removed everything related to MySQL/MariaDB. Here we go!
Script
Before you run the script, make sure that you are root.
$~: sudo -i
Then copy this into a file and run it with bash:
PLEASE REMEMBER TO KEEP BACKUPS IN CASE ANYTHING WOULD GO WRONG.
#!/bin/bash
## Convert to PostgreSQL ##
# Tested on Ubuntu Server 18.04
# Make sure only root can run our script
if [[ $EUID -ne 0 ]]; then
echo "This script must be run as root, please type sudo -i and run it again." 1>&2
exit 1
fi
service apache2 stop
. <(curl -sL https://raw.githubusercontent.com/nextcloud/vm/master/lib.sh)
NCUSER=pgsql_user_nextcloud
# Install PostgreSQL
apt update
check_command apt install postgresql-10
# Create DB
cd /tmp || exit
sudo -u postgres psql <<END
CREATE USER $NCUSER WITH PASSWORD '$PGDB_PASS';
CREATE DATABASE nextcloud_db WITH OWNER $NCUSER TEMPLATE template0 ENCODING 'UTF8';
END
check-command service postgresql restart
# Convert DB
sudo -u www-data php /var/www/nextcloud/occ db:convert-type --all-apps --password "$PGDB_PASS" pgsql $NCUSER 127.0.0.1 nextcloud_db
sudo -u www-data php /var/www/nextcloud/occ maintenance:repair
# Remove MySQL / MariaDB
read -p "Are you sure you want to remove MySQL?" -n 1 -r
echo
if [[ $REPLY =~ ^[Yy]$ ]]
then
apt clean
apt update
dpkg -r mysql-client-5.7
dpkg -r mysql-server-5.7
dpkg -r libmysqlclient20:i386
dpkg -r libmysqlclient20:amd64
dpkg -r libmysqlclient18:amd64
dpkg -r mysql-common
dpkg -r mysql
apt purge mysql\* mariadb\* libmysql\* libmariadb\*
apt autoremove -y
rm -R /var/lib/mysql /var/lib/mysql-files /var/lib/mysql-keyring /var/mysql-upgrade /etc/mysql /var/lib/mysql
fi
# Remove mysql.utf8mb4
if grep -q "mysql.utf8mb4" /var/www/nextcloud/config/config.php
then
sed -i "s|'mysql.utf8mb4' => true,||g" /var/www/nextcloud/config/config.php
sed '/^\s*$/d' /var/www/nextcloud/config/config.php
fi
# Show password
echo "Your new PostgreSQL password is: $PGDB_PASS. It's also written in your Nextcloud config.php file."
# Start Apache2
echo "Apache will start in 30 seconds... Press CTRL+C to abort."
sleep 30
service apache2 start
# Fetch the correct update script
if [ -f "$SCRIPTS"/update.sh ]
then
rm "$SCRIPTS"/update.sh
wget https://raw.githubusercontent.com/nextcloud/vm/master/static/update.sh -P "$SCRIPTS"
chmod +x "$SCRIPTS"/update.sh
fi
exit
If you installed phpMyadmin you have to remove that as well and install phpPgAdmin instead with this script. It might also be a good idea to do FLUSHALL on Redis.
If you want to tune PGSQL you can use PgTune. It’s just a few settings to adapt to your hardware and the result is amazing. Default amount of connections is 100 and we found that to be the best setting. If you want to benchmark your installation, here is a good guide using pgbench.
We hope that you will enjoy the upgrade and please let us know what you think of this post by writing in the comments section below. Until next time; happy Nextclouding! :)
