Upgrading the database to postgres 14 with NC22 update

lazmol · December 25, 2021, 10:52pm

Hello,
I was running nextcloud 21 with docker-compose based off the posgres-fpm example. Tried to upgrade with docker-compose pull and up -d as suggested here.
Now comes the first failure:

The data directory was initialized by PostgreSQL version 13, which is not compatible with this version 14.1.

I tried to follow a dumping and importing to fresh db strategy but then i got this error:

myconfig_postgres_fpm-db-1  | 2021-12-25 21:42:48.753 UTC [106] FATAL:  password authentication failed for user <ncadminuser>
myconfig_postgres_fpm-db-1  | 2021-12-25 21:42:48.753 UTC [106] DETAIL:  User <ncadminuser> does not have a valid SCRAM secret.

EDIT: this problem below is solved (only need to find a way to updrade db):

Ok then, I went back to stick to postgresql13 with tagging image: postgres:13-alpine
Then the nc server is running and could start the upgrade but this time i got pls update one major at a time: ok then added the tag nextcloud:22-fpm-alpine in my docker-compose.yml as per the link given under the error messsage from the web ui.

But with the 22 tag i get this:

$docker-compose logs app
myconfig_postgres_fpm-app-1  | Configuring Redis as session handler
myconfig_postgres_fpm-app-1  | Can't start Nextcloud because the version of the data (23.0.0.10) is higher than the docker image version (22.2.3.0) and downgrading is not supported. Are you sure you have pulled the newest image version?

now what? Pls don’t tell me I am stuck her in between versions.
What do you suggest doing? Thanks for any help!

simonspa · December 25, 2021, 11:19pm

Hi,

any reason you didn’t chose the “normal” way to upgrade PostgeSQL databases?

# Drop currently installed default 14 DB
pg_dropcluster --stop 14 main 
# Upgrade your 13 DB to 14
pg_upgradecluster 13 main
# Drop the old 13 DB
pg_dropcluster --stop 13 main

/S

lazmol · December 25, 2021, 11:37pm

Thanks @simonspa for the quick answer. I did not know its the normal way. Googled the error message and 3 of the first few hits were pretty much about dumping and importing. Where do you run those commands? docker-compose exec db bash does not even start for me since the db service’s container is not running due to the error
FATAL: database files are incompatible with server (already quoted above). So what version of postgres image do you start with? Thanks!

simonspa · December 26, 2021, 2:28pm

Hi @lazmol

sorry, I don’t have any experience with docker-compose - just with the database part
Maybe someone else can help here?

/S

lazmol · January 2, 2022, 6:13pm

Managed to run the nc server again by editing the version strings in version.php in the nextcloud volume. I use the stable tag in the nextcloud images in my docker-compose.yml from now on (and will probably check the current version before upgrading). I also use postgres:13-alpine for now. Need to find a way to upgrade my db to postgres 14. Any help appreciated!

szaimen · January 2, 2022, 6:32pm

I think the best way is to do a pg_dump on the old database, update the image and import the database dump again. Here is an example how it is done in the database container of Nextcloud AIO:

github.com

nextcloud/all-in-one/blob/main/Containers/postgresql/start.sh

#!/bin/bash

# Variables
DATADIR="/var/lib/postgresql/data"
DUMP_DIR="/mnt/data"
DUMP_FILE="$DUMP_DIR/database-dump.sql"
export PGPASSWORD="$POSTGRES_PASSWORD"

# Don't start database as long as backup is running
while [ -f "$DUMP_DIR/backup-is-running" ]; do
    echo "Waiting for backup container to finish..."
    sleep 10
done

# Check if dump dir is writeable
if ! [ -w "$DUMP_DIR" ]; then
    echo "DUMP dir is not writeable by postgres user."
    exit 1
fi

This file has been truncated. show original

(Of course DUMP_DIR and DATADIR need to be mounted somewhere on the host or in a volume)