Nextcloud
Migrate Nextcloud Database from MariaDB to PostgreSQL
By jplee ~ Published – 2022/05/25
Even though MariaDB/MySQL is the recommended database for Nextcloud according to the Nextcloud documentation, to us PostgreSQL is the better choice. PostgreSQL outperforms MariaDB in regard to reads and writes and is therefore more efficient. MariaDB is more suitable for smaller databases, and is also capable of storing data entirely in-memory, – something not offered by PostgreSQL. MariaDB is a lighter database than PostgreSQL which allows a smaller memory footprint. PostgreSQL uses an internal cache and the server’s page cache for retrieving frequently accessed data, which helps it to perform better than MariaDB’s query cache. Therefore, PostgreSQL is a lightning faster reponding database to use. Thankfully, PostgreSQL functions very well with Nextcloud and the only downside is that not all apps are supported. But, all of the official apps are supported. So, lets go ahead and do this, migrate from MariaDB to PostgreSQL.
Step 1: Install PostgreSQL
sudo apt update && sudo apt upgrade -y
sudo apt install -y postgresql postgresql-contrib PostgreSQL and MariaDB can run on the same server. You don’t need to remove MariaDB.
Step 2: Install PostgreSQL PHP module.
sudo apt install php8.1-pgsql Step 3: Create Database for NextCloud in PostgreSQL.
Log into PostgreSQL as the postgres user.
sudo -u postgres psql Create the Nextcloud Database in PostgreSQL with a user and granted privileges.
CREATE DATABASE nextcloud_db TEMPLATE template0 ENCODING 'UNICODE';
CREATE USER nextclouduser WITH PASSWORD 'nextcloud';
ALTER DATABASE nextcloud_db OWNER TO nextclouduser;
GRANT ALL PRIVILEGES ON DATABASE nextcloud_db TO nextclouduser;
Press Ctrl+D or \q inorder to logout of PostgreSQL console.
Run the following command to test if you can log in to PostgreSQL as nextclouduser.
psql -h 127.0.0.1 -d nextcloud_db -U nextclouduser -W
Step 4: Start the Migration using Nextcloud OCC command.
sudo -u www-data php8.1 /var/www/nextcloud/occ db:convert-type --all-apps --password "nextcloud" pgsql nextclouduser localhost nextcloud_db FYI: We used the following for the above occ command. db:convert-type [–port PORT] [–password PASSWORD] [–clear-schema] [–all-apps] [–chunk-size CHUNK-SIZE] [–] <type> <username> <hostname> <database>
Once it’s done, go to Nextcloud web interface Settings -> System, scroll down and you will find that Nextcloud is now using PostgreSQL. The Nextcloud System is fully available with the PostgreSQL database!
Create an update script file using Vim to optimize and update Nextcloud system:
vim update.sh Copy and paste below information into the new update.sh file:
# Ahuitech (May 25, 2022)
# Update script for Nextcloud 24.0.1.1 on ubuntu 22.04 system
#!/bin/bash
/usr/sbin/service nginx stop
sudo -u www-data php /var/www/nextcloud/updater/updater.phar
sudo -u www-data php /var/www/nextcloud/occ status
sudo -u www-data php /var/www/nextcloud/occ -V
sudo -u www-data php /var/www/nextcloud/occ db:add-missing-primary-keys
sudo -u www-data php /var/www/nextcloud/occ db:add-missing-indices
sudo -u www-data php /var/www/nextcloud/occ db:add-missing-columns
sudo -u www-data php /var/www/nextcloud/occ db:convert-filecache-bigint
sed -i "s/output_buffering=.*/output_buffering=0/" /var/www/nextcloud/.user.ini
chown -R www-data:www-data /var/nc_data /var/www
redis-cli -s /var/run/redis/redis-server.sock <<EOF
FLUSHALL
quit
EOF
sudo -u www-data php /var/www/nextcloud/occ user:list
sudo -u www-data php /var/www/nextcloud/occ files:scan --all
sudo -u www-data php /var/www/nextcloud/occ files:scan-app-data
sudo -u www-data php /var/www/nextcloud/occ app:update --all
/usr/sbin/service php8.0-fpm restart
/usr/sbin/service nginx restart
exit 0
Save the update.sh file in Vim using the following commands:
Press the Esc key first and then type :wq and then press Enter key.
Now, execute the update.sh file with the following commands:
chmod +x update.sh
./update.sh
chmod -x update.sh
Congratulations, you now have your own personal data on your own secured Nextcloud system!
At Ahuitech, we create clouds systems that allow you to have complete control of your Data.
Ahuitech – Cloud Creations are always in your Control