How to fix thes errors in PostgreSQL Logs related to oc_filecache_extended ?

JOduMonT

While I still have bearly 500MiB (but a lot of tiny files) as data in this Nextcloud instance and not Nextcloud Client yet, I installed almost all recommended apps

While on Redis and Nextcloud side I don't see anything abnormal
Related to this issue it seams to be related to memcached
occ maintenance:repair only Removed 2 orphaned children entries
I also did an occ files:scan --all

Jan 14 09:14:13 2021-01-14 02:14:13.822 UTC [68545] user85bc018b6fe74f26913fd53467e14a05@db85bc018b6fe74f26913fd53467e14a05 STATEMENT: INSERT INTO "oc_filecache_extended" ("fileid", "upload_time") VALUES($1, $2)
Jan 14 09:14:51 2021-01-14 02:14:51.089 UTC [68562] user85bc018b6fe74f26913fd53467e14a05@db85bc018b6fe74f26913fd53467e14a05 ERROR: duplicate key value violates unique constraint "oc_filecache_extended_pkey"
Jan 14 09:14:51 2021-01-14 02:14:51.089 UTC [68562] user85bc018b6fe74f26913fd53467e14a05@db85bc018b6fe74f26913fd53467e14a05 DETAIL: Key (fileid)=(1532) already exists.
Jan 14 09:14:51 2021-01-14 02:14:51.089 UTC [68562] user85bc018b6fe74f26913fd53467e14a05@db85bc018b6fe74f26913fd53467e14a05 STATEMENT: INSERT INTO "oc_filecache_extended" ("fileid", "upload_time") VALUES($1, $2)
Jan 14 09:14:51 2021-01-14 02:14:51.479 UTC [68563] user85bc018b6fe74f26913fd53467e14a05@db85bc018b6fe74f26913fd53467e14a05 ERROR: duplicate key value violates unique constraint "oc_filecache_extended_pkey"
Jan 14 09:14:51 2021-01-14 02:14:51.479 UTC [68563] user85bc018b6fe74f26913fd53467e14a05@db85bc018b6fe74f26913fd53467e14a05 DETAIL: Key (fileid)=(1565) already exists.
Jan 14 09:14:51 2021-01-14 02:14:51.479 UTC [68563] user85bc018b6fe74f26913fd53467e14a05@db85bc018b6fe74f26913fd53467e14a05 STATEMENT: INSERT INTO "oc_filecache_extended" ("fileid", "upload_time") VALUES($1, $2)
Jan 14 09:14:51 2021-01-14 02:14:51.651 UTC [68565] user85bc018b6fe74f26913fd53467e14a05@db85bc018b6fe74f26913fd53467e14a05 ERROR: duplicate key value violates unique constraint "oc_filecache_extended_pkey"
Jan 14 09:14:51 2021-01-14 02:14:51.651 UTC [68565] user85bc018b6fe74f26913fd53467e14a05@db85bc018b6fe74f26913fd53467e14a05 DETAIL: Key (fileid)=(1534) already exists.
Jan 14 09:14:51 2021-01-14 02:14:51.651 UTC [68565] user85bc018b6fe74f26913fd53467e14a05@db85bc018b6fe74f26913fd53467e14a05 STATEMENT: INSERT INTO "oc_filecache_extended" ("fileid", "upload_time") VALUES($1, $2)
Jan 14 09:14:52 2021-01-14 02:14:52.363 UTC [68566] user85bc018b6fe74f26913fd53467e14a05@db85bc018b6fe74f26913fd53467e14a05 ERROR: duplicate key value violates unique constraint "oc_filecache_extended_pkey"
Jan 14 09:14:52 2021-01-14 02:14:52.363 UTC [68566] user85bc018b6fe74f26913fd53467e14a05@db85bc018b6fe74f26913fd53467e14a05 DETAIL: Key (fileid)=(1543) already exists.
Jan 14 09:14:52 2021-01-14 02:14:52.363 UTC [68566] user85bc018b6fe74f26913fd53467e14a05@db85bc018b6fe74f26913fd53467e14a05 STATEMENT: INSERT INTO "oc_filecache_extended" ("fileid", "upload_time") VALUES($1, $2)
Jan 14 09:14:52 2021-01-14 02:14:52.486 UTC [68569] user85bc018b6fe74f26913fd53467e14a05@db85bc018b6fe74f26913fd53467e14a05 ERROR: duplicate key value violates unique constraint "oc_filecache_extended_pkey"
Jan 14 09:14:52 2021-01-14 02:14:52.486 UTC [68569] user85bc018b6fe74f26913fd53467e14a05@db85bc018b6fe74f26913fd53467e14a05 DETAIL: Key (fileid)=(1573) already exists.
Jan 14 09:14:52 2021-01-14 02:14:52.486 UTC [68569] user85bc018b6fe74f26913fd53467e14a05@db85bc018b6fe74f26913fd53467e14a05 STATEMENT: INSERT INTO "oc_filecache_extended" ("fileid", "upload_time") VALUES($1, $2)
Jan 14 09:14:52 2021-01-14 02:14:52.974 UTC [68572] user85bc018b6fe74f26913fd53467e14a05@db85bc018b6fe74f26913fd53467e14a05 ERROR: duplicate key value violates unique constraint "oc_filecache_extended_pkey"
Jan 14 09:14:52 2021-01-14 02:14:52.974 UTC [68572] user85bc018b6fe74f26913fd53467e14a05@db85bc018b6fe74f26913fd53467e14a05 DETAIL: Key (fileid)=(1711) already exists.
Jan 14 09:14:52 2021-01-14 02:14:52.974 UTC [68572] user85bc018b6fe74f26913fd53467e14a05@db85bc018b6fe74f26913fd53467e14a05 STATEMENT: INSERT INTO "oc_filecache_extended" ("fileid", "upload_time") VALUES($1, $2)
Jan 14 09:14:53 2021-01-14 02:14:53.063 UTC [68571] user85bc018b6fe74f26913fd53467e14a05@db85bc018b6fe74f26913fd53467e14a05 ERROR: duplicate key value violates unique constraint "oc_filecache_extended_pkey"
Jan 14 09:14:53 2021-01-14 02:14:53.063 UTC [68571] user85bc018b6fe74f26913fd53467e14a05@db85bc018b6fe74f26913fd53467e14a05 DETAIL: Key (fileid)=(1707) already exists.
Jan 14 09:14:53 2021-01-14 02:14:53.063 UTC [68571] user85bc018b6fe74f26913fd53467e14a05@db85bc018b6fe74f26913fd53467e14a05 STATEMENT: INSERT INTO "oc_filecache_extended" ("fileid", "upload_time") VALUES($1, $2)
Jan 14 09:14:53 2021-01-14 02:14:53.198 UTC [68573] user85bc018b6fe74f26913fd53467e14a05@db85bc018b6fe74f26913fd53467e14a05 ERROR: duplicate key value violates unique constraint "oc_filecache_extended_pkey"
Jan 14 09:14:53 2021-01-14 02:14:53.198 UTC [68573] user85bc018b6fe74f26913fd53467e14a05@db85bc018b6fe74f26913fd53467e14a05 DETAIL: Key (fileid)=(1708) already exists.
Jan 14 09:14:53 2021-01-14 02:14:53.198 UTC [68573] user85bc018b6fe74f26913fd53467e14a05@db85bc018b6fe74f26913fd53467e14a05 STATEMENT: INSERT INTO "oc_filecache_extended" ("fileid", "upload_time") VALUES($1, $2)
Jan 14 09:14:53 2021-01-14 02:14:53.954 UTC [68579] user85bc018b6fe74f26913fd53467e14a05@db85bc018b6fe74f26913fd53467e14a05 ERROR: duplicate key value violates unique constraint "oc_filecache_extended_pkey"
Jan 14 09:14:53 2021-01-14 02:14:53.954 UTC [68579] user85bc018b6fe74f26913fd53467e14a05@db85bc018b6fe74f26913fd53467e14a05 DETAIL: Key (fileid)=(1736) already exists.
Jan 14 09:14:53 2021-01-14 02:14:53.954 UTC [68579] user85bc018b6fe74f26913fd53467e14a05@db85bc018b6fe74f26913fd53467e14a05 STATEMENT: INSERT INTO "oc_filecache_extended" ("fileid", "upload_time") VALUES($1, $2)
Jan 14 09:14:54 2021-01-14 02:14:54.070 UTC [68581] user85bc018b6fe74f26913fd53467e14a05@db85bc018b6fe74f26913fd53467e14a05 ERROR: duplicate key value violates unique constraint "oc_filecache_extended_pkey"
Jan 14 09:14:54 2021-01-14 02:14:54.070 UTC [68581] user85bc018b6fe74f26913fd53467e14a05@db85bc018b6fe74f26913fd53467e14a05 DETAIL: Key (fileid)=(1751) already exists.
Jan 14 09:14:54 2021-01-14 02:14:54.070 UTC [68581] user85bc018b6fe74f26913fd53467e14a05@db85bc018b6fe74f26913fd53467e14a05 STATEMENT: INSERT INTO "oc_filecache_extended" ("fileid", "upload_time") VALUES($1, $2)
Jan 14 09:14:54 2021-01-14 02:14:54.394 UTC [68584] user85bc018b6fe74f26913fd53467e14a05@db85bc018b6fe74f26913fd53467e14a05 ERROR: duplicate key value violates unique constraint "oc_filecache_extended_pkey"
Jan 14 09:14:54 2021-01-14 02:14:54.394 UTC [68584] user85bc018b6fe74f26913fd53467e14a05@db85bc018b6fe74f26913fd53467e14a05 DETAIL: Key (fileid)=(1756) already exists.
Jan 14 09:14:54 2021-01-14 02:14:54.394 UTC [68584] user85bc018b6fe74f26913fd53467e14a05@db85bc018b6fe74f26913fd53467e14a05 STATEMENT: INSERT INTO "oc_filecache_extended" ("fileid", "upload_time") VALUES($1, $2)
Jan 14 09:14:54 2021-01-14 02:14:54.492 UTC [68583] user85bc018b6fe74f26913fd53467e14a05@db85bc018b6fe74f26913fd53467e14a05 ERROR: duplicate key value violates unique constraint "oc_filecache_extended_pkey"
Jan 14 09:14:54 2021-01-14 02:14:54.492 UTC [68583] user85bc018b6fe74f26913fd53467e14a05@db85bc018b6fe74f26913fd53467e14a05 DETAIL: Key (fileid)=(1759) already exists.
Jan 14 09:14:54 2021-01-14 02:14:54.492 UTC [68583] user85bc018b6fe74f26913fd53467e14a05@db85bc018b6fe74f26913fd53467e14a05 STATEMENT: INSERT INTO "oc_filecache_extended" ("fileid", "upload_time") VALUES($1, $2)
Jan 14 09:14:54 2021-01-14 02:14:54.767 UTC [68586] user85bc018b6fe74f26913fd53467e14a05@db85bc018b6fe74f26913fd53467e14a05 ERROR: duplicate key value violates unique constraint "oc_filecache_extended_pkey"
Jan 14 09:14:54 2021-01-14 02:14:54.767 UTC [68586] user85bc018b6fe74f26913fd53467e14a05@db85bc018b6fe74f26913fd53467e14a05 DETAIL: Key (fileid)=(1760) already exists.
Jan 14 09:14:54 2021-01-14 02:14:54.767 UTC [68586] user85bc018b6fe74f26913fd53467e14a05@db85bc018b6fe74f26913fd53467e14a05 STATEMENT: INSERT INTO "oc_filecache_extended" ("fileid", "upload_time") VALUES($1, $2)
Jan 14 09:14:54 2021-01-14 02:14:54.933 UTC [68587] user85bc018b6fe74f26913fd53467e14a05@db85bc018b6fe74f26913fd53467e14a05 ERROR: duplicate key value violates unique constraint "oc_filecache_extended_pkey"
Jan 14 09:14:54 2021-01-14 02:14:54.933 UTC [68587] user85bc018b6fe74f26913fd53467e14a05@db85bc018b6fe74f26913fd53467e14a05 DETAIL: Key (fileid)=(1765) already exists.
Jan 14 09:14:54 2021-01-14 02:14:54.933 UTC [68587] user85bc018b6fe74f26913fd53467e14a05@db85bc018b6fe74f26913fd53467e14a05 STATEMENT: INSERT INTO "oc_filecache_extended" ("fileid", "upload_time") VALUES($1, $2)
Jan 14 09:14:55 2021-01-14 02:14:55.060 UTC [68585] user85bc018b6fe74f26913fd53467e14a05@db85bc018b6fe74f26913fd53467e14a05 ERROR: duplicate key value violates unique constraint "oc_filecache_extended_pkey"
Jan 14 09:14:55 2021-01-14 02:14:55.060 UTC [68585] user85bc018b6fe74f26913fd53467e14a05@db85bc018b6fe74f26913fd53467e14a05 DETAIL: Key (fileid)=(1762) already exists.
Jan 14 09:14:55 2021-01-14 02:14:55.060 UTC [68585] user85bc018b6fe74f26913fd53467e14a05@db85bc018b6fe74f26913fd53467e14a05 STATEMENT: INSERT INTO "oc_filecache_extended" ("fileid", "upload_time") VALUES($1, $2)
Jan 14 09:15:22 2021-01-14 02:15:22.545 UTC [68606] user85bc018b6fe74f26913fd53467e14a05@db85bc018b6fe74f26913fd53467e14a05 ERROR: duplicate key value violates unique constraint "oc_filecache_extended_pkey"
Jan 14 09:15:22 2021-01-14 02:15:22.545 UTC [68606] user85bc018b6fe74f26913fd53467e14a05@db85bc018b6fe74f26913fd53467e14a05 DETAIL: Key (fileid)=(3982) already exists.
Jan 14 09:15:22 2021-01-14 02:15:22.545 UTC [68606] user85bc018b6fe74f26913fd53467e14a05@db85bc018b6fe74f26913fd53467e14a05 STATEMENT: INSERT INTO "oc_filecache_extended" ("fileid", "upload_time") VALUES($1, $2)

