Migration from OracleSQL 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 does not guarantee it not causes data loss or corruption. Verification of all data's successful migration is the responsibility of the customer.

Preparation

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

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

Migration

Steps

1. Stop the nevisIDM service.

2. Extract oracle_migration.zip from here: oracle_to_postgres_migration.zip to a directory (in the example, the home directory), if you want to use another directory, change the path with the following steps.

   Configuration

   Change the database connection string, password and username in the config/ora2pg_tables.conf and config/ora2pg_history.conf files to point to the MariaDB database. Change the schema name in the SCHEMA line to match the schema name of the nevisIDM database.

3. Run the the sql command in sql/truncate.sql to truncate tables in the target schema.

4. Run the ora2pg tool with the config/ora2pg_tables.conf configuration file to migrate the non-historical tables.

docker run --name ora2pg-tables --network="host" -it -v ~/ora2pg/config:/config -v ~/ora2pg/data:/data -e CONFIG_LOCATION=/config/ora2pg_tables.conf georgmoser/ora2pg

6. Run the ora2pg tool with the config/ora2pg_history.conf configuration file to migrate the non-historical tables.

docker run --name ora2pg-history --network="host" -it -v ~/ora2pg/config:/config -v ~/ora2pg/data:/data -e CONFIG_LOCATION=/config/ora2pg_history.conf georgmoser/ora2pg

7. Run the the sql commands in sql/restart_sequences.sql to truncate tables in the target schema.
8. Change the database connection string in the nevisidm-prod.properties file to point to the PostgreSQL database.
9. Restart the nevisIDM service.