REUNICO | Digital transformation laboratory. Certified partner of Camunda: OTRS CloneDB Step-By-Step Guide

OTRS CloneDB Step-By-Step Guide

OTRS CloneDB database migration MySQL PostgreSQL

One day, one of our customers faced the need to transfer OTRS from the old MySQL database to PostgreSQL RDBMS. By the next morning, migration has been completed. How did we manage to do this? Find out below :)

  1. First step is: CloneDB package installation. Package can be found here.
    Installation can be run in two ways: the web interface, or using OTRS CLI:

    bin/otrs.Console.pl Admin::Package::Install
  2. Fix CloneDB (apply patch):

    patch < Base.pm.diff

    Base.pm.diff:
    
    --- Base.pm.old 2017-11-08 14:59:17.676848207 +0300
    +++ Base.pm 2017-11-08 15:09:14.560954253 +0300
    @@ -279,6 +279,7 @@
                 COLUMNVALUES:
                 for my $ColumnCounter ( 1 .. $#Columns ) {
                     my $Column = $Columns[$ColumnCounter];
    +       next COLUMNVALUES if ( $Self->{BlobColumns}->{ lc "$Table.$Column" } );
      
                     # get column value
                     my $ColumnValue = $Row[$ColumnCounter];
                
  3. CloneDB configuration settings: target DB, blob fields, web configurator, etc.
  4. Get the list of all blob fields:
    MySQL Example:
                    
    select table_name,column_name,data_type from INFORMATION_SCHEMA.COLUMNS where 
        data_type like '%blob%' and table_schema = 'otrs';
    
                
  5. Check selected fields data. If content is REAL binary (like article_attachment.content) - specify it in: CloneDB::BlobColumns setting.
  6. Stop all services related to OTRS:

    service apache2
    bin/otrs.Daemon.pl
                
  7. Start clone process (depends on database size - our experience is 30 GB database in less than one hour)

    bin/otrs.Console.pl Maint::Database::Clone
                
  8. Change database connection options in Kernel/Config.pm to a new (target) database.
  9. Migration completed!
    Clean session data, cache, etc. Start web server and OTRS Daemon:

    bin/otrs.Console.pl Maint::Session::DeleteAll
    bin/otrs.Console.pl Maint::Cache::Delete
    service apache2 start
    bin/otrs.Daemon.pl start