Migrate - Migrating a Database

From ADempiere ERP Wiki
Jump to navigationJump to search


Preparation[edit]

Definitions[edit]

The reference (or source) database is the database whose structure and definitions are about to be applied (migrated, injected) to the target database. This database is usually the ADempiere seed and has more advanced changes than the target database.

The reference database does not change during the migration process.

The target database is the database which you want to be changed (updated). It is the database which lags behind changes and you want to become updated. Usually this is a legacy database from former revisions which one wants to get updated.

The target database does change during the migration process. It may be also in an inconsistent or incomplete state during the migration process

Disconnect all Users[edit]

The target database should be up and running.

No users should be logged in. Make sure all users are disconnected from the reference and target database.

That includes the Adempiere server itself: Shut down the application server. It applies to other connections, too like PGAdmin or any other database browser

If you want to be sure, shutdown and restart the database server

Create a Backup[edit]

You must have a backup of your reference database before starting the migration process, because it will be modified and it can also happen that you may need to restart the migration process anew.

Remember the disclaimer at the beginning of this document: This program is distributed without warranty of fitness for a particular purpose. It may migrate your data, or it may completely mess up your database.

The easiest way to quickly create a backup is with ./RUN_DBExport.sh (or RUN_DBExport.bat) in the utils directory.

That script will create a file ExpDat.dmp in the data directory, which can be easily restored using ./RUN_DBRestore.sh (or RUN_DBRestore.bat), if necessary.

Create a Backup for Postgres[edit]

For Postgres you may alternatively do the backup with psql; here you have to login as postgres user. For a backup of the whole database you can run a command like pg_dump -f file_name.backup -v DATABASE_NAME. Sometimes -as we will later see- it is convenient to backup single tables. This can be done with pg_dump -f file_tablename.backup -t TABLE_NAME -v DATABASE_NAME.

A restore for Postgres can be done also with psql; here you have to login as postgres user, the database must have been created and be empty. For a restore of the whole database you can run a command like psql DATABASE_NAME<file_name.backup or psql –d DATABASE_NAME –f file_name.backup. A more complete command using pg_restore would look like follows pg_restore --verbose --clean --no-acl --no-owner -h 192.168.1.111 -U postgres -d DATABASE_NAME file_name.backup.

For restoring a single table, when using psql the notation is the same as restoring the whole database. For restoring a single table with pg_restore, it would look as follows: pg_restore -t TABLE_NAME --verbose --clean --no-acl --no-owner -h 192.168.1.111 -U postgres -d DATABASE_NAME file_name.backup. Do not forget that the backup copies the table definition, the contents, constraints and indices. If you only want to restore the contents, you must delete or comment the objects not needed out of the backup file.

Install new Adempiere version[edit]

If you want to do an upgrade migration, download the Adempiere version you want to upgrade to and install it.

Then execute ./RUN_setup.sh (or RUN_setup.bat) in $ADEMPIERE_HOME to configure Adempiere. The settings saved are also used by Migrate.

Please remember that the database to which the installation points to is the Reference database

Import Reference Database[edit]

If you want to do an upgrade migration, install the reference database:

Execute ./RUN_ImportReference.sh (or RUN_ImportReference.bat) in the utils directory.

If you want to do a transfer migration, make sure the source database is up and running.

Alternative: import seed database into a custom database[edit]

If you want your seed to have a distinct name like ADEMPIERE382, do create the database (in our example ADEMPIERE382) and install ADempiere referencing to it. Do not care if the database is empty. After the installation has been successfully done (all steps but sign database) you drop (delete) the database.

Execute ./RUN_ImportAdempiere.sh (or RUN_ImportAdempiere.bat) in the utils directory.

Do open the database (in our example ADEMPIERE382) and check if it has the changes of the new seed (AD_Column, AD_Field, AD_Element, etc.).

Update Reference Database[edit]

It is possible that the official Reference database (the seed) is lagging behind the actual repository. This is the case when in the repository there have been added XML Migration files for error corrections or for functional enhancements, both of which you may want to include in your reference database.

Before executing the following steps, do not forget to have installed ADempiere pointing to the Reference Database

Copy all XML Migration files from trunk (and perhaps other projects) to $ADEMPIERE_HOME/migration/380lts-release (directory can differ according with the ADempiere release).

