Moving app from Oracle to Postgresql 11

Support
#1

I am moving an app from Oracle to Postgresql 11. I copied my schema from Oracle to Postgres using AWS SCT and then moved the data using ora2pg.

I discovered that there are some fundamental differences within your internal CUBA Schemas between the two databases. Specifically, you represent the same field as Boolean in Postgresql and character(1) in Oracle (since they don’t support the Boolean data type). For example, see sec_user.active. Copying it from Oracle to Postgresql leaves it as character(1), but Eclipselink is trying to treat it as a Boolean. I get this error:

javax.persistence.PersistenceException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.7.3.6-cuba): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.postgresql.util.PSQLException: ERROR: operator does not exist: character = boolean
  Hint: No operator matches the given name and argument types. You might need to add explicit type casts.
  Position: 367
Error Code: 0
Call: SELECT ID, ACTIVE, CHANGE_PASSWORD_AT_LOGON, CREATE_TS, CREATED_BY, DELETE_TS, DELETED_BY, EMAIL, FIRST_NAME, GROUP_NAMES, IP_MASK, LANGUAGE_, LAST_NAME, LOGIN, LOGIN_LC, MIDDLE_NAME, NAME, PASSWORD, PASSWORD_ENCRYPTION, POSITION_, SYS_TENANT_ID, TIME_ZONE, TIME_ZONE_AUTO, UPDATE_TS, UPDATED_BY, VERSION, GROUP_ID FROM SEC_USER WHERE (((LOGIN_LC = ?) AND ((ACTIVE = ?) OR (ACTIVE IS NULL))) AND (DELETE_TS IS NULL))
        bind => [anonymous, true]

So, what’s the best way to transfer my schema and data? If I let the app re-build the schema, I will lose all the transferred data. And if I try to transfer the data afterwards, then I need to translate all the character(1) data to Boolean. How will I find them all?

Do you have any best practices for this kind of migration?

#2

https://wiki.postgresql.org/wiki/Oracle_to_Postgres_Conversion

In your place I would do the following:

  1. Compare schemas
    Create default CUBA schemas on both Oracle and PostgreSQL database.
    Compare the 2 schemas and see where there are differences. There are tools to do that, one simple is
    Winmerge, just open 2 db text schema files and compare, you can search postgres schema for “binary” and see what it translates to, to speed up the comparision.
    Check also what kind of type is used by the primary keys (id columns) it may be that oracle doesn’t support UUID type either.

  2. Migrate the data
    Any decent tool can also create a migration script. You can modify it to create char to boolean if needed.

#3

Yes, I was considering the same thing. I was looking through the init scripts in the core module to see the differences. There are many of them, but they aren’t 100% consistent, unfortunately.

For example, almost every time Oracle has a char(1) field it is a boolean in Postgresql. But, once in a while, the char(1) is a multi-valued flag. This means I can’t blindly translate char(1) to boolean.

However, varchar(32) seems to translate to uuid 100% of the time.

So, sometimes I can do it by data type and sometimes I have to do it by field, which makes the conversion time consuming.

#4

Since the eclipselink expects boolean when connected to postgres CUBA schema, while it expects char(1) while accessing to oracle, I thought to look around and maybe somehow do the dark magic of replacing the definitions, but I think that would be bad idea, especially when upgrading to a newer cuba version.

How many cuba forms, usergroups and such cuba specific elements you have? Maybe just re-create app in postgres, and then copy the data only, not the metadata.

#5

Well, I think I managed to solve it. I let Cuba do a DB init in the Postgres database from scratch. Using AWS SCT, I loaded the Oracle schema and let it do a first pass conversion. Then I went table by table and compared what SCT did with what was in the Postgres database. I created type mapping rules (when you see this, replace it with that) for all the differences.

I ended up with 14 rules - not terrible. Some of them are due to inconsistencies in the Cuba definitions. For example, filter_xml field in most Postgres tables is varchar(7000). However, in sys_attr_value table, it is varchar(4000). Not sure why. group_id is defined in the sec_constraint Oracle table as varchar2(36) when it should be varchar2(32), because it is a uuid in Postgres.

Anyway, I then let SCT re-create the schema in the database and used ora2pg to transfer the data itself.

I was then able to connect the Cuba app and my initial testing showed no obvious errors.