Migrate - Migrating a Database

From ADempiere
Revision as of 23:42, 22 September 2011 by Kkalice (Talk)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search
This Wiki is read-only for reference purposes to avoid broken links.


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

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

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

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

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

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

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

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

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.

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

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>"></span>-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