nebulon

Hm this issue again...we have had various problems with those cache keys during app update. Apparently some nextcloud addons use fields and until recently the table primary key was not correctly set, resulting in not unique keys.
We have a special section for attempts to handle this in the start.sh since recently: https://git.cloudron.io/cloudron/nextcloud-app/-/blob/master/start.sh#L173

Which nextcloud package version are you currently at?

JOduMonT

@nebulon said in How to fix thes errors in PostgreSQL Logs related to oc_filecache- _extended ?:

Which nextcloud package version are you currently at?

Nextcloud Package v4.8.5 App v20.0.5

Nextcloud apps

Auditing / Logging 1.10.0 Featured
Bookmarks 4.0.5
Calendar 2.1.3 Featured
Circles 0.20.6 Featured
Collaborative tags 1.10.0 Featured
Comments 1.10.0 Featured
Contacts 3.4.3 Featured
Contacts Interaction 1.1.0 Featured
Dashboard 7.0.0 Featured
Deck 1.2.3 Featured
Deleted files 1.10.1 Featured
Duplicate Finder 0.0.4
External storage support 1.11.1 Featured
Federation 1.10.1 Featured
File sharing 1.12.2 Featured
First run wizard 2.9.0 Featured
Flow Upload 1.1.2
Forms 2.1.0 Featured
Impersonate 1.7.0 Featured
LDAP user and group backend 1.10.2 Featured
Log Reader 2.5.0 Featured
Monitoring 1.10.0
Featured News 15.1.1
Nextcloud announcements 1.9.0 Featured
Notes 4.0.2 Featured
Notifications 2.8.0 Featured
ONLYOFFICE 6.2.0 Featured
Password policy 1.10.1 Featured
PDF viewer 2.0.1 Featured
Photos 1.2.3 Featured
Privacy 1.4.0 Featured
QOwnNotesAPI 20.9.0
Recommendations 0.8.0 Featured
Retention 1.9.0 Featured
Right click 0.17.0 Featured
Share by mail 1.10.0 Featured
Social 0.4.2 Featured
Social sharing via Diaspora 2.1.0 Featured
Social sharing via email 2.1.0 Featured
Social sharing via Facebook 2.1.0 Featured
Social sharing via Twitter 2.1.0 Featured
Support 1.3.0 Featured
Talk 10.0.5 Featured
Tasks 0.13.6 Featured
Text 3.1.0 Featured
Theming 1.11.0 Featured
Two-Factor TOTP Provider 5.0.0
Update notification 1.10.0 Featured
Usage survey 1.8.0 Featured
User status 1.0.1 Featured
Versions 1.13.0 Featured
Video player 1.9.0 Featured
Weather status 1.0.0 Featured

