How to migrate Nextcloud 17 Database Backend from MySQL to postgreSQL

Short time ago, I published how to install Nextcloud with postgreSQL as Database Backend and how to Install Nextcloud 17 with MariaDB as Database-Backend, but if you are running on MySQL/mariaDB, how to migrate to postgreSQL?

Well it is simple, let me show you how to.

Migrate from MySQL/mariaDB/SQLLite to postgreSQL

Requirements:

– a running Nextcloud Instance
– shell access and appropriate rights

Step 1: Install postgreSQL

First, we install the postgreSQL as Database-Server:

apt install -y postgresql postgresql-contrib

Now you can connect to postgreSQL with:

root@db01:/# sudo -u postgres psql
psql (11.5 (Debian 11.5-1+deb10u1))
Type "help" for help.

postgres=#

you can get information about connection with:

postgres=#  \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".

and end session with:

postgres-#  \q

Step 2: Install postgreSQL-php-module

With the following command we will install the php-module for postgreSQL:

apt install -y php7.3-pgsql

If you are running Nextcloud on other than php7.3 please adjust the version-number, as example:

apt install -y php7.2-pgsql

Step 3: Create Database

Before we can migrate our database backend of Nextcloud, we first have to create a database in postgreSQL. To do this, we execute the following commands:

sudo -u postgres psql

then execute:

CREATE USER nextcloud WITH PASSWORD 'your-password';
CREATE DATABASE nextclouddb TEMPLATE template0 ENCODING 'UNICODE';
ALTER DATABASE nextclouddb OWNER TO nextcloud;
GRANT ALL PRIVILEGES ON DATABASE nextclouddb TO nextcloud;
\q

Step 4: Start migration step

Migration can took a long time to finish, it is recommended to use tmux or screen to run this step.

With the next command you will start the migration (adjust path of your nextcloud):

sudo -u www-data php7.3 /var/www/html/nextcloud/occ db:convert-type --all-apps --password "Your-Password" pgsql nextcloud localhost nextclouddb

When finished, control Database Information in Settings of your Nextcloud:

Now you are running on postgreSQL as Database Backend 🙂

Problems with the tutorial? Then comment below or contact me per Mail or Mastodon.

Happy nextclouding and do not forget to share 🙂