Mittwoch, 6. August 2014

ownCloud: Migration from SQLite to MySQL/PostgreSQL possible via CLI - finally!

I've once created an ownCloud (5.x IIRC) installation for private purposes. Because I tried to create a "low footprint" installation, I used sqlite - which turned out to be a bad idea.

If you are running a ownCloud installation, you're going to use it. And after a while it's not only you using it. Your wife/husband will use it, your relatives and your friends for sure. Just because it is working and it is not only you who wants to share content but them as well - probably content you requested them to share with you. BTW, and just in case if you are not aware of, the ownCloud Android app can upload your pictures taken with your device instantly.

Ok, so what's the problem with sqlite? Locking! If you have some users, some Androids, some iOS devices and some shared links your ownCloud will definitely be slow. So you have to migrate. My first attempt was to migrate the data directory. But then you've to create all of your users manually. Remember the relatives, the friends and your wife/husband. If you want to avoid setting new passwords and telling them, you're going to change something (which might sometimes be a good idea, because they might tell you that they do not need the account any longer because they moved to their own-ownCloud), you want to migrate the database as well. This part turned out to be the hardest and was not possible without investing hours into a database migration.

Just before I migrated my ownCloud (6.something) to a different server, I updated it to version 7.0.1.1. This update was the very best update of my ownCloud installation (moving from 5 to 6 was like a mess). The admin interface told me I should migrate the database with this simple command:
./occ db:convert-type mysql owncloud localhost owncloud
To be fair, it told me to use "occ db:convert-type" but this echos
db:convert-type [--port="..."] [--password="..."] [--clear-schema] [--all-apps] type username hostname database
So my database migration took me 7 minutes and now I can move the whole installation to my new server - hopefully my puppet integration allows me to do this without much effort.

BTW, occ offers some cool stuff for administrating your ownCloud. I've not yet tried them all, but they look very useful to me:
# ./occ
ownCloud version 7.0.1

Usage:
  [options] command [arguments]

Options:
  --help           -h Display this help message.
  --quiet          -q Do not output any message.
  --verbose        -v|vv|vvv Increase the verbosity of messages: 1 for normal output, 2 for more verbose output and 3 for debug
  --version        -V Display this application version.
  --ansi              Force ANSI output.
  --no-ansi           Disable ANSI output.
  --no-interaction -n Do not ask any interactive question.

Available commands:
  help                        Displays help for a command
  list                        Lists commands
  status                      show some status information
  upgrade                     run upgrade routines
app
  app:disable                 disable an app
  app:enable                  enable an app
  app:list                    List all available apps
db
  db:convert-type             Convert the ownCloud database to the newly configured one
  db:generate-change-script   generates the change script from the current connected db to db_structure.xml
files
  files:scan                  rescan filesystem
ldap
  ldap:set-config             modifies an LDAP configuration
  ldap:show-config            shows the LDAP configuration
  ldap:test-config            tests an LDAP configuration
maintenance
  maintenance:mode            set maintenance mode
  maintenance:repair          repair this installation
  maintenance:singleuser      set single user mode
user
  user:lastseen               shows when the user was logged it last time
  user:report                 shows how many users have access
  user:resetpassword          Resets the password of the named user

Kommentare:

CA van Dijk hat gesagt…

Hi,

Glad I found your blogpost, it describes exactly my situation. I started with a sqlite setup on v5, then started to add some more users and somewhere around december I upgraded (with lots of problems) to v6.

Last week I did the upgrade to v7 (again, not without problems)

I'm still on Sqlite but would like to upgrade to MySQL. I noticed that the occ command was advertised in the backend for a migration, but I figured it would probably break my installaton.

Did you had to do any steps before issuing the migration command from the CLI?

Did you install Mysql first? Or does it all go automatically?

Thanks for helping out!

kallies hat gesagt…

I've previously installed MySQL and - because of a previous test - I already configured a owncloud user, created a owncloud database and set up propper permissions.

So this is basically what should be done before using occ. Besides that, you just need to make occ executable.

The configuration of ownClout itself will be updated by occ automatically.

Romain Jourdan hat gesagt…

Hello,

I am running owncloud v5 for some times now and when I moved my installation was down for a while.

I continued working in the mean time on my "local repository" i.e. my owncloud folders on my laptop.

Now I am settled down, I have put together my IT infra at home and want to start again my Owncloud service. It worked at the beginning but due to the multiple changes, I ended up with my data base to be locked.

Owncloud is not able to open it again.

I am planning to move to owncloud 7, a new server is already up and running with MYsql.

The good piece of news is that all my data from owncloud are in a specific ISCSI LUN so can be moved from a server to another.

How should I migrate? mount the iscsi volume on the new server and use the occ command as you describe? The full database will be migrated and from there I would be able to resume?

Or shall I have to unlock my database first?
Thans in advance!

CA van Dijk hat gesagt…
Dieser Kommentar wurde vom Autor entfernt.
CA van Dijk hat gesagt…

I finally found time for the conversion and it has worked flawlessly following your instructions. Thanks again!

Jesús hat gesagt…

I have a fresh owncloud 7 installation and your solution works perfectly.

Thanks!