Execute the command $ADEMPIERE_HOME/RUN_MigrateXML.sh, and verify on the console that everything's OK.

If there is any error during the execution of RUN_MigrateXML.sh, you have to log in to ADempiere as SystemAdministrator, open the window Migration and see which Migrations XML file failed. Then you go into the tabs Steps/Data and check the error. You may either correct the XML file and go back to RUN_ImportAdempiere.sh.

Having finished this, you will have a reference database (seed) with the latest repository changes.

Verify Preconditions[edit]

Make sure that

  • no users are logged in

  • the Adempiere application server is shut down

  • you have a backup

  • the reference database is imported (for upgrade migrations)

  • the reference (source) database is up and running

  • the target database is up and running

Running the Migration Tool[edit]

Once all preparations have been done and verified, you can start Migrate by executing ./RUN_Migrate.sh (or RUN_Migrate.bat) from the utils directory.

This will start the migration tool and display the interactive graphical user interface.8

When Migrate is started, it will read environment variables for setting parameters and options. Since the RUN_Migrate script loads Adempiere's environment before calling Migrate, it effectively means that Adempiere's settings will also be used by Migrate. Any settings not defined by environment variables will be supplemented with sensible values.

If $ADEMPIERE_HOME is defined, Migrate looks for a configuration file called migration.config in the $ADEMPIERE_HOME/utils directory, otherwise it will look for the configuration file in the current directory. If the file exists, configuration settings will be read from that configuration file, and any settings loaded from the environment will be overwritten. Once a migration was run, Migrate saves its settings to that configuration file, so next time it is started, your last parameters and options will be used again.

Any command line arguments passed to Migrate will override the settings loaded from the configuration file or from the environment so that command line arguments always take precedence.

The User Interface[edit]

Migrate's interactive Graphical User Interface

Once the user interface is displayed, you need to select the migration mode, select some options to be used by the migration process, and set the database connection parameters.

Migration Mode[edit]

Migration Mode Settings

Select the mode in which to run the migration process.

Two different modes of migration can be performed:

upgrade

Upgrade target to newest version as found in source.

This mode can also be used to convert from other applications to Adempiere.

transfer

Copy source to target.

This mode can also be used to convert from other databases to postgreSQL.

The default is to run an upgrade migration, but if different vendors are used as source and target database (see Parameters below), only a transfer migration can be performed.

Options[edit]

Options

Several options can be set to control migration behavior. Which options are available depends on the migration mode.

log level

Migrate creates three log files containing results of the migration process:

  • migration_timestamp.error.log

    contains any errors encountered during migration which must be fixed.

  • migration_timestamp.warning.log

    contains hints for the database administrator of what has to be checked or might need to be done manually after migration has finished.

  • migration_timestamp.trace.log

    contains the output messages of what steps and actions Migrate has performed.

The log level option sets the threshold for messages to be recorded in the trace log. Messages with a lower priority will not be logged.

Available log levels in order of descending priority are:

  • no logging

  • errors only

  • post-migration tasks (warnings)

  • migration steps

  • actions

  • details

  • SQL update queries

  • SQL read queries

  • everything

The default log level is actions.

Note that levels of details or lower can create huge trace files. Be sure to have enough disk space available.

attempt translations

This option is only available in transfer mode.

When converting from one database to another, views and functions need to be translated.

If selected, Migrate will attempt to translate views and functions, otherwise they will be replaced with a compilable stub.

(Note that currently only translation of views is implemented).

The default is yes.

preserve table IDs

This option is only available in upgrade mode.

When running an upgrade, all system information is dropped. Table IDs therefore restart with the highest used sequence number available after migration. It may be beneficial, however, to remember higher ID numbers used before migration to ensure consistency over different versions.

If selected, table ID numbers are preserved through migration, otherwise Migrate restarts counting after migration

The default is yes.

drop source

This option is only available in upgrade mode.

When done with upgrading, the source database is no longer required and may be dropped to clear space. However, the database administrator may wish not to drop it for reference purposes.

If selected, the source is dropped after a successful upgrade, otherwise it is kept remaining in the database after migration.

(Note that the source will only be dropped if no errors occurred during migration).

