nextcloud

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 🙂

8 Gedanken zu „How to migrate Nextcloud 17 Database Backend from MySQL to postgreSQL

  1. Hi tried the procedure and not getting an error when trying to logon again on nextcloud that fails and getting this error in the log
    SELECT \“m\“.\“mount_id\“, \“mount_point\“, \“storage_backend\“, \“auth_backend\“, \“priority\“, \“m\“.\“type\“ FROM \“oc_external_mounts\“ \“m
    \“ INNER JOIN \“oc_external_applicable\“ \“a\“ ON \“m\“.\“mount_id\“ = \“a\“.\“mount_id\“ WHERE ((\“a\“.\“type\“ = ?) AND (\“a\“.\“value\“ IS NULL)) OR ((\“a\“.\“type\“ = ?) AND (\“a\“.\“value\“ = ?)) OR ((\“a\“
    .\“type\“ = ?) AND (\“a\“.\“value\“ IN (?)))‘
    and i see 253 tables in mysql versus 180 tables in postgrsql so data is not all migrated
    So now trying to found a way to migrate the rest
    Regards
    Filip

  2. Hi,
    Im running NC within a Docker installation (nextcloud, nginx, letsencrypt, mariadb).
    Is there also a simple way to migrate from Maria to Postgres?

  3. if recieve mysql error:
    The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay

    enter in mysql shell and enter „SET @@global.SQL_BIG_SELECTS = 1;“ before start occ for migration.

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht.