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
Let's first add a database to our
The sample below shows the absolute minimum configuration to run a Nextcloud instance with a PostgreSQL database.
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
db service has been added make sure to start the database by running
# 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
docker-compose exec -u www-data nextcloud bash
then run the database conversion script
./occ db:convert-type --port 5432 --all-apps --clear-schema pgsql ncuser db ncdb
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.
--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
pgsql and have values for the properties
This can be checked with the following command:
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.
# 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! :)