System load is 100% after upgrade to 29.0.1

I have a problem with my installation of Nextcloud after the update to version 29.0.1. The system runs very slow and is unresponsive. I have 25 active users and a 100% memory and CPU load. The database engine is pg-sql and Nextcloud is running on a virtual machine with 16 CPU vrcores and 16GB RAM allocated.

Top shows a great deal of apache2, php-fpm and postgress processes occupying the entire server resources.

Every few hours the system gets overloaded and I have to restart the apache2 and pgsql services to get the server working properly again.

Nextcloud version: 29.0.1
Operating system and version: Ubuntu 20.04 LTS
Apache or nginx version: 2.4.41
PHP version: 8.2.19 (FPM)
Database engine - pg-sql.

Nextcloud log is full of errors:

{"reqId":"uOz366Khbg7QzG0o5sn1","level":3,"time":"2024-05-28T10:11:12+02:00","remoteAddr":"","user":"--","app":"spreed","method":"","url":"--","message":"Getting the account information failed: account not found - HTTP status: 404 Response body: ","userAgent":"--","version":"29.0.1.1","data":{"app":"spreed"}}
{"reqId":"mUApp50zKpNTFNwhVITg","level":3,"time":"2024-05-28T10:44:48+02:00","remoteAddr":"192.168.0.1","user":"--","app":"PHP","method":"GET","url":"/ocs/v2.php/apps/dashboard/api/v2/widget-items?widgets%5B%5D=spreed","message":"Doctrine\\DBAL\\Exception: Failed to connect to the database: An exception occurred in the driver: SQLSTATE[08006] [7] FATAL:  the database system is starting up\nFATAL:  the database system is starting up at /var/www/nextcloud/lib/private/DB/Connection.php#155","userAgent":"Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:126.0) Gecko/20100101 Firefox/126.0","version":"29.0.1.1","data":{"app":"PHP"}}
{"reqId":"b86u9ua34sPioOROLpS0","level":3,"time":"2024-05-28T10:44:48+02:00","remoteAddr":"192.168.0.1","user":"--","app":"PHP","method":"GET","url":"/ocs/v2.php/apps/notifications/api/v2/notifications","message":"Doctrine\\DBAL\\Exception: Failed to connect to the database: An exception occurred in the driver: SQLSTATE[08006] [7] FATAL:  the database system is starting up\nFATAL:  the database system is starting up at /var/www/nextcloud/lib/private/DB/Connection.php#155","userAgent":"Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:126.0) Gecko/20100101 Firefox/126.0","version":"29.0.1.1","data":{"app":"PHP"}}
{"reqId":"fqdPclWb5akUKHe12QeC","level":3,"time":"2024-05-28T10:44:48+02:00","remoteAddr":"192.168.0.1","user":"--","app":"PHP","method":"GET","url":"/ocs/v2.php/apps/spreed/api/v1/reaction/wdsk8ttp/106288","message":"Doctrine\\DBAL\\Exception: Failed to connect to the database: An exception occurred in the driver: SQLSTATE[08006] [7] FATAL:  the database system is starting up\nFATAL:  the database system is starting up at /var/www/nextcloud/lib/private/DB/Connection.php#155","userAgent":"Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:126.0) Gecko/20100101 Firefox/126.0","version":"29.0.1.1","data":{"app":"PHP"}}
{"reqId":"G2QjyTqLhl4XvhAF1csJ","level":3,"time":"2024-05-28T10:44:48+02:00","remoteAddr":"185.127.121.143","user":"--","app":"PHP","method":"GET","url":"/ocs/v2.php/apps/notifications/api/v2/notifications","message":"Doctrine\\DBAL\\Exception: Failed to connect to the database: An exception occurred in the driver: SQLSTATE[08006] [7] FATAL:  the database system is starting up\nFATAL:  the database system is starting up at /var/www/nextcloud/lib/private/DB/Connection.php#155","userAgent":"Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:126.0) Gecko/20100101 Firefox/126.0","version":"29.0.1.1","data":{"app":"PHP"}}
{"reqId":"suXke3h2rDQlKTKHPlp0","level":3,"time":"2024-05-28T10:44:48+02:00","remoteAddr":"77.46.47.82","user":"--","app":"index","method":"GET","url":"/index.php/204","message":"Failed to connect to the database: An exception occurred in the driver: SQLSTATE[08006] [7] FATAL:  the database system is starting up\nFATAL:  the database system is starting up","userAgent":"Mozilla/5.0 (Windows) mirall/3.13.0stable-Win64 (build 20240423) (Nextcloud, windows-10.0.22631 ClientArchitecture: x86_64 OsArchitecture: x86_64)","version":"29.0.1.1","exception":{"Exception":"Doctrine\\DBAL\\Exception","Message":"Failed to connect to the database: An exception occurred in the driver: SQLSTATE[08006] [7] FATAL:  the database system is starting up\nFATAL:  the database system is starting up","Code":7,"Trace":[{"file":"/var/www/nextcloud/3rdparty/doctrine/dbal/src/Connection.php","line":453,"function":"connect","class":"OC\\DB\\Connection","type":"->"},{"file":"/var/www/nextcloud/3rdparty/doctrine/dbal/src/Connection.php","line":411,"function":"getDatabasePlatformVersion","class":"Doctrine\\DBAL\\Connection","type":"->"},{"file":"/var/www/nextcloud/3rdparty/doctrine/dbal/src/Connection.php","line":318,"function":"detectDatabasePlatform","class":"Doctrine\\DBAL\\Connection","type":"->"},{"file":"/var/www/nextcloud/lib/private/DB/ConnectionAdapter.php","line":200,"function":"getDatabasePlatform","class":"Doctrine\\DBAL\\Connection","type":"->"},{"file":"/var/www/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php","line":121,"function":"getDatabasePlatform","class":"OC\\DB\\ConnectionAdapter","type":"->"},{"file":"/var/www/nextcloud/lib/private/AppConfig.php","line":1237,"function":"expr","class":"OC\\DB\\QueryBuilder\\QueryBuilder","type":"->"},{"file":"/var/www/nextcloud/lib/private/AppConfig.php","line":262,"function":"loadConfig","class":"OC\\AppConfig","type":"->"},{"file":"/var/www/nextcloud/lib/private/AppConfig.php","line":1378,"function":"searchValues","class":"OC\\AppConfig","type":"->"},{"file":"/var/www/nextcloud/lib/private/App/AppManager.php","line":131,"function":"getValues","class":"OC\\AppConfig","type":"->"},{"file":"/var/www/nextcloud/lib/private/App/AppManager.php","line":152,"function":"getInstalledAppsValues","class":"OC\\App\\AppManager","type":"->"},{"file":"/var/www/nextcloud/lib/private/legacy/OC_App.php","line":234,"function":"getInstalledApps","class":"OC\\App\\AppManager","type":"->"},{"file":"/var/www/nextcloud/lib/private/AppFramework/Bootstrap/Coordinator.php","line":90,"function":"getEnabledApps","class":"OC_App","type":"::"},{"file":"/var/www/nextcloud/lib/base.php","line":706,"function":"runInitialRegistration","class":"OC\\AppFramework\\Bootstrap\\Coordinator","type":"->"},{"file":"/var/www/nextcloud/lib/base.php","line":1181,"function":"init","class":"OC","type":"::"},{"file":"/var/www/nextcloud/index.php","line":47,"args":["/var/www/nextcloud/lib/base.php"],"function":"require_once"}],"File":"/var/www/nextcloud/lib/private/DB/Connection.php","Line":155,"message":"Failed to connect to the database: An exception occurred in the driver: SQLSTATE[08006] [7] FATAL:  the database system is starting up\nFATAL:  the database system is starting up","exception":{},"CustomMessage":"Failed to connect to the database: An exception occurred in the driver: SQLSTATE[08006] [7] FATAL:  the database system is starting up\nFATAL:  the database system is starting up"}}
{"reqId":"suXke3h2rDQlKTKHPlp0","level":3,"time":"2024-05-28T10:44:48+02:00","remoteAddr":"77.46.47.82","user":"--","app":"core","method":"GET","url":"/index.php/204","message":"Exception thrown: Doctrine\\DBAL\\Exception","userAgent":"Mozilla/5.0 (Windows) mirall/3.13.0stable-Win64 (build 20240423) (Nextcloud, windows-10.0.22631 ClientArchitecture: x86_64 OsArchitecture: x86_64)","version":"29.0.1.1","exception":{"Exception":"Doctrine\\DBAL\\Exception","Message":"Failed to connect to the database: An exception occurred in the driver: SQLSTATE[08006] [7] FATAL:  the database system is starting up\nFATAL:  the database system is starting up","Code":7,"Trace":[{"file":"/var/www/nextcloud/3rdparty/doctrine/dbal/src/Connection.php","line":453,"function":"connect","class":"OC\\DB\\Connection","type":"->"},{"file":"/var/www/nextcloud/3rdparty/doctrine/dbal/src/Connection.php","line":411,"function":"getDatabasePlatformVersion","class":"Doctrine\\DBAL\\Connection","type":"->"},{"file":"/var/www/nextcloud/3rdparty/doctrine/dbal/src/Connection.php","line":318,"function":"detectDatabasePlatform","class":"Doctrine\\DBAL\\Connection","type":"->"},{"file":"/var/www/nextcloud/lib/private/DB/ConnectionAdapter.php","line":200,"function":"getDatabasePlatform","class":"Doctrine\\DBAL\\Connection","type":"->"},{"file":"/var/www/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php","line":121,"function":"getDatabasePlatform","class":"OC\\DB\\ConnectionAdapter","type":"->"},{"file":"/var/www/nextcloud/lib/private/AppConfig.php","line":1237,"function":"expr","class":"OC\\DB\\QueryBuilder\\QueryBuilder","type":"->"},{"file":"/var/www/nextcloud/lib/private/AppConfig.php","line":262,"function":"loadConfig","class":"OC\\AppConfig","type":"->"},{"file":"/var/www/nextcloud/lib/private/AppConfig.php","line":1378,"function":"searchValues","class":"OC\\AppConfig","type":"->"},{"file":"/var/www/nextcloud/lib/private/App/AppManager.php","line":131,"function":"getValues","class":"OC\\AppConfig","type":"->"},{"file":"/var/www/nextcloud/lib/private/App/AppManager.php","line":152,"function":"getInstalledAppsValues","class":"OC\\App\\AppManager","type":"->"},{"file":"/var/www/nextcloud/lib/private/legacy/OC_App.php","line":234,"function":"getInstalledApps","class":"OC\\App\\AppManager","type":"->"},{"file":"/var/www/nextcloud/lib/private/AppFramework/Bootstrap/Coordinator.php","line":90,"function":"getEnabledApps","class":"OC_App","type":"::"},{"file":"/var/www/nextcloud/lib/base.php","line":706,"function":"runInitialRegistration","class":"OC\\AppFramework\\Bootstrap\\Coordinator","type":"->"},{"file":"/var/www/nextcloud/lib/base.php","line":1181,"function":"init","class":"OC","type":"::"},{"file":"/var/www/nextcloud/index.php","line":47,"args":["/var/www/nextcloud/lib/base.php"],"function":"require_once"}],"File":"/var/www/nextcloud/lib/private/DB/Connection.php","Line":155,"CustomMessage":"Exception thrown: Doctrine\\DBAL\\Exception"}}
{"reqId":"suXke3h2rDQlKTKHPlp0","level":3,"time":"2024-05-28T10:44:48+02:00","remoteAddr":"77.46.47.82","user":"--","app":"core","method":"GET","url":"/index.php/204","message":"Exception thrown: Doctrine\\DBAL\\Exception","userAgent":"Mozilla/5.0 (Windows) mirall/3.13.0stable-Win64 (build 20240423) (Nextcloud, windows-10.0.22631 ClientArchitecture: x86_64 OsArchitecture: x86_64)","version":"29.0.1.1","exception":{"Exception":"Doctrine\\DBAL\\Exception","Message":"Failed to connect to the database: An exception occurred in the driver: SQLSTATE[08006] [7] FATAL:  the database system is starting up\nFATAL:  the database system is starting up","Code":7,"Trace":[{"file":"/var/www/nextcloud/3rdparty/doctrine/dbal/src/Connection.php","line":453,"function":"connect","class":"OC\\DB\\Connection","type":"->"},{"file":"/var/www/nextcloud/3rdparty/doctrine/dbal/src/Connection.php","line":411,"function":"getDatabasePlatformVersion","class":"Doctrine\\DBAL\\Connection","type":"->"},{"file":"/var/www/nextcloud/3rdparty/doctrine/dbal/src/Connection.php","line":318,"function":"detectDatabasePlatform","class":"Doctrine\\DBAL\\Connection","type":"->"},{"file":"/var/www/nextcloud/lib/private/DB/ConnectionAdapter.php","line":200,"function":"getDatabasePlatform","class":"Doctrine\\DBAL\\Connection","type":"->"},{"file":"/var/www/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php","line":121,"function":"getDatabasePlatform","class":"OC\\DB\\ConnectionAdapter","type":"->"},{"file":"/var/www/nextcloud/lib/private/AppConfig.php","line":1237,"function":"expr","class":"OC\\DB\\QueryBuilder\\QueryBuilder","type":"->"},{"file":"/var/www/nextcloud/lib/private/AppConfig.php","line":262,"function":"loadConfig","class":"OC\\AppConfig","type":"->"},{"file":"/var/www/nextcloud/lib/private/legacy/OC_App.php","line":736,"function":"searchValues","class":"OC\\AppConfig","type":"->"},{"file":"/var/www/nextcloud/lib/private/TemplateLayout.php","line":236,"function":"getAppVersions","class":"OC_App","type":"::"},{"file":"/var/www/nextcloud/lib/private/legacy/OC_Template.php","line":145,"function":"__construct","class":"OC\\TemplateLayout","type":"->"},{"file":"/var/www/nextcloud/lib/private/Template/Base.php","line":132,"function":"fetchPage","class":"OC_Template","type":"->"},{"file":"/var/www/nextcloud/lib/private/legacy/OC_Template.php","line":320,"function":"printPage","class":"OC\\Template

My Nextcloud config.php

<?php
$CONFIG = array (
  'passwordsalt' => 'xxx',
  'secret' => 'xxx',
  'trusted_domains' =>
  array (
    0 => 'localhost',
    1 => '192.168.0.xxx',
    2 => 'xxx.xxx.xxx',
    3 => 'xxx.xxx.xxx',
  ),
  'datadirectory' => '/mnt/ncdata',
  'dbtype' => 'pgsql',
  'version' => '29.0.1.1',
  'overwrite.cli.url' => 'xxx.xxx.xxx',
  'dbname' => 'nextcloud_db',
  'dbhost' => 'localhost',
  'dbport' => '',
  'dbtableprefix' => 'oc_',
  'dbuser' => 'ncadmin',
  'dbpassword' => 'xxx.xxx.xxx',
  'installed' => true,
  'instanceid' => 'xxx.xxx.xxx',
  'upgrade.disable-web' => false,
  'log_type' => 'file',
  'logfile' => '/var/log/nextcloud/nextcloud.log',
  'loglevel' => 3,
  'mail_smtpmode' => 'smtp',
  'remember_login_cookie_lifetime' => '1800',
  'log_rotate_size' => '10485760',
  'trashbin_retention_obligation' => 'auto, 180',
  'versions_retention_obligation' => 'auto, 365',
  'simpleSignUpLink.shown' => 'false',
  'filelocking.enabled' => true,
  'logtimezone' => 'Europe/Warsaw',
  'htaccess.RewriteBase' => '/',
  'maintenance' => false,
  'maintenance_window_start' => 1,
  'app_install_overwrite' =>
  array (
    0 => 'keeweb',
    1 => 'calendar',
    2 => 'rainloop',
    3 => 'documentserver_community',
    4 => 'shifts',
    5 => 'backup',
  ),
  'ldapIgnoreNamingRules' => false,
  'ldapProviderFactory' => 'OCA\\User_LDAP\\LDAPProviderFactory',
  'memcache.local' => '\\OC\\Memcache\\APCu',
  'memcache.locking' => '\\OC\\Memcache\\Redis',
  'redis' =>
  array (
    'host' => '/var/run/redis/redis-server.sock',
    'port' => 0,
    'dbindex' => 0,
    'timeout' => 1.5,
    'password' => 'xxx.xxx.xxx',
  ),
  'theme' => '',
  'has_rebuilt_cache' => true,
  'mail_sendmailmode' => 'smtp',
  'mail_smtpauth' => 1,
  'mail_smtpname' => 'xxx.xxx.xxx',
  'mail_smtppassword' => 'xxx.xxx.xxx',
  'updater.release.channel' => 'stable',
  'allow_local_remote_servers' => true,
  'default_phone_region' => 'xxx.xxx.xxx',
  'mail_smtpsecure' => 'tls',
  'mail_from_address' => 'xxx.xxx.xxx',
  'mail_domain' => 'xxx.xxx.xxx',
  'mail_smtphost' => 'xxx.xxx.xxx',
  'mail_smtpport' => '587',
  'mail_smtpauthtype' => 'LOGIN',
);

At the moment I have deactivated almost all Nextcloud applications leaving only Talk, Files, Activities and a few others necessary for Nextcloud itself to work, but this has no apparent effect.

The system is fully updated, the log data does not indicate a specific problem with the server configuration. I have performed occ:maintenance:repair - to no avail. The only thing that has changed is the Nextcloud update to version 29.0.1.

I also did some work on the apache2 configuration in the sites enabled section to get rid of the error: ‘Your data directory and your files are probably accessible from the Internet. The .htaccess file is not working. …’ but no solution was found here either. I have undone all the changes made.

Do you have ways to check the database? Many errors are related to that, so perhaps there is some repair/validation/check procedure (sorry, I don’t know your database).

If it happened after the update, how did you upgrade, via CLI (updater/updater.phar), manually, via updater-app (webinterface)? Just via webinterface, it can be risky that some jobs run into timeouts, and for db-operations that could explains issues.

Don’t forget to backup before you change things.

1 Like

FATAL: the database system is starting up

Did something recently happen with your database? An improper shutdown? A new backup method? Using replication or anything? Etc.

These errors are coming from PostgreSQL. They indicate your database is trying to recover from something like an improper shutdown or the like.

I would shutdown Nextcloud and monitor your PostgreSQL server and logs. Only re-enable Nextcloud once your db recovers !and a fresh restart demonstrates it comes back cleanly).

‘loglevel’ => 3,

Set your loglevel back to be default (2) and you may get additional clues about what is going on, though from the looks of it your db is the problem.

I changed the log level to 2. I also checked the postgresql logs and there are several thousand errors there:

x2024-05-28 10:17:32.071 UTC [131073] ncadmin@nextcloud_db ERROR:  duplicate key value violates unique constraint "user_status_uid_ix"                                            x
x2024-05-28 10:17:32.071 UTC [131073] ncadmin@nextcloud_db DETAIL:  Key (user_id)=(_karolina.kalinska) already exists.                                                            x
x2024-05-28 10:17:32.071 UTC [131073] ncadmin@nextcloud_db STATEMENT:  UPDATE "oc_user_status" SET "is_backup" = $1, "user_id" = $2 WHERE "user_id" = $3                          x
x2024-05-28 10:17:36.564 UTC [131193] ncadmin@nextcloud_db ERROR:  duplicate key value violates unique constraint "user_status_uid_ix"                                            x
x2024-05-28 10:17:36.564 UTC [131193] ncadmin@nextcloud_db DETAIL:  Key (user_id)=(_karolina.kalinska) already exists.                                                            x
x2024-05-28 10:17:36.564 UTC [131193] ncadmin@nextcloud_db STATEMENT:  UPDATE "oc_user_status" SET "is_backup" = $1, "user_id" = $2 WHERE "user_id" = $3                          x
x2024-05-28 10:19:25.744 UTC [132945] ncadmin@nextcloud_db ERROR:  duplicate key value violates unique constraint "user_status_uid_ix"                                            x
x2024-05-28 10:19:25.744 UTC [132945] ncadmin@nextcloud_db DETAIL:  Key (user_id)=(_karolina.kalinska) already exists.                                                            x
x2024-05-28 10:19:25.744 UTC [132945] ncadmin@nextcloud_db STATEMENT:  UPDATE "oc_user_status" SET "is_backup" = $1, "user_id" = $2 WHERE "user_id" = $3                          x
x2024-05-28 10:20:00.953 UTC [133415] ncadmin@nextcloud_db ERROR:  duplicate key value violates unique constraint "user_status_uid_ix"                                            x
x2024-05-28 10:20:00.953 UTC [133415] ncadmin@nextcloud_db DETAIL:  Key (user_id)=(_karolina.kalinska) already exists.                                                            x
x2024-05-28 10:20:00.953 UTC [133415] ncadmin@nextcloud_db STATEMENT:  UPDATE "oc_user_status" SET "is_backup" = $1, "user_id" = $2 WHERE "user_id" = $3                          x
x2024-05-28 10:25:01.390 UTC [136802] ncadmin@nextcloud_db ERROR:  duplicate key value violates unique constraint "user_status_uid_ix"                                            x
x2024-05-28 10:25:01.390 UTC [136802] ncadmin@nextcloud_db DETAIL:  Key (user_id)=(_karolina.kalinska) already exists.                                                            x
x2024-05-28 10:25:01.390 UTC [136802] ncadmin@nextcloud_db STATEMENT:  UPDATE "oc_user_status" SET "is_backup" = $1, "user_id" = $2 WHERE "user_id" = $3                          x
x2024-05-28 10:27:12.325 UTC [137859] ncadmin@nextcloud_db ERROR:  duplicate key value violates unique constraint "user_status_uid_ix"                                            x
x2024-05-28 10:27:12.325 UTC [137859] ncadmin@nextcloud_db DETAIL:  Key (user_id)=(_karolina.kalinska) already exists.                                                            x
x2024-05-28 10:27:12.325 UTC [137859] ncadmin@nextcloud_db STATEMENT:  UPDATE "oc_user_status" SET "is_backup" = $1, "user_id" = $2 WHERE "user_id" = $3                          x
x2024-05-28 10:32:50.221 UTC [1725] LOG:  starting PostgreSQL 12.18 (Ubuntu 12.18-0ubuntu0.20.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.2) 9.4.0,x
x2024-05-28 10:32:50.223 UTC [1725] LOG:  listening on IPv4 address "127.0.0.1", port 5432                                                                                        x
x2024-05-28 10:32:50.224 UTC [1725] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"                                                                            x
x2024-05-28 10:32:50.414 UTC [1884] LOG:  database system was interrupted; last known up at 2024-05-28 10:28:28 UTC                                                               x
x2024-05-28 10:32:50.996 UTC [2071] ncadmin@nextcloud_db FATAL:  the database system is starting up                                                                               x
x2024-05-28 10:32:50.997 UTC [2073] ncadmin@nextcloud_db FATAL:  the database system is starting up                                                                               x
x2024-05-28 10:32:50.998 UTC [2074] ncadmin@nextcloud_db FATAL:  the database system is starting up                                                                               x
x2024-05-28 10:32:50.999 UTC [2075] ncadmin@nextcloud_db FATAL:  the database system is starting up                                                                               x
x2024-05-28 10:32:51.001 UTC [2072] ncadmin@nextcloud_db FATAL:  the database system is starting up                                                                               x
x2024-05-28 10:32:51.003 UTC [2076] ncadmin@nextcloud_db FATAL:  the database system is starting up                                                                               x
x2024-05-28 10:32:51.012 UTC [2078] ncadmin@nextcloud_db FATAL:  the database system is starting up                                                                               x
x2024-05-28 10:32:51.015 UTC [2077] ncadmin@nextcloud_db FATAL:  the database system is starting up                                                                               x
x2024-05-28 10:32:51.015 UTC [2080] ncadmin@nextcloud_db FATAL:  the database system is starting up                

I searched for solutions on github, found a similar problem and it is marked as solved: [Bug]: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'userid' for key 'user_status_uid_ix' · Issue #42625 · nextcloud/server · GitHub.

I have set up maintenace mode on Nextcloud and am waiting until tomorrow morning for a possible recovery of the database, although I have not been able to get any information from the logs to indicate that the database at the moment requires a recovery repair. I don’t have much experience with pgsql so I could be wrong.

The update from version 28.0.5 to 29.0.1 was carried out with the update-app from the webinterface. The entire process ran correctly - no errors.

Before you change anything, try to make a backup of your database like it is today, so worst case, you can always get back to the current situation.

If it is just the oc_user_status table, you could even go through that table manually and delete duplicate entries. Since it is just the status, you could even delete the whole content of the table.

You can try with the occ command to run the upgrade again, perhaps some table conversion weren’t finished properly:
sudo -u www-data php occ upgrade

Perhaps try to add missing indices:
https://docs.nextcloud.com/server/latest/admin_manual/configuration_server/occ_command.html#add-missing-indices

Big question is if there is a problem in the database itself (so you’d need the database specific tools to repair it), or if the inconsistency was created by Nextcloud and it is a bug and something to repair.

I have tried with waiting for Nextcloud’s base to ‘self-repair’. The postgress process did not perform any operations during this time.

At the moment the load on the server is at about 70% and increasing successively to 100%. The min. load I had before was 0.22 now it reaches the max. load, which is 32.

Nextcloud log records do not correlate with server load. Yes there are errors, but they occurred two hours ago and the server has a very high load now and log does not contain any new records.

I am not able to keep track of all the tasks currently being performed by Nextcloud, is there any tool that allows me to view the operations currently being performed? Is it worth pausing cron for a while?

sudo -u www-data php occ upgrade returns that Nextcloud is already on the latest version. In the settings menu, I don’t have the information to run the db:add-missing-indices command. Running this command returns no result - there is nothing to add.

pg-sql log:

2024-06-03 07:20:40.776 UTC [347689] ncadmin@nextcloud_db ERROR:  duplicate key value violates unique constraint "user_status_uid_ix"                                                      x
x2024-06-03 07:20:40.776 UTC [347689] ncadmin@nextcloud_db DETAIL:  Key (user_id)=(_karolina.kalinska) already exists.                                                                      x
x2024-06-03 07:20:40.776 UTC [347689] ncadmin@nextcloud_db STATEMENT:  UPDATE "oc_user_status" SET "is_backup" = $1, "user_id" = $2 WHERE "user_id" = $3                                    x
x2024-06-03 07:33:28.680 UTC [358445] ncadmin@nextcloud_db ERROR:  duplicate key value violates unique constraint "user_status_uid_ix"                                                      x
x2024-06-03 07:33:28.680 UTC [358445] ncadmin@nextcloud_db DETAIL:  Key (user_id)=(_karolina.kalinska) already exists.                                                                      x
x2024-06-03 07:33:28.680 UTC [358445] ncadmin@nextcloud_db STATEMENT:  UPDATE "oc_user_status" SET "is_backup" = $1, "user_id" = $2 WHERE "user_id" = $3                                    x
x2024-06-03 07:34:13.684 UTC [359021] ncadmin@nextcloud_db ERROR:  duplicate key value violates unique constraint "user_status_uid_ix"                                                      x
x2024-06-03 07:34:13.684 UTC [359021] ncadmin@nextcloud_db DETAIL:  Key (user_id)=(_karolina.kalinska) already exists.                                                                      x
x2024-06-03 07:34:13.684 UTC [359021] ncadmin@nextcloud_db STATEMENT:  UPDATE "oc_user_status" SET "is_backup" = $1, "user_id" = $2 WHERE "user_id" = $3                                    x
x2024-06-03 08:36:04.047 UTC [415127] ncadmin@nextcloud_db LOG:  could not receive data from client: Connection reset by peer                                                               x
x2024-06-03 08:36:04.503 UTC [451389] ncadmin@nextcloud_db LOG:  could not send data to client: Broken pipe                                                                                 x
x2024-06-03 08:36:04.503 UTC [451389] ncadmin@nextcloud_db STATEMENT:  SELECT "storage_id", "root_id", "user_id", "mount_point", "mount_id", "f"."path", "mount_provider_class" FROM "oc_moux
x2024-06-03 08:36:04.519 UTC [451389] ncadmin@nextcloud_db FATAL:  connection to client lost                                                                                                x
x2024-06-03 08:36:04.519 UTC [451389] ncadmin@nextcloud_db STATEMENT:  SELECT "storage_id", "root_id", "user_id", "mount_point", "mount_id", "f"."path", "mount_provider_class" FROM "oc_moux
x2024-06-03 08:36:04.542 UTC [421160] ncadmin@nextcloud_db LOG:  could not send data to client: Broken pipe                                                                                 x
x2024-06-03 08:36:04.542 UTC [421160] ncadmin@nextcloud_db STATEMENT:  SELECT "storage_id", "root_id", "user_id", "mount_point", "mount_id", "f"."path", "mount_provider_class" FROM "oc_moux
x2024-06-03 08:36:04.542 UTC [421160] ncadmin@nextcloud_db FATAL:  connection to client lost                                                                                                x
x2024-06-03 08:36:04.542 UTC [421160] ncadmin@nextcloud_db STATEMENT:  SELECT "storage_id", "root_id", "user_id", "mount_point", "mount_id", "f"."path", "mount_provider_class" FROM "oc_moux
x2024-06-03 08:36:04.548 UTC [429373] ncadmin@nextcloud_db LOG:  could not send data to client: Broken pipe                                                                                 x
x2024-06-03 08:36:04.548 UTC [429373] ncadmin@nextcloud_db STATEMENT:  SELECT "storage_id", "root_id", "user_id", "mount_point", "mount_id", "f"."path", "mount_provider_class" FROM "oc_moux
x2024-06-03 08:36:04.548 UTC [429373] ncadmin@nextcloud_db FATAL:  connection to client lost                                                                                                x
x2024-06-03 08:36:04.548 UTC [429373] ncadmin@nextcloud_db STATEMENT:  SELECT "storage_id", "root_id", "user_id", "mount_point", "mount_id", "f"."path", "mount_provider_class" FROM "oc_moux
x2024-06-03 08:36:04.549 UTC [465198] FATAL:  terminating connection due to administrator command                                                                                           x
x2024-06-03 08:36:04.549 UTC [465198] STATEMENT:  SELECT "storage_id", "root_id", "user_id", "mount_point", "mount_id", "f"."path", "mount_provider_class" FROM "oc_mounts" "m" INNER JOIN "x
x2024-06-03 08:36:04.594 UTC [426842] ncadmin@nextcloud_db LOG:  could not send data to client: Broken pipe                                                                                 x
x2024-06-03 08:36:04.594 UTC [426842] ncadmin@nextcloud_db STATEMENT:  SELECT "storage_id", "root_id", "user_id", "mount_point", "mount_id", "f"."path", "mount_provider_class" FROM "oc_moux
x2024-06-03 08:36:04.594 UTC [426842] ncadmin@nextcloud_db FATAL:  connection to client lost                                                                                                x
x2024-06-03 08:36:04.594 UTC [426842] ncadmin@nextcloud_db STATEMENT:  SELECT "storage_id", "root_id", "user_id", "mount_point", "mount_id", "f"."path", "mount_provider_class" FROM "oc_moux
x2024-06-03 08:36:04.617 UTC [1684] LOG:  background worker "parallel worker" (PID 465198) exited with exit code 1                                                                          x
x2024-06-03 08:36:04.622 UTC [439382] ncadmin@nextcloud_db LOG:  could not send data to client: Connection reset by peer                                                                    x
x2024-06-03 08:36:04.622 UTC [439382] ncadmin@nextcloud_db STATEMENT:  SELECT "storage_id", "root_id", "user_id", "mount_point", "mount_id", "f"."path", "mount_provider_class" FROM "oc_moux
x2024-06-03 08:36:04.622 UTC [439382] ncadmin@nextcloud_db FATAL:  connection to client lost                                                                                                x
x2024-06-03 08:36:04.622 UTC [439382] ncadmin@nextcloud_db STATEMENT:  SELECT "storage_id", "root_id", "user_id", "mount_point", "mount_id", "f"."path", "mount_provider_class" FROM "oc_moux
x2024-06-03 08:36:04.623 UTC [465196] FATAL:  terminating connection due to administrator command                                                                                           x
x2024-06-03 08:36:04.623 UTC [465196] STATEMENT:  SELECT "storage_id", "root_id", "user_id", "mount_point", "mount_id", "f"."path", "mount_provider_class" FROM "oc_mounts" "m" INNER JOIN "x
x2024-06-03 08:36:04.649 UTC [440508] ncadmin@nextcloud_db LOG:  could not receive data from client: Connection reset by peer                                                               x
x2024-06-03 08:36:04.653 UTC [1684] LOG:  background worker "parallel worker" (PID 465196) exited with exit code 1                                                                          x
x2024-06-03 08:36:04.734 UTC [444466] ncadmin@nextcloud_db LOG:  could not send data to client: Connection reset by peer                                                                    x
x2024-06-03 08:36:04.734 UTC [444466] ncadmin@nextcloud_db STATEMENT:  SELECT "storage_id", "root_id", "user_id", "mount_point", "mount_id", "f"."path", "mount_provider_class" FROM "oc_moux
x2024-06-03 08:36:04.734 UTC [444466] ncadmin@nextcloud_db FATAL:  connection to client lost                                                                                                x
x2024-06-03 08:36:04.734 UTC [444466] ncadmin@nextcloud_db STATEMENT:  SELECT "storage_id", "root_id", "user_id", "mount_point", "mount_id", "f"."path", "mount_provider_class" FROM "oc_moux
x2024-06-03 08:36:04.734 UTC [465201] FATAL:  terminating connection due to administrator command                                                                                           x
x2024-06-03 08:36:04.734 UTC [465201] STATEMENT:  SELECT "storage_id", "root_id", "user_id", "mount_point", "mount_id", "f"."path", "mount_provider_class" FROM "oc_mounts" "m" INNER JOIN "x
x2024-06-03 08:36:04.744 UTC [440507] ncadmin@nextcloud_db LOG:  could not send data to client: Broken pipe                                                                                 x
x2024-06-03 08:36:04.744 UTC [440507] ncadmin@nextcloud_db STATEMENT:  SELECT "storage_id", "root_id", "user_id", "mount_point", "mount_id", "f"."path", "mount_provider_class" FROM "oc_moux
x2024-06-03 08:36:04.744 UTC [440507] ncadmin@nextcloud_db FATAL:  connection to client lost                                                                                                x
x2024-06-03 08:36:04.744 UTC [440507] ncadmin@nextcloud_db STATEMENT:  SELECT "storage_id", "root_id", "user_id", "mount_point", "mount_id", "f"."path", "mount_provider_class" FROM "oc_moux
x2024-06-03 08:36:04.748 UTC [450707] ncadmin@nextcloud_db LOG:  could not send data to client: Connection reset by peer                                                                    x
x2024-06-03 08:36:04.748 UTC [450707] ncadmin@nextcloud_db STATEMENT:  SELECT "storage_id", "root_id", "user_id", "mount_point", "mount_id", "f"."path", "mount_provider_class" FROM "oc_moux
x2024-06-03 08:36:04.748 UTC [450707] ncadmin@nextcloud_db FATAL:  connection to client lost                                                                                                x
x2024-06-03 08:36:04.748 UTC [450707] ncadmin@nextcloud_db STATEMENT:  SELECT "storage_id", "root_id", "user_id", "mount_point", "mount_id", "f"."path", "mount_provider_class" FROM "oc_moux
x2024-06-03 08:36:04.762 UTC [452286] ncadmin@nextcloud_db LOG:  could not send data to client: Broken pipe                                                                                 x
x2024-06-03 08:36:04.762 UTC [452286] ncadmin@nextcloud_db STATEMENT:  SELECT "storage_id", "root_id", "user_id", "mount_point", "mount_id", "f"."path", "mount_provider_class" FROM "oc_moux
x2024-06-03 08:36:04.762 UTC [452286] ncadmin@nextcloud_db FATAL:  connection to client lost                                                                                                x
x2024-06-03 08:36:04.762 UTC [452286] ncadmin@nextcloud_db STATEMENT:  SELECT "storage_id", "root_id", "user_id", "mount_point", "mount_id", "f"."path", "mount_provider_class" FROM "oc_moux
x2024-06-03 08:36:04.801 UTC [1684] LOG:  background worker "parallel worker" (PID 465201) exited with exit code 1                                                                          x
x2024-06-03 08:36:04.863 UTC [452106] ncadmin@nextcloud_db LOG:  could not receive data from client: Connection reset by peer                                                               x
x2024-06-03 08:36:04.887 UTC [457018] ncadmin@nextcloud_db LOG:  could not send data to client: Broken pipe                                                                                 x
x2024-06-03 08:36:04.887 UTC [457018] ncadmin@nextcloud_db STATEMENT:  SELECT "storage_id", "root_id", "user_id", "mount_point", "mount_id", "f"."path", "mount_provider_class" FROM "oc_moux
x2024-06-03 08:36:04.887 UTC [457018] ncadmin@nextcloud_db FATAL:  connection to client lost                                                                                                x
x2024-06-03 08:36:04.887 UTC [457018] ncadmin@nextcloud_db STATEMENT:  SELECT "storage_id", "root_id", "user_id", "mount_point", "mount_id", "f"."path", "mount_provider_class" FROM "oc_moux
x2024-06-03 08:36:04.897 UTC [465220] FATAL:  terminating connection due to administrator command                                                                                           x
x2024-06-03 08:36:04.897 UTC [465220] STATEMENT:  SELECT "storage_id", "root_id", "user_id", "mount_point", "mount_id", "f"."path", "mount_provider_class" FROM "oc_mounts" "m" INNER JOIN "x
x2024-06-03 08:36:04.923 UTC [1684] LOG:  background worker "parallel worker" (PID 465220) exited with exit code 1                                                                          x
x2024-06-03 08:36:04.929 UTC [455157] ncadmin@nextcloud_db LOG:  could not receive data from client: Connection reset by peer                                                               x
x2024-06-03 08:36:04.962 UTC [455336] ncadmin@nextcloud_db LOG:  could not send data to client: Connection reset by peer                                                                    x
x2024-06-03 08:36:04.962 UTC [455336] ncadmin@nextcloud_db STATEMENT:  SELECT "storage_id", "root_id", "user_id", "mount_point", "mount_id", "f"."path", "mount_provider_class" FROM "oc_moux
x2024-06-03 08:36:04.962 UTC [455336] ncadmin@nextcloud_db FATAL:  connection to client lost                                                                                                x
x2024-06-03 08:36:04.962 UTC [455336] ncadmin@nextcloud_db STATEMENT:  SELECT "storage_id", "root_id", "user_id", "mount_point", "mount_id", "f"."path", "mount_provider_class" FROM "oc_moux
x2024-06-03 08:36:04.972 UTC [448793] ncadmin@nextcloud_db LOG:  could not send data to client: Broken pipe                                                                                 x
x2024-06-03 08:36:04.972 UTC [448793] ncadmin@nextcloud_db STATEMENT:  SELECT "storage_id", "root_id", "user_id", "mount_point", "mount_id", "f"."path", "mount_provider_class" FROM "oc_moux
x2024-06-03 08:36:04.972 UTC [448793] ncadmin@nextcloud_db FATAL:  connection to client lost                                                                                                x
x2024-06-03 08:36:04.972 UTC [448793] ncadmin@nextcloud_db STATEMENT:  SELECT "storage_id", "root_id", "user_id", "mount_point", "mount_id", "f"."path", "mount_provider_class" FROM "oc_moux
x2024-06-03 08:36:04.985 UTC [433414] ncadmin@nextcloud_db LOG:  could not send data to client: Broken pipe                                                                                 x
x2024-06-03 08:36:04.985 UTC [433414] ncadmin@nextcloud_db STATEMENT:  SELECT "storage_id", "root_id", "user_id", "mount_point", "mount_id", "f"."path", "mount_provider_class" FROM "oc_moux
x2024-06-03 08:36:04.985 UTC [433414] ncadmin@nextcloud_db FATAL:  connection to client lost                                                                                                x
x2024-06-03 08:36:04.985 UTC [433414] ncadmin@nextcloud_db STATEMENT:  SELECT "storage_id", "root_id", "user_id", "mount_point", "mount_id", "f"."path", "mount_provider_class" FROM "oc_moux
x2024-06-03 08:36:04.992 UTC [455152] ncadmin@nextcloud_db LOG:  could not send data to client: Connection reset by peer                                                                    x
x2024-06-03 08:36:04.992 UTC [455152] ncadmin@nextcloud_db STATEMENT:  SELECT "storage_id", "root_id", "user_id", "mount_point", "mount_id", "f"."path", "mount_provider_class" FROM "oc_moux
x2024-06-03 08:36:04.992 UTC [455152] ncadmin@nextcloud_db FATAL:  connection to client lost                                                                                                x
x2024-06-03 08:36:04.992 UTC [455152] ncadmin@nextcloud_db STATEMENT:  SELECT "storage_id", "root_id", "user_id", "mount_point", "mount_id", "f"."path", "mount_provider_class" FROM "oc_moux
x2024-06-03 08:36:05.016 UTC [456102] ncadmin@nextcloud_db LOG:  could not receive data from client: Connection reset by peer                                                               x
x2024-06-03 08:36:05.118 UTC [444840] ncadmin@nextcloud_db LOG:  could not send data to client: Broken pipe                                                                                 x
x2024-06-03 08:36:05.118 UTC [444840] ncadmin@nextcloud_db STATEMENT:  SELECT "storage_id", "root_id", "user_id", "mount_point", "mount_id", "f"."path", "mount_provider_class" FROM "oc_moux
x2024-06-03 08:36:05.119 UTC [444840] ncadmin@nextcloud_db FATAL:  connection to client lost                                                                                                x
x2024-06-03 08:36:05.119 UTC [444840] ncadmin@nextcloud_db STATEMENT:  SELECT "storage_id", "root_id", "user_id", "mount_point", "mount_id", "f"."path", "mount_provider_class" FROM "oc_moux
x2024-06-03 08:54:22.861 UTC [506710] ncadmin@nextcloud_db ERROR:  duplicate key value violates unique constraint "user_status_uid_ix"            
2024-06-03 08:36:05.119 UTC [444840] ncadmin@nextcloud_db FATAL:  connection to client lost                                                                                                x
x2024-06-03 08:36:05.119 UTC [444840] ncadmin@nextcloud_db STATEMENT:  SELECT "storage_id", "root_id", "user_id", "mount_point", "mount_id", "f"."path", "mount_provider_class" FROM "oc_moux
x2024-06-03 08:54:22.861 UTC [506710] ncadmin@nextcloud_db ERROR:  duplicate key value violates unique constraint "user_status_uid_ix"                                                      x
x2024-06-03 08:54:22.861 UTC [506710] ncadmin@nextcloud_db DETAIL:  Key (user_id)=(_karolina.kalinska) already exists.                                                                      x
x2024-06-03 08:54:22.861 UTC [506710] ncadmin@nextcloud_db STATEMENT:  UPDATE "oc_user_status" SET "is_backup" = $1, "user_id" = $2 WHERE "user_id" = $3                                    x
x2024-06-03 09:06:27.324 UTC [532927] ncadmin@nextcloud_db ERROR:  duplicate key value violates unique constraint "user_status_uid_ix"                                                      x
x2024-06-03 09:06:27.324 UTC [532927] ncadmin@nextcloud_db DETAIL:  Key (user_id)=(_karolina.kalinska) already exists.                                                                      x
x2024-06-03 09:06:27.324 UTC [532927] ncadmin@nextcloud_db STATEMENT:  UPDATE "oc_user_status" SET "is_backup" = $1, "user_id" = $2 WHERE "user_id" = $3                                    x
x2024-06-03 09:06:31.319 UTC [533033] ncadmin@nextcloud_db ERROR:  duplicate key value violates unique constraint "user_status_uid_ix"                                                      x
x2024-06-03 09:06:31.319 UTC [533033] ncadmin@nextcloud_db DETAIL:  Key (user_id)=(_karolina.kalinska) already exists.                                                                      x
x2024-06-03 09:06:31.319 UTC [533033] ncadmin@nextcloud_db STATEMENT:  UPDATE "oc_user_status" SET "is_backup" = $1, "user_id" = $2 WHERE "user_id" = $3                                    x
x2024-06-03 09:06:31.559 UTC [533040] ncadmin@nextcloud_db ERROR:  duplicate key value violates unique constraint "user_status_uid_ix"                                                      x
x2024-06-03 09:06:31.559 UTC [533040] ncadmin@nextcloud_db DETAIL:  Key (user_id)=(_karolina.kalinska) already exists.                                                                      x
x2024-06-03 09:06:31.559 UTC [533040] ncadmin@nextcloud_db STATEMENT:  UPDATE "oc_user_status" SET "is_backup" = $1, "user_id" = $2 WHERE "user_id" = $3                                    x
x2024-06-03 09:06:34.073 UTC [533111] ncadmin@nextcloud_db ERROR:  duplicate key value violates unique constraint "user_status_uid_ix"                                                      x
x2024-06-03 09:06:34.073 UTC [533111] ncadmin@nextcloud_db DETAIL:  Key (user_id)=(_karolina.kalinska) already exists.                                                                      x
x2024-06-03 09:06:34.073 UTC [533111] ncadmin@nextcloud_db STATEMENT:  UPDATE "oc_user_status" SET "is_backup" = $1, "user_id" = $2 WHERE "user_id" = $3                                    x
x2024-06-03 09:06:41.827 UTC [533328] ncadmin@nextcloud_db ERROR:  duplicate key value violates unique constraint "user_status_uid_ix"                                                      x
x2024-06-03 09:06:41.827 UTC [533328] ncadmin@nextcloud_db DETAIL:  Key (user_id)=(_karolina.kalinska) already exists.                                                                      x
x2024-06-03 09:06:41.827 UTC [533328] ncadmin@nextcloud_db STATEMENT:  UPDATE "oc_user_status" SET "is_backup" = $1, "user_id" = $2 WHERE "user_id" = $3                                    x
x2024-06-03 09:06:42.699 UTC [533344] ncadmin@nextcloud_db ERROR:  duplicate key value violates unique constraint "user_status_uid_ix"                                                      x
x2024-06-03 09:06:42.699 UTC [533344] ncadmin@nextcloud_db DETAIL:  Key (user_id)=(_karolina.kalinska) already exists.                                                                      x
x2024-06-03 09:06:42.699 UTC [533344] ncadmin@nextcloud_db STATEMENT:  UPDATE "oc_user_status" SET "is_backup" = $1, "user_id" = $2 WHERE "user_id" = $3                                    x
x2024-06-03 09:06:49.141 UTC [533501] ncadmin@nextcloud_db ERROR:  duplicate key value violates unique constraint "user_status_uid_ix"                                                      x
x2024-06-03 09:06:49.141 UTC [533501] ncadmin@nextcloud_db DETAIL:  Key (user_id)=(_karolina.kalinska) already exists.                                                                      x
x2024-06-03 09:06:49.141 UTC [533501] ncadmin@nextcloud_db STATEMENT:  UPDATE "oc_user_status" SET "is_backup" = $1, "user_id" = $2 WHERE "user_id" = $3                                    x
x2024-06-03 09:06:59.101 UTC [533717] ncadmin@nextcloud_db ERROR:  duplicate key value violates unique constraint "user_status_uid_ix"                                                      x
x2024-06-03 09:06:59.101 UTC [533717] ncadmin@nextcloud_db DETAIL:  Key (user_id)=(_karolina.kalinska) already exists.                                                                      x
x2024-06-03 09:06:59.101 UTC [533717] ncadmin@nextcloud_db STATEMENT:  UPDATE "oc_user_status" SET "is_backup" = $1, "user_id" = $2 WHERE "user_id" = $3      

The symptoms I am observing tell me that the problem is not in the database engine. It’s a problem with my Nextcloud, but I don’t know how to trace the database queries to find out what Nextcloud is actually doing on my server at the moment.

I would try to remove the duplicate entries manually.

Then if you suspect the cronjobs, there is a table oc_jobs, perhaps there is already one that takes a long time (column execution_duration).

And you can also try and if by disabling the user_status app, if it gets faster (especially if you think the bugs in issue #42625 might be back).