Migration

From ADempiere ERP Wiki

Jump to: navigation, search

'This page is obsolete. See Upgrading and Migration.'

Migrating and upgrading to new versions of ADempiere is FREE: one of the great benefits of open source software.

As new software is developed, the database also changes. When an existing installation is upgraded, the existing database has to be modified to keep it in sync with the software without loss of any data. The software upgrade includes a new reference database or seed. This is compared to the existing database and the necessary modifications are made.

Contents

Migration with the Migration Tool (since 3.8.0)

With the release of 3.8.0, the migration process uses a tool and is quite simple:

  • Backup the existing database ($ADEMPIERE_HOME/utils/RUN_DBExport.[sh|bat]
  • Import the reference database using the command $ADEMPIERE_HOME/utils/RUN_ImportReference.[sh|bat]. This will import a copy of the seed into your database with user "reference"
  • Run the new Migration Tool with the command $ADEMPIERE_HOME/utils/RUN_Migrate.[sh|bat]. For more information about the tool see the Migrate page.
  • In the tool dialog, click "Start Migration". The process could take a while - several hours for a large database.
  • If there are no errors, the migration is complete and you are done.
  • If there are errors, refer to the Migration Tool documentation for help on fixing them.

The rest of this page contains historical methods of migrating databases that is kept for reference only.


Image:Caution.gif Caution!

The following script-based processes are based on assumptions and should only be used when upgrading the reference/seed database. For production databases, use the Migration Tool RUN_Migrate.[sh

Migration with Scripts

The previous approach to migration used xml or sql scripts that are applied to the database sequentially.

All scripts to migrate between versions of the database can be found in migration folder in the Github repository. Typically, these files will also be included in patch and update packages which can be downloaded from Bintray.

Prior to release 3.8.0, the scripts are in the form of SQL files which are database dependent. From release 3.8.0 onwards, the scripts are in XML files which are independent of the database. Another benefit of the xml scripts is that they can be rolled back in the Migration Window of the Application Dictionary. When a software patch or update is installed, the required migration files will be stored in the $ADEMPIERE_HOME/migration directory.

How are the scripts applied?

Following release 3.8.0

Following release 3.8.0, migration is easy: execute the $ADEMPIERE_HOME/utils/RUN_MigrateXML.[bat|sh] tool. This will compare the migrations in the $ADEMPIERE_HOME/migration folder with those already applied in the database and load and apply the missing ones. Following the application of the migrations, the tool will run a number of utilities to check sequences, add missing translations, perform a role-access update to prepare the database for use.

The migrations can also be loaded from within the application using a process in the Application Dictionary.

Prior to release 3.8.0

Prior to release 3.8.0, the scripts are SQL files, so they must be applied with some SQL tool (i.e. you can use dbvisualizer, Toad, sqlplus or sqldeveloper to Oracle, or you can use pgAdmin if your database is PostgreSQL).

Once the scripts are applied, it is not possible to rollback the changes easily, so its a good idea to backup your database first.

From release 3.15 the PostgreSQL files are in a subdirectory named postgresql and the Oracle files are in the main directory of the migration scripts (ie migration/314-315/postgresql and migration/314-315)

Prior to release 3.15, a suffix like _oracle (E.G. 001_xxxx_oracle.SQL) indicates the script is Oracle specific, and should not to be run on a PostgreSQL installation. Similarly, a script with suffix _psql (E.G. 001_xxxx_psql.SQL) is PostgreSQL specific, and should not to be run on an Oracle installation. A script without any suffix it can be run on either an Oracle or a PostgreSQL installation.

The names for all scripts should begin with a number, such as 001_xxxx.SQL, 002_xxx.SQL. It is important to apply ALL the scripts in ascending order. Ensure each script is executed without errors.

Migration from older ADempiere versions

Using the Migration Tool mentioned above, you can migrate from old version to new versions in one step.

The rest of this page is old information made redundant by the Migration Tool.

If you are migrating with XML or SQL scripts from older ADempiere versions, older than than the previous to the last, you must migrate from version to version of ADempiere.

An Example:

Your current ADempiere version is 3.1.1 and you want to migrate to the last ADempiere version, we suppose 3.1.4.
So, you must migrate:
  • From ADempiere 3.1.1 -> 3.1.2
  • From ADempiere 3.1.2 -> 3.1.3
  • From ADempiere 3.1.3 -> 3.1.4

You can also skip all these intermediary steps if you make your own script. To do that refer to the note below.

To migrate from release 3.7.0lts to release 3.8.0 you have to perform a mix of sql and xml migrations. First migrate 3.7.0lts to the start of the xml series using the sql scripts. Then install the release 3.8.0 software and execute RUN_Migrate.[bat|sh].

Migration from Compiere to ADempiere

To migrate from Compiere to ADempiere, first migrate from version to version of Compiere, until your reach Compiere 2.5.3d.

The migration script from version 2.5.3a to version 2.5.3d of Compiere was developed by Karsten-Thiemann, assisted by Carlos Ruiz and tested by Colin Rooney.

You can find these scripts in the same place as the ADempiere version migration scripts: trunk/migration on SourceForge.

Once you have migrated your current Compiere database to version 2.5.3d (unpublished), you are at ADempiere 3.1.1. You can then continue the migration steps from there to the next ADempiere version.

Performing the Migration: a HowTo

To migrate from one version to another involves updating the database structure and updating the software. The database is modified with SQL scripts. This section describes how.

To perform the migration, you will need to be able to shutdown the application server and make backups of the database.

Steps

  • Backup Database: it is highly recommendable that you previously make a backup of your current database. To start the backup, run the RUN_DBExport.[bat|sh] script (located under the ADEMPIERE_HOME\utils folder). The script creates under ADEMPIERE_HOME\data the files ExpDat.dmp, ExpDat.jar and ExpDat.log. The file containing the actual backup is ExpDat.dmp, which you can recogize because of its size. Remember to log in as oracle user to accomplish this task.
Image:Caution.gif Caution!

Please, perform a backup before starting the migration!.

  • Apply ALL corresponding SQL scripts from trunk/migration to migrate your database.
    • If you use Eclipse, the scripts can be applied by an ANT Build found in the Migration directory.
    • You can do this manually with the script tool that comes with your database. Either graphical or command line. For example:
cd $ADEMPIERE_TRUNK/migration
./migrate_postgresql.sh 354a-trunk commit | psql -U $ADEMPIERE_DB_USER -d $ADEMPIERE_DB_NAME > 354a-trunk.lst 
echo ==== ... Run migration scripts done!
Image:Note.gif Note:

If applying scripts manually, remember to do it in the right order.

Image:Caution.gif Caution!

The scripts to be run depend on your actual version. If you fail or forget to run the SQL scripts, you will get a message at login stating the mismatch between the version of Adempiere and database and Adempiere will refuse to start.

  • Backup your current ADempiere installation: rename your current ADempiere folder (i.e. from Adempiere to _Adempiere).
  • Extract the new Adempiere version to your former Adempiere folder (Adempiere).
  • Copy the AdempiereEnv.properties and Adempiere.properties from your old installation folder (the renamed _Adempiere) into the new Adempiere folder.
  • Make sure that the Application Server is not running and database engine and listener (for oracle) are up. You can stop the Application Server by running RUN_Server2Stop.[bat|sh] in ADEMPIERE_HOME\utils. If the Application Server runs during the next step, the script could tell you that a port is busy. You should as well close all applications that clucth the ports (mostly 1099 for jnp, 443 for SSL, 1521 for database).
  • Run the RUN_Setup.[bat|sh] script. It should give you the setup screen with all correct settings (derived from your old *.properties). First press Test. If the configuration entries get checked, meaning that they work, select Save. After some time, depending on the machine you are installing, the installation window closes.
  • Start the Application Server by running RUN_Server2.[bat|sh] in ADEMPIERE_HOME\utils.
  • Start ADempiere and login as System Administrator (SuperUser, System -> role System Administrator).
  • Run Sequence Check.
  • If you use additional languages: login with the standard English language as System Administrator, open Language (under General Rules -> System Rules -> Language), select your language and push Language Maintenance button. Choose Add Missing Translation and run the process.
  • Exit from ADempiere.
  • Start ADempiere again.

That is all!

How to build a migration script

  • Karsten-Thiemann also contributed within his migration script for us to get the difference between two Oracle database versions. It is not limited to just ADempiere/Compiere. For example you should be able to get the difference between a Compiere 252d and the latest ADempiere 314. To do that, please refer to SVN/migration. You can also follow the instructions in Readme.txt file.
  • Now things become more automated when Generating Migration Scripts.

Related Links

Tutorial Movies

Other Useful Information

Warning After All Migration Scripts Applied

The following information was taken from the following link from Sourceforge Help forum: https://sourceforge.net/projects/adempiere/forums/forum/610547/topic/3480689

I keep getting the version mismatch error. I am using 354a with patches_354 and has run all scripts in 354a- trunk.

The solution established by the writer (edwin_ang) was:

It is not related with applying migration scripts at all. It is the value of DB_Version=2009-10-02 in org\adempiere\version.properties of patches_354 which is not consistent with the value of field "version" in AD_System.

ERROR: relation "ad_entitytype" does not exist / SQL state: 42P01

If you receive an error similar to the title above with SQL state 42P01 you may find it is related to not being able to find the schema of your database.

Try the following command to fix the error:

SET SEARCH_PATH TO <schema_name>;

Where schema name is possibly adempiere

Place the command at the start of the script.

Further note:

The message was received when pgAdmin III was used to process the scripts. When psql was used the error message was not received and the SET SEARCH_PATH command was not used.

How to compile migration scripts to a single script? (Windows)

After downloading the migration script, open dos/command promt and change directory to the migration scripts folder and type the following command and press enter key

DIR /ON /B > GenScrpt.CSV

This will create a new file GenScrpt.CSV in the same folder.

Now open the file GenScrpt.CSV using MS-Excel and look for any file name entries which is not a migration script ( eg.Build.Properties etc) and remove such entries by deleteing that row.

Now insert a new column in A1 position and fill it with the value ( TYPE ) throughout the entire list.

Also fill the third column with the value ( >> MIGSCRPT.SQL ) in the same manner and save the file.

Just modify the first row only to ( > MIGSCRPT.SQL ) ie. single greater than sign >

This will ensure that it will overwrite existing MIGSCRPT.SQL each time the batch file is run.

Now rename the file GenScrpt.CSV to GenScrpt.BAT

Edit the file GenScrpt.BAT using NOTEPAD or any other text editor and replace the commas generated by the .CSV format with bank space and save the file.

Just type GenScrpt.BAT on command prompt and press enter to key.

Refresh the Migration Script folder to find the new Compiled Single Migration Script

file named MIGSCRPT.SQL

Personal tools