Upgrade to Nextcloud 30.0.1 and database upgrade error

ℹ️ Support
,
1

Hello,

I use Nextcloud with docker.

I’ve just upgraded to Nextcloud 30.0.1 (with PostgreSQL 16) and I have to do a database upgrade.
But it does not work:

./occ upgrade
Nextcloud or one of the apps require upgrade - only a limited number of commands are available
You may use your browser or the occ upgrade command to do the upgrade
Setting log level to debug
Turned on maintenance mode
Updating database schema
Updated database
Doctrine\DBAL\Exception\DriverException: An exception occurred while executing a query: SQLSTATE[42501]: Insufficient privilege: 7 ERROR: permission denied for schema public
LINE 1: CREATE TABLE oc_appconfig_ex (id BIGSERIAL NOT NULL, appid V…
^
Update failed

Do you have any idea?

Thank you.

David.

2

welcome to the forum @dbercot :handshake:

it sounds you didn’t perform the upgrade in a right way, please review Docker upgrade procedure DB upgrades are handled automatically… the only remaining tasj ist usually to add new indices using occ

3

Normal upgrade is

sudo -u www-data php /path/to/nextcloud/updater/updater.phar

dokumentation

Unfortunately, I don’t know if this is relevant for you.

4

Not for Docker images. :slight_smile:

Those are merely docker compose pull && docker compose up -d

The image itself takes care of updating the code and triggering database upgrades.

1 Like
5

This suggests your PostgreSQL authentication configuration has changed.

Have you made any changes to your PostgreSQL environment?

Have you upgraded this instance previously?

Have you made any changes like upgrading PostgreSQL since your last Nextcloud upgrade?

P.S. If you had to resort to manually running occ upgrade with the Docker image, I presume the image triggered upgrade failed. You can confirm by checking your container startup log.

1 Like
6

Hi WWE,
I’ve followed the upgrade procedure.
I still was in Postgres 16 with Nextcloud 30.0.0 and the upgrade concerned only Nextcloud (now in 30.0.1).
But it seems that the DB upgrade is not OK…

7

In principle, I agree (I did docker-compose pull & docker-compose up -d), but it seems that there’s an issue with the process here.

8

Hello JTR,
I’ve made no change to my PostgreSQL environment.
This instance was upgraded many times previously with no problem.
Here are the logs:

docker-compose logs

Attaching to nextcloud, nextcloud_db
nextcloud_db |
nextcloud_db | PostgreSQL Database directory appears to contain a database; Skipping initialization
nextcloud_db |
nextcloud_db | 2024-10-19 23:30:45.807 UTC [1] LOG: starting PostgreSQL 16.4 (Debian 16.4-1.pgdg120+2) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
nextcloud_db | 2024-10-19 23:30:45.807 UTC [1] LOG: listening on IPv4 address “0.0.0.0”, port 5432
nextcloud_db | 2024-10-19 23:30:45.807 UTC [1] LOG: listening on IPv6 address “::”, port 5432
nextcloud_db | 2024-10-19 23:30:45.941 UTC [1] LOG: listening on Unix socket “/var/run/postgresql/.s.PGSQL.5432”
nextcloud_db | 2024-10-19 23:30:46.128 UTC [29] LOG: database system was shut down at 2024-10-19 23:30:40 UTC
nextcloud_db | 2024-10-19 23:30:46.326 UTC [1] LOG: database system is ready to accept connections
nextcloud_db | 2024-10-19 23:35:46.160 UTC [27] LOG: checkpoint starting: time
nextcloud_db | 2024-10-19 23:35:46.484 UTC [27] LOG: checkpoint complete: wrote 3 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.054 s, sync=0.115 s, total=0.325 s; sync files=2, longest=0.067 s, average=0.058 s; distance=0 kB, estimate=0 kB; lsn=0/774094C0, redo lsn=0/77409488

9

To clarify, I mean the container startup logs of the Nextcloud container.

I still was in Postgres 16 with Nextcloud 30.0.0 and the upgrade concerned only Nextcloud (now in 30.0.1).

Had you previously ever upgraded Nextcloud? Or was 30.0.0 the first version you deployed?

On the surface this looks like a simple matter of the CREATE permission not being enabled on schema public for the user you’re using to connect to your db server. Your Nextcloud would have needed these privileges at installation time and for (most) upgrades since.

Can you post your Compose file as well as the output of occ config:list system?

10

Hi again,

Here is all the logs:

Attaching to nextcloud, nextcloud_db
nextcloud | Warning: /var/www/html/config/redis.config.php differs from the latest version of this image at /usr/src/nextcloud/config/redis.config.php
nextcloud | Warning: /var/www/html/config/reverse-proxy.config.php differs from the latest version of this image at /usr/src/nextcloud/config/reverse-proxy.config.php
nextcloud | Warning: /var/www/html/config/s3.config.php differs from the latest version of this image at /usr/src/nextcloud/config/s3.config.php
nextcloud | Warning: /var/www/html/config/smtp.config.php differs from the latest version of this image at /usr/src/nextcloud/config/smtp.config.php
nextcloud | Warning: /var/www/html/config/upgrade-disable-web.config.php differs from the latest version of this image at /usr/src/nextcloud/config/upgrade-disable-web.config.php
nextcloud | => Searching for scripts (*.sh) to run, located in the folder: /docker-entrypoint-hooks.d/before-starting
nextcloud | AH00558: apache2: Could not reliably determine the server’s fully qualified domain name, using 172.18.0.4. Set the ‘ServerName’ directive globally to suppress this message
nextcloud | AH00558: apache2: Could not reliably determine the server’s fully qualified domain name, using 172.18.0.4. Set the ‘ServerName’ directive globally to suppress this message
nextcloud | [Sun Oct 20 14:50:27.262892 2024] [mpm_prefork:notice] [pid 1:tid 1] AH00163: Apache/2.4.62 (Debian) PHP/8.2.24 configured – resuming normal operations
nextcloud | [Sun Oct 20 14:50:27.262926 2024] [core:notice] [pid 1:tid 1] AH00094: Command line: ‘apache2 -D FOREGROUND’
nextcloud_db |
nextcloud_db | PostgreSQL Database directory appears to contain a database; Skipping initialization
nextcloud_db |
nextcloud_db | 2024-10-20 14:50:24.832 UTC [1] LOG: starting PostgreSQL 16.4 (Debian 16.4-1.pgdg120+2) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
nextcloud_db | 2024-10-20 14:50:24.832 UTC [1] LOG: listening on IPv4 address “0.0.0.0”, port 5432
nextcloud_db | 2024-10-20 14:50:24.832 UTC [1] LOG: listening on IPv6 address “::”, port 5432
nextcloud_db | 2024-10-20 14:50:25.379 UTC [1] LOG: listening on Unix socket “/var/run/postgresql/.s.PGSQL.5432”
nextcloud_db | 2024-10-20 14:50:25.670 UTC [29] LOG: database system was shut down at 2024-10-20 14:48:29 UTC
nextcloud_db | 2024-10-20 14:50:25.945 UTC [1] LOG: database system is ready to accept connections

Before, I was in Nextcloud 28, then 29, …
I’ve tried to change permissions (GRANT ALL ON SCHEMA public TO nextcloud;) but the result was the same.

Here is my compose file:

services:
nextcloud: # https://hub.docker.com/_/nextcloud/
image: nextcloud:latest
container_name: nextcloud
restart: always
depends_on:
- nextcloud_db
volumes:
- ./data/:/var/www/html/
environment:
- POSTGRES_HOST=nextcloud_db # nom du container qui héberge la base de données
- POSTGRES_USER=nextcloud # nom du superuser
- POSTGRES_PASSWORD=password # mot de passe du superuser
- POSTGRES_DB=nextcloud # nom de la base de données
- NEXTCLOUD_ADMIN_USER=admin_nextcloud # nom de l’administrateur Nextcloud
- NEXTCLOUD_ADMIN_PASSWORD=password # mot de passe de l’administrateur Nextcloud
- NEXTCLOUD_TRUSTED_DOMAINS=nextcloud.mydomain # autorise la connexion via le nom du serveur
- OVERWRITEPROTOCOL=https # redirige vers https
- VIRTUAL_HOST=nextcloud.mydomain # nom du serveur proxyfié (pour Let’s encrypt)
- LETSENCRYPT_HOST=nextcloud.mydomain # nom du certificat à créer (pour Let’s encrypt)
- LETSENCRYPT_EMAIL=nextcloud@mydomain # adresse mail (pour Let’s encrypt)
networks:
- frontend
- nextcloud_db
nextcloud_db: # https://hub.docker.com/_/postgres
image: postgres:16
container_name: nextcloud_db
restart: always
volumes:
- ./db/:/var/lib/postgresql/data/
environment:
- POSTGRES_USER=nextcloud # nom du superuser (le même nom est utilisé pour la base de données elle-même)
- POSTGRES_PASSWORD=password # mot de passe du superuser
networks:
- nextcloud_db
networks:
frontend:
name: frontend
external: true
nextcloud_db:
name: nextcloud_db

And the output of occ config:list system

Nextcloud or one of the apps require upgrade - only a limited number of commands are available
You may use your browser or the occ upgrade command to do the upgrade
{
“system”: {
“htaccess.RewriteBase”: “/”,
“memcache.local”: “\OC\Memcache\APCu”,
“apps_paths”: [
{
“path”: “/var/www/html/apps”,
“url”: “/apps”,
“writable”: false
},
{
“path”: “/var/www/html/custom_apps”,
“url”: “/custom_apps”,
“writable”: true
}
],
“overwriteprotocol”: “https”,
“passwordsalt”: “REMOVED SENSITIVE VALUE”,
“secret”: “REMOVED SENSITIVE VALUE”,
“trusted_domains”: [
“localhost”,
“nextcloud.mydomain”
],
“datadirectory”: “REMOVED SENSITIVE VALUE”,
“dbtype”: “pgsql”,
“version”: “30.0.0.14”,
“overwrite.cli.url”: “https://localhost”,
“dbname”: “REMOVED SENSITIVE VALUE”,
“dbhost”: “REMOVED SENSITIVE VALUE”,
“dbport”: “”,
“dbtableprefix”: “oc_”,
“dbuser”: “REMOVED SENSITIVE VALUE”,
“dbpassword”: “REMOVED SENSITIVE VALUE”,
“installed”: true,
“instanceid”: “REMOVED SENSITIVE VALUE”,
“mail_smtpmode”: “smtp”,
“mail_smtpsecure”: “tls”,
“mail_sendmailmode”: “smtp”,
“mail_from_address”: “REMOVED SENSITIVE VALUE”,
“mail_domain”: “REMOVED SENSITIVE VALUE”,
“mail_smtpauthtype”: “PLAIN”,
“mail_smtpauth”: 1,
“mail_smtphost”: “REMOVED SENSITIVE VALUE”,
“mail_smtpport”: “587”,
“mail_smtpname”: “REMOVED SENSITIVE VALUE”,
“mail_smtppassword”: “REMOVED SENSITIVE VALUE”,
“loglevel”: 0,
“maintenance”: true,
“theme”: “”
}
}

Thank you for your help.

David.

11

I don’t think there is schema change between 30.0.0 and 30.0.1. As the problem is in the DB connection I would double check if there was a (minor) postgres update which might have changed something… please double check all the VARs maybe there is some issue…

I just upgraded my test instance from 30.0.0 to 30.0.1 and this was very fast and the DB step was instant (0/0 looks like nothing to do):

app-1  | Configuring Redis as session handler
app-1  | Initializing nextcloud 30.0.1.2 ...
app-1  | Upgrading nextcloud from 30.0.0.14 ...
app-1  | => Searching for scripts (*.sh) to run, located in the folder: /docker-entrypoint-hooks.d/pre-upgrade
app-1  | Nextcloud or one of the apps require upgrade - only a limited number of commands are available
app-1  | You may use your browser or the occ upgrade command to do the upgrade
app-1  | Setting log level to debug
app-1  | Turned on maintenance mode
app-1  | Updating database schema
app-1  | Updated database
app-1  | Update app tables from App Store
app-1  | Copy the data into the new db structure
app-1  |
app-1  |  Starting ...
app-1  |
app-1  | Copy the data into the new db structure
app-1  |
app-1  |     0/0 [>---------------------------]   0% Starting ...
app-1  |
app-1  | Update app user_oidc from App Store
app-1  | Starting code integrity check...
app-1  | Finished code integrity check
app-1  | Update successful
app-1  | Turned off maintenance mode

but this one is on postgres:15 now.. sorry no more reference now.

12

Hi,
I think I’ve maybe found a solution.
I “manually” create objects…
I’ve started with the first table:

CREATE TABLE public.oc_appconfig_ex (id BIGSERIAL NOT NULL, appid VARCHAR(32) NOT NULL, configkey VARCHAR(64) NOT NULL, configvalue TEXT DEFAULT NULL, sensitive SMALLINT DEFAULT 0 NOT NULL, PRIMARY KEY(id));

then, after another error, a second one:

CREATE TABLE public.oc_ex_apps (id BIGSERIAL NOT NULL, appid VARCHAR(32) NOT NULL, version VARCHAR(32) NOT NULL, name VARCHAR(64) NOT NULL, daemon_config_name VARCHAR(64) DEFAULT ‘0’ NOT NULL, port SMALLINT NOT NULL, secret VARCHAR(256) NOT NULL, status JSON NOT NULL, enabled SMALLINT DEFAULT 0 NOT NULL, created_time BIGINT NOT NULL, PRIMARY KEY(id));

and now, the error is:

Doctrine\DBAL\Schema\Exception\IndexDoesNotExist: Index “ex_apps_c_port__idx” does not exist on table “oc_ex_apps”.

So, I’d like to create this index but I don’t know how…
Something like

CREATE INDEX ex_apps_c_port__idx ON public.oc_ex_apps USING btree…

Can someone tell me the format of this index?

Thank you very much.

David.

13

here the whole table of my 30.0.1 but creating the tables manually is not required an IMHO would not resolve the initial issue with the rights - I would focus there.

table public.oc_ex_apps

created with docker compose exec -it db pg_dump -d nextcloud -U nextcloud --schema-only --table=public.oc_ex_apps

--
-- PostgreSQL database dump
--

-- Dumped from database version 15.8 (Debian 15.8-1.pgdg120+1)
-- Dumped by pg_dump version 15.8 (Debian 15.8-1.pgdg120+1)

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: oc_ex_apps; Type: TABLE; Schema: public; Owner: nextcloud
--

CREATE TABLE public.oc_ex_apps (
    id bigint NOT NULL,
    appid character varying(32) NOT NULL,
    version character varying(32) NOT NULL,
    name character varying(64) NOT NULL,
    daemon_config_name character varying(64) DEFAULT '0'::character varying NOT NULL,
    port smallint NOT NULL,
    secret character varying(256) NOT NULL,
    status json NOT NULL,
    enabled smallint DEFAULT 0 NOT NULL,
    created_time bigint NOT NULL
);


ALTER TABLE public.oc_ex_apps OWNER TO nextcloud;

--
-- Name: oc_ex_apps_id_seq; Type: SEQUENCE; Schema: public; Owner: nextcloud
--

CREATE SEQUENCE public.oc_ex_apps_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.oc_ex_apps_id_seq OWNER TO nextcloud;

--
-- Name: oc_ex_apps_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: nextcloud
--

ALTER SEQUENCE public.oc_ex_apps_id_seq OWNED BY public.oc_ex_apps.id;


--
-- Name: oc_ex_apps id; Type: DEFAULT; Schema: public; Owner: nextcloud
--

ALTER TABLE ONLY public.oc_ex_apps ALTER COLUMN id SET DEFAULT nextval('public.oc_ex_apps_id_seq'::regclass);


--
-- Name: oc_ex_apps oc_ex_apps_pkey; Type: CONSTRAINT; Schema: public; Owner: nextcloud
--

ALTER TABLE ONLY public.oc_ex_apps
    ADD CONSTRAINT oc_ex_apps_pkey PRIMARY KEY (id);


--
-- Name: ex_apps__appid; Type: INDEX; Schema: public; Owner: nextcloud
--

CREATE UNIQUE INDEX ex_apps__appid ON public.oc_ex_apps USING btree (appid);


--
-- Name: ex_apps_c_port__idx; Type: INDEX; Schema: public; Owner: nextcloud
--

CREATE UNIQUE INDEX ex_apps_c_port__idx ON public.oc_ex_apps USING btree (daemon_config_name, port);


--
-- PostgreSQL database dump complete
--
14

Hi again,

I’ve found the solution…
The problem was only on the oc_admin_nextcloud…
So, I’ve done:

GRANT ALL ON SCHEMA public TO oc_admin_nextcloud;

and then, the upgrade process was OK.

Thank you.

David.

3 Likes
15

This topic was automatically closed 8 days after the last reply. New replies are no longer allowed.