1. Home
  2. OpenKM 6.3 - CE
  3. Migration guide
  4. How to switch between databases
  1.  

How to switch between databases

The process of migration between two databases can be divided into several steps:

Generate users and roles SQL scripts

From HSQLDB

SELECT 'INSERT INTO OKM_USER (USR_ID, USR_NAME, USR_PASSWORD, USR_EMAIL, USR_ACTIVE) VALUES (''' + USR_ID + ''', ''' + USR_NAME + ''', ''' + USR_PASSWORD + ''', ''' + USR_EMAIL + ''', ''' + USR_ACTIVE + ''');' FROM OKM_USER;
SELECT 'INSERT INTO OKM_ROLE (ROL_ID, ROL_ACTIVE) VALUES (''' + ROL_ID + ''', ''' + ROL_ACTIVE + ''');' FROM OKM_ROLE;
SELECT 'INSERT INTO OKM_USER_ROLE (UR_USER, UR_ROLE) VALUES (''' + UR_USER + ''', ''' + UR_ROLE + ''');' FROM OKM_USER_ROLE;

From MySQL

SELECT CONCAT('INSERT INTO OKM_USER (USR_ID, USR_NAME, USR_PASSWORD, USR_EMAIL, USR_ACTIVE) VALUES (''', USR_ID, ''', ''', USR_NAME, ''', ''', USR_PASSWORD, ''', ''', USR_EMAIL, ''', ''', USR_ACTIVE, ''');') FROM OKM_USER;
SELECT CONCAT('INSERT INTO OKM_ROLE (ROL_ID, ROL_ACTIVE) VALUES (''', ROL_ID, ''', ''', ROL_ACTIVE, ''');') FROM OKM_ROLE;
SELECT CONCAT('INSERT INTO OKM_USER_ROLE (UR_USER, UR_ROLE) VALUES (''', UR_USER, ''', ''', UR_ROLE, ''');') FROM OKM_USER_ROLE;

Generate database configuration parameters SQL scripts

  • Click at top right the Configuration parameters export icon .
  • Browser will download an SQL file with the configuration.

Export Metadata

  • Click top right Edit metadata.
  • In Edit view, copy the XML.

Export crontab

  • Go to  Administration > Crontab  > Download

Download ONLY the tasks that you have created and need to be migrated to the new OpenKM. The other tasks will be automatically created by OpenKM during the installation process.

Export the repository

Check if you have enough free space in the file system before exporting the entire repository.

  • Go to  Administration > Repository export
  • Set export repository from node okm:root
  • Set file system export folder /mnt/backup/root ( you should create one folder for each principal nodes; /okm:root, /okm:categories, /okm:temples, /okm:personal, /okm:mail, /okm:trash )
  • Check metadata ( mandatory )
  • Check history ( mandatory )
  • Click on "Execute" button

 

If you want to export okm:personal among other workspaces you should repeat the process for each one.

Installation of OpenKM

Install the same OpenKM version as you have right now in production.

Make a new Installation with the new database server.

From this point, all the steps are done in the new OpenKM installation.

Import the repository 

When you choose the option "preserve UUIDs", the nodes UUIDs are preserved while importing. This feature is useful when these UUID's are used by third-party applications and MANDATORY when you have categories in your repository.

Take into consideration that if you are importing into non-empty OpenKM, preserving the UUID's might have some collision with existing ones.

  • Go to Administration > Tools > Repository export
  • Set import repository destination /okm:root
  • Set file system location of the exported data /mnt/backup/root
  • Check metadata and history
  • Check "Restore UUIDs"
  • Click on "Execute" button

Execute scripts

Execute SQL script of users and roles generated at the beginning.

  • Go to Administration > Database query
  • On the bottom right choose type JDBC.
  • Write some valid SQL.
  • Click on Execute button.

Restore configuration parameters

Restoring configuration parameters will remove actual parameters and will be replaced by newer.

  • Go to Administration > Configuration parameters 
  • Go to bottom page.
  • Select configuration parameter SQL file.
  • Click  Import button.

Restore metadata

To add, modify or delete metadata there's only one operation for all, edit the metadata XML definition.

  • Go to Administration > Metadata 
  • Click top right Edit metadata.
  • In Edit view, paste the XML metadata definition you previously kept safe.
  • At bottom right, click Edit button.
  • At top right, click Register metadata groups to apply these changes.
  • Add groups to the profile.

Restore crontab task

If you have download Crontab task you might register them again:

  • Go to Administration > Crontab and follow the steps to register the tasks as they were in the old OpenKM.

Data what cannot be migrated and might be set again

Activity log migration it means migrate all data into OKM_ACTIVITY which might contains millions of rows. We suggest not to migrate it and only keep safe the old database if you need it in the future.