Self-hosted Next Cloud database migration from SQLite to Postgres

Edwardon

I’m hosting my own Next Cloud with Docker on alwyzon.com storage VPS, it works quite well so far. Though, when more and more data has been stored on Next Cloud, it became less responsive. Therefore, I want to switch from SQLite to MariaDB or Postgres DB.

Note: I encourage being sure to make full backup of your Next Cloud data and database before proceed to database migration step.

https://docs.nextcloud.com/server/latest/admin_manual/maintenance/backup.html

My original SQLite Docker compose YAML

version: "2.1"
services:
  nextcloud:
    image: lscr.io/linuxserver/nextcloud:latest
    container_name: nextcloud
    environment:
      - PUID=1000
      - PGID=1000
      - TZ=Asia/Taipei
    volumes:
      - ./appdata:/config
      - ./data:/data
    ports:
      - 8081:443
    restart: unless-stopped

Docker exec into nextcloud container and run command below

# https://docs.nextcloud.com/server/latest/admin_manual/configuration_database/db_conversion.html

occ db:convert-type --clear-schema --password="xxx" --all-apps pgsql <db_username> pg_db <db_table_name>

First try with MariaDB but failed

version: "2.1"
services:

  db:
    container_name: maria-db
    restart: unless-stopped
    command: --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci --innodb-read-only-compressed=OFF --transaction-isolation=READ-COMMITTED --binlog-format=ROW
    image: mariadb
    environment:
      MYSQL_ROOT_PASSWORD: xxxxx
      MYSQL_DATABASE: xxxxx
      MYSQL_USER: xxxxx
      MYSQL_PASSWORD: xxxxx
    volumes:
      - ./mariadb:/var/lib/mysql
# Run schema conversion 

root@336dc85f8e9c:/# occ db:convert-type --clear-schema --password="xxxx" --all-apps mysql <db_username> pg_db <db_table_name>
Error message

I try to fix it with https://gist.github.com/ichiTechs/83e228fa1e6c83543623a1bf06f3eb32?permalink_comment_id=3944344#gistcomment-3944344 this example but not work at all.

Try with Postgres DB and it works

Docker compose YAML config

  pg_db:
    container_name: pg_db
    image: postgres:alpine
    restart: always
    environment:
      - POSTGRES_DB=xxx
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=xxx
    volumes:
      - ./pg_db:/var/lib/postgresql/data

Start the Postgres DB

docker-compose up -d pg_db

docker exec -it nextcloud bash

occ db:convert-type --clear-schema --password="xxx" --all-apps pgsql <db_username> pg_db <db_table_name>

Once migration process finish, then recreate your Next Cloud.

docker-compose restart nextcloud
docker-compose up -d --build
# https://www.codegrepper.com/code-examples/shell/docker+compose+down+single+service

Final result

References

  1. https://gist.github.com/ichiTechs/83e228fa1e6c83543623a1bf06f3eb32
  2. https://docs.nextcloud.com/server/latest/admin_manual/maintenance/backup.html
  3. https://stackoverflow.com/questions/32612650/how-to-get-docker-compose-to-always-re-create-containers-from-fresh-images
  4. https://github.com/nextcloud/docker
  5. https://help.nextcloud.com/t/how-to-nextcloud-with-postgres-in-docker/80820/4