The default is no.

optimize database

After migration, the database can be automatically optimized. Most databases nowadays have scheduled processes which regularly run optimization tasks, so it may not be necessary to explicitly run them here. Examples for optimization tasks are space allocation or gathering of statistics, but what is actually performed depends on which kind of database is running.

If selected, the target database is optimized after migration, otherwise it is left to the database's automatic scheduler.

The default is no.

Parameters[edit]

Connection Parameters

Parameters are used to define the connections to the source and target databases.

In upgrade mode, the source is the reference against which the target's structure is updated, and live data in the target remains intact.

In transfer mode, the source is copied to the target, and all live data in the target is overwritten.

Two identical sets of parameters must be defined, one for the source connection and one for the target connection.

version

This field is read-only and displays the Adempiere version number found in the database.

If no version number is displayed, it means that either no connection to the database could be established, or the database contains no Adempiere version information (which means it is not an Adempiere database).

vendor

The vendor (or product) of the database. Supported vendors currently are:

  • Oracle

  • postgreSQL

The default is postgresql.

host

The name or IP-address of the server on which the database is running.

The default is localhost.

port

The port on which the database is listening.

Common port numbers are 5432 for postgreSQL or 1521 for Oracle.

The default is 5432.

user

The normal database user as which to log in.

The default is reference for source and adempiere for target.

password

The normal database user's password.

The default is adempiere for both source and target.

system user

Some databases require a system user for certain operations9. This is the name of the system user as which to log in.

The default is postgres.

system password

The system user's password9.

The default is postgres.

database

The name of the database to use.

The default is reference for source and adempiere for target.

driver

This field is read-only and displays the URL which will be used by Migrate to connect to the database. The driver and format used depend on the database vendor.

catalog

The catalog to use.

The usage and meaning of catalogs varies according to database vendor. If none is given, Migrate will try to find a sensible catalog.

schema

The schema to use.

The usage and meaning of schemas varies according to database vendor. If none is given, Migrate will try to find a sensible schema.

reset

Pressing this button resets the parameters to their original settings.

Command Buttons[edit]

Command Buttons
Start Migration

Start the migration process.

Pressing this button runs sanity checks and starts the migration process. Once the target database has been modified, the process must not be interrupted.

Status[edit]

Status Display

The current status of the running migration process is displayed, indicating what action is being performed in which migration step.

step

This field displays the current migration step being performed, which can be one of:

  • CONNECT TO DATABASES

  • LOAD METADATA

  • SYNCHRONIZE TARGET FROM SOURCE

  • CLOSE DATABASE CONNECTIONS

  • DONE

action

This field displays which action or operation is currently being performed within above migration step.

detail

This field displays details of the current action being performed, for example which record is presently being updated.

View Buttons[edit]

You can click anytime on any of the buttons during the execution of the migration process. The momentarily contents will be displayed.

View Buttons

Press one of these buttons to view the different log files.

view trace

View a snapshot of the last 500 lines of the trace log. The trace log contains all output messages as defined with the log level.

view warnings

View a snapshot of the last 500 lines of the warning log. The warning log contains tasks to be performed manually by the database administrator after migration, such as making sure that views and functions were translated correctly.

view errors

View a snapshot of the last 500 lines of the error log. The error log contains all errors which occurred during migration and need to be fixed.

Close Buttons[edit]

Close Buttons
Cancel

Stop the migration process and close the program without saving any settings.

Close

Stop the migration process and save settings and parameters before closing the program.

Running the Migration Tool from Eclipse[edit]

In this section, it will be explained why and how to execute the migration process from Eclipse.

It is expected that the migration process runs without a single error. This can happen if the legacy database was implemented fully following the ADempiere best practices and no undue changes were done to the columns, tables, indexes, constraints, sequences, triggers, functions, etc. Mostly, customers do changes which vary from small to very invasive. This may cause errors and warnings during execution of the migration process, which are a sign that the migration has failed.

Examples of cases which lead to errors are creating or deleting constraints which are in conflict with the official ADempiere changes (which may also happen after installation!), creating custom columns with Entity type Dictionary (instead of User maintained or a custom Entity type) to name a few.

