Migrate - Migrating a Database
Contents
Migrating a Database
Preperation
Disconnect all Users
The target database should be up and running.
No users should be logged in. Make sure all users are disconnected from the target and source database.
That includes the Adempiere server itself: Shut down the application server.
Create a Backup
You must have a backup of your live data before starting the migration process.
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.
Install new Adempiere version
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.
Import Reference Database
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.
Verify Preconditions
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 source or reference database is up and running
the target database is up and running
Running the Migration Tool
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
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
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
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
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 or1521
for Oracle.The default is
5432
.- user
The normal database user as which to log in.
The default is
reference
for source andadempiere
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 andadempiere
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
- 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
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
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.
Starting from the Command Line
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
- Classpath
The classpath should contain the file
migrate.jar
as well as the JDBC database drivers- 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
- -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
If the database contains large objects, higher settings may be necessary.
$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.
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
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
The warning log contains tasks to be performed manually by the database administrator after migration.
Table 3.1. Warning Messages
Warning | Mode | Cause | Solution |
---|---|---|---|
|
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. |
|
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. |
|
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. |
|
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. |
|
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. |
|
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. |
|
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
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
Error | Cause | Solution | |||
---|---|---|---|---|---|
|
The required JDBC driver could not be found. |
Make sure the JDBC driver is in the classpath. | |||
|
A connection to the database could not be established. |
| |||
|
Consult the database vendor's manual about the cause of the error. |
Eliminate the cause of the error. | |||
| |||||
| |||||
|
The database vendor could not be determined or is unsupported. |
Explicitly set the database vendor. | |||
|
No meaningful catalog could be determined. |
Explicitly set the catalog to use. | |||
|
No meaningful schema could be determined. |
Explicitly set the schema to use. | |||
|
The target schema could not be dropped. |
Make sure the user has sufficient privileges to drop a schema. | |||
|
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 | |||
|
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. | |||
| |||||
| |||||
| |||||
| |||||
|
Consult the database vendor's manual about the cause of the error. |
Eliminate the cause of the error. | |||
| |||||
| |||||
| |||||
| |||||
| |||||
| |||||
| |||||
| |||||
| |||||
| |||||
| |||||
| |||||
| |||||
| |||||
| |||||
| |||||
| |||||
| |||||
|
No unambiguous data type ID exists for the data type |
File a bug report. | |||
|
The unambiguous data type ID could not be converted to a vendor-specific data type |
File a bug report. | |||
|
A Java interface could not be instantiated. |
File a bug report. | |||
| |||||
| |||||
|
Source and target connection parameters must point to different databases. |
Make sure source and target connection parameters are correct. | |||
|
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
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.