Convertire il DB di Nextcloud da Mysql a PostgeSQL

• Per NC 30, la conversione è disabilitata, occorre fare un patch dei files:
• p1.txt

# diff lib/private/DB/ConnectionFactory.orig.php lib/private/DB/ConnectionFactory.php
111c111
< 		$connectionParams = $this->createConnectionParams('', $additionalConnectionParams);
---
> 		$connectionParams = $this->createConnectionParams('', $additionalConnectionParams, $type);
182,183c182,185
< 	public function createConnectionParams(string $configPrefix = '', array $additionalConnectionParams = []) {
< 		$type = $this->config->getValue('dbtype', 'sqlite');
---
> 	public function createConnectionParams(string $configPrefix = '', array $additionalConnectionParams = [], $type = '') {
> 		if ($type === '') {
> 			$type = $this->config->getValue('dbtype', 'sqlite');
> 		}
215c217
< 		if ($this->config->getValue('mysql.utf8mb4', false)) {
---
> 		if ($this->config->getValue('mysql.utf8mb4', false) && $type==="mysql") {
# patch --backup --verbose  lib/private/DB/ConnectionFactory.php  p1.txt

• p2.txt

# diff core/Command/Db/ConvertType.orig.php core/Command/Db/ConvertType.php
158,160c158,160
< 		throw new \InvalidArgumentException(
< 			'This command is temporarily disabled (until the next maintenance release).'
< 		);
---
> 		// throw new \InvalidArgumentException(
> 		// 	'This command is temporarily disabled (until the next maintenance release).'
> 		// );
229c229
< 		$connectionParams = $this->connectionFactory->createConnectionParams();
---
> 		$connectionParams = $this->connectionFactory->createConnectionParams('', [], $type);

# patch --backup --verbose  core/Command/Db/ConvertType.php  p2.txt

• Preparare un file per la conversione docker-compose.conversion.yml dove si aggiunge solo l'istanza Postgresql:

  nextcloud-rvm-postgres:
    image: postgres:16.4-bookworm
    container_name: nextcloud-rvm-postgres
    restart: unless-stopped
    volumes:
      - ./pgdata:/var/lib/postgresql/data
    environment:
      - TZ=Europe/Rome
      - POSTGRES_DB=nextcloud
      - POSTGRES_USER=nextcloud
      - POSTGRES_PASSWORD=secretpass

• Preparare un file definitivo, dove si rimuove l'siatnza Mysql, si aggiornao i riferimenti al container postgres e si mettono i valori del DB Postgres docker-compose-pgsql.yml:

services:
  nextcloud-rvm-postgres:
    image: postgres:16.4-bookworm
    container_name: nextcloud-rvm-postgres
    restart: unless-stopped
    volumes:
      - ./pgdata:/var/lib/postgresql/data
    environment:
      - TZ=Europe/Rome
      - POSTGRES_DB=nextcloud
      - POSTGRES_USER=nextcloud
      - POSTGRES_PASSWORD=secretpass

  nextcloud-rvm-redis:
    image: redis:alpine
    container_name: nextcloud-rvm-redis
    restart: unless-stopped
    volumes:
      - ./redis:/data
    command: redis-server --save 60 1 --loglevel warning

  nextcloud-rvm-app:
    container_name: nextcloud-rvm-app
    # change in cron too
    image: nextcloud:30
    restart: unless-stopped
    ports:
      - 8182:80
    depends_on:
#      - nextcloud-rvm-db
      - nextcloud-rvm-postgres
      - nextcloud-rvm-redis
    volumes:
      - ./www:/var/www/html
      - /home/nextcloud/data:/var/www/html/data
      - ./custom_apps:/var/www/html/custom_apps
      - ./config:/var/www/html/config
      # better tmp fs for better performance, see https://github.com/nextcloud/recognize#tmp
      # Make sure that your RAM is big enough to store big files. Otherwise public uploads will fail.
      - type: tmpfs
        target: /tmp:exec
    environment:
      - TZ=Europe/Rome
      - NEXTCLOUD_TRUSTED_DOMAINS=cloud.rvmgroup.it
      - APPIMAGE_EXTRACT_AND_RUN=1
      - TRUSTED_PROXIES=192.168.253.254
      - OVERWRITEHOST=cloud.rvmgroup.it
      - OVERWRITEPROTOCOL=https
      - REDIS_HOST=nextcloud-rvm-redis
#      - MYSQL_PASSWORD=secretpass
#      - MYSQL_DATABASE=nextcloud
#      - MYSQL_USER=nextcloud
#      - MYSQL_HOST=nextcloud-rvm-db
      - POSTGRES_HOST=nextcloud-rvm-postgres
      - POSTGRES_DB=nextcloud
      - POSTGRES_USER=nextcloud
      - POSTGRES_PASSWORD=secretpass

  nextcloud-rvm-cron:
    # change in app too
    image: nextcloud:30
    container_name: nextcloud-rvm-cron
    restart: unless-stopped
    environment:
      - TZ=Europe/Rome
      - NEXTCLOUD_TRUSTED_DOMAINS=cloud.rvmgroup.it
      - APPIMAGE_EXTRACT_AND_RUN=1
      - TRUSTED_PROXIES=192.168.253.254
      - OVERWRITEHOST=cloud.rvmgroup.it
      - OVERWRITEPROTOCOL=https
#      - MYSQL_PASSWORD=secretpass
#      - MYSQL_DATABASE=nextcloud
#      - MYSQL_USER=nextcloud
#      - MYSQL_HOST=nextcloud-rvm-db
      - POSTGRES_HOST=nextcloud-rvm-postgres
      - POSTGRES_DB=nextcloud
      - POSTGRES_USER=nextcloud
      - POSTGRES_PASSWORD=secretpass
    volumes:
      - ./www:/var/www/html
      - /home/nextcloud/data:/var/www/html/data
      - ./custom_apps:/var/www/html/custom_apps
      - ./config:/var/www/html/config
    entrypoint: /cron.sh
    depends_on:
      - nextcloud-rvm-app

• Sequenza. Se dà errore per una colonna mancante in qualche tabella, verificare confrontandola su una installazione pulita, ed eliminarla (attenzione agli indici che vanno tolti prima ...)

docker compose exec --user www-data nextcloud-rvm-app ./occ  maintenance:mode --on
docker compose exec --user www-data nextcloud-rvm-app ./occ db:add-missing-indices
docker compose exec --user www-data nextcloud-rvm-app ./occ maintenance:repair --include-expensive

docker compose down

cp docker-compose-conversion.yml docker-compose.yml

docker compose pull && docker compose down && docker compose up -d --remove-orphans; docker compose logs -f; docker system prune; docker image prune -a; docker container prune

cp p1.txt www/
docker compose exec --user www-data nextcloud-rvm-app patch --backup --verbose  lib/private/DB/ConnectionFactory.php  p1.txt

cp p2.txt www/
docker compose exec --user www-data nextcloud-rvm-app patch --backup --verbose  core/Command/Db/ConvertType.php  p2.txt

docker compose exec --user www-data nextcloud-rvm-app ./occ db:convert-type --all-apps --password="secretpass" --port=5432 pgsql nextcloud nextcloud-rvm-postgres nextcloud

docker compose down

cp docker-compose-pgsql.yml docker-compose.ym

docker compose pull && docker compose down && docker compose up -d --remove-orphans; docker compose logs -f; docker system prune; docker image prune -a; docker container prune

docker compose exec --user www-data nextcloud-rvm-app ./occ db:add-missing-indices
docker compose exec --user www-data nextcloud-rvm-app ./occ maintenance:repair --include-expensive

Riferimenti

• Nextcloud migrate to PostgreSQL – Jean's Blog

• Changing database from sqlite to mariadb failed - ℹ️ Support / ???? Appliances (Docker, Snappy, VM, NCP, AIO) - Nextcloud community