Migration/DDLUtils

From ADempiere ERP Wiki

Jump to: navigation, search

Contents

Introduction

This article should cover the general process of migrating an Oracle database to PostgreSQL. The migration uses ddlutils that are shipped with every ADempiere release.

Prerequisites

In order for migration to work correctly, you should have done the following steps prior to migration:

  • Install Pl/Java into the template database (usually template1).
 $ psql -d template1 -U postgres
 template1=# \i PATH_TO_PLJAVA/install.sql
  • Create Pl/PGSQL language:
 template1=# CREATE PROCEDURAL LANGUAGE plpgsql;

General Procedure

  • Modifying the specific build scripts and properties files according our environment.
  • Exporting the Oracle database schema and data
  • Importing the exported data into PostgreSQL
  • Modifying ADempiere setup to run with the new database

General information about files and folders

The ddlutils folder which is relevant for our migration is located in db/ddlutils in every ADempiere source release. I will refer to this folder as <root>. If you are on windows, just replace / with \ in any path information.

PostgreSQL requirements before migrating

In order for a successfull migration you must have a working PostgreSQL server with pljava installed as a usable language (read this article on how to do that). You also have to create the adempiere database create the owning adempiere database user. How to create them is stated in the article mentioned above. In our case we would do everything in that article except the lines below #Installing database dump

Modifying ddlutils configuration

Oracle specific

Go to the folder <root>/oracle and customize the file build.properties to match your needs. It could look like this on a linux machine:

oracle.home=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server

oracle.connect.param=adempiere/adempiere@xe

Next do the same with the file jdbc.properties.oracle10. Here is the relevant part of mine:

#
# Using the plain DBCP datasource
#

datasource.class=org.apache.commons.dbcp.BasicDataSource
datasource.driverClassName=oracle.jdbc.driver.OracleDriver
datasource.url=jdbc:oracle:thin:@localhost:1521:xe
datasource.username=adempiere
datasource.password=adempiere

PostgreSQL specific

Go to the folder <root>/postgresql and customize the file build.properties to match your needs. It could look like this on a linux machine:

postgresql.home=/usr/local/pgsql

postgresql.database=adempiere
postgresql.user=adempiere
postgresql.password=adempiere
sqlj.jar.url=file:///home/adempiere/adempiere313/lib/sqlj.jar

The last line would look like this on a windows machine:

sqlj.jar.url=file:///d:/Opensource/Applications/Adempiere/sqlj/sqlj.jar

Next do the same with the file jdbc.properties.postgresql. Here is the relevant part of mine:

datasource.class=org.apache.commons.dbcp.BasicDataSource
datasource.driverClassName=org.postgresql.Driver
datasource.url=jdbc:postgresql://localhost/adempiere
datasource.username=adempiere
datasource.password=adempiere

Exporting the Oracle database

Now we will have to export the Oracle database schema and data. In order to do that change to the <root>/oracle folder and execute the following commands:

ant -f build-ddl.xml writeSchemaToFile
ant -f build-ddl.xml splitSchemaFileByTable
ant -f build-ddl.xml writeDataToFile
ant -f build-ddl.xml splitDataFileByTable

Importing the data into PostgreSQL

Now we have to import the data previously exported from Oracle into PostgreSQL. Chnage to the folder <root>/postgresql and simply execute:

ant create

Modifying ADempiere setup to run on PostgreSQL

To let ADempiere run with PostgreSQL we have to tweak some settings and restart the application server.

  • Stop the ADempiere server
  • Change to your ADempiere installation folder and execute RUN_setup.sh/RUN_setup.bat
  • Change the database type to postgresql and set the respectively database name, username and passwords
  • Save your setup and let the configuration/deployment process finish
  • Start the ADempiere server
  • Start a client and change the connection profile (clicking the "Test Applicationserver" button should do the trick)
  • Voila, now login with your client and enjoy the power of PostgreSQL as your database backend ;)

Last comments

This process should also work for other migrations in any way you want to, but has not been tested for. If you have informations on migrating from Oracle to Fyracle or Fyracle to PostgreSQL, please add your experience and tips here, others will follow in your footsteps...

Additional Information

DDL Utils can not import views. If you changed or added a view you have to recreate it by hand after the migration or add/change the sql files in postgresql/views folder before the migration.

Personal tools