Postgres database incompatible

Hi,

I just install 23.05.03 firmware on my RPI4 and now I have the following error on postgresql(used for nextcloud):

Wed Jul 3 18:08:30 2024 daemon.err postmaster[5830]: 2024-07-03 18:08:30.616 PDT [1] FATAL: database files are incompatible with server
Wed Jul 3 18:08:30 2024 daemon.err postmaster[5830]: 2024-07-03 18:08:30.616 PDT [1] DETAIL: The data directory was initialized by PostgreSQL version 14, which is not compatible with this version 15.1.
Wed Jul 3 18:08:30 2024 daemon.info postmaster[5830]: jail: jail (5831) exited with exit: 1

How to update the database so it will be compatible with v15.1?

Is this an existing own cloud instance that you have migrated over to OpenWrt 23?

The error is self descriptive.
OpenWrt packages PostgreSQL 15
Your own cloud DB was created under 14

You need to look up PostgreSQL 14->15 upgrade process which is not unique to OpenWrt. For some upgrades you can run a script. For others you need to dump all data out and restore it on a new database.
I would suggest that doing this on the router itself might be slow.

In any case, this isn’t an OpenWrt question so you will get limited support here.

2 Likes

Thanks, following the step I am able to convert the database to version 15. However, it seems nextcloud does not like the converted database. I got internal error when opening nextcloud webpage,

Again, you’re looking for support with postgresql or nextcloud, not OpenWrt :smile: you’re asking the wrong group of experts.

My advice, check the logs for what the internal error is. I would guess that you have a password encryption issue so you might need to change the postgres user password so it encrypts fresh. Or a permissions issue.
The logs are going to tell you what is wrong.

And then you can take those logs to a more appropriate forum :slight_smile: not trying to be an ass but your problem is not with OpenWrt.

I know, However Openwrt is somehow special linux, I cannot install two versions of Postgresql which required by Postgresql upgrade (or maybe it can but I don't know how).

edit: The root cause seems below:
PostgreSQL 15 database update is not working · Issue #1880 · nextcloud/docker · GitHub

OK seems Postgres has made major changes to the security concept, introducing that you need to grant permissions for each user who wants to create things in public schema. soruce

Means if you are like me, coming from Postgres 14 and upgraded a while back ago to Postgres 15. You need to assign your Nextcloud Admin user, not the DB admin user you defined for Container, permissions to create new Data Tables.
Means execute the following in your database (login to your container and use psql):
GRANT ALL ON SCHEMA public TO oc_ADMIN;
Please change ADMIN to the username of your admin account, but don't get rid of "oc_"! if you are unsure please use \dt, to see all tables and current owner of them.

It could happen, that the Container not auto execute the upgrade process again. To do so use:
docker exec --user www-data CONTAINERNAME php occ upgrade and
docker exec --user www-data CONTAINERNAME php occ maintenance:mode --off

P.S. would be nice, if we get a notification after an upgraded DB is detected.
Something like: warning we detect your have upgraded to Postgres 15 please be sure you grant permissions to all on public schema to your admin user!

Well imagine that.

So you have fixed it then?

unfortunately not.