Upgrade Question: Howto upgrade Postgres 13 to 14 for nextcloud (docker)?

[Traszi]

Nextcloud version: 23.0.0
Operating system and version : Ubuntu 21.10

I have a running installation hosted in docker containers with docker compose.

docker/.examples/docker-compose/with-nginx-proxy/postgres/apache at master ·...

I wanted to switch from PostgreSQL 13 to 14, but it seems not to work if i just pull the newest docker image.

Is there a tutorial how to migrate to the latest postgres version?

My docker compose looks like this, and right now everything works fine, but i forced the Postgres 13 image. I read something that i need to migrate the dumped data, but i don’t exactly know how.

version: '3'

services:

  db:
    image: postgres:13-alpine
    restart: always
    volumes:
      - db:/var/lib/postgresql/data
    env_file:
      - db.env

  redis:
    image: redis:alpine
    restart: always

  app:
    image: nextcloud:apache
    entrypoint: sh -c "apt update && apt install -y libmagickcore-6.q16-6-extra && /entrypoint.sh apache2-foreground"
    restart: always
    volumes:
      - nextcloud:/var/www/html
    environment:
      - VIRTUAL_HOST=myhost.de
      - LETSENCRYPT_HOST=myhost.de
      - LETSENCRYPT_EMAIL=myemail@myemail.myemail
      - POSTGRES_HOST=db
      - REDIS_HOST=redis
    env_file:
      - db.env
    depends_on:
      - db
      - redis
    networks:
      - proxy-tier
      - default

  cron:
    user: "www-data:root"
    image: nextcloud:apache
    restart: always
    volumes:
      - nextcloud:/var/www/html
    entrypoint: /cron.sh
    depends_on:
      - db
      - redis

  proxy:
    build: ./proxy
    restart: always
    ports:
      - 80:80
      - 443:443
    labels:
      com.github.jrcs.letsencrypt_nginx_proxy_companion.nginx_proxy: "true"
    volumes:
      - certs:/etc/nginx/certs:ro
      - vhost.d:/etc/nginx/vhost.d
      - html:/usr/share/nginx/html
      - /var/run/docker.sock:/tmp/docker.sock:ro
    networks:
      - proxy-tier

  letsencrypt-companion:
    image: nginxproxy/acme-companion
    restart: always
    volumes:
      - certs:/etc/nginx/certs
      - acme:/etc/acme.sh
      - vhost.d:/etc/nginx/vhost.d
      - html:/usr/share/nginx/html
      - /var/run/docker.sock:/var/run/docker.sock:ro
    networks:
      - proxy-tier
    depends_on:
      - proxy


volumes:
  db:
  nextcloud:
  certs:
  acme:
  vhost.d:
  html:

networks:
  proxy-tier:

[rtznprmpftl]

postgres can’t really do an inplace upgrade just by changing out the executeables.

If you look at the logging output of that container, it will tell you that the data was created with an older version.

The easiest way to migrate would be creating a backup with pg_dumpall, starting a new container with the new version and restoring the backup there.

Alternatively you might want to read into the usage of pg_upgrade https://www.postgresql.org/docs/13/pgupgrade.html

[Traszi]

Hello rtznprmpftl, thank you for the reply.

I tried the following:

Dump the Postgres 13 database.

docker exec -i docker_nextcloud_db_1 /bin/bash -c "PGPASSWORD=1234 pg_dumpall --username nextcloud" > /home/steff/backup_postgresql/dumpall.sql

I also made a manual save from the postgres docker volume under “/var/lib/docker/volumes/docker_nextcloud_db/_data”.

Then i brought all containers down.

docker-compose down

Then i deleted everything in “/var/lib/docker/volumes/docker_nextcloud_db/_data”.
Edited the docker compose so it would pull postgres 14 container.

docker-compose pull
docker-compose up -d

Then i tried to restore from the dump.

docker exec -i docker_nextcloud_db_1 /bin/bash -c "psql -U nextcloud" < /home/steff/backup_postgresql/dumpall.sql

Here is a part of the Terminal Output, where i had 2 errors. The rest seemed to work?
Edit: Here is the COMPLETE part of the output…

