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 🙂
18 Gedanken zu „How to migrate Nextcloud 17 Database Backend from MySQL to postgreSQL“
Thanks for this super helpful post.
I ran into a problem where my system wouldn’t switch to pgsql after the upgrade.
I’m running with php-fpm and the change wouldn’t take affect until after I restarted php-fpm.
Now it’s good.
Thanks for your post !
Works on nextcloud 20 docker installation. Nice
Works on nextcloud 22 installed on lxd on Gentoo container. „apt“ was modified for Gentoo.
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 ))
^
PostgreSQL Version? PHP Version?
I am getting the same issue, I posted about it on [reddit](https://www.reddit.com/r/NextCloud/comments/km6p61/nextcloud_database_migration_fails/).
My postgresql version is 11 and php7. Any help would be appreciated!
Which PHP 7. ? 7.2 7.3 7.4 ?
I am on 7.3. Looks like this is not an isolated issue. Another user has filed a bug on github, https://github.com/nextcloud/server/issues/24884
Yes. You are right.
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
Hi, was there an error message after occ command on commandline?
There are a lot of tables, that are „old“ and will not converted ->
You can retry the migration
Markus
Hi,
Im running NC within a Docker installation (nextcloud, nginx, letsencrypt, mariadb).
Is there also a simple way to migrate from Maria to Postgres?
It depends 😉 you need a postresql-db. Docker or non-docker where you can migrate to. If you use docker-compose add an additional pg to your config, migrate and then remove mariaDB
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.
mysql -u root -p
Then:
SET @@global.SQL_BIG_SELECTS = 1;
Exit and try again
Great post! Thanks
thank’s a lot 😉
I just needed to a the port to make the migration works.