This post describes the method we used to migrate the database from MySQL to PostgreSQL for our JBoss based UserApp installations on SLES (IDM 4.0.2). This is achieved by reinstalling both JBoss with PostgreSQL and the UserApp, and then exporting the db from MySQL and importing it into PostgreSQL.

Please note that the newly installed version of JBoss and UserApp should be the same version as the one currently installed. Also, when migrating our installations, I've always used the GUI version of the JBoss and UserApp installers (I'm not sure if the console versions behave identically). I worked on this together with nsanson.

1. Before stopping the UserApp, verify what exact version you are running

• Stop JBoss and MySQL
  
  
  
  1. service jboss_init stop
  
  
• su novlua /opt/novell/idm/mysql/stop-mysql.sh root your_db_admin_password

• Rename the idm install directory so we can reinstall JBoss on the standard path without overwriting the previous install
  
  
  
  1. cd /opt/novell
  
  
• mv idm idm_mysql

• we won't modify the content of this dir, so it can be used as a backup of current configuration

• Keep a backup of your /etc/init.d/jboss_init in case you have made any customization
  
  
  
  1. cp /etc/init.d/jboss_init /opt/novell/idm_mysql
  
  
  

• Reinstall Jboss with postgres backend
  
  
  
  1. execute /dvd_media_path/products/RBPM/JBossPostgreSQL.bin
  
  
• choose install set "Default: install both JBoss and PostgreSQL"

• use same JBoss Parent Folder, db name, admin user (idmadmin) and password as previously with MySQL

• Reinstall UserApp
  
  
  
  1. cd /dvd_media_path/products/RBPM/user_app_install (it is better to launch the installer from this dir, otherwise you will have to specify the .war location later on)
     /opt/novell/idm/jre/bin/java -jar IdmUserApp.jar
  
  
• accept all defaults but on the "Database Username and Password" page, enter the same values you specified in step 3.3 above and Database Driver JAR File: /opt/novell/idm/Postgres/postgresql-8.4-701.jdbc4.jar (version number can be different)

• make sure to select option "Create Tables NOW" in the "Create Database Tables" page, and "NEW database" in the following one

• in page "IDM Configuration" , select the same clustering option you were using with the MySQL installation and the same Application Context and Workflow Engine ID

• if using cluster mode, import the master key from the renamed idm directory
  
  
  
  1. cp /opt/novell/idm{_mysql,}/master-key.txt
  
  
• cat /opt/novell/idm/master-key.txt and copy the value into the installer input field

• in "Configure IDM" choose "Configure Later" and complete the installation

• at this point the idmuserappdb PostgreSQL schema has been created but missing the "jbm" JBoss tables

• Start JBoss so that it creates the "jbm" tables
  
  
  
  1. service jboss_init start
  
  
• watch log file /opt/novell/idm/jboss/server/IDM/log/jboss.log

• ignore errors and just wait until jboss logs something like (usually in a few minutes)

• [ServerImpl] JBoss (Microcontainer) [5.1.0.GA (build: SVNTag=JBoss_5_1_0_GA date=200905221053)] Started in...

• at this point, the PostgreSQL db schema should include the "jbm" tables

• Stop JBoss
  
  
  
  1. service jboss_init stop
  
  
  

