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.

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

1.3 References

• Overview - IOM Setup
• Overview - IOM Technical Overview
• Overview - IOM System Requirements

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.

# 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

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

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.