Nextcloud Migration and Database Performance: Solving Deadlocks with PostgreSQL

Getting the famous “1213 Deadlock found when trying to get lock; try restarting transaction” error in Nextcloud can be frustrating. This issue affected many users and was discussed in bug reports like this: Nextcloud Deadlock Issue
. The community frequently recommends switching the backend database to PostgreSQL. While I was initially skeptical, the migration proved to be the definitive solution for this recurring issue in my setup.

This guide outlines the streamlined procedure for migrating Nextcloud from MariaDB/MySQL to PostgreSQL. The process is uncomplicated and can drastically improve system stability.

1. Prerequisites and PostgreSQL Setup

The migration process begins with installing PostgreSQL and the necessary PHP drivers.

Adding the PostgreSQL Repository

Since we need the latest version of PostgreSQL, we add the official PostgreSQL Global Development Group (PGDG) repository:

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

Now we install the packages:

apt-get install postgresql php-pgsql

Hint: If you are using many apps, it is recommended to remove them before migrating and reinstall afterward to avoid data conversion issues.

Database and User Creation

Next, we prepare the new PostgreSQL database. It is best practice to create a dedicated user and database for Nextcloud.

su - postgres
psql

# Replace CHANGEME, nc_user, and nc_db with your dedicated credentials.
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

CRITICAL: Backup

Before executing the data migration, always create a full backup of your existing MariaDB/MySQL database. This is the only way to guarantee a rollback to the previous state.

2. The Migration Process (occ db:convert-type)

The actual data migration is handled entirely by the Nextcloud occ command-line utility.

Execute Conversion

Switch to the user Nextcloud runs as (e.g., www-data) and navigate to the Nextcloud installation directory:

su - www-data
cd /var/www/nextcloud

Now, trigger the migration. The --all-apps flag ensures all installed applications are covered.

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

3. Post-Migration Verification and Optimization

Performance and Stability (MVCC)

The increased stability provided by PostgreSQL is largely attributable to its superior MVCC (Multi-Version Concurrency Control) implementation. Unlike MariaDB/MySQL, where high-contention read/write operations often lead to deadlocks, PostgreSQL’s MVCC allows for more efficient and concurrent transaction processing.

After verifying the database type and version in the admin interface, my synchronization tests confirmed the fix: I was synchronizing 23,070 files without any Deadlock errors, and the system felt noticeably faster.

Database Connection Hardening (Unix Socket)

It is highly recommended to switch from TCP (127.0.0.1) to a Unix Socket for enhanced security and performance, as the connection stays local to the server.

1. PostgreSQL Configuration (pg_hba.conf): Modify the authentication method for Unix domain socket connections to md5.
   
   # "local" is for Unix domain socket connections only
local all all md5
   
2. Nextcloud Configuration (config.php): Update the dbhost to point to the socket path, and ensure the dbtype is correctly set.
   
   'dbtype' => 'pgsql',
'dbname' => 'nextcloud',
'dbhost' => '/run/postgresql', // Unix Socket Path
'dbport' => '', // Port remains empty
'dbtableprefix' => 'oc_',

Final Note on Configuration Management: If this is a dedicated Nextcloud host, it’s good practice to remove unnecessary MariaDB/MySQL packages and their PHP drivers (e.g., php-mysql) to minimize the system’s attack surface.

See Also

• Nextcloud Documentation. Converting database type. https://docs.nextcloud.com/server/latest/admin_manual/configuration_database/db_conversion.html
• Nextcloud GitHub. Deadlock found when trying to get lock (Issue 6899). https://github.com/nextcloud/server/issues/6899
• PostgreSQL Documentation. Chapter 13. Concurrency Control (MVCC). https://www.postgresql.org/docs/current/mvcc-intro.html
• PostgreSQL Documentation. SQL Language: CREATE USER and CREATE DATABASE. https://www.postgresql.org/docs/current/sql-createuser.html