I'm welling to execute command and disable apps for test purpose
just tell me

nebulon

@jodumont For a start, can you put the app into developer mode and then verify that indeed sudo -u www-data php /app/code/occ db:add-missing-primary-keys is the failing command?

After that you can run the sql commands mentioned in that start.sh if section. As far as I can tell if that is run, you shouldn't hit the issue you are seeing.

JOduMonT

@nebulon said in How to fix thes errors in PostgreSQL Logs related to oc_filecache_extended ?:

@jodumont For a start, can you put the app into developer mode and then verify that indeed sudo -u www-data php /app/code/occ db:add-missing-primary-keys is the failing command?

Dev Mode ?
I restarted the Nextcloud Apps in recovery mode
and executed the command in the Terminal App
it ran without issue

Check primary keys.
Done.

nebulon

yes I meant recovery mode. So can you then see in the logs where the issue you initially posted appears?

JOduMonT

@nebulon said in How to fix thes errors in PostgreSQL Logs related to oc_filecache_extended ?:

yes I meant recovery mode. So can you then see in the logs where the issue you initially posted appears?

ok I did it again this (my) morning
Nextcloud ran the command without any issue

Check primary keys.
Done.

and in PostgreSQL logs nothing appear

yesterday = 21:15
today = 06:46

Jan 15 17:15:36 Starting supervisor
Jan 15 17:15:37 2021-01-15 10:15:37,375 CRIT Supervisor running as root (no user in config file)
Jan 15 17:15:37 2021-01-15 10:15:37,393 INFO Included extra file "/etc/supervisor/conf.d/postgresql-service.conf" during parsing
Jan 15 17:15:37 2021-01-15 10:15:37,393 INFO Included extra file "/etc/supervisor/conf.d/postgresql.conf" during parsing
Jan 15 17:15:37 2021-01-15 10:15:37,462 INFO RPC interface 'supervisor' initialized
Jan 15 17:15:37 2021-01-15 10:15:37,481 CRIT Server 'inet_http_server' running without any HTTP authentication checking
Jan 15 17:15:37 2021-01-15 10:15:37,482 INFO RPC interface 'supervisor' initialized
Jan 15 17:15:37 2021-01-15 10:15:37,485 CRIT Server 'unix_http_server' running without any HTTP authentication checking
Jan 15 17:15:37 2021-01-15 10:15:37,485 INFO supervisord started with pid 1
Jan 15 17:15:38 2021-01-15 10:15:38,488 INFO spawned: 'postgresql' with pid 37
Jan 15 17:15:38 2021-01-15 10:15:38,490 INFO spawned: 'postgresql-service' with pid 38
Jan 15 17:15:38 2021-01-15 10:15:38.598 UTC [37] LOG: listening on IPv4 address "0.0.0.0", port 5432
Jan 15 17:15:38 2021-01-15 10:15:38.606 UTC [37] LOG: listening on IPv6 address "::", port 5432
Jan 15 17:15:38 2021-01-15 10:15:38.610 UTC [37] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
Jan 15 17:15:38 2021-01-15 10:15:38.785 UTC [45] LOG: database system was shut down at 2021-01-15 10:15:35 UTC
Jan 15 17:15:38 2021-01-15 10:15:38.817 UTC [37] LOG: database system is ready to accept connections
Jan 15 17:15:39 Postgresql service endpoint listening on https://:::3000
Jan 15 17:15:40 2021-01-15 10:15:40,459 INFO success: postgresql entered RUNNING state, process has stayed up for > than 1 seconds (startsecs)
Jan 15 17:15:40 2021-01-15 10:15:40,459 INFO success: postgresql-service entered RUNNING state, process has stayed up for > than 1 seconds (startsecs)
Jan 15 21:15:56 [POST] /databases
Jan 15 21:18:54 [POST] /databases
Jan 16 06:45:34 [POST] /databases
Jan 16 06:46:12 [GET] /healthcheck

nebulon

So where and when do those error logs initially posted appear then?

JOduMonT

@nebulon said in How to fix thes errors in PostgreSQL Logs related to oc_filecache_extended ?:

So where and when do those error logs initially posted appear then?

In PostgreSQL, the January 14, probably while I was messing with my CIFS

nebulon

@jodumont Actually I meant the original error logs from Nextcloud or are those gone by now and the issue somehow resolved? Asking since apparently then it was not related to the add-missing-primary-keys command?

amg-web

@nebulon said in How to fix thes errors in PostgreSQL Logs related to oc_filecache_extended ?:

into developer mode

Hi,

I could not find how to put app into developer mode
Can you tell me? I have same issue in DB

nebulon

@amg-web I mean the recovery mode, which you can enable for the app in the "Repair" section on the app configure view in the Cloudron dashboard.

Cloudron makes it easy to run web apps like WordPress, Nextcloud, GitLab on your server. Find out more or install now.

Cloudron Forum

How to fix thes errors in PostgreSQL Logs related to oc_filecache_extended ?

Nextcloud Package v4.8.5 App v20.0.5

Nextcloud apps