1. Ora2Pg will create a file that has all of your tables, columns, indices, keys etc. in Postgres format. Unfortunately it is great as a first pass, but we found that we had to do a lot of manually tweaking to get it into a really usable state. For instance, it does it's best guess at picking the right data type, but we didn't agree with all of its choices so we had to look through each one and decide which needed changing. Also, the way primary keys are setup on Oracle and Postgres is very different Ora2Pg takes a literal translation of translating how Oracle primary keys are set up instead of using the Postgres way - so we had to fix all of these.
2. It can't really convert functions and triggers. All of these had to be rewritten by hand.
3. It takes a ton of tweaking and experimentation to get Ora2Pg to transfer the data in a decent amount of time. Our first try took nearly 24 hears. We eventually tweaked it down to about an hour.
4. You will still need to rewrite all of your SQL queries into Postgres format. This is the part that took the most time since we have about 5,000 queries and each one had to be checked.
5. The biggest pain is that we couldn't stop development while the database migration was going on. So every time changes were made to the Oracle database, we had to manually make those same changes to the Postgres DDL files.
Sigh. I am so glad that project is behind me. I don't ever want to be involved in something like that again. It was totally worth it (Postgres is a dream to work with compared to Oracle), but the process was a nightmare.
Some notes about converting queries from Oracle to Postgres:
"foo IN (a, b, c)" can often be better done as "foo = ANY(ARRAY[a, b, c])" in Postgres - not least because you can shift to passing in an array of variable size without having to construct a special statement for each.
for sorting on the result of a function, consider using a subselect. "SELECT foo FROM (select fun(z) as foo from blaz) tmp order by 1".
replace nvl with coalesce
replace rownum <= 1 with LIMIT 1
replace listagg with string_agg
replace recursive hierarchy (start with/connect by/prior) with recursive
replace minus with except
replace SYSDATE with CURRENT_TIMESTAMP
replace trunc(sysdate) with CURRENT_DATE
replace trunc(datelastupdated) with DATE(datelastupduted) or datelastupdated::date
replace artificial date sentinels/fenceposts like to_date(’01 Jan 1900’) with '-infinity'::date
remove dual table references
replace decode with case statements
replace unique with distinct
replace to_number with ::integer
replace regexp_like() with ~ or ~*
replace mod with % operator
replace merge into with INSERT ... ON CONFLICT… DO UPDATE/NOTHING
change the default of any table using sys_guid() as a default to gen_random_uuid()
oracle pivot and unpivot do not work in postgres - use unnest
ORDER BY NLSSORT(english, 'NLS_SORT=generic_m') becomes ORDER BY gin(insensitive_query(english) gin_trgm_ops)
replace UNISTR( with U&’
Oracle: uses IS NULL to check for empty string; postgres uses empty string and null are different
If a varchar/text column has a unique index a check needs to be made to make sure empty strings are changed to nulls before adding or updating the column.
PostgreSQL requires a sub-SELECT surrounded by parentheses, and an alias must be provided for it. - SELECT * FROM ( ) A
any functions in the order by clause must be moved to the select statement (e.g. order by lower(column_name))
Any sort of numeric/integer/bigint/etc. inside of a IN statement must not be a string (including 'null' - don't bother trying to use null="", it won't work).
Concatenating a NULL with a NOT NULL will result in a NULL.
Pay attention to any left joins. If a column from a left join is used in a where or select clause it might be null.
For sequences, instead of sequence_name.nextval use nextval('sequence_name')