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".
![]() | 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
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')
![]() | 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. |