Demand is changing quite often. As it comes to me, I was always a fan of the classic Apache/PHP/MySQL-/MariaDB-stack. Over the time I had (wether I wanted or not) to deal with Nginx and PostgreSQL. Quite a challenging task if you think of the 20 years I have been using the first combination now. Anyway, trying to consolidate the database on a virtual host that contains my beloved Nextcloud, a Searx-instance and my actual PixelFed-project followed by a planned Mastodon-environment forced me to deal with PostgreSQL.
My aim: Due to the diversity of services, I want just to use one database infrastructure although MariaDB and PostgreSQL can be installed on one single host. I just don’t want mix up structure unnecessarily – one database to rule them all, less possible errors. While I didn’t need to migrate anything regarded my home projects with Searx and the new PixelFed-instance, the challenge was to transfer the database structure of my Nextcloud-installation. This sounds quite difficult but – thanks to the good folks at Nextcloud – this turned out to be rather simple. Indeed just a single command was needed to safely push the whole database contents from MariaDB to PostgreSQL. Let me show you how you can achieve this!
Let’s assume you have already installed PostgreSQL by typing the “sudo apt install postgresql postgresql-contrib”-command and the database is running on port 5432. Now let’s head to the prompt and create a new database “nextcloud”. Additionally, we create a user, assign a password him and grant all permissions of the new database to this account. User as well as database are entitled “nextcloud” in this example. Switch to the “postgres”-user from the prompt. Now you can either look which databases are already present (just a hint) or change straight into the “postgresql”-database.
su - postgres psql -l psql postgres
Here we create a new database:
CREATE DATABASE nextcloud;
After exiting, we enter the newly created database:
Now we create a user (remember: “nextcloud” as well), assign a password (“test_password” – please change with your preferred, ultra-secret password!) and grant him all privileges on the “nextcloud”-database:
CREATE USER nextcloud WITH PASSWORD 'test_password'; GRANT ALL PRIVILEGES ON DATABASE "nextcloud" to nextcloud;
Finish the whole thing by typing
After we have made all pre-arrangements, Nextcloud’s task is now to transfer everything from database engine A (MySQL/MariaDB) to B (PostgreSQL). Like we already know from Nextcloud, the “occ”-command is a pretty nice Swiss knife-tool belonging to each Nextcloud-installation. In my example, the command
sudo -u www-data php occ db:convert-type --all-apps pgsql nextcloud 127.0.0.1 nextcloud
takes care that the migration process starts (the “occ”-file should be executable – if you haven’t used it before, a “chmod +x occ” should change this state). Nextcloud’s “occ” asks for the PostgreSQL-database password now:
Just enter it and you are ready to go – after doing some magic, everything’s ready. Quite ordinary, but effective!
The status at the end of the migration should tell you that your Nextcloud-instance is now running on PostgreSQL. Now enter your browser and check the availability of your installation – if everything went well, you should be able to log in without problems.
As a proof that the database is now really based on PostgreSQL, just check your config.php-file. It is available in the “config”-folder located within your Nextcloud-webroot.
The “dbtype” should now be “pgsql” instead of “mysql” – a good sign that the magic we conjured with a single command finally worked without problems! You may now drop your MySQL-/MariaDB user and database after – of course – having backed up the old database credentials and contents. As always I recommend to backup anyway, no matter what you are doing on a system in production. Have fun once you ever should need this, too – and don’t forget to leave your comments in case of any questions!