Copy large files to Nextcloud

I'm running my own Nextcloud instance in a [docker container][nc-docker] with the `data` directory mounted from a local folder on my disk.
Unfortuantely it's not very satisfying to use the web UI or using a [davfs2 mount][nc-davfs2] of the disk to copy large files to the Nextcloud instance.
This is because files must be passed through the full [LAMP stack][lamp] which involves a lot overhead and slows down any large file uploads.

An alternative to upload files via the browser or WebDAV is to copy them directly into the user data folder and then force Nextcloud to sync the content with its internal file cache and database entries.

Say our Nextcloud instance data lives in `/opt/nextcloud/data` and our source files live in `/tmp/stage` on the server.
Further let the Nextcloud username be `my-user` and say the source data should be copied to `~/my-folder` in Nextcloud.

Copying the data would then look like this:

```bash
# copy files
rsync -avP /tmp/stage/ /opt/nextcloud/data/my-user/files/my-folder/
# fix permissions
chmod www-data:www-data /opt/nextcloud/data/my-user/files/my-folder
```

It's necessary to fix the permissions since the [default user][nc-default-user] in the [official Nextcloud docker images][nc-docker] is the `www-data` user.
See also the [Nextcloud documentation][nc-file-operations].
Details:

```console
$ docker-compose exec nextcloud id www-data
uid=33(www-data) gid=33(www-data) groups=33(www-data)
```

After the files have been copied, Nextcloud must be forced to re-index all files within this directory:

```console
$ docker-compose exec -u www-data nextcloud ./occ files:scan --path="/my-user/files/my-folder"
Starting scan for user 1 out of 1 (my-user)
+---------+-------+--------------+
| Folders | Files | Elapsed time |
+---------+-------+--------------+
| 6       | 354   | 00:00:01     |
+---------+-------+--------------+
```

The internal database and caches have been updated and your files will pop up in the web UI afterwards.

[nc-docker]: https://hub.docker.com/_/nextcloud/
[nc-davfs2]: https://docs.nextcloud.com/server/16/user_manual/files/access_webdav.html
[nc-default-user]: https://github.com/nextcloud/docker/blob/b23910be9215f8338aee419007feb70cdacb7741/20.0/apache/entrypoint.sh#L16
[nc-file-operations]: https://docs.nextcloud.com/server/stable/admin_manual/configuration_server/occ_command.html#file-operations
[lamp]: https://en.wikipedia.org/wiki/LAMP_%28software_bundle%29

Nextcloud: Migrate from SQLite to PostgreSQL

I recently changed the database in my personal Nextcloud instance.
Migrating databases is quite simple and took about an hour in my instance (~70GB of data).
I noticed a huge improvement in speed when using the PostgreSQL database – especially when it comes to concurrent access of multiple users.

I'm running Nextcloud in a Docker container, so this post describes the procedure when using Docker containers.
It might be slightly different in cases where Nextcloud has been directly installed to the server.

*I have done the migration in Nextcloud 18.0.4. Older or newer versions might differ, so please be careful.*

## Step 0: Preparations

Make sure to backup old data – especially the SQLite database file.
In case the migration will fail your original database will be left untouched, but still it's good to have a backup in case things are messed up accidentally.

## Step 1: Stop routing traffic to the Nextcloud instance

Make sure to stop any traffic to the instance.
In my case I disabled the rules in the reverse proxy which sits in front of my Nextcloud application, so that the instance does not receive any traffic and thus updates/modifications during the migration phase.

## Step 2: Add database to `docker-compose.yaml`

Let's first add a database to our `docker-compose.yaml`.
The sample below shows the absolute minimum configuration to run a Nextcloud instance with a PostgreSQL database.

```yaml
services:
  nextcloud:
    image: "nextcloud:18.0.4"
    ports:
      - "8080:80"
    volumes:
      - ./data:/var/www/html
  db:
    image: postgres:9.6
    environment:
      - POSTGRES_USER=ncuser
      - POSTGRES_PASSWORD=secret
      - POSTGRES_DB=ncdb
    volumes:
      - ./db:/var/lib/postgresql/data
```

After the `db` service has been added make sure to start the database by running `docker-compose up`.

## Step 3: Run migration

Now comes the database migration.
Fortunately the Nextcloud developers provide a tool for that which can be used to essentially make this a one-liner.

First connect to the running `nextcloud` instance as user `www-data`

```console
$ docker-compose exec -u www-data nextcloud bash
```

then run the database conversion script

```console
$ ./occ db:convert-type --port 5432 --all-apps --clear-schema pgsql ncuser db ncdb
```

The arguments `ncuser` (database user) and `ncdb` (database name) might differ depending on your setup.
The tool will interactively ask for your password and once it has access start the migration.
Both flags `--all-apps` and `--clear-schema` are optional.
The first one define if database tables of deactivated apps should be migrated or not.
The second one drops tables in the target database in case they have been existing before the migration.

This might take a while.
For me it took under an hour for a Nextcloud instance that has a size of about 70GB.

## Step 4: Confirm that config has been updated

After the database has been migrated successfully check that the config file has been updated properly.
It should list `dbtype` as `pgsql` and have values for the properties `dbname`, `dbhost`, `dbuser` and `dbpassword`.
This can be checked with the following command:

```console
$ docker-compose exec -u www-data nextcloud bash -c "cat /var/www/html/config/config.php | grep db[a-z]"
```

Restart the service once so that the new config is read and applied.

```console
$ docker-compose restart
```

## Step 5: Start routing traffic to the Nextcloud instance

You can now start to route traffic again to the instance.
Now the PostgreSQL database will be used instead of the SQLite database file.
Well done! :)


[Nextcloud](https://nextcloud.com) allows users to set up a set of tools to save and share data privately.
It is not tied to the public cloud, so that users can spin up instances on their own private hardware and keep their data privately.


Tag: nextcloud

Copy large files to Nextcloud

Nextcloud: Migrate from SQLite to PostgreSQL