View source | Discuss this page | Page history | Printable version   

ERP 2.50:Oracle to PostgreSQL migration



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.


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



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.

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 is not suppose to be changed so this process don't take it into account.


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.


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.



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

This page has been accessed 12,508 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.