SET
SET
SET
ERROR:  role "nextcloud" already exists
ALTER ROLE
CREATE ROLE
ALTER ROLE
You are now connected to database "template1" as user "nextcloud".
SET
SET
SET
SET
SET
 set_config
------------

(1 row)

SET
SET
SET
SET
SET
SET
SET
SET
SET
 set_config
------------

(1 row)

SET
SET
SET
SET
ERROR:  database "nextcloud" already exists
ALTER DATABASE
You are now connected to database "nextcloud" as user "nextcloud".
SET
SET
SET
SET
SET
 set_config
------------

(1 row)

SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
COPY 2
COPY 12
COPY 22096
COPY 0
COPY 7
COPY 2
COPY 227
COPY 0
COPY 11
COPY 1
COPY 0
COPY 0
COPY 0
COPY 0
COPY 0
COPY 0
COPY 0
COPY 0
COPY 0
COPY 5
COPY 0
COPY 2
COPY 10
COPY 6
COPY 2
COPY 8
COPY 3
COPY 0
COPY 0
COPY 0
COPY 0
COPY 0
COPY 0
COPY 0
COPY 0
COPY 0
COPY 0
COPY 0
COPY 0
COPY 0
COPY 0
COPY 0
COPY 17
COPY 25528
COPY 11691
COPY 44
COPY 0
COPY 0
COPY 0
COPY 0
COPY 1
COPY 1
COPY 36
COPY 0
COPY 0
COPY 108
COPY 67
COPY 3
COPY 0
COPY 0
COPY 0
COPY 1
COPY 0
COPY 0
COPY 88
COPY 0
COPY 1
COPY 0
COPY 0
COPY 0
COPY 0
COPY 1
COPY 0
COPY 3
COPY 0
COPY 0
COPY 0
COPY 0
COPY 1
COPY 8
COPY 0
COPY 0
COPY 0
COPY 2
COPY 1
COPY 0
COPY 2
COPY 2
COPY 6
COPY 0
COPY 3
 setval
--------
     42
(1 row)

 setval
--------
  22096
(1 row)

 setval
--------
      1
(1 row)

 setval
--------
      7
(1 row)

 setval
--------
      2
(1 row)

 setval
--------
      1
(1 row)

 setval
--------
    156
(1 row)

 setval
--------
     59
(1 row)

 setval
--------
      1
(1 row)

 setval
--------
      1
(1 row)

 setval
--------
      1
(1 row)

 setval
--------
      1
(1 row)

 setval
--------
      1
(1 row)

 setval
--------
      1
(1 row)

 setval
--------
      1
(1 row)

 setval
--------
      1
(1 row)

 setval
--------
      1
(1 row)

 setval
--------
      5
(1 row)

 setval
--------
      1
(1 row)

 setval
--------
      2
(1 row)

 setval
--------
     34
(1 row)

 setval
--------
      6
(1 row)

 setval
--------
      8
(1 row)

 setval
--------
      1
(1 row)

 setval
--------
      1
(1 row)

 setval
--------
      1
(1 row)

 setval
--------
      1
(1 row)

 setval
--------
      1
(1 row)

 setval
--------
      1
(1 row)

 setval
--------
      1
(1 row)

 setval
--------
      1
(1 row)

 setval
--------
      1
(1 row)

 setval
--------
     10
(1 row)

 setval
--------
      5
(1 row)

 setval
--------
     18
(1 row)

 setval
--------
  37040
(1 row)

 setval
--------
   1587
(1 row)

 setval
--------
      1
(1 row)

 setval
--------
      1
(1 row)

 setval
--------
      1
(1 row)

 setval
--------
   4640
(1 row)

 setval
--------
      1
(1 row)

 setval
--------
      4
(1 row)

 setval
--------
     67
(1 row)

 setval
--------
      3
(1 row)

 setval
--------
     10
(1 row)

 setval
--------
     29
(1 row)

 setval
--------
      2
(1 row)

 setval
--------
      1
(1 row)

 setval
--------
      1
(1 row)

 setval
--------
      1
(1 row)

 setval
--------
      1
(1 row)

 setval
--------
      1
(1 row)

 setval
--------
      1
(1 row)

 setval
--------
      1
(1 row)

 setval
