Document Properties
Kbid28176D
Last Modified17-Sep-2020
Added to KB15-Feb-2017
Public AccessEveryone
StatusOnline
Doc TypeGuidelines, Concepts & Cookbooks
Product
  • IOM 2.0
  • IOM 2.1
  • IOM 2.2
  • IOM 2.9
  • IOM 2.15

Guide - IOM Database Migration (2.0 - 2.17)

1 Introduction

The following guide describes how to migrate the database to a newer version of IOM. The guide mainly targets technical services e.g. administrators, consultants and developers.

Note

No Migration of System

Please note that there is currently no migration of the system itself. A new installation is required using the appropriate installation guide.

1.1 Prerequisites

The installation of IOM in its next desired version is required. E.g., from 2.1.0.0 the version 2.1.5.0.

1.1.1 Knowledge

Knowledge on the following documents is required:

  • Operations and maintenance of PostgreSQL database, e.g., how to back up and restore an existing database
  • Installation guide of desired version of IOM, i.e., Overview - IOM Setup and referenced documents

1.2 Glossary

WordingDescription
IOMThe abbreviation for Intershop Order Management
OMSThe abbreviation for Order Management System, the technical name of IOM
SQLStructured Query Language

1.3 References

2 Release Package

The release package contains all scripts to migrate the database.

Ensure you are familiar with the IOM properties and variables that are delivered as part of release package.

Refer to the section Definition of Properties of the installation guide - at least the following ones:

  • Installation Properties - $OMS_USER and $OMS_HOME
  • Cluster Properties - is.oms.db.host, is.oms.db.port, is.oms.db.user and is.oms.db.user

2.1 Extraction

Reuse the properties of cluster, deployment, and installation from installation as much as possible. Hence only specific contents are extracted from the release package on backend server.

Extracting specific contents from the IOM release package
# after placing the package in $OMS_HOME in backend server
# as $OMS_USER

# extract for database migration
tar -xvf IOM-2.X.X.X.tgz postgres/migrations 

3 Data Migration - dbmigrate

This sub-section explains the steps for the data migration.

The script dbmigrate.sh located in $OMS_HOME/bin will first rebuild all stored procedures and then apply all migration scripts.

Possible errors during the stored procedure rebuild phase will appear in the output but will not stop the processing, whereas any error during the data migration itself will abort immediately, i.e., all changes of the corresponding migration script (and only this one) will be rolled back.

For a complete list of options, call

 $OMS_HOME/bin/dbmigrate.sh -?

3.1 On a Test Server

Note

Database Backup