In order to run the migration process from Eclipse, create an Eclipse project and once the project has been created, define a Run/Debug configuration, with the project and the following main class: com.kkalice.adempiere.migrate.Migrate.

Looking at the code, you'll see that the migration process uses the database dictionary to do the migration. This is a great feature, as the process does follow the definitions as stored within the database while executing the migration.Also important is to know that for every step during the migration process there are save points which are separate transactions. This way you can put a breakpoint at every program line of the process and see after every save point how the target database is being changed.

The log files are deployed in $ADEMPIERE_HOME. They are the trace log (example: migration_201606132022.trace.log), the warnings log (example: migration_201606132022.warning.log) and the error log (example: migration_201606132022.error.log). It is important that the error log has no entries, so you know the migration process has been done correctly

I came across migration processes with thousands of errors. Mostly the first error was detected and the others had a message like not continuing until end of transaction block. In this case, you must restore the target database, correct the first error (mostly in the target database) and restart the process.

Another error was when the following constraint was about to be created: CREATE UNIQUE INDEX ppproductbomunique ON adempiere.pp_product_bom(ad_client_id, value). In the target database, the BOM value could be repeated in former versions, whereas in the new version, the creation of the index causes an error in cases of repeated values for BOMs. In such cases, you must restore the target database, correct the conflicting records and restart the migration process.

In another case, the migration tool truncated during execution of the method purgeOrphans() -due to some changes the customer had made- the tables m_production and m_productionline. This led to errors while creating constraints (foreign keys pointing to both tables)later on the process. In order to overcome this, I had to create a backup of both tables, stop the execution after execution of the method purgeOrphans(), restore the table contents and continue with the process

You have to bear in mind that official views and functions will be updated from the reference to the target database. This implies that if you have changed an official view, function or any other object in the database, it will be overwritten during the migration process and you will have to re-create them. This can be complex, perhaps needing to do it in cascade.

Look closely at the log files where you can detect inconsistencies which you must correct mostly manually

Starting from the Command Line[edit]

Of course Migrate does not have to be started with the RUN_Migrate script but can also be started directly from the command line. This allows Migrate to be called from other scripts for automating migration, if required.

The command to start Migrate from the command line is:

java [java Options] -cp classpath [migrate Options] com.kkalice.adempiere.migrate.Migrate

Java Options

These are the options used by the Java Runtime Engine.

Sufficiently high memory settings should be used so that Migrate does not run out of memory.

Recommended are: -Xms64M -Xmx512M

If the database contains large objects, higher settings may be necessary.

Classpath

The classpath should contain the file migrate.jar as well as the JDBC database drivers

for the databases to be used, for example:

$ADEMPIERE_HOME/lib/migrate.jar:$ADEMPIERE_HOME/lib/postgresql.jar:$ADEMPIERE_HOME/lib/oracle.jar

or:

migrate.jar:/usr/share/java/postgresql-jdbc.jar:/opt/oracle/jdbc/lib/ojdbc14.jar

Of course only the JDBC drivers for the database vendors you will actually be connecting to need to be supplied.

Migrate Options

Options passed to Migrate must be prefixed with -D so that java knows it must pass the options on to the application as system properties.

It is highly recommended that all options and parameters are explicitly set on the command line to avoid unpleasant surprises when values you were expecting as default are unexpectedly overridden by environment variables or the configuration file.

GUI Mode / Text Mode / Silent Mode

Two options are only available when starting Migrate from the command line:

-DisText

Migrate will run in Text mode, the GUI will not be started. All parameters and options must be provided by environment variables, the configuration file, or command line arguments.

-DisSilent

All console output will be suppressed. This implies -DisText.

If none of these arguments are passed, Migrate will run interactively with a Graphical User Interface.

Migration Mode

Upgrade mode or transfer mode is selected by the isUpgrade property:

-DisUpgrade=Y

run migration in upgrade mode.

-DisUpgrade=N

run migration in transfer mode.

Options
<span id="-DmaxLogLevel=<log level>">-DmaxLogLevel=<log level>

Use following Java log levels to correspond to the thresholds which can be selected from the GUI:

OFF

= no logging

SEVERE

= errors only

WARNING

= post-migration tasks

INFO

= migration steps

CONFIG

= actions

FINE

= details

