Migration from MariaDB database To PostgreSQL

danger

This guide contains a possible free migration solution. It can be used to be the base of your migration script, but it is not guaranteed to work in all cases, since we only tested in our test environment. Nevis can not guarantee that it does not cause data loss or corruption. Verification of all data's successful migration is the responsibility of the customer.

Preparation

To migrate the MariaDB database to PostgreSQL, you need to prepare the following:

• A freshly deployed nevisIDM database schema on PostgreSQL with the same schema version as the mariaDB. The contents of the schema will be removed during migration. For installation steps visit Database Preparing
• An installed pgloader tool.

Migration

Steps

1. Stop the nevisIDM service.
2. Create idm_tables.lisp and idm_history_tables.lisp configuration files (as described below in teh Configuration section).
   Configuration

   Change the database connection string in the idm_tables.lisp and idm_history_tables.lisp files to point to the MariaDB database. Change the schema name in the ALTER SCHEMA command to match the schema name of the nevisIDM database.

3. Run the pgloader tool with the idm_tables.lisp configuration file to migrate the non-historical tables.

pgloader idm_tables.lisp

4. Run the pgloader tool with the idm_history_tables.lisp configuration file to migrate the historical tables.

pgloader idm_history_tables.lisp

5. Change the database connection string in the nevisidm-prod.properties file to point to the PostgreSQL database.
6. Restart the nevisIDM service.

Configuration

idm_tables.lisp

The following configuration file is used to map the MariaDB non-historical idm tables to PostgreSQL tables. The file is used by the pgloader tool to migrate the data from MariaDB to PostgreSQL. Changing values of the LOAD DATABASE and INTO commands is required to match the MariaDB and PostgreSQL connection strings respectively. Also changing of ALTER SCHEMA is required to match the schema name of the nevisIDM database.

LOAD DATABASE
FROM mysql://root:root@localhost/nevisidm
INTO postgresql://UIDM01:UIDM01@localhost/nevisidm
cast
    type datetime to timestamp, type bigint to bigint,
    column tidma_application.ctl_tcn to integer,
    column tidma_authorization.ctl_tcn to integer,
    column tidma_authorization_appl.ctl_tcn to integer,
    column tidma_authorization_client.ctl_tcn to integer,
    column tidma_authorization_erole.ctl_tcn to integer,
    column tidma_authorization_unit.ctl_tcn to integer,
    column tidma_client.ctl_tcn to integer,
    column tidma_client_application.ctl_tcn to integer,
    column tidma_consent.ctl_tcn to integer,
    column tidma_credential.ctl_tcn to integer,
    column tidma_credential.state_change_reason_cd to integer,
    column tidma_credential.reset_count to integer,
    column tidma_cred_login_info.ctl_tcn to integer,
    column tidma_cred_login_info.login_success_count to integer,
    column tidma_cred_login_info.login_failure_count to integer,
    column tidma_dict_entry.ctl_tcn to integer,
    column tidma_enterprise_auth.ctl_tcn to integer,
    column tidma_enterprise_role.ctl_tcn to integer,
    column tidma_erole_member.ctl_tcn to integer,
    column tidma_fido_uaf.sign_counter to integer,
    column tidma_fido_uaf.authenticator_version to integer,
    column tidma_oath.digits to integer,
    column tidma_persist_queue.state to integer,
    column tidma_persist_queue.type to integer,
    column tidma_personal_answer.ctl_tcn to integer,
    column tidma_personal_answer.reveal_count to integer,
    column tidma_personal_answer.success_count to integer,
    column tidma_personal_answer.failure_count to integer,
    column tidma_personal_question.ctl_tcn to integer,
    column tidma_policy_configuration.ctl_tcn to integer,
    column tidma_policy_parameter.ctl_tcn to integer,
    column tidma_policy_parameter.precedence to integer,
    column tidma_profile.ctl_tcn to integer,
    column tidma_property.ctl_tcn to integer,
    column tidma_property.gui_precedence to integer,
    column tidma_property_allowed_val.ctl_tcn to integer,
    column tidma_recovery_code.ctl_tcn to integer,
    column tidma_role.ctl_tcn to integer,
    column tidma_template.ctl_tcn to integer,
    column tidma_template.precedence to integer,
    column tidma_template_collection.ctl_tcn to integer,
    column tidma_template_text.ctl_tcn to integer,
    column tidma_terms.ctl_tcn to integer,
    column tidma_unit.ctl_tcn to integer,
    column tidma_unit_cred_policy.ctl_tcn to integer,
    column tidma_user.ctl_tcn to integer,
    column tidma_user.pobox_number to integer,
    column tidma_user.state_change_reason_cd to integer,
    column tidma_user_login_info.ctl_tcn to integer,
    column tidma_vasco_dp_token.ctl_tcn to integer,
    column tidmq_fired_triggers.priority to integer,
    column tidmq_fired_triggers.is_nonconcurrent to boolean,
    column tidmq_fired_triggers.requests_recovery to boolean,
    column tidmq_job_details.is_durable to boolean,
    column tidmq_job_details.is_nonconcurrent to boolean,
    column tidmq_job_details.is_update_data to boolean,
    column tidmq_job_details.requests_recovery to boolean,
    column tidmq_simprop_triggers.int_prop_1 to integer,
    column tidmq_simprop_triggers.int_prop_2 to integer,
    column tidmq_simprop_triggers.bool_prop_1 to boolean,
    column tidmq_simprop_triggers.bool_prop_2 to boolean,
    column tidmq_triggers.priority to integer,
    column tidmr_change_reason.code to integer
