How to migrate Nextcloud 17 Database Backend from MySQL to postgreSQL

By Markus Weingärtnernextcloud

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 🙂

Post Views: 8.566

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

  2. I’ve tried it today with NC20, but:

    In AbstractPostgreSQLDriver.php line 54:

    An exception occurred while executing ‚SELECT setval(‚oc_text_documents_id_seq‘, (SELECT MAX() FROM ))‘:

    SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near „)“
    LINE 1: …ECT setval(‚oc_text_documents_id_seq‘, (SELECT MAX() FROM ))
    ^

    In PDOConnection.php line 83:

    SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near „)“
    LINE 1: …ECT setval(‚oc_text_documents_id_seq‘, (SELECT MAX() FROM ))
    ^

    In PDOConnection.php line 78:

    SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near „)“
    LINE 1: …ECT setval(‚oc_text_documents_id_seq‘, (SELECT MAX() FROM ))
    ^

    Antworten

  3. 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

    Antworten

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

    Antworten

  5. 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.

    Antworten

Schreibe einen Kommentar

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