Database Migration #33662935/HEAD / v270 |
1Database MigrationThis guide provides information on database migration.Table of Contents
1.1ConceptMigration tool creates source_hash and target_hash table for all tables and store a primary key(s) and the MD5 hash of the rest of the columns. Hash is computed for all rows, on source and target as well.
1.2Prerequisites
From MySQL 8.30, the Generated Invisible Primary Keys
![]() Before starting the migration script, ensure that sql_generate_invisible_primary_key is OFF. Use the IP address of the host machine instead of Localhost at the host of DB (both source and target). To edit the config.conf in Windows, use Notepad. Do not use rich text editor. If possible, avoid special characters in the target DB password. Ensure to enter a LF (Line Feed) character (hit Enter) after the value of the last line ( cb.minor.version= ) of the config.conf. Otherwise, the Minor field of the Version table remains empty due to which Codebeamer cannot be started. 1.3LimitationTemp tables will not be dropped after migration in the source database. Temp tables end with _temp .
1.4How to Create PostgreSQLFollowing configuration should be used for database creation: LC_COLLATE='en_US.UTF-8' LC_CTYPE='en_US.UTF-8' ENCODING 'UTF8'
1.5How to Create OracleFollowing configuration should be used for database creation: GRANT CREATE SESSION, CREATE TYPE, CREATE TABLE, CREATE CLUSTER, CREATE TRIGGER, CREATE OPERATOR, CREATE SEQUENCE, CREATE INDEXTYPE, CREATE PROCEDURE, CREATE VIEW GRANT EXECUTE ON CTXSYS.CTX_DDL GRANT EXECUTE ON DBMS_LOB GRANT EXECUTE ON DBMS_CRYPTO
DBMS_CRYPTO grant can be removed after the migration.
1.6Migration StagesPre-Stage - Steps are running on the source database
Java-Migration-Stage
Post-Stage - Steps are running on target database
1.7How to Run the Migration Tool1.7.1ConfigurationCreate a configuration file using the following template.
config.conf # Number of threads is used for running hash computation script. Use the number of CPUs of source database. db.parallel.sql_job_number=4 # Number of threads is used for running PK creation script. Use the number of CPUs of source database.(max 11) db.parallel.pk_job_number=2 db.source.database.type=<mysql|postgresql|oracle> db.target.database.type=<postgresql|oracle> db.source.host=<database host> db.source.port=<database port> db.source.username=<database username> db.source.password=<database password> db.source.database=<database name> db.target.host=<database host> db.target.port=<database port> db.target.username=<database username> db.target.password=<database password> db.target.database=<database name> # default is true migrator.run.pre_stage=<true|false> # default is true migrator.run.java_stage=<true|false> # default is true migrator.run.post_stage=<true|false> # Run hash computation and validation or not migrator.verify_rows=<true|false> # It depends on your memory of the migration server and database server migrator.fetch_size=3000 # It depends on your memory of the migration server and database server migrator.batch_size=3000 # Number of maximum threads is used for running migration script. Use the number of CPUs of your migration machine migrator.worker.threads=12 # Number is threads is used for processing one table, smaller or same as the total.sections configuration migrator.table.parallel=12 # Number of sections that table is cut of migrator.total.sections=24 # Drop or keep the hash tables, it is helpful for investigating migration issues, by default it is true migrator.drop_hash_tables=<true|false> # See the related section below, by default it is false migrator.drop_document_cache=<true|false> # See the related section below, by default it is true migrator.update_boolean_values=<true|false> # See the related section below, by default it is true migrator.remove_000_characters=<true|false> # Major version of the source Codebeamer. To obtain the value to use for this configuration, execute the following database query: SELECT major FROM version; cb.major.version=22.10 # Minor version of the source Codebeamer. To obtain the value to use for this configuration, execute the following database query: SELECT minor FROM version; cb.minor.version=-SP7
1.7.2How to Download the Migrator Tool1.7.2.1Docker Images
1.7.2.2How to Pull Docker Image
1.7.3How to Start the Migration Tooldocker run -v <path of config.conf>:/conf/config -v <path of directory>:/conf/logs -i <docker image, see above> /rootfs/run-migration.sh
1.8Database Configuration1.8.1PostgreSQL Tuningsynchronous_commit = off
This configuration must set to ON for production use.
1.8.2Oraclenls_length_semantics = CHAR
This configuration is required for migration and production use as well.
1.9Execution Times of Test RunsWe used a ~200G database running on AWS.
Details:
1.9.1Execution Time for MySQL -> Oracle1.9.1.1With Verification
1.9.1.2Without Verification
1.9.2Execution Time for MySQL -> PostgreSQL1.9.2.1With Verification
1.9.2.2Without Verification
1.9.3RecommendationHalf of the migration time is the verification step, in order to speed up the migration.
Dry-run migration with verification and validation: migrator.verify_rows=true migrator.drop_hash_tables=false
Real migration with verification and validation: migrator.verify_rows=true migrator.drop_hash_tables=true
Real migration without verification and validation: migrator.verify_rows=false migrator.drop_hash_tables=false
1.9.4Remove Data of document_cachedocument_cache, document_cache_data, and document_cache_data_blobs contain data for faster re-index of the database. These tables can be truncated before the migration: SET foreign_key_checks = 0; TRUNCATE TABLE document_cache; TRUNCATE TABLE document_cache_data; TRUNCATE TABLE document_cache_data_blobs; SET foreign_key_checks = 1;
1.9.5Data InconsistencyIf migrator_update_boolean_values is set to true, on MySQL the following SQL scripts will be executed in order to fix data inconsistency: UPDATE object_notification_temp SET only_members = 0 WHERE only_members IS NULL; UPDATE object_notification_temp SET deleted = 0 WHERE deleted IS NULL; UPDATE task_type SET service_desk = 0 WHERE service_desk IS NULL; UPDATE task_type SET template = 0 WHERE template IS NULL;
If migrator.remove_000_characters is set to true, on MySQL the following SQL scripts will be executed in order to fix data inconsistency: UPDATE task_field_history SET new_value = replace(new_value, 0x00, "") WHERE new_value LIKE CONCAT("%", CHAR(0x00 using utf8), "%"); UPDATE task_field_history SET old_value = replace(old_value, 0x00, "") WHERE old_value LIKE CONCAT("%", CHAR(0x00 using utf8), "%"); UPDATE task_field_value SET field_value = replace(field_value, 0x00, "") WHERE field_value LIKE CONCAT("%", CHAR(0x00 using utf8), "%");
Setting migrator.remove_000_characters to true on the source database is necessary because the 0x00 character is not valid in PostgreSQL and Oracle.
|
Fast Links
![]() codebeamer Overview codebeamer Knowledge Base Services by Intland Software |
This website stores cookies on your computer. These cookies are used to improve your browsing experience, constantly optimize the functionality and content of our website, furthermore helps us to understand your interests and provide more personalized services to you, both on this website and through other media. With your permission we and our partners may use precise geolocation data and identification through device scanning. You may click accept to consent to our and our partners’ processing as described above. Please be aware that some processing of your personal data may not require your consent, but you have a right to object to such processing. By using our website, you acknowledge this notice of our cookie practices. By accepting and continuing to browse this site, you agree to this use. For more information about the cookies we use, please visit our Privacy Policy.Your preferences will apply to this website only.
Note that user-behavior analytics are being captured on this server for the purpose of improving the Codebeamer user experience.