WITH 
    data only, 
    truncate, 
    disable triggers
excluding table names matching
    -- exclude history tables
    ~<tidma_application_v>,
    ~<tidma_authorization_appl_v>,
    ~<tidma_authorization_client_v>,
    ~<tidma_authorization_erole_v>,
    ~<tidma_authorization_unit_v>,
    ~<tidma_authorization_v>,
    ~<tidma_cert_info_v>,
    ~<tidma_client_application_v>,
    ~<tidma_client_v>,
    ~<tidma_consent_v>,
    ~<tidma_cred_login_info_v>,
    ~<tidma_credential_v>,
    ~<tidma_dict_entry_v>,
    ~<tidma_dict_entry_value_v>,
    ~<tidma_enterprise_auth_v>,
    ~<tidma_enterprise_role_v>,
    ~<tidma_erole_member_v>,
    ~<tidma_fido2_v>,
    ~<tidma_fido_uaf_v>,
    ~<tidma_mobile_signature_v>,
    ~<tidma_oath_v>,
    ~<tidma_personal_answer_v>,
    ~<tidma_personal_question_v>,
    ~<tidma_policy_configuration_v>,
    ~<tidma_policy_parameter_v>,
    ~<tidma_profile_v>,
    ~<tidma_property_allowed_val_v>,
    ~<tidma_property_v>,
    ~<tidma_property_value_v>,
    ~<tidma_role_v>,
    ~<tidma_saml_federation_v>,
    ~<tidma_template_collection_v>,
    ~<tidma_template_text_v>,
    ~<tidma_template_v>,
    ~<tidma_terms_application_v>,
    ~<tidma_terms_url_v>,
    ~<tidma_terms_v>,
    ~<tidma_unit_cred_policy_v>,
    ~<tidma_unit_v>,
    ~<tidma_user_login_info_v>,
    ~<tidma_user_v>,
    -- and migration history table
    ~<tidma_db_history>
BEFORE LOAD DO
    -- renaming schema to the temporary name
    $$ ALTER SCHEMA "UIDM01" RENAME TO "nevisidm"; $$,
    -- temporary fields to contain generated column's data, they will be deleted later
    $$ ALTER TABLE nevisidm.TIDMA_CERT_INFO  add column upper_fingerprint      varchar(80); $$,
    $$ ALTER TABLE nevisidm.tidma_property_value  add column upper_value      text; $$,
    $$ ALTER TABLE nevisidm.tidma_unit  add column upper_hname      text; $$,
    $$ ALTER TABLE nevisidm.tidma_unit  add column upper_name      varchar(100); $$,
    $$ ALTER TABLE nevisidm.tidma_user  add column upper_name      varchar(120); $$,
    $$ ALTER TABLE nevisidm.tidma_user  add column upper_first_name      varchar(100); $$,
    $$ ALTER TABLE nevisidm.tidma_user  add column upper_login_id      varchar(300); $$,
    $$ ALTER TABLE nevisidm.tidma_user  add column upper_email      text; $$,
    $$ ALTER TABLE nevisidm.tidma_user  add column upper_unique_email      text; $$

AFTER LOAD DO
    -- deleting helper columns
    $$ ALTER TABLE nevisidm.tidma_user  DROP column upper_name, DROP column upper_first_name, DROP column upper_login_id, DROP column upper_email, DROP column upper_unique_email; $$,
    $$ ALTER TABLE nevisidm.tidma_cert_info drop column upper_fingerprint;$$,
    $$ ALTER TABLE nevisidm.tidma_property_value drop column upper_value; $$,
    $$ ALTER TABLE nevisidm.tidma_unit drop column upper_name, drop column upper_hname;$$,
    -- renaming schema to the original name
    $$ ALTER SCHEMA "nevisidm" RENAME TO "UIDM01";$$;

idm_history_tables.lisp

