ERP 2.50:Oracle to PostgreSQL migration

Contents 1 Introduction 2 Recommendations 3 Migration 3.1 Export 3.1.1 Modules and Packages 3.1.2 Industry Templates 3.1.3 Masterdata 3.1.4 Clients 3.2 Import

Introduction

This document describes the steps needed to migrate an Openbravo ERP installation running on an Oracle database into PostgreSQL.

The SQL and PL/SQL syntax of Oracle differs in some cases from PostgreSQL's, so it is necessary to transform all the unsupported syntax elements into PostgreSQL synonyms.

DBSourceManager is the recommended tool for the migration. This tool transforms the most common particular elements of Oracle or Postgresql into standard SQL and PL/SQL code that can be executed in both Oracle or PostgreSQL without problems.

Recommendations

To avoid incompatibility in the SQL and PL/SQL code between Oracle and PostgreSQL, please follow the Openbravo SQL/PL-SQL code rules.

Migration

Export

Modules and Packages

To export the structure of the database related to all modules installed, it's need to mark all the modules, packages or industry templates in development.

In openbravo application, go to Application Dictionary || Module, and for each module, package, or industry template, mark the checkbox "In development".

Go to command line and do an:

ant export.database -Drd=true

Industry Templates

Mark all the modules and packages as NOT in development. And mark ONLY one industry template as in development and do

ant export.config.script

You need to repeat the steps for each template that you have installed.

Masterdata

Masterdata is not suppose to be changed so this process don't take it into account.

Clients

Create the folders for the clients export:

$ mkdir referencedata/importclient

If the folder already exists make a backup and clean of the directory:

$ mkdir referencedata/backup
$ mv referencedata/importclient/* referencedata/backup/

Export the client information. Go to General Setup || Client || Export client, and export ALL the clients.

Each client should generate a xml file with the name of the client in referencedata/importclient.

It is possible that all the clients do not appear in the combo, so it is usually needed to change the role in the Preferences window, as all roles don't have access to all clients.

Import

In order to be automatically imported the clients data, the xml files should be moved from referencedata/importclient to referencedata/sampledata

$ mv referencedata/importclient/* referencedata/sampledata/

Point to the PostgreSQL database. It is necessary to modify Openbravo.properties adding the information to connect to PostgreSQL and commenting the information to connect to Oracle. The result should be something similar to this:

# bbdd.rdbms=ORACLE
# bbdd.driver=oracle.jdbc.driver.OracleDriver
# bbdd.url=jdbc:oracle:thin:@localhost:1521:xe
# bbdd.sid=xe
# bbdd.systemUser=SYSTEM
# bbdd.systemPassword=SYSTEM
# bbdd.user=TAD
# bbdd.password=TAD
# bbdd.sessionConfig=ALTER SESSION SET NLS_DATE_FORMAT='DD-MM-YYYY' NLS_NUMERIC_CHARACTERS='.,'

bbdd.rdbms=POSTGRE
bbdd.driver=org.postgresql.Driver
bbdd.url=jdbc:postgresql://localhost:5432
bbdd.sid=openbravo
bbdd.systemUser=postgres
bbdd.systemPassword=syspass
bbdd.user=tad
bbdd.password=tad
bbdd.sessionConfig=select update_dateFormat('DD-MM-YYYY')

Create the new database in PostgreSQL:

$ sudo /etc/init.d/tomcat stop
$ ant install.source
$ sudo /etc/init.d/tomcat start

Note: in old versions of Openbravo ERP 2.50 there are memory problems when importing clients with a huge amount of data. In order to workaround this issue you can expand the maximum memory for tomcat: the -Xmx parameter in JAVA_OPTS/CATALINA_OPTS variable. To see where this parameter is specified depending of your operating system, take a look to the Tomcat configuration guide.

Category: Migration and Upgrades