- 1 Introduction
- 2 Oracle specific
- 3 PostgreSQL specific
- 4 Last comments
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.
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;
- 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
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:
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
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:
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:
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 ;)
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...
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.