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.
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:
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 install postgresql php-pgsql
Hint: if not installed, you may want to install apt-transport-https first.
Create user and database
su - postgres
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;
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
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.
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)