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 - 220.127.116.11-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?