[Bug]: Migration to Postgresql 15 fails

jbmolle commented 1 year ago

⚠️ This issue respects the following points: ⚠️

[X] This is a bug, not a question or a configuration/webserver/proxy issue.
[X] This issue is not already reported on Github (I've searched it).
[X] Nextcloud Server is up to date. See Maintenance and Release Schedule for supported versions.
[X] Nextcloud Server is running on 64bit capable CPU, PHP and OS.
[X] I agree to follow Nextcloud's Code of Conduct.

Bug description

The migration from MariaDB to Postgresql 15 fails. resynchronizeDatabaseSequences function fails with an error nextcloud.xxx_id_seq', (SELECT MAX() FROM ))

Steps to reproduce

Get nextcloud on MariaDB
Install Postgresql 15 with user nextcloud and schema nextcloud
Launch the migration with occ db:convert-type
The migration fails during the resynchronization of the database sequences

Expected behavior

The migration should end without any problem and the database configuration of Nextcloud should point to the new Postgres database.

Installation method

Other Community project

Operating system

None

PHP engine version

None

Web server

None

Database engine version

PostgreSQL

Is this bug present after an update or on a fresh install?

None

Are you using the Nextcloud Server Encryption module?

None

What user-backends are you using?

[ ] Default user-backend (database)
[ ] LDAP/ Active Directory
[ ] SSO - SAML
[ ] Other

Configuration report

No response

List of activated Apps

- activity: 2.17.0
  - circles: 25.0.0
  - cloud_federation_api: 1.8.0
  - comments: 1.15.0
  - dashboard: 7.5.0
  - dav: 1.24.0
  - federatedfilesharing: 1.15.0
  - federation: 1.15.0
  - files: 1.20.1
  - files_pdfviewer: 2.6.0
  - files_rightclick: 1.4.0
  - files_sharing: 1.17.0
  - files_trashbin: 1.15.0
  - files_versions: 1.18.0
  - firstrunwizard: 2.14.0
  - logreader: 2.10.0
  - lookup_server_connector: 1.13.0
  - nextcloud_announcements: 1.14.0
  - notifications: 2.13.1
  - oauth2: 1.13.0
  - password_policy: 1.15.0
  - photos: 2.0.1
  - privacy: 1.9.0
  - provisioning_api: 1.15.0
  - recommendations: 1.4.0
  - related_resources: 1.0.3
  - serverinfo: 1.15.0
  - settings: 1.7.0
  - sharebymail: 1.15.0
  - support: 1.8.0
  - systemtags: 1.15.0
  - text: 3.6.0
  - theming: 2.0.1
  - twofactor_backupcodes: 1.14.0
  - updatenotification: 1.15.0
  - user_status: 1.5.0
  - viewer: 1.9.0
  - weather_status: 1.5.0
  - workflowengine: 2.7.0

Nextcloud Signing status

No response

Nextcloud Logs

No response

Additional info

No response

szaimen commented 1 year ago

Hi, can you post the exact commands that you used to create the psql 15 db?

jbmolle commented 1 year ago

Hi, I've just created a pull request to solve the bug. The problem is coming from the sequence resynchronization. In Postgres 15 you don't have access to public schema per default so I've created a nextcloud schema for my nextcloud user (which is the default schema). So my column_default is nextval('oc_xxxx_id_seq'::regclass) but the sequenceName is nextcloud.oc_xxx_id_seq and the WHERE clause does not work.

szaimen commented 1 year ago

I've just created a pull request to solve the bug.

Where?

In Postgres 15 you don't have access to public schema per default so I've created a nextcloud schema for my nextcloud user (which is the default schema).

Indeed you need to grant access to the public schema which is documented here: https://docs.nextcloud.com/server/latest/admin_manual/configuration_database/linux_database_configuration.html#postgresql-database

jbmolle commented 1 year ago

Thanks for the link, I didn't see it. If we don't use the public schema but instead the username schema, Nextcloud works the same (as username schema is the default). For the sequence migration, we just need to use getShortestName and getNamespaceName instead of getName.

nextcloud / server