How to upgrade postgresql from V13 to V14 or V15

ℹ️ Support
,
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