FINER

= SQL update queries

FINEST

= SQL read queries

ALL

= everything

-DattemptTranslation=Y, N

whether to translate views and functions

-DpreserveTableID=Y, N

whether to preserve table IDs

-DdropSource=Y, N

whether to drop the source database after successful migration

-DoptimizeDatabase=Y, N

whether to optimize the target database

Parameters

Source connection parameters:

-DsourceDB_vendor=<database vendor>
-DsourceDB_host=<host>
-DsourceDB_port=<port>
-DsourceDB_name=<database name>
-DsourceDB_catalog=<catalog>
-DsourceDB_schema=<schema>
-DsourceDB_user=<normal user>
-DsourceDB_passwd=<normal password>
-DsourceDB_systemUser=<system user>
-DsourceDB_systemPasswd=<system password>

And target connection parameters:

-DtargetDB_vendor=<database vendor>
-DtargetDB_host=<host>
-DtargetDB_port=<port>
-DtargetDB_name=<database name>
-DtargetDB_catalog=<catalog>
-DtargetDB_schema=<schema>
-DtargetDB_user=<normal user>
-DtargetDB_passwd=<normal password>
-DtargetDB_systemUser=<system user>
-DtargetDB_systemPasswd=<system password>

To pass an empty string, either omit the string after the equal sign or write only the parameter name without any equal sign:

-DsourceDB_catalog=

or just

-DsourceDB_catalog

Example:

The following command runs a transfer migration from an Oracle to a postgreSQL database, assuming that migrate.jar is in the current directory. Everything should be typed on one line:

java -Xms64M -Xmx512M -cp migrate.jar:/usr/share/java/postgresql-jdbc.jar:/opt/oracle/jdbc/lib/ojdbc14.jar -DisText -DisUpgrade=N -DmaxLogLevel=CONFIG -DattemptTranslation=Y -DoptimizeDatabase=N -DsourceDB_vendor=oracle -DsourceDB_host=localhost -DsourceDB_port=1521 -DsourceDB_name=erp -DsourceDB_schema=compiere -DsourceDB_user=compiere -DsourceDB_passwd=compiere -DsourceDB_systemUser=system -DsourceDB_systemPasswd=manager -DtargetDB_vendor=postgresql -DtargetDB_host=localhost -DtargetDB_port=5432 -DtargetDB_name=adempiere -DtargetDB_schema=adempiere -DtargetDB_user=adempiere -DtargetDB_passwd=adempiere com.kkalice.adempiere.migrate.Migrate

Post-Migration Tasks[edit]

Migrate already runs sanity checks and clean-up procedures after migration, so it is not necessary to start any post-migration scripts such as RUN_PostMigration.sh (or RUN_PostMigration.bat).

However, the database administrator should check the log files to verify whether any manual intervention is required after migration has completed, particularly the warning log and the error log.

For a transfer migration, warnings and errors issued for non-customized objects or system records can usually be ignored, as they will be replaced during the subsequent version migration anyway. Only problems with customized objects or live data of real clients need to be addressed by the database administrator.

Warnings[edit]

The warning log contains tasks to be performed manually by the database administrator after migration.

Table 3.1. Warning Messages

WarningModeCauseSolution

Preserving node … in tree …

upgrade

System nodes would normally be purged from trees, but are preserved if they are recognized as a customization (for example, custom entries in the system-wide menu).

Review this list to verify whether all customized system nodes are really needed in the new version.

Not dropping customized table …

upgrade

A table not existing in the reference database would normally be dropped, but it is kept alive if recognized as a customized table.

Review this list to verify whether all customized tables are really needed in the new version.

Must re-write customized trigger function …

transfer

If data is migrated from a database in which triggers can contain inline code to a database in which triggers themselves can not contain code but only point to functions, the inline code has to be converted to a callable function. At the time of conversion, the number of arguments to the function is unknown, and since also translation of functions is not implemented yet, the trigger is basically rendered useless.

Translate the function called by the trigger into the target database's syntax.

Must verify customized object …

transfer

Migrate attempts to translate objects, but the result is not guaranteed to be correct.

Review that the object is translated correctly and works the way it is intended to.

Must re-write object … [error message]

transfer