--------
      1
(1 row)

 setval
--------
      1
(1 row)

 setval
--------
     13
(1 row)

 setval
--------
      1
(1 row)

 setval
--------
      3
(1 row)

 setval
--------
      1
(1 row)

 setval
--------
    427
(1 row)

 setval
--------
   1997
(1 row)

 setval
--------
      1
(1 row)

 setval
--------
      1
(1 row)

 setval
--------
      1
(1 row)

 setval
--------
      1
(1 row)

 setval
--------
      2
(1 row)

 setval
--------
      1
(1 row)

 setval
--------
      3
(1 row)

ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
REVOKE
GRANT
You are now connected to database "postgres" as user "nextcloud".
SET
SET
SET
SET
SET
 set_config
------------

(1 row)

SET
SET
SET
SET

The problem is, after that it did not work. After restarting all containers again, i see the error:

Internal Server Error

The server encountered an internal error and was unable to complete your request.
Please contact the server administrator if this error reappears multiple times, please include the technical details below in your report.
More details can be found in the server log.

EDIT:

I was able to reroll to Postgres 13: i changed the docker-compose to 13, restarted everything and copied my previously saved files directly back to “/var/lib/docker/volumes/docker_nextcloud_db/_data”.

I really wanted to upgrade, but i think the import / restore into the postgres 14 is not working correctly…

Any ideas what went wrong?
Do i use a wrong method to restore the dump or is there a problem with the errors when restoring?

[rtznprmpftl]

Please take a look at the Error messages from Nextcloud.

As the error message states More details can be found in the server log.

So the next thing to take a look at is the output of the nextcloud container, or the nextcloud.log file in the data directory.

Also, you could start a new Postgres container without destorying the old one. Dump the postgres 13 database, import it to the postgres 14 instance and then just change the database server that nextcloud uses, that way, changing it back is quite easy

[Traszi]

the /data/nextcloud.log is empty…

I run out of ideas.

The deleting was not elegant, that is true. I will try the way with an additional container to test again.

[Traszi]

So i made in the docker compose an additional container.

Tried then

docker exec -i docker_nextcloud_db_1 /bin/bash -c "pg_dumpall -U nextcloud" > /home/steff/backup_postgresql/dumpall.sql

docker exec -i docker_nextcloud_dbnew_1 /bin/bash -c "psql -U nextcloud" < /home/steff/backup_postgresql/dumpall.sql

I get the same result with the errors:

SET
SET
SET
ERROR:  role "nextcloud" already exists
ALTER ROLE
CREATE ROLE
ALTER ROLE

and

SET
SET
SET
ERROR:  database "nextcloud" already exists
ALTER DATABASE
You are now connected to database "nextcloud" as user "nextcloud".
SET
SET
SET

Same as before.

Edited the docker-compose, restarted everything, got the same Internal Server Error. There is nothing for this timestamp in the /data/nextcloud.log.

Something must go wrong with while restoring the database? I don’t really understand if i even have to use dumpall, or just dump?

I have initialized the postgres container with a password, username “nextcloud” and database “nextcloud”. In the nextcloud config though there is another database user with another password.

[Traszi]

Thank you rtznprmpftl for the help.

I now found out what the problem was, thought i’d share it, perhaps someone else has the same problem.

In my nextcloud in the …/config/config.php file there was a different user + password than in my .env file for the postgres docker container.

It worked for me, to psql like this with the user (“oc_steff”) from the config to my new postgres 14 container:

docker-compose exec -T db pg_dump -U oc_steff -d nextcloud | docker-compose exec -T db2 psql -U oc_steff -d nextcloud

This time there were no errors while restoring with psql.

db is my old postgres container and db2 my new one, definded in the docker-compose.yml:

  db:
    image: postgres:13-alpine
    restart: always
    volumes:
      - db:/var/lib/postgresql/data
    env_file:
      - db.env

  db2:
    image: postgres:14-alpine
    restart: always
    volumes:
      - db2:/var/lib/postgresql/data
    env_file:
      - db2.env

Finally i changed the config file of the nextcloud to the new “db2”:
sudo nano /var/lib/docker/volumes/docker_nextcloud_nextcloud/_data/config/config.php