The only way to roll back the database migration in case of an error is to replace the database with a backup.
It is thus essential to first test the migration against a recent copy of the database before running it against the live data.

  1. Extract the release package of the IOM.
  2. Execute the script dbmigrate.sh located in $OMS_HOME/bin

    Setting environment variables
    # as $OMS_USER
    cd $OMS_HOME/bin
    
    # mind the empty space after the first dot
    . ./set_env.sh

    Note

    Migration of Major and Minor Versions

    For major upgrades, e.g., from 2.0.x.x to 2.1.x.x, dbmigrate must be run twice, first with the option -dir set to postgres/migrations/archive, and then normally, using the default postgres/migrations.

  3. Perform the required SQL migration scripts located within the folder $OMS_HOME/postgres/migrations.

    Performing the data migration
    # as $OMS_USER 
    # set the connection variables
    PGHOST_TEST=<value of your test database server's IP address> # Example: 10.10.0.11
    PGPORT_TEST=<value of your test database server's port> # Example: 5432 
    PGUSER_TEST=<value of your test database user> # Example: oms_user
    PGDATABASE_TEST=<value of your test database name> # Example: oms_db
    
    # perform the migration 
    
    # 1) only for major upgrade (e.g from version 2.1.x.x to 2.2.x.x)
    #    apply older migration scripts located in the "..../migration/archive" directory 
    cd $OMS_HOME/bin
    ./dbmigrate.sh -h $PGHOST_TEST -p $PGPORT_TEST -U $PGUSER_TEST -d $PGDATABASE_TEST -dir ..../postgres/migrations/archive 2>&1 | tee dbmigrate.archive.test.out
    
    # 2) perform the standard migration
     ./dbmigrate.sh -h $PGHOST_TEST -p $PGPORT_TEST -U $PGUSER_TEST -d $PGDATABASE_TEST  2>&1 | tee dbmigrate.test.out 

    Abort on connect error

    The script will abort immediately with a message if it cannot connect to the database, e.g., due to some missing entry within the postgres ph_hba.conf file.

    Note

    Since IOM version 2.3.0.0:

    Possible issue with new foreign keys

    Some dbmigrate scripts will try to add some foreign keys to enforce the database integrity.
    Such operations may fail if your database is not clean, i.e. some tables contain weak references to non-existing entries in other tables.

    In such cases, the migration will raise an according WARNING rather than to stop, and the failed foreign keys will be registered within the table admin.missing_foreign_keys.
    You should clean such table per hand when possible and add the proposed foreigned key.
    The query to add the missing foreign key can also be found within the table admin.missing_foreign_keys.

    The next data migration will only retry to build those missing foreign key when the corresponding entry in admin.missing_foreign_keys has been manually removed.

  4. When the script runs without any error, the last line of the output will be success.

    dbmigrate.sh output example
    use following data  for the migration ?
       pg user: oms_user
       pg host: 10.0.66.9
       pg port: 5456
       db     : Oms22_TD_MM
       logging: /cygdrive/c/IOM/var/log
       scripts:
                 /cygdrive/d/SVN/OMS/trunk/postgres/migrations/1.1.0.0/migrate_to_1.1.0.0_027.sql
                 /cygdrive/d/SVN/OMS/trunk/postgres/migrations/1.1.0.0/migrate_to_1.1.0.0_028.sql
                 ...
    Yes/No: Yes
    
    
    rebuild all stored procedures
    364 files to process
    
    Processing all migration scripts
    ...
    next: /cygdrive/d/SVN/OMS/trunk/postgres/migrations/2.0.x.x/migrate_to_2.x.x.x_007.sql
    0
    next: /cygdrive/d/SVN/OMS/trunk/postgres/migrations/2.0.x.x/migrate_to_2.x.x.x_008.sql
    0
    
    success

    Errors will be displayed in the console output and logged in the dbmigrate.test.out file. The postgres log file may contain additional information.

    If any errors occur, they must all be fixed prior to executing dbmigrate.sh against the live system. (The migration is idempotent, you can hence call it more than once against the same database after your possible fixes)

3.2 On a Production Server

WARNING

The following operation must not be performed until all IOM application server instances are stopped!

  1. Stop all server instances.

    Stopping WildFly service
    # as root user
    systemctl stop <wildfly-service-name>
  2. Execute the database migration.

    Note

    Migration of Major and Minor Versions

    For major upgrades, e.g. from 2.0.x.x to 2.1.x.x, dbmigrate must be run twice, first with the option -dir set to postgres/migrations/archive, and then normally, using the default postgres/migrations.

    Performing the database migration
    # as $OMS_USER
    cd $OMS_HOME/bin
    
    # Set environment variables.
    # By default, dbmigrate uses the connection parameters from environment variables which are set by set_env.sh.
    # (mind the empty space after the first dot)
    . ./set_env.sh
    
    # perform the migration 
    
    # 1) only for major upgrade (e.g from version 2.1.x.x to 2.2.x.x)
    # apply older migration scripts located in the "..../migration/archive" directory 
    ./dbmigrate.sh -dir ..../postgres/migrations/archive 2>&1 | tee dbmigrate.archive.out
    
    # 2) perform the standard migration
    ./dbmigrate.sh  2>&1 | tee dbmigrate.test.out 
    
    

    Errors will be displayed in the console output as well as logged in the dbmigrate.log.out. The postgres log file may contain additional information.

Disclaimer

The information provided in the Knowledge Base may not be applicable to all systems and situations. Intershop Communications will not be liable to any party for any direct or indirect damages resulting from the use of the Customer Support section of the Intershop Corporate Web site, including, without limitation, any lost profits, business interruption, loss of programs or other data on your information handling system.

Customer Support
Knowledge Base
Product Resources
Support Tickets