Sometimes translation of an object fails. Migrate then just replaces the object's code with a compilable stub and indicates the last error as hint why translation failed.

Manually translate the object into the target database's syntax.

Modified … rows in … to comply with check constraint …

upgrade

A table contained values which would violate the check constraint rule. Those values have been modified to comply with the constraint.

Review the table to make sure that the modifications do not disrupt any business logic.

Could not find correct parent for … from … in … to …

upgrade

If a new column is added to a table and that column is part of a foreign key, Migrate attempts to find the correct parents for records already existing in the child table. This warning is issued if the correct parents could not be found.

If no error is reported when the foreign key is created, this warning can be ignored. Otherwise the child records must be linked to the correct parents manually. (If you know what hint can be used to deduce the correct parent, file a bug report).


Errors[edit]

The error log contains all errors which occurred during migration and need to be fixed. If an error was raised by the database driver, the original error message is added as a hint.

Table 3.2. Error Messages

ErrorCauseSolution

Could not find driver … [error message]

The required JDBC driver could not be found.

Make sure the JDBC driver is in the classpath.

Could not connect to database … [error message]

A connection to the database could not be established.

Make sure host name, port, database name, user name, and user password are correct.
Make sure the server is reachable over the network.
Make sure access configuration allows connections from your IP address.

Could not commit changes in … [error message]

Consult the database vendor's manual about the cause of the error.

Eliminate the cause of the error.

Could not roll back changes in … [error message]

Could not close … [error message]

Could not determine product vendor for … [error message]

The database vendor could not be determined or is unsupported.

Explicitly set the database vendor.

Could not determine catalog for … [error message]

No meaningful catalog could be determined.

Explicitly set the catalog to use.

Could not determine schema for … [error message]

No meaningful schema could be determined.

Explicitly set the schema to use.

Could not drop schema … [error message]

The target schema could not be dropped.

Make sure the user has sufficient privileges to drop a schema.

Could not test character set in … [error message]

Migrate temporarily creates a table with some string fields to check how the JDBC driver reports character sizes. An error occurred while trying to create this table.

Make sure no table with the name kkax_migr_chartest previously exists in the database.

Target table … does not exist

Tables which were expected to exist for terminology checking could not be found.

Terminology checking will only be successful on databases with an Adempiere-style Application Dictionary.

Source table … does not exist

Target translation table … does not exist

Join table … does not exist

Extra table … does not exist

Could not set savepoint … [error message]

Consult the database vendor's manual about the cause of the error.

Eliminate the cause of the error.

Could not get savepoint name [error message]

Could not rollback to savepoint … [error message]

Could not release savepoint … [error message]

Could not prepare statement … [error message]

Could not reset prepared statement … [error message]

Could not close prepared statement … [error message]

Could not count parameters for prepared statement … [error message]

Could not set parameter … of prepared statement … [error message]

Could not create statement [error message]

Could not close statement [error message]

Could not execute prepared statement query … [error message]

Could not execute sql query … [error message]

Could not close resultset … [error message]

Could not move cursor in result set … [error message]

Could not read column … from result set … [error message]

Could not check last column value from result set … [error message]

Could not execute prepared statement command … [error message]

Could not execute sql command … [error message]

unknown data type …

No unambiguous data type ID exists for the data type

File a bug report.

unknown data type or extra logic required for data type ID …

The unambiguous data type ID could not be converted to a vendor-specific data type

File a bug report.

Instantiation Exception for class … [error message]

A Java interface could not be instantiated.

File a bug report.

Illegal Access Exception for class … [error message]

Could not find interface … [error message]

A database can not be migrated to itself (source and target must be different)

Source and target connection parameters must point to different databases.

Make sure source and target connection parameters are correct.

Source and target need to be same database vendor for upgrades

Upgrades can only be run if source and target are the same database vendor.

Choose the correct reference database or run a transfer migration.


Start the Application Server[edit]

Now that your database has been successfully migrated, all errors have been fixed, and all warnings have been taken care of, the application server may be started again.

Users are welcome to log in.



[8] To run in text mode and/or suppress console output, the keywords text or silent can be given to the RUN_Migrate script as command line arguments.

[9] The system user and system password fields are not used if the selected database does not require log in by a system user for migration.