Home / Oracle DBA / Oracle to PostgreSQL
Database migration
Oracle to PostgreSQL migration, planned by people who know Oracle inside out
Most of the risk in an Oracle migration sits in PL/SQL, data types and Oracle-specific features. We assess what will convert cleanly, what needs rewriting, and whether moving is worth it at all.
Quick answer
How do you migrate from Oracle to PostgreSQL?
An Oracle to PostgreSQL migration has five steps: assess the schema and code with tools such as ora2pg or AWS Schema Conversion Tool, convert schema and PL/SQL to PL/pgSQL, move data with a bulk load plus change data capture, test with row counts, checksums and application tests, then cut over with a fallback plan. Packages, Oracle-only features and RAC designs need the most rework.
What changes
What needs converting from Oracle to PostgreSQL
| Oracle | PostgreSQL | Effort |
|---|---|---|
| PL/SQL procedures and functions | PL/pgSQL | Medium: mostly automated, then reviewed |
| Packages and package variables | Schemas plus functions; session state redesigned | High |
| NUMBER, VARCHAR2, DATE | numeric / integer, varchar / text, timestamp | Low, but check precision and time parts |
| Sequences, triggers | Sequences or identity columns, triggers | Low |
| CONNECT BY hierarchical queries | Recursive CTEs | Medium |
| Optimizer hints, ROWNUM, DUAL | Removed or rewritten | Low to medium |
| RAC, Data Guard | Streaming replication with Patroni or a managed service | Architecture decision |
Approach
How the migration runs
Assess
Run ora2pg or AWS Schema Conversion Tool reports to size the work object by object, and flag features with no direct equivalent.
Convert schema and code
Automated conversion, then senior review of every package, procedure and trigger.
Move data
Bulk load followed by change data capture, using AWS DMS, ora2pg or GoldenGate, so the target stays current.
Test
Row counts and checksums per table, application regression tests and performance tests under realistic load.
Cut over
A short final sync, application switch and a planned way back if checks fail.
Honest advice
When not to leave Oracle
- Packaged applications that are only certified on Oracle, such as E-Business Suite
- Heavy use of Oracle-only features with no clean PostgreSQL equivalent
- When license savings are smaller than the cost of rewriting and retesting the code
- For analytics workloads, Snowflake may fit better than PostgreSQL
FAQ
Questions buyers ask us
How long does an Oracle to PostgreSQL migration take?
It depends mostly on the amount of PL/SQL and how much the application relies on Oracle-specific features. An assessment with ora2pg or AWS SCT gives an object-by-object estimate before you commit.
What tools are used to migrate Oracle to PostgreSQL?
Common tools are ora2pg and AWS Schema Conversion Tool for schema and code, and AWS DMS, ora2pg or GoldenGate for data movement and change data capture.
Can PL/SQL packages be converted automatically?
Partly. Tools convert most procedural code, but packages with session state, autonomous transactions or Oracle-only built-ins need manual redesign.
Is PostgreSQL as reliable as Oracle?
PostgreSQL runs many large production systems. High availability is built differently, with streaming replication and tools such as Patroni or a managed cloud service instead of RAC.
Next step
Tell us what you need to build or who you need to hire.
A 30-minute call with a senior architect. You leave with a scoped plan or a role profile, whether or not you work with us.