• Copy MySQL back from the renamed dir to the standard one, so we can start it without modifying configuration files
  
  
  
  1. cp -a /opt/novell/idm{_mysql,}/mysql (alternatively you can just move it instead of copying it, but then the idm_mysql directory wouldn't be a valid backup anymore)
  
  
  

• Start MySQL (we need it to export db data)
  
  
  
  1. su novlua /opt/novell/idm/mysql/start-mysql.sh
  
  
  

• Run the migration shell script shown below
  
  
  
  1. update the db password in the first lines and db admin usernames if different than default
  
  
• run the script and make sure it doesn't report any error (just SET, INSERT, DELETE and UPDATE lines)

• Stop MySQL (we won't need it ever again)
  
  
  
  1. su novlua /opt/novell/idm/mysql/stop-mysql.sh root your_db_admin_password
  
  
• eventually delete the /opt/novell/idm/mysql directory

• Restore your previous idm war install, cert store and config files over to the new idm directory (keeping backups)
  
  
  
  1. cp -b /opt/novell/idm{_mysql,}/jboss/server/IDM/deploy/IDM.war
  
  
• cp -b /opt/novell/idm{_mysql,}/jboss/server/IDM/conf/sys-configuration-xmldata.xml

• also check if you have other customized files and eventually copy them too (such as  idm/jboss/server/IDM/conf/idmuserapp_logging.xml)

• cp -b /opt/novell/idm{_mysql,}/jre/lib/security/cacerts

• copy over eventual other files you have created under /opt/novell/idm_mysql (e.g. other certs in the security dir)

• Update your IDM.war with the postgres specific hibernate.cfg.xml file from the unconfigured war
  
  
  
  1. cd /tmp
  
  
• unzip -o /opt/novell/idm/jboss/server/IDM/deploy/IDM.war~ WEB-INF/classes/hibernate.cfg.xml

• zip -u /opt/novell/idm/jboss/server/IDM/deploy/IDM.war WEB-INF/classes/hibernate.cfg.xml

• ignore zip CRC warning from last command

• Update liquibase data by running the following two scripts in /etc/novell/idm - you have to edit them beforehand and substitute your context (IDMProv) and db username/password)
  
  
  
  1. clear-checksums.sh
  
  
• update-context.sh

• Eventually make sure you don't have anything else useful in /opt/novell/idm_mysql and then delete this directory if you don't need to keep it as backup

• Check that your new /etc/init.d/jboss_init is not missing anything with respect your old one and eventually incorporate differences
  
  
  
  1. diff {/etc/init.d,/opt/novell/idm_mysql}/jboss_init
  
  
  

• Start JBoss and test the UserApp
  
  
  
  1. service jboss_init start
  
  
  

--- migration script
This script dumps the MySQL db and imports it in PostgreSQL, overwriting exisitng data (if any) but skipping the liquibase tables (databasechangelog and databasechangeloglock). It doesn't modify the MySQL db.
--- cut here ---

#!/bin/bash
passwd=your_db_admin_password
MSQLdump=/tmp/msql.sql
PSQLdump=/tmp/psql.sql

export PGPASSWORD="$passwd"

key_tables=(AFMODEL AFPROCESS PORTALPRODUCERS PORTALREGISTRY DSS_CONFIG DSS_BROWSER_TYPE DSS_APPLET_CFG SECURITYACCESSRIGHTS SEC_DELPROXY_CFG)
all_tables=$(/opt/novell/idm/mysql/bin/mysql --defaults-file=/opt/novell/idm/mysql/my.cnf -u root --password="$passwd" -B -D idmuserappdb -e "show tables" | tail -n  2 | grep -ve ^DATABASECHANGELOG)

# dump data from mysql
/opt/novell/idm/mysql/bin/mysqldump --defaults-extra-file=/opt/novell/idm/mysql/my.cnf --compatible=postgresql --default-character-set=utf8 --no-create-db --no-create-info --compact --skip-quote-names --complete-insert --hex-blob -r $MSQLdump -u root --password="$passwd" idmuserappdb

# this is to get escapes in mysql strings to be accepted by psql
echo "SET standard_conforming_strings = off; SET escape_string_warning = off;" > $PSQLdump

# this is to have 0/1 in mysql tinyint to be accepted by psql as booleans
echo "update pg_cast set castcontext='a' where casttarget = 'boolean'::regtype;" >> $PSQLdump

# clear data
# first clear non key tables
(
  for t in $all_tables
  do
    (echo "${key_tables[@]}"|grep -q -w $t) || echo $t
  done
  # then clear key_tables in reverse order
  for ((i=${#key_tables[@]}-1; i >=0; i--))
  do
    echo ${key_tables[$i]}
  done
) | while read t
do
  echo "DELETE FROM $t;";
done >> $PSQLdump
# insert data
# first insert into key tables
(
  for t in "${key_tables[@]}"
  do
    echo $t
  done
  for t in $all_tables
  do
    (echo "${key_tables[@]}"|grep -q -w $t) || echo $t
  done
) | while read t
do
  if $t = "JBM_MSG"
  then
    # this table has blobs, convert them via decode function
    grep "^INSERT INTO $t " $MSQLdump | sed "s/\(,\|(\)0x\([0-9A-F]*\)/\1decode('\2','hex')/g" >> $PSQLdump
  else
    grep "^INSERT INTO $t " $MSQLdump >> $PSQLdump
  fi
done

# revert the pg_cast change
echo "update pg_cast set castcontext='e' where casttarget = 'boolean'::regtype;" >> $PSQLdump

/opt/novell/idm/Postgres/bin/psql -U postgres -f $PSQLdump idmuserappdb