Best practices for migrating an Oracle database to Amazon PostgreSQL

https://aws.amazon.com/fr/corretto/

Anyway, I made a promise to myself that when the project hits 300 stars I'd write tests and refactor for scalability. I need 3 more. Probably will be looking for some help if anyone's interested... https://www.github.com/seanharr11/etlalchemy

"This looks like it'll cost $3M-$5M to migrate away from Oracle. But Oracle only increased our licensing costs $500K per year and I already have a team of 5 Oracle DBAs!"

Okay... but those costs compound each year.

Don't get me wrong... I'm all in the "migrate to cloud" camp. But DB migration is one of the most difficult and political conversations to have these days.

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 oracle insensitive query format with insensitive_query()

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

Fix string IS NULL comparisons: field1 IS NULL becomes COALESCE(field1::text, '') = ''

Fix string IS NOT NULL comparisons: field2 IS NOT NULL becomes (field2 IS NOT NULL AND field2::text != '')

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 table.nextval use nextval()

Recommendation: change all varchar2 columns to be not null and set the default to be ''. This fixes a lot of the issues with the difference between how oracle and postgres treat empty strings and nulls.

I wrote an adapter that tied PHP (pre-1.0) and Java together before we had servlets and Tomcat. We used Java for the ‘backend’ and PHP for the ‘frontend’ rendering. Java talked to Oracle, pulled data out of the db and sent it to PHP for rendering the HTML. Oracle was forced on me because Ascend had a contract with them. This project was a nightmare on many levels, but Oracle was the majority of my drama.

Eventually, this project drove me to figure out servlets and Apache Jserv (first open source servlet engine)… which then led to myself and a couple random guys from Italy who had joined to work on JServ, to get Sun to open source Tomcat and create Java Apache (later renamed to Jakarta). If it wasn’t for all that, other Java stuff might not have happened at Apache (Hadoop, Lucene, etc).

Wow the guts of that thing are a god-awful mess.

Context: https://www.webpronews.com/larry-ellison-amazon-oracle-datab...

In the CAP theorem context if you need CA you can't beat an IBM mainframe. IBM can also co-locate nodes of it's latest Power9/GPU supercomputer racks.