This document gives the requirements and best practices for changing the technology of your backend database from Oracle to PostgreSQL.

Requirements & Warning

Model Migration Process

To migrate from Oracle to PostgreSQL:

1. Create a new repository in PostgreSQL.
2. Manually re-create the platform artifacts: roles, plugins, notification servers, variable value providers, image library, etc in the PostgreSQL repository.
3. Export and import the latest model edition from the Oracle repository to the new PostgreSQL repository as a new model.
4. Change the model's Target Database to PostgreSQL.
5. Fix the model
   1. Review User-Defined Functions
      1. Oracle uses the notion of package which does not exist in PostgreSQL. Remove the Package for all functions.
   2. Use model validation to raise immediate syntactic issues (different functions or syntax elements)
      1. Fix syntactic issues raised by the validation.
   3. Review all SemQL expressions:
      1. Built-in functions behaviour may differ (e.g: SEM_EDIT_DISTANCE).
      2. Implicit type conversion: Oracle does implicit type conversion, PostgreSQL does not.
   4. Review possible SQL expressions:
      1. SQL Hooks defined into Jobs.
      2. Model variables using a database value providers
      3. Lookup Plugin select statement
6. Deploy this model in a new data location
7. Transfer the data from the Oracle schema to the PostgreSQL database using you ETL or middleware.
   1. Load all data from the Oracle data location tables (SD, MD, .... and DL_ tables). Make sure to exclude the DL_DATA_LOC table from this process.
      For this phase, pay attention to the Datatype Mapping differences.
   2. Set the SEQ_* SSQ_* sequences in the PostgreSQL data location to the current Oracle values.
   3. Set the SEQ_BATCHID and SEQ_LOADID in the PostgreSQL repository to the values from the Oracle repository.
   4. Perform  adjustments
      
      1. PL/SQL Functions used by the model should be converted to PL/pgSQL into the equivalent PostgreSQL location (PostgreSQL data location for example). 
8. Rewrite or review ETL jobs to point to the PostgreSQL hub.
9. Test the entire data hub:
   1. Inbound integration using the SQL and REST API, as well as data certification process results.
   2. Application behavior when browsing, authoring data and managing duplicates.
   3. Outbound Integration using SQL or REST API.

Noticeable Database Differences

This section lists differences in the database that you should review for the migration process.

Datatype Mapping

The following table lists the datatype mapping and differences between Oracle and PostgreSQL.
Datatypes not listed below have the same behaviour in Oracle and PostgreSQL.

Null Handling

For Oracle, an empty string is equal to a null. PostgreSQL makes the difference between a null string and an empty string.
The integration job takes care of empty strings and convert them to null values. However, null or empty strings may be created by other means (steppers, PL/SQL triggers, etc).
SemQL expressions, PL/SQL functions or SQL Hooks handling strings assuming that empty string is implicitly null should handle this explicitly.

Examples of NULL handling

Reference Information

The following link provides reference Information to convert Oracle to PostgreSQL.

• Oracle to PostgreSQL Conversion

Customer feedback

A French customer decided to move from Oracle to Postgres in their AWS environment.

Migrating the model from Oracle to Postgres was easy, they only had a couple of validation errors and few PL/SQL functions.

The challenge came from the data to transfer from PROD Oracle RDS to new PROD Postgre RDS.

The whole data location represents 65 million rows (counting all tables).

Our official answer on data transfer is to use an ETL and write mappings for all tables but in this case we used DI/Stambia and a template written by Hilaire Godinot (Deactivated) that generates the insert / select from an oracle schema to a postgres schema, applying correct semarchy type casts (for booleans, UUIDs...) and also update sequences.

After tuning and optimizing the template and the servers, the actual transfer time was 40 minutes (all tables, sequences and indexes rebuild).

This is the first metric we have for an oracle to postgres migration, on a real customer project with a decent amount of data.

Technical details below

Instances 

ec2 t3.2xlarge (8CPU/32GRAM)

postgres RDS r5.2xlarge (8CPU/64GRAM)

oracle RDS db.m5.2xlarge (8CPU/32GRAM)

The DI runtime was installed on the ec2 instance where Semarchy runs, in the same region as both RDS source and target instances with only 4G of RAM.

Last RUN parameters : batch/fetch size 30k + disable all indexes except pk + 7 parallel threads
Data transfer = 36 minutes
Deploy model to recreate and rebuild indexes = 3mins
Total time 39mins

Production migration scenario

1. Make sure all servers use the same semarchy version
2. Stop Semarchy (tomcat) on postgres
3. Make sure postgre_mdm schema contains table structures and rows only in DL_ tables (drop / recreate schema and deploy model if needed)
4. Manually drop indexes before initial load (prepared script)
5. Stop continuous load / execution engine on mdm_oracle
6. Run DI template to load data from oracle to postgres (data + sequences dataloc)
7. Stop Semarchy on oracle
8. Align repository sequences from oracle to postgres
9. Start Semarchy on postgres and deploy model to recreate and rebuild indexes
10. Restore delta from oracle to postgres (SD to SD with prepared ETL script, group by load)
11. Stop RDS / ec2 instances for oracle and reuse elastic ip / name for postgres instances or edit DNS