"Shared with others" empty after upgrade to Postgresql 16 using pg_upgradecluster

When I upgrade from postgresql 15.5 to 16.1:

  • “Shared with others” is empty
  • “Shared with me” shows many entries

Background
I switched from mysql to postgresql 10 in 2021.

Since then I have painlessly upgraded postgresql using “pg_upgradecluster…”

System
Nextcloud version(s): 27.1.3, 27.1.4
Ubuntu 22.04.3 LTS
vmware guest / snapshot restored after each unsuccessful attempt
user backend: database

Steps to upgrade the database

sudo -u www-data php /var/www/nextcloud/occ maintenance:mode --on
sudo systemctl restart php8.2-fpm
sudo pg_dropcluster --stop 16 main
sudo pg_upgradecluster 15 main
sudo -u www-data php /var/www/nextcloud/occ maintenance:mode --off
sudo systemctl restart php8.2-fpm

After the database upgrade nextcloud appears functional except that “Shared with others” is empty.

Unsuccessful Steps Taken
The “oc_shares” table seems to be OK; this command returns the same counts before and after the upgrade:

psql -t -c "select share_type from oc_share;" -e nextcloud |sort |uniq -c 

pg_upgradecluster mentions a comment about a new default collation order:

DETAIL:  The database was created using collation version 2.27, but the operating system provides version 2.35.
HINT:  Rebuild all objects in this database that use the default collation and run ALTER DATABASE postgres REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.
WARNING:  database "postgres" has a collation version mismatch
DETAIL:  The database was created using collation version 2.27, but the operating system provides version 2.35.
HINT:  Rebuild all objects in this database that use the default collation and run ALTER DATABASE postgres REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.

The interwebs point to these commands to fix the collation -

  • “template1” continues to throw errors unless included in the repairs
  • This does not affect the problem with “Shared with others”
psql
\c nextcloud
reindex database nextcloud;
alter database nextcloud refresh collation version;
\c template1
reindex database template1;
alter database template1 refresh collation version;

I tried upgrading the database using “pg_dumpall” as described in this percona post, but that created a problem with the database user that I did not pursue.