5.4.1 Offline Migration
By default, Migration Toolkit reads the definition of each selected object and creates an equivalent object in Advanced Server. If you specify the -offlineMigration option in the command line, Migration Toolkit performs an offline migration. During an offline migration, Migration Toolkit reads the definition of each selected object and creates an SQL script that, when executed at a later time, replicates each object in Postgres Plus Advanced Server.
To perform an offline migration, enter the command:
Each database object is saved in a separate file with a name derived from the schema name and object type (e.g. mtk_hr_table_ddl.sql) in the user home folder. To specify an alternative file destination, include a directory name after the -offlineMigration option:
Note: You must include the -schemaOnly option in any commands that contain the -offlineMigration option (at this time, data cannot be migrated offline).
5.4.2 Import Options
By default, Migration Toolkit imports both the data and the object definition (DDL) when migrating a schema; alternatively you can choose to import either the data or the DDL.
5.4.3 Schema Creation Options
By default, Migration Toolkit imports the source schema objects and/or data into a schema of the same name. If the target schema does not exist, Migration Toolkit creates a new schema. Alternatively, you may specify a custom schema name via the -targetSchema option. You can choose to drop the existing schema and create a new schema using the following option:
Use the -targetSchema option to specify the name of the migrated schema. If you are migrating multiple schemas, specify a name for each schema in a comma-separated list. If the command line does not include the -targetSchema option, the name of the new schema will be the same as the name of the source schema.
You cannot specify 'information-schema', 'dbo', 'sys' or 'pg_catalog' as target schema names. These schema names are reserved for meta-data storage in Advanced Server.
5.4.4 Schema Object Selection Options
Use the following options to select specific schema objects to migrate:
5.4.5 Migration Options
If you include the -safeMode option, Migration Toolkit commits each row as migrated; if the migration fails to transfer all records, rows inserted prior to the point of failure will remain in the target database.
Including the -fastCopy option specifies that Migration Toolkit should bypass WAL logging to perform the COPY operation in an optimized way, default disabled. If you choose to use the -fastCopy option, migrated data may not be recoverable (in the target database) if the migration is interrupted.
Use the -fetchSize option to specify the number of rows fetched in a result set. If the designated -fetchSize is too large, you may encounter Out of Memory exceptions; include the -fetchSize option to avoid this pitfall when migrating large tables. The default fetch size is specific to the JDBC driver implementation, and varies by database.
Specify the default datetime value (in yyyy-MM-dd_hh:mm:ss format) when migrating tables that include incompatible datetime values.
Note: This option is applicable only when migrating from MySQL databases. Unlike Advanced Server, MySQL supports datetime values of 0. Avoid migration error when importing MySQL data by using -defaultDateTime to replace datetime values of zero with datetime values that are PostgreSQL compatible.
file_name specifies the name of a file that contains constraints in key=value pairs. Each record read from the database is evaluated against the constraints; those that satisfy the constraints are migrated. The left side of the pair lists a qualified table name; the right side specifies a condition that must be true for each row migrated. For example, including the following constraints in the property file:
migrates only those countries with a country_id value that is not equal to AR; this constraint applies to the HR.countries table.
Use custom type mapping to change the data type of migrated columns. The left side of each pair specifies the columns with a regular expression; the right side of each pair names the data type that column should assume. You can include multiple pairs in a semi-colon separated column_list. For example, to map any column whose name ends in ID to type INTEGER, use the following custom mapping entry:
Custom mapping is applied to all table columns that match the criteria unless the column is table-qualified.
The '\\' characters act as an escape string; since '.' is a reserved character in regular expressions, use '\\.' to represent the '.' character. For example, to use custom mapping to select rows from the EMP_ID column in the EMP table, specify the following custom mapping entry:
5.4.6 Oracle Specific Options
The following options apply only when the source database is Oracle.
The dblink_ora module provides Advanced Server-to-Oracle connectivity at the SQL level. dblink_ora is bundled and installed as part of the Advanced Server database installation. dblink_ora utilizes the COPY API method to transfer data between databases. This method is considerably faster than the JDBC COPY method.
The following example uses the dblink_ora COPY API to migrate all tables from the HR schema:
The target Advanced Server database must have dblink_ora installed and configured. For installation details, refer to the dblink_ora setup guide (README-DBLINK_ORA_SETUP.txt), in the /dbserver/lib/postgresql/contrib subfolder under the Postgres Plus Advanced Server installation home directory.
Choose this option to migrate Oracle database links. The password information for each link connection in the source database is encrypted, so unless specified, a dummy password (edb) is substituted.
To migrate all database links using edb as the dummy password for the connected user:
You can alternatively specify the password for each of the database links through a comma-separated list of name=value pairs. Specify the link name on the left side of the pair and the password value on the right side.
To migrate all database links with the actual passwords specified on the command-line:
Migration Toolkit migrates only the database link types that are currently supported by EnterpriseDB; this includes Fixed User Links of Public and Private type.
5.4.7 Miscellaneous Options
Include this option to specify where the log files will be written; log_path represents the path where application log files are saved. By default, the log files are saved in the logs sub-folder under the application root path (/opt/PostgresPlus/8.3R2AS/migrationstudio/logs).