Converting database type
You can convert a SQLite database to a better performing MySQL, MariaDB or PostgreSQL database with the Nextcloud command line tool. SQLite is good for testing and simple single-user Nextcloud servers, but it does not scale for multiple-user production servers.
Run the conversion
Conversion consists of two steps:
Establishing the target database (including its credentials)
Triggering the conversion tool which migrates the contents of the existing database to the target database
Establishing the target database
First create up the target (new) database (along with its associated username and password) by following the manual database configuration instructions for your chosen target database type:
Since the above db instructions uses the database name nextcloud
for the newly created database we will do so here for consistency, but you are free to use whatever database name you prefer. Use
the database name, database username, and database password you specified when creating the new database.
Triggering the conversion
The occ db:convert-type
command handles all the tasks of the conversion. The following are the parameters available:
php occ db:convert-type [options] type username hostname database
type
should be the target database type. The same values are available here as for the config.php
dbtype
parameter. It should be one of: mysql
for MariaDB/MySQL,
pgsql
for PostgresSQL, or oci
for Oracle.
The options:
--port="3306"
the database port (optional) [defaults to “3306”]--password="mysql_user_password"
password for the new database. If omitted the tool will ask you (optional)--clear-schema
clear schema (optional)--all-apps
by default, tables for enabled apps are converted, use to convert also tables of deactivated apps (optional)-n, --no-interaction
do not ask any interactive question
Note
The conversion tool searches for apps in your configured app folders and uses
the schema (table) definitions in the apps to create the new tables. Any tables that still exist for removed
apps will not be converted (even with option --all-apps
).
Let’s convert our existing (functioning) sqlite3 installation to be MariaDB/MySQL based:
php occ db:convert-type --password="<password>" --port="3306" --all-apps mysql <username> <hostname> nextcloud
Note
It was unnecessary to specify the port in this example because 3306
is already the default. We did so
merely for demonstration purposes and completeness in case the reader is using a non-standard port on their target
database server.
On success the converter will automatically configure the new database in your
Nextcloud config config.php
.
If you are converting to a MySQL/MariaDB database, you will also want to set mysql.utf8mb4
parameter to true in your config.php
:
php occ config:system:set mysql.utf8mb4 --type boolean --value="true"
If you like, you can view the changes that were made by looking for the db*
parameters in your config.php
(you could also use this command before
doing the conversion to compare your configuration before/after):
grep db config/config.php
Inconvertible tables
If you updated your Nextcloud instance, there might be remnants of old tables which are not used any more. The updater will tell you which ones these are.
The following tables will not be converted:
oc_permissions
...
You can ignore these tables. Here is a list of known old tables:
oc_calendar_calendars
oc_calendar_objects
oc_calendar_share_calendar
oc_calendar_share_event
oc_fscache
oc_log
oc_media_albums
oc_media_artists
oc_media_sessions
oc_media_songs
oc_media_users
oc_permissions
oc_privatedata - this table was later added again by the app privatedata (https://apps.nextcloud.com/apps/privatedata) and is safe to be removed if that app is not enabled
oc_queuedtasks
oc_sharing