View source | Discuss this page | Page history | Printable version   
Toolbox
Main Page
Upload file
What links here
Recent changes
Help

PDF Books
Show collection (0 pages)
Collections help

Search

ERP 2.50:Oracle to PostgreSQL migration

Contents

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".

Bulbgraph.png   Remember to NOT mark core in development. Core should not be modified, use an Industry Template instead.
MarkModuleInDevelopment.png

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.

Bulbgraph.png   Client "System" should not be exported.

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


ExportClient.png


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.


SelectRole.png

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')
Bulbgraph.png   The systemUser and systemPassword should be the valid information of postgres system user. The sid (database name), user and password can be changed on your own.

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.

Retrieved from "http://wiki.openbravo.com/wiki/ERP_2.50:Oracle_to_PostgreSQL_migration"

This page has been accessed 7,298 times. This page was last modified on 14 June 2011, at 11:04. Content is available under Creative Commons Attribution-ShareAlike 2.5 Spain License.