Nextcloud migrate to PostgreSQL

Also getting the famous “1213 Deadlock found when trying to get lock; try restarting transaction”? A few users reported switching to PostgreSQL fixed it. While I did not believe at first… Yes… It solved that issue for me as well.

Looks like I was constantly hitting this bug.

Migrating Nextcloud from MySQL/MariaDB to PostgreSQL is pretty easy (I do not have a lot of apps so it worked quiet fine for me – If you’re using lots of apps you might want to remove them before migrating and re-install afterwards). The procedure is simple:

Install PostgreSQL

echo "deb [arch=amd64] https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list
apt-key adv --keyserver keyserver.ubuntu.com --recv-keys 7FCC7D46ACCC4CF8
apt-get update
apt-get install postgresql php-pgsql

Hint: if not installed, you may want to install apt-transport-https first.

Create user and database

su - postgres
psql

You should see the CLI. Replace CHANGEME, nc_user and nc_db with appropriate values 🙂

CREATE USER nc_user WITH PASSWORD 'CHANGEME';
CREATE DATABASE nc_db WITH OWNER nc_user TEMPLATE template0 ENCODING 'UTF8';
GRANT ALL PRIVILEGES ON DATABASE nc_db TO nc_user;
quit

Hint: Output / cat the nextcloud config to get current database data and re-use the same here.

Start the migration

Switch to the user nextcloud runs as – in my case www-data:

su - www-data

Switch to the directory in which the occ file is. In my case /var/www/nextcloud

cd /var/www/nextcloud

Trigger the migration

php occ db:convert-type --all-apps --password="CHANGEME" pgsql nc_user 127.0.0.1 nc_db

That’s it. In the Nextcloud admin interface on the page system you should see something like:

Art: pgsql
Version: PostgreSQL 15.1 (Debian 15.1-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
Größe: 51,9 MB

Synchronizing 23070 files without ANY 1213 Deadlock. And well, it feels a little bit faster.

Hint: Don’t forget to remove MySQL / MariaDB as well as php-mysql from the system – at least if it is dedicated to nextcloud.

Additions

Always check the Nextcloud documentation. It shows a few php.ini settings for PostgreSQL.

You may want to switch to a UNIX-Socket instead of using TCP. (Change dbhost from 127.0.0.1 to /run/postgresql, replace peer with md5 in pg_hba.conf at unix domain socket communication)

Update 26.03.24: Just the snippets from the files as per the explanation above.

nextcloud config.php:

  'dbtype' => 'pgsql',
  'dbname' => 'nextcloud',
  'dbhost' => '/run/postgresql',
  'dbport' => '',
  'dbtableprefix' => 'oc_',

pg_hba.conf:

# "local" is for Unix domain socket connections only
local   all             all                                     md5