The following configuration file is used to map the MariaDB historical idm tables to PostgreSQL tables. The file is used by the pgloader tool to migrate the data from MariaDB to PostgreSQL. Changing values of the LOAD DATABASE and INTO commands is required to match the MariaDB and PostgreSQL connection strings respectively. Also changing of ALTER SCHEMA is required to match the schema name of the nevisIDM database.

LOAD DATABASE
FROM mysql://root:root@localhost/nevisidm
INTO postgresql://UIDM01:UIDM01@localhost/nevisidm
cast 
    type datetime to timestamp, type bigint to bigint, 
    column tidma_application_v.ctl_tcn to integer,
    column tidma_authorization_appl_v.ctl_tcn to integer,
    column tidma_authorization_client_v.ctl_tcn to integer,
    column tidma_authorization_erole_v.ctl_tcn to integer,
    column tidma_authorization_unit_v.ctl_tcn to integer,
    column tidma_authorization_v.ctl_tcn to integer,
    column tidma_cert_info_v.ctl_tcn to integer,
    column tidma_client_application_v.ctl_tcn to integer,
    column tidma_client_v.ctl_tcn to integer,
    column tidma_consent_v.ctl_tcn to integer,
    column tidma_cred_login_info_v.ctl_tcn to integer,
    column tidma_credential_v.ctl_tcn to integer,
    column tidma_dict_entry_v.ctl_tcn to integer,
    column tidma_dict_entry_value_v.ctl_tcn to integer,
    column tidma_enterprise_auth_v.ctl_tcn to integer,
    column tidma_enterprise_role_v.ctl_tcn to integer,
    column tidma_erole_member_v.ctl_tcn to integer,
    column tidma_fido2_v.ctl_tcn to integer,
    column tidma_fido_uaf_v.ctl_tcn to integer,
    column tidma_mobile_signature_v.ctl_tcn to integer,
    column tidma_oath_v.ctl_tcn to integer,
    column tidma_personal_answer_v.ctl_tcn to integer,
    column tidma_personal_question_v.ctl_tcn to integer,
    column tidma_policy_configuration_v.ctl_tcn to integer,
    column tidma_policy_parameter_v.ctl_tcn to integer,
    column tidma_profile_v.ctl_tcn to integer,
    column tidma_property_allowed_val_v.ctl_tcn to integer,
    column tidma_property_v.ctl_tcn to integer,
    column tidma_property_value_v.ctl_tcn to integer,
    column tidma_role_v.ctl_tcn to integer,
    column tidma_saml_federation_v.ctl_tcn to integer,
    column tidma_template_collection_v.ctl_tcn to integer,
    column tidma_template_text_v.ctl_tcn to integer,
    column tidma_template_v.ctl_tcn to integer,
    column tidma_terms_application_v.ctl_tcn to integer,
    column tidma_terms_url_v.ctl_tcn to integer,
    column tidma_terms_v.ctl_tcn to integer,
    column tidma_unit_cred_policy_v.ctl_tcn to integer,
    column tidma_unit_v.ctl_tcn to integer,
    column tidma_user_login_info_v.ctl_tcn to integer,
    column tidma_user_v.ctl_tcn to integer,
    column tidma_credential_v.state_change_reason_cd to integer,
    column tidma_credential_v.reset_count to integer,
    column tidma_cred_login_info_v.login_success_count to integer,
    column tidma_cred_login_info_v.login_failure_count to integer,
    column tidma_fido_uaf_v.sign_counter to integer,
    column tidma_fido_uaf_v.authenticator_version to integer,
    column tidma_oath_v.digits to integer,
    column tidma_personal_answer_v.reveal_count to integer,
    column tidma_personal_answer_v.success_count to integer,
    column tidma_personal_answer_v.failure_count to integer,
    column tidma_policy_parameter_v.precedence to integer,
    column tidma_property_v.gui_precedence to integer,
    column tidma_template_v.precedence to integer,
    column tidma_user_v.pobox_number to integer,
    column tidma_user_v.state_change_reason_cd to integer
WITH 
    data only,
    truncate,
    disable triggers

including only table names matching ~/_v/

excluding table names matching 
    ~<val>, 
    ~<vasco>

BEFORE LOAD DO
    -- renaming schema to the temporary name
    $$ ALTER SCHEMA "UIDM01" RENAME TO "nevisidm"; $$,
    -- Add a temporary field to handle missing column in the target table, will need to be removed after NEVISIDM-9562 is released 
    $$ ALTER TABLE nevisidm.TIDMA_CERT_INFO_V ADD COLUMN CERTIFICATE_VALUE TEXT;$$
    AFTER LOAD DO
    -- removing the temporary field
    $$ ALTER TABLE nevisidm.TIDMA_CERT_INFO_V DROP COLUMN CERTIFICATE_VALUE;$$,
    -- renaming schema to the original name
    $$ ALTER SCHEMA "nevisidm" RENAME TO "UIDM01"; $$;