Upgrade postgres for nextcloud

Postgres upgrade for nextcloud

After realizing I’m using an old (v11) version of PostgreSQL for my nextcloud, I decide to upgrade this to a newer one, v15. In the end, it wasn’t that easy to do this, as many of the instructions sounded like: “just do a dump of your data and import them to the new version”, but it won’t. So if you have a similar problem, I summarize my process here:

Just some of my errors I got:

[...]
FATAL:  password authentication failed for user "oc_admin"
User "nextcloud" does not have a valid SCRAM secret
[...]

Prerequisites

Docker and docker compose installed
Access to your nextcloud and postgreSQL containers
Backup storage for your database dumps and data directory

Step-by-Step Upgrade Instructions

Shut down all containers
Stop all running containers to prevent data changes during the upgrade:
```
docker-compose down
```
Start only the database container
Bring up just the PostgreSQL container so you can create backups:
```
docker-compose up -d db
```

Create a SQL dump backup

Dump your entire database to a file:

docker exec --user postgres nextcloud-db pg_dump --dbname=postgresql://nextcloud:<password>@nextcloud-db/nextcloud -f /var/lib/postgres/nextcloud.sql

Dump users and roles separately

It’s a good practice to export users and roles:

docker exec --user postgres nextcloud-db pg_dumpall -r --dbname=postgresql://nextcloud:<password>@nextcloud-db/nextcloud -f /var/lib/postgres/user_roles.sql

Shut down the database container
```
docker-compose down
```
Update postgreSQL version in docker compose
Edit your docker-compose.yml file and change the postgreSQL image tag to the desired new version, for example:
```
image: postgres:15
```
Backup your old postgreSQL data directory
Before starting the new container, back up your existing data directory (usually mapped as a docker volume or local path):
```
cp -r ./pg_data pg_data.old
```
Start the new database container
```
docker-compose up -d db
```

Drop and re-create a new nextcloud database

Connect to psql of your db

docker exec -it --user postgres nextcloud-db psql --dbname=postgresql://nextcloud:<password>@nextcloud-db

Inside the psql prompt, drop the existing database and create a new one:

DROP DATABASE nextcloud;
CREATE DATABASE nextcloud WITH OWNER nextcloud ENCODING 'UTF8' LC_COLLATE='C' LC_CTYPE='C' TEMPLATE template0;

Import users and roles

Restore the roles:

docker exec --user postgres nextcloud-db psql --dbname=postgresql://nextcloud:<password>@nextcloud-db/nextcloud -f /var/lib/postgresql/data/user_roles.sql

Import the data dump

Restore the database:

docker exec --user postgres nextcloud-db psql --dbname=postgresql://nextcloud:<password>@nextcloud-db/nextcloud -f /var/lib/postgresql/data/nextcloud.sql

Reset user passwords

Connect to PostgreSQL and reset passwords for your nextcloud users:

docker exec -it --user postgres nextcloud-db psql --dbname=postgresql://nextcloud:<password>@nextcloud-db/nextcloud

Inside psql run commands:

nextcloud=# \password nextcloud
nextcloud=# \password oc_admin

Restart all docker containers
```
docker-compose restart
```

Final Checks

Verify nextcloud is running and can connect to the database.
Check logs for any errors.
Remove old backups if everything works as expected.

Note: The boilerplate instructions were generated with the assistance of a large language model (LLM).

Sources:

openenterprise.it