How to upgrade postgresql from V13 to V14 or V15

ℹ️ Support
,
1

Nextcloud version (eg, 20.0.5): 25.0.2
Operating system and version (eg, Ubuntu 20.04): Ubuntu 20.04.5 LTS
Apache or nginx version (eg, Apache 2.4.25): nginx 1.23.2
PHP version (eg, 7.4): 131.9

Issue :

Hello,
I am running Nextcloud on container using the following ansible script :
https://github.com/spantaleev/nextcloud-docker-ansible-deploy

It runs the following containers :
Traefik
nginx
nextcloud
postgres

Here my issue is with postgres. I m running postgres 13, and try to follow all upgrade of nextcloud and postgres.

This time, I would like to upgrade from postgres 13 to postgres 15

But it seems the password encryption changed from V13 to V14(and V15)

It went from md5 to SCRAM.

So when I update from postgres v13 to v15, I get the “Internal Server error” on my browser.
and in syslog I get the following error message :

Dec 18 17:32:00 Nuage nextcloud-postgres\[3123447\]: 2022-12-18 16:32:00.114 UTC \[43\] FATAL:  password authentication failed for user "oc\_feydreva"
Dec 18 17:32:00 Nuage nextcloud-postgres\[3123447\]: 2022-12-18 16:32:00.114 UTC \[43\] DETAIL:  User "oc\_feydreva" does not have a valid SCRAM secret.

Is this the first time you’ve seen this error? (Y/N): Each time I tryu to upgrade from pgsql v13 to v15

Steps to replicate it:

  1. upgrade pgsql to v15

There is no log to show, since nextcloud doesn’t start at all.

As anybody been able to upgrade its db from v13 to v14 and v15 ?

thank you !

2

AFAIK there is no “upgrade” for Postgres - you need to dump/backup and restore into new version. Usually if there is braking change you will find upgrade procedures in the release notes of specific software version.

As this question is related to Postgres I think it better to ask this question at Postgres forum - likely you get better answers there.

3

Starting V14, password encryption is SCRAM instead of md5… that s why I get a SCRAM error.
I can change the pg_hba.conf from
host all all all scram-sha-256
to
host all all all md5
and get password working, but after that i get an aother error :

sudo docker exec -it nextcloud-apache php occ upgrade
Nextcloud or one of the apps require upgrade - only a limited number of commands are available
You may use your browser or the occ upgrade command to do the upgrade
Setting log level to debug
Turned on maintenance mode
Updating database schema
Exception: Database error when running migration 25000Date20220602190540 for app core
An exception occurred while executing a query: SQLSTATE[42501]: Insufficient privilege: 7 ERROR: permission denied for schema public
Update failed
Maintenance mode is kept active

I wonder if anybody has that issue.

4

as fas as i remember this is a “no go” to update nextcloud in a container.

that said, how did you update your postgres container? did you follow something like that: How to Upgrade PostgreSQL in Docker and Kubernetes - CloudyTuts

1 Like
5

I’m using docker-compose as per examples

I have succesfully upgraded NextCloud from version 22 to 27 (one major version at a time).

Then I tried to upgrade the postgresql from version 12 to 15 by dumping the previous db with pg_dumpall and restoring it with psql.

As far as I remember the commands were:

# Edit docker-compose.yml to create a a new db_15 service
# with the new postgresql version in a different folder
# (so it doesn't overwrite the original db)

sudo docker-compose up -d

sudo docker-compose exec db pg_dumpall -c -U nextcloud > db_12_dump_`date +%d-%m-%Y"_"%H_%M_%S`.sql

cat db_12_dump_24-06-2023_22_35_39.sql | sudo docker-compose exec -T db_15 psql -U nextcloud

sudo docker-compose down

# Edit docker-compose.yml to rename db_15 service to db and remove the old db service

sudo docker-compose up -d

Then I got the same error message about lacking of a valid SCRAM secret.


db_1 | 2023-06-25 02:16:13.015 UTC [112] FATAL: password authentication failed for user "oc_abinoam"

db_1 | 2023-06-25 02:16:13.015 UTC [112] DETAIL: User "oc_abinoam" does not have a valid SCRAM secret.

As @Feydreva , I also guessed it could be something related to a change in the method postgresql use to encrypt the passwords.

So I had a look and found out that the custom user “oc_abinoam”'s password is saved in config/config.php

sudo docker-compose exec app less config/config.php

  'dbname' => 'nextcloud',
  'dbhost' => 'db',
  'dbport' => '',
  'dbtableprefix' => 'oc_',
  'dbuser' => 'oc_abinoam',
  'dbpassword' => '<the_generated_password>',

So I just issued an “ALTER” postgresql command so it “reencrypts” the same password.

echo "ALTER USER oc_abinoam WITH PASSWORD '<the_password_that_I_saw_in_config.php>';" | sudo docker-compose exec -T db psql -U nextcloud

I did some quick tests and everything seems to be running ok.

1 Like
6

permission denied for schema public

In regard to this specific insufficient privilege error reported…

I’ve had the same issue when trying to install an app (preview_generator).

I ended up spinning up a fresh docker-compose based to use it for comparisons with my actual sever.

My exact error messages at the logs were:

[settings] Erro: Doctrine\DBAL\Exception\DriverException: An exception occurred while executing a query: SQLSTATE[42501]: Insufficient privilege: 7 ERROR:  permission denied for schema public
LINE 1: CREATE TABLE oc_calendar_appt_configs (id BIGSERIAL NOT NULL...
                     ^ at <<closure>>

 0. /var/www/html/3rdparty/doctrine/dbal/src/Connection.php line 1814
    Doctrine\DBAL\Driver\API\PostgreSQL\ExceptionConverter->convert(["Doctrine\\DBAL ... "], ["Doctrine\\DBAL\\Query"])

I then used psql on the new container to check schema permissions.

16:03 $ docker-compose exec db psql -U nextcloud
psql (15.3)
Type "help" for help.

nextcloud=# \dnS+
                                                  List of schemas
        Name        |       Owner       |           Access privileges            |           Description            
--------------------+-------------------+----------------------------------------+----------------------------------
 information_schema | nextcloud         | nextcloud=UC/nextcloud                +| 
                    |                   | =U/nextcloud                           | 
 pg_catalog         | nextcloud         | nextcloud=UC/nextcloud                +| system catalog schema
                    |                   | =U/nextcloud                           | 
 pg_toast           | nextcloud         |                                        | reserved schema for TOAST tables
 public             | pg_database_owner | pg_database_owner=UC/pg_database_owner+| standard public schema
                    |                   | =U/pg_database_owner                  +| 
                    |                   | oc_abinoam=C/pg_database_owner         | 
(4 rows)

nextcloud=# exit

When comparing to my actual server the line oc_abinoam=C/pg_database_owner at public schema was missing.

Then I granted the permissions with:

echo "GRANT ALL PRIVILEGES ON SCHEMA public TO oc_abinoam;" | sudo docker-compose exec -T db psql -U nextcloud

I’m not sure, but I’d be probably fine issuing only a GRANT CREATE.

Then I was able to install apps like preview_generator and calendar.

I hope this help anybody with the same issue.