Migrating From Oracle to PostgreSQL: A Practical Plan
Key takeaway
Moving from Oracle to PostgreSQL usually pays for itself through licensing savings, but the cost lives in stored procedures and subtle behaviour differences, not the data copy. Inventory your PL/SQL first, test the known traps explicitly, and cut over with change-data replication so rollback stays possible.
Oracle licensing and support renewals are one of the most common triggers for a modernisation project. PostgreSQL is a mature, open-source alternative that runs comfortably on every major cloud as a managed service. Copying tables across is the easy part; the effort and risk sit in the code that lives inside the database and in the places where the two engines behave slightly differently.
Assess before you estimate
- Count and classify PL/SQL objects — packages, procedures, triggers, and functions — and how much business logic they hold.
- Identify Oracle-specific features in use: packages with state, autonomous transactions, database links, scheduler jobs, advanced queuing.
- List every application, report, and integration that connects to the database, including the ones nobody remembers.
- Run an automated assessment tool (ora2pg or AWS Schema Conversion Tool, for example) to size the conversion — then treat its estimate as a floor.
The traps that break applications
- Empty strings — Oracle treats '' as NULL; PostgreSQL does not, so comparisons and NOT NULL constraints behave differently.
- Dates — an Oracle DATE includes a time component; mapping it to a PostgreSQL date silently drops the time.
- Numbers — NUMBER without precision needs a deliberate mapping to integer, bigint, or numeric.
- Identifier case — Oracle folds unquoted names to upper case, PostgreSQL to lower case, which breaks code that quotes names.
- Proprietary syntax — (+) outer joins, CONNECT BY hierarchies, ROWNUM, and DECODE all need rewriting to standard SQL.
- Transaction behaviour — in PostgreSQL an error aborts the whole transaction until rollback, which changes error-handling code.
Convert, then prove equivalence
Convert the schema and code in stages, and build a comparison harness that runs the same queries and procedures against both databases with the same data and diffs the results. That harness is the most valuable artefact of the project: it turns "we think it works" into a measurable pass rate, and it catches the behaviour differences above before users do. Performance-test the heaviest queries separately, since the two optimisers make different choices.
Cut over with a way back
Use change-data-capture replication to keep PostgreSQL in sync with Oracle while applications are tested against it, then switch traffic during a short window. Keeping replication running in reverse for a period afterwards means rollback is a configuration change rather than a restore. Budget for a few weeks of post-cutover tuning, and don't cancel the Oracle licence until the reconciliation reports have been clean for a full business cycle — month-end included.