This document gives the requirements and best practices for changing the technology of your backend database from Oracle to PostgreSQL.
This migration method does not preserve:
A full migration of the entire repository and data location data across database backends is not supported. |
Do not plan a backend migration along with an upgrade of Semarchy xDM. Upgrade first your Semarchy xDM infrastructure to the latest xDM release, make sure that xDM as well as your models are fully functional with this release and then perform a backend database migration. |
Cross version migration is not supported. You cannot export a model from an xDM version and import it into another version. |
It is not recommended to mix database backend technologies. The database technology used for the repository and the data locations should be the same. |
To migrate from Oracle to PostgreSQL:
This section lists differences in the database that you should review for the migration process.
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.
Semarchy Datatype | Oracle Datatype | PostgreSQL Datatype | Comments |
---|---|---|---|
Boolean | CHAR(1) | BOOLEAN | |
String | VARCHAR2(n CHAR) | VARCHAR | See the Null Handling note below. |
UUID | RAW(16) | UUID | |
Binary | BLOB | BYTEA | |
LongText | CLOB | TEXT | |
Date | DATE | DATE | Date in Oracle stores a time component which is automatically removed by the integration job. |
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.
Oracle | Postgres | Postgres | SemQL (Oracle, PostgreSQL) |
---|---|---|---|
SELECT 'Something plus' || NULL FROM dual; Something plus | SELECT 'Something plus' || NULL; [NULL] | select concat('Something plus', null); Something plus | CONCAT( 'Something plus', null ) Something plus |
The following link provides reference Information to convert Oracle to PostgreSQL.
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.
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