Nextcloud user and data tansfer after (somehow) corrupted database

18. February 2021 - Reading time: 14 minutes

I run a Nextcloud instance since Nextcloud 10 came out, which means since somewhen 2016. Initially, this instance was used for file sharing and syncing calenders and address books for me and my wife. During the years until today, both its range of tasks and the amount of active users grew up, and similarly its demand for compute power. As a consequence, this Nextcloud instance went not only through all major releases that came out. It was also transferred from a shared webspace hoster over a 1-core-4-GiB virtual server to the 2-core-8-GiB virtual server where it lives today, all without setting it up from scratch. So actually pretty impressive that this Nextcloud survived the transfers, the release upgrades and me messing around with everything on that server while... gathering experience with Linux ;-).
Even if the Nextcoud's services ran without (noticeable) failures, during the last months my error log filled up with database-related issues, especially after my MariaDB database died one day mysteriously and had to be restored from a two-day-ago backup (I point to the hoster and some file system problems, but this was never admitted). While I was able to solve some of the issues by e.g. changing manually data types, new issues popped up faster and faster (surpise ;-)). So, over time, the need to tackle the database problem properly and fundamentally grew. To do that as clean and straightforward as possible, I planned (and performed) to set up a fresh Nextcloud, while finding the best compromise between transferring as little as possible from the (corrupted) database and annoying my users as little as possible.

To achieve that, the goal was to keep

• user names and passwords
• groups
• contacts and calendars
• file shares

After some discussions at help.nextcloud.com, I managed to realize all of these goals except of the file shares by performing the following steps:

1. Setup of a fresh Nextcould on a subdomain on the same server
   That makes it easy and safe to test the following transfer steps while the old Nextcloud is still running. As soon as everything is successfully transferred, I'm just switching the DNS records and the new Nextcloud appears at the same place where the old one existed. Configure a data directory that is located in the same base directory as the data directory of the old Nextcloud. Before starting any transfer, I recommend to take some time to fully configure this new instance and to install and configure all desired apps. I also strongly recommend to use the same Nextcloud server version for the new instance as it is used for the old one.
2. Transfer of user names, groups and passwords
   To perform that, I took a mysqldump of the related tables, deleted the equivalent tables in the new database, and injected the dumped tables into the new database with a script built from commands like

   mysqldump olddatabase oc_users > /.../oc_users.sql # dumps the table entry into the related sql-file
   mysql newdatabase -e "DROP TABLE oc_users"
   mysql newdatabase < /.../oc_users.sql

   The related tables are

   • oc_users
   • oc_addressbooks
   • oc_accounts
   • oc_groups
   • oc_group_admin
   • oc_group_user

This can be easily automated in a shell script. This becomes especially handy, when this sequence is called once again short before the final switch to the new Nextcloud, since users could have changed their passwords in between. After that, you still have to go through the user list in the new Nextcloud to check quotas etc.

3. Initial Transfer of contacts and calendars
   This was much more complicated than expected. Since I wanted to avoid as much transfer as possible from the old database (since I could transfer corrupted parts), I did not want to transfer the data tables related to contacts and calendars. Additionally, all contributing people to my threads at help.nextcloud.com advised me to avoid any direct calendar or contact data transfer as well. A possible alternative could be to use the impersonate app, log in as every user in the old instance, perform a calendar and contact export for each user and restore it the same way in the new instance. Since this is very laborious and also privacy-violating to mess around in someone else's user account, I decided not to do so. Instead, I found the following much better working sequence:
   • Export of all contact and calendar data from the old Nextcloud with the incredibly handy tool calcardbackup
     I used the options -one and -x to directly get one file per calendar event and contact, because some of my users store incredibly large calendars, where all tools I've tried later refuse to work with if these calendars are stored in one file. I also used the option -d 0 to avoid getting a time stamp to the related directory, because this makes the repeated performance of the following steps much more straightforward.
   • Sorting all calendar and contact files to single folders
     This is necessary for the following step and can be easily done with mv and the use of wildcards, since the related user name and calendar name is part of each contact or calendar file.
   • Sync the calendar and contact files to the new Nextcloud with the super cool vdirsyncer, a command-line CalDAV/CardDAV client.
     Here, the application was not that intuitive for me, but if one follows carefully the full configuration manual and allows some test time, it works flawlessly well and is very reliable.
     The important point is that you sync all the calendars to an extra created transfer account. The assignment of each address book and calendar to the respective user accounts happens in a later step. In short, the used commands look like that:

     vdirsyncer -c /.../vdirsyncerconfig-all-Contacts discover
     vdirsyncer -c /.../vdirsyncerconfig-all-Calendars discover
     vdirsyncer -c /.../vdirsyncerconfig-all-Contacts sync
     vdirsyncer -c /.../vdirsyncerconfig-all-Calendars sync

     With the vdirsyncer config scripts looking like:
     vdirsyncerconfig-all-Contacts

     general]
     status_path = "/.../migration/vdirsyncerstatus/"
     [pair addressbooks]
     a = "local"
     b = "new"
     collections = ["userA-Contacts", "userB-Contacts", "userC-Contacts", "..."]
     [storage local]
     type = "filesystem"
     path = "/.../migration/dav/"
     read_only = "true"
     fileext = ".vcf"
     [storage new]
     type = "carddav"
     url = "https://newnextcloud.tld/"
     username = "transfer"
     password = "password"

     
     vdirsyncerconfig-all-Calendars

     [general]
     status_path = "/.../migration/vdirsyncerstatus/"
     [pair calendars]
     a = "local"
     b = "new"
     collections = ["CalendarA", "CalendarB", "CalendarC", "..."]
     [storage local]
     type = "filesystem"
     path = "/.../migration/dav/"
     read_only = "true"
     fileext = ".ics"
     [storage new]
     type = "caldav"
     url = "https://newnextcloud.tld/"
     username = "transfer"
     password = "password"

• The big advantage of this sequence is that one is able to do the initial sync of contacts and calendars to the new Nextcloud while the old Nextcloud is still in active use, and no user data is ever leaving the server. Due to the huge calendars of some users, this initial transfer took some hours in my case, but went in background without being noticeable for the users. The final sync when switching over to the new Nextcloud is then only transferring the changes that happened in the meantime, which results in a very short downtime.

4. Performing the final transfer
   This step is the first one that the users are noticing. Since it means some downtime to the Nextcloud instance, it is best to check once again if everything is well prepared. It is also a good idea to run a full backup - you never know what could happen...
   After this, the relevant steps are:

   • Activate the maintenance mode for the old Nextcloud. This prevents users from login, so no changes to user accounts, calendars, contacts and so on can happen.
   • Renew the database tables to transfer as described above. This updates e.g. group assignments or passwords that were changed in the meantime.
   • Repeat the contact and calendar synchronization as described above to bring the new Nextcloud up to date.
   • Now you have all updated calendars and address books in the transfer account. I recommend to take the time to log into that transfer account in the new nextcloud and check if all calendars and address books appear. Then log out from this account.
   • Now change the ownership for all calendars and address books in the database from the transfer account to the actual owner. Since I like graphical interfaces, I took phpmyadmin for that. The related tables are oc_addressbooks and oc_calendars, the entries to change belong to the respective columns principaluri, displayname and uri. This is quite easy, since the vdirsyncer put the actual owner to the calendar or address book name.
   • Now activate the maintenance mode for the new Nextcloud to perform the file transfer. This can happen either by copying (cp) or moving (mv) all user directories from the data directory of the old Nextcloud to the new data directory (not the appdata_ and the updater- directory!!!). I took mv since it is much faster and I took a backup befor anyhow (if something goes wrong).
   • Run occ files:scan --all in the new Nextcloud to update the new Nextcloud's database for all the transferred files.
   • Run occ files:scan-app-data in the new Nextcloud, to update that database as well (maybe not necessary, but I felt better to do that)
   • Change your webserver settings that the subdomain of the old Nextcloud now points to the new Nextcloud. For that you have to adjust the trusted_domains and overwrite_cli settings in the config.php of your new Nextcloud.
   • Now you can switch off the maintenance mode of the new Nextcloud so that your users can log in again. Everything should work, but it is better to check some critical points (Are my files in the right place? Are my address books named right? Do I see the right calendar entries?).
   • Please remember that no shares are transferred, so inform your users that they have to renew all user shares and link shares.
   • If you use the preview generator app (as I do) you can run the initial preview generation no in the background.
   • You also have to inform your users that all login tokens as e.g. smartphone apps usually use are no longer valid and the apps will maybe not be smart enough to trigger a new login, so you have to do that manually. Since this can result in lots of failed login attempts from these apps in the meantime, it is useful to deactivate the brute force settings in the new Nextcloud for a week or so, e.g. by whitelisting all IPs (0.0.0.0).

5. Conclusions
   With the above described steps I was able to transfer all user account data, files, address books and calendars from an old Nextcloud with somehow corrupted database to a new one. No further problems appeared for me. I know that my method has some downsides (e.g. no transfer of shares, manual calendar and address book assignation and the unpredictable reaction of some smartphone apps to the fact that an (allegedly) known Nextcloud no longer accepts the login token), but it is pretty easy and well preparable even for people who don't work in IT (like me). Also the downtime is well controllable, mainly dependent on the quality of preparation and the amount of files to be scanned during the final occ files:scan --all . For me it worked well enough to be documented for the next time ;-). If you have questions or found mistakes, please add a comment below!