| The code is the work. Tables and rows convert almost for free; PL/SQL, Oracle-specific SQL in the applications, and a handful of behavioural differences are where the months go. |
| Data moves three ways: a bulk load with Ora2Pg or COPY, a managed replication service with change data capture, or query-based pulls through oracle_fdw for phased migrations. |
| Most post-migration defects trace to four Oracle behaviours: empty string as NULL, DATE carrying a time component, identifier case folding, and transaction-level error handling. |
| Validate with row counts, column checksums and query-result comparison before cutover. A row count alone will not catch any of the four behaviours above. |
Migrating from Oracle to PostgreSQL takes eight steps: assess the database, pick a target and a cutover pattern, convert the schema, convert the PL/SQL, fix the application SQL, move the data, validate, and cut over. Tables and data are the easy part, and most tools handle them well. Packages, Oracle-specific SQL and a few behaviours that PostgreSQL handles differently are where projects slip, usually at the point where someone runs the converted code against real data for the first time.
This is the engineering procedure. If you are still deciding whether to move at all, start with why companies are moving from Oracle to PostgreSQL, which covers the licence position, the cases where migrating is the wrong call, and what the business case actually has to clear.
Oracle to PostgreSQL migration, step by step
Step 1: Inventory and assess the Oracle database
Count everything before estimating anything. The inventory is tables, views, materialized views, packages, procedures, functions, triggers, sequences, types, synonyms, database links and scheduler jobs, and it is the only honest input to an effort estimate. PL/SQL volume, not data volume, sets the timeline.
Alongside the counts, flag the Oracle-only features in use: Real Application Clusters, Advanced Queuing, Spatial, Text, Virtual Private Database. Each one is a design decision rather than a conversion task, and finding them in month three is how a plan comes apart.
Three tools produce an assessment for you. Ora2Pg has a SHOW_REPORT export type that, with --estimate_cost, grades the database and puts a number on the conversion effort; its --cost_unit_value defaults to five minutes per unit and --human_days_limit defaults to five person-days, both tunable to your team’s actual pace. AWS Schema Conversion Tool produces an assessment report for AWS targets. Google Cloud Database Migration Service does the equivalent inside a conversion workspace.
Treat all three as a first pass. They measure what is mechanically convertible, which is not the same as what is correct after conversion.
Step 2: Choose the target and the cutover pattern
Two decisions, and the second one shapes the whole project. First the target: a managed service (Amazon RDS for PostgreSQL, Amazon Aurora PostgreSQL, Azure Database for PostgreSQL flexible server, Cloud SQL for PostgreSQL, AlloyDB for PostgreSQL) or self-managed PostgreSQL. Pick the major version up front, because it determines which features you can rely on during conversion rather than after it.
Then the cutover pattern. A one-time migration means exporting, loading, and pointing the application at the new database, with writes paused on Oracle for the duration. Google’s own guidance is explicit that a one-time migration without change data capture requires exactly that. For a database with a maintenance window and a few hundred gigabytes, this is simpler and entirely reasonable.
A phased migration with CDC keeps PostgreSQL continuously in step with Oracle, so the cutover becomes a short planned switch rather than an outage sized by your data volume. It costs more to set up and it is the only workable answer above a certain size or below a certain downtime tolerance. Where the application itself is being decomposed at the same time, this pairs with a strangler fig approach: move one bounded context at a time, with both databases live.
Step 3: Convert the schema and data types (how to convert Oracle to Postgres)
Schema conversion is largely automated and almost entirely safe, with the exception of a handful of type decisions that change application behaviour rather than just storage. Those are worth deciding deliberately instead of accepting a tool’s default.
| Oracle type | PostgreSQL type | Watch out for |
|---|---|---|
| NUMBER(p,0) | smallint / integer / bigint by precision | Do not default everything to numeric; integer arithmetic is materially faster |
| NUMBER(p,s) | numeric(p,s) | Direct equivalent |
| NUMBER (no precision) | numeric, or integer / double precision after profiling | Profile the actual values before choosing; the declaration tells you nothing |
| VARCHAR2(n) / NVARCHAR2(n) | varchar(n) or text | Oracle n may count bytes; PostgreSQL counts characters. Multi-byte data overflows |
| CHAR(n) | char(n) | Blank-padding and comparison semantics differ; prefer text unless padding is load-bearing |
| DATE | timestamp(0) | Oracle DATE includes time of day. Mapping to date silently truncates it |
| TIMESTAMP WITH TIME ZONE | timestamptz | PostgreSQL normalises to UTC and does not retain the original zone |
| CLOB / NCLOB / LONG | text | No practical size limit; the issue is transfer, not storage |
| BLOB / LONG RAW | bytea | Very large objects may need large-object storage and a separate load strategy |
| RAW(16) used as a GUID | uuid | Check how the application formats and compares them before converting |
| BINARY_FLOAT / BINARY_DOUBLE | real / double precision | Direct equivalent |
| XMLTYPE | xml | XPath function coverage differs; stored XML queries need testing |
One more decision has to be made before a single object is generated: identifier case. Oracle folds unquoted identifiers to upper case, PostgreSQL folds them to lower case, and a converter that quotes everything will hand you a database where "EMPLOYEE_ID" and employee_id are different columns forever. Pick a rule, usually unquoted lower case, and apply it to the schema and the application together.
Step 4: Convert PL/SQL to PL/pgSQL
PL/pgSQL was designed to look like PL/SQL, which is a help and a trap. Declarations, cursors, loops, %TYPE and %ROWTYPE, and exception blocks all carry across with small edits. RAISE_APPLICATION_ERROR becomes RAISE EXCEPTION. That similarity makes the first 70% feel fast and disguises how different the remainder is.
Packages have no equivalent. A package becomes a schema containing functions, which is a clean mapping until you reach package state: variables declared at package level that persist for the session. Those have to move to session configuration parameters, a temporary table, or an explicit state argument, and each option changes the calling code.
Autonomous transactions have no equivalent either. PRAGMA AUTONOMOUS_TRANSACTION commits independently of its caller, which PostgreSQL functions cannot do. The usual answers are dblink, opening a second connection back to the same database, or the pg_background extension. Both work; both mean the logging or auditing code that relied on the pragma now has a new failure mode to handle.
Procedures that commit internally are fine, in procedures. PostgreSQL 11 added procedures that can contain COMMIT and ROLLBACK. Functions still cannot. If converted code commits mid-body, it has to become a procedure, and every caller has to use CALL rather than SELECT.
orafce covers more than you expect. The extension supplies PostgreSQL implementations of a wide range of Oracle built-in functions and some DBMS_ package behaviour. Installing it early removes a large amount of mechanical rewriting and makes the remaining differences easier to see.
Tools convert a share of this automatically and the rest is engineering. Ora2Pg’s own documentation is direct about it: it “does its best to automatically convert your Oracle database to PostgreSQL but there’s still manual work to do.” Treat any vendor percentage, including ours, as a claim about a different codebase than yours until a sample conversion has been run against your actual packages.
Step 5: Rewrite Oracle-specific SQL in the application
The database is only half the migration. Oracle dialect in application code is the half that gets missed, because it is spread across every repository rather than concentrated in one schema.
| Oracle | PostgreSQL |
|---|---|
| NVL(a, b) | COALESCE(a, b) |
| DECODE(x, 1, ‘a’, ‘b’) | CASE WHEN x = 1 THEN ‘a’ ELSE ‘b’ END |
| SELECT … FROM DUAL | SELECT … (no FROM clause needed) |
| seq.NEXTVAL | nextval(‘seq’) |
| ROWNUM <= 10 | LIMIT 10. Oracle applies ROWNUM before ORDER BY, so the rows differ |
| a.id = b.id(+) | LEFT JOIN |
| MINUS | EXCEPT |
| SYSDATE | LOCALTIMESTAMP(0), or clock_timestamp() for real wall-clock time inside a long transaction |
The ROWNUM row deserves more than a line. In Oracle, ROWNUM is assigned as rows are produced, before ORDER BY runs, so WHERE ROWNUM <= 10 ORDER BY created_at DESC returns ten arbitrary rows sorted, not the ten most recent. A conversion to ORDER BY created_at DESC LIMIT 10 is almost always what the author intended, and it is still a behaviour change. Decide which of the two you want, deliberately, rather than inheriting it from whichever tool did the conversion.
Hierarchical queries need a real rewrite rather than a substitution:
-- Oracle
SELECT employee_id, manager_id, LEVEL
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;
-- PostgreSQL
WITH RECURSIVE org AS (
SELECT employee_id, manager_id, 1 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.employee_id, e.manager_id, o.level + 1
FROM employees e
JOIN org o ON e.manager_id = o.employee_id
)
SELECT employee_id, manager_id, level FROM org;
The recursive CTE does not guarantee the same row order as CONNECT BY. Add an explicit ORDER BY if anything downstream depends on it, which for tree-shaped output it usually does.
Finally, the plumbing: OJDBC becomes the PostgreSQL JDBC driver, ODP.NET becomes Npgsql, and connection strings change everywhere including in configuration you do not control. An ORM absorbs part of this through a dialect switch, and absorbs none of the hand-written SQL sitting behind it in native queries and stored-procedure calls.
Step 6: Migrate data from Oracle to PostgreSQL
There are three practical ways to move data from Oracle to Postgres: a bulk export and load, a managed replication service with change data capture, or querying Oracle directly from PostgreSQL. Which one you use is decided by the cutover pattern from Step 2, not by preference.
Bulk export and load. Ora2Pg can emit COPY output directly, or you extract to CSV and load with PostgreSQL’s COPY. Fast, free, and requires the source to stop accepting writes once the extract begins.
Managed replication with CDC. AWS Database Migration Service and Google Cloud Database Migration Service both do a full load and then stream ongoing changes. This is what makes a short cutover window possible. The limitation to plan around on AWS is that DMS handles data and not schema objects, covered below.
Query-based through oracle_fdw. A PostgreSQL foreign data wrapper that makes Oracle tables queryable from PostgreSQL. It suits phased moves and selective pulls, where you want to transfer data from Oracle to PostgreSQL one domain at a time while both systems stay in use.
Whichever you pick, the loading sequence matters more than the tool:
- Load table data first, then build secondary indexes and enable foreign keys and triggers. Every index is maintained on every inserted row, so creating them up front can multiply load time.
- Reset sequences after the load. Set each one above the maximum value of the column it feeds. A migration that completes and then throws duplicate-key errors on the first insert has almost always skipped this.
- Treat LOB columns as their own project. They dominate transfer time and memory use for most migration tools, and they are worth moving in a separate pass with their own batch sizing.
- Parallelise by table. Split the largest tables across streams on a key with even distribution, and keep small tables in a single task rather than paying coordination cost on them.
Step 7: Test and validate
Validation has four layers, and skipping any of them moves a defect from your test window to your production window.
Row counts per table. The cheapest check, and the one that catches the least. It confirms the load finished. It confirms nothing about correctness.
Column-level checksums. Aggregate each column on both sides and compare. This catches truncation, type-conversion drift and encoding problems that row counts cannot see, including the DATE truncation from Step 3.
Query-result comparison. Run the reports and the high-value queries against both databases and diff the output. This is the layer that catches the behavioural differences: empty strings, ROWNUM ordering, SYSDATE semantics. It is also the layer teams skip when the schedule tightens, which is why those defects reach production.
Performance baseline. Capture EXPLAIN (ANALYZE, BUFFERS) for the queries that matter and compare against Oracle. Run ANALYZE first; a freshly loaded PostgreSQL database with no statistics produces plans that will mislead you about where the real problems are. Put PgBouncer in front before any load test, because PostgreSQL runs a process per connection and an application pool sized for Oracle will saturate it.
Write PL/pgSQL unit tests for converted business logic as you go, rather than at the end. On most legacy Oracle estates there is no existing test suite, which means the tests you write during conversion are the only specification of what the old system did.
Step 8: Cut over and keep a rollback path
The cutover itself is short and mostly administrative: freeze schema changes on Oracle, let CDC drain the remaining changes, stop application writes, confirm the replication lag is zero, switch connection strings, start the application, and run a scripted smoke test.
The part that needs deciding in advance is the rollback. AWS documents a rollback for this path, and it is worth reading carefully because of what it assumes: the steps are to stop application services on PostgreSQL, let a DMS task replicate the changes back to Oracle, stop that task, and restart the applications against Oracle. That reverse task has to be built and running before cutover. A rollback plan that depends on infrastructure nobody created is a paragraph, not a plan.
Keep Oracle running and read-only for a defined period after cutover, long enough to cover a full business cycle including month-end. Decommission on a date, not on a feeling.
Which tool should you use to migrate Oracle to PostgreSQL?
No single tool does the whole job. Conversion and data movement are separate problems, and the tools that are good at one are generally weak at the other.
| Tool | Schema | PL/SQL | Data | CDC | Best fit |
|---|---|---|---|---|---|
| Ora2Pg | Yes | Partly | Yes (COPY / INSERT) | No | Any target; free and open source |
| AWS SCT + AWS DMS | Yes (SCT) | Partly (SCT) | Yes (DMS) | Yes | Amazon RDS and Aurora PostgreSQL |
| Google Database Migration Service | Yes (conversion workspace) | Partly; Gemini-assisted for AlloyDB | Yes | Yes | Cloud SQL and AlloyDB for PostgreSQL |
| PostgreSQL extension for VS Code | Yes | Partly; model-assisted | No | No | Azure Database for PostgreSQL flexible server |
| oracle_fdw | No | No | Yes (query-based) | No | Phased moves and selective pulls |
| Commercial tools (EDB, Ispirer, Cybertec Migrator) | Yes | Varies | Varies | Varies | Large estates; evaluate case by case |
Migrating from Oracle to PostgreSQL with Ora2Pg
Ora2Pg connects to Oracle, reads the data dictionary and emits PostgreSQL DDL, converted code and data. It covers a wide object range: tables with constraints, views, materialized views, tablespaces, sequences, indexes, triggers, grants, functions, procedures, packages, partitions, types, synonyms, foreign data wrappers and query conversion.
Start with --init_project, which scaffolds a working tree rather than leaving you to invent one. Ora2Pg’s documentation describes the two options --project_base and --init_project as telling it “to create a project template with a work tree, a configuration file and a script to export all objects from the Oracle database.” You get a sources/ tree holding the original Oracle code, a schema/ tree holding the PostgreSQL port, plus data/, config/ and reports/, along with generated export_schema.sh and import_all.sh scripts. Having the before and after side by side in version control is worth more than it sounds during review.
Run the assessment before anything else. SHOW_REPORT with --estimate_cost produces a migration cost and difficulty grading, output as HTML, CSV or JSON. The two knobs that matter are --cost_unit_value, which defaults to five minutes per unit, and --human_days_limit, which defaults to five person-days. Both are calibration against your team, so set them from a sample conversion rather than leaving the defaults and quoting the result.
What Ora2Pg does not do is change data capture. For a migration that cannot pause writes, pair it with a replication service.
Using AWS DMS for Oracle to Postgres
On AWS the division is clean: AWS Schema Conversion Tool converts the schema and code, AWS Database Migration Service moves the data and keeps it moving.
The limitation to design around is stated plainly in AWS’s own walkthrough. DMS “doesn’t migrate your secondary indexes, sequences, default values, stored procedures, triggers, synonyms, views, and other schema objects not specifically related to data migration.” AWS SCT is what handles those. This is not a bug and it is not hidden, but it is routinely missed in planning, and the symptom is a test load that technically succeeds while the application fails on the first query.
DMS does a full load and then streams ongoing changes, which is what makes a short cutover window achievable. Build the reverse task during the cutover preparation, not after, because as covered above it is the thing the documented rollback depends on.
Our own database migration accelerator sits on top of these rather than replacing them: automated conversion, then engineer review of every converted object before it reaches a target schema.
Oracle to Postgres migration challenges (and how teams handle them)
Eight differences account for most of the defects we see in converted systems, ordered by how often they bite.
Empty strings become NULL in Oracle, but not in PostgreSQL
In Oracle, '' is NULL. In PostgreSQL it is a zero-length string. Any code that assigned an empty string to mean “no value” changes meaning on migration: IS NULL tests that used to pass now fail, and = '' comparisons that used to fail now pass. It is invisible to row counts and it surfaces as wrong results rather than errors. Find it by searching for '' assignments and NULL comparisons on the columns involved, and decide per column whether to normalise the data on load or change the predicate.
Oracle DATE carries a time component
An Oracle DATE stores date and time to the second. Map it to PostgreSQL date and the time is gone. Reports grouped by day keep working, which is what makes this dangerous, while duration calculations and same-day ordering start disagreeing with the old system. Map to timestamp(0) and review every TRUNC(date_col) for its PostgreSQL equivalent.
Identifier case folding
Oracle stores unquoted identifiers in upper case, PostgreSQL in lower case. A converter that quotes identifiers preserves Oracle’s upper case permanently, and from then on every query must quote them too. Set the rule before generating the schema, apply it to the application in the same change, and avoid mixed quoting at all costs.
An error aborts the whole PostgreSQL transaction
In Oracle, a failed statement inside a transaction leaves the transaction usable. In PostgreSQL the transaction enters an aborted state and rejects everything until rollback. Code that catches an exception and carries on, a common pattern in batch processing, stops working. The fix is savepoints around the statements expected to fail, or restructuring the loop so each iteration is its own transaction.
Packages and package state
Covered in Step 4, and it is the single largest line item in most conversions. A package of twenty procedures is twenty functions plus a decision about where the package variables live. Make that decision once, as a pattern, rather than per package.
Optimizer hints do not exist
PostgreSQL has no native hint mechanism. Queries carrying Oracle hints need rewriting, better statistics, or the pg_hint_plan extension. Before reaching for any of those, run ANALYZE: a large share of “PostgreSQL is slower” findings on a freshly migrated database are missing statistics rather than a planner difference.
now() returns transaction start time, not wall-clock time
now() and CURRENT_TIMESTAMP in PostgreSQL return the time the current transaction began, and stay constant for its duration. Oracle’s SYSDATE advances. In a long-running batch, every row gets the same timestamp where the old system stamped each one differently. Use clock_timestamp() where real elapsed time is wanted.
A process per connection
PostgreSQL forks a backend process per connection, which makes connection count a resource decision rather than an afterthought. An application pool sized against Oracle’s shared-server model will exhaust PostgreSQL well before the hardware is busy. Put PgBouncer in front and size the pool deliberately. This belongs in load testing, not in the first week of production, and it is one of the recurring reasons cloud migrations fail to deliver the performance that was modelled.
Not sure how much of your PL/SQL will convert automatically?
Send us the object counts and we will run an assessment against your actual packages: an object inventory, a conversion-complexity grading, and the Oracle-only features in use. Estimates built on your code rather than on an average.
How long does an Oracle to PostgreSQL migration take?
There is no credible range without an object inventory, because the same five drivers vary by an order of magnitude between estates:
- PL/SQL volume and shape. Ten thousand lines in standalone procedures is a different project from ten thousand lines in packages with state.
- Number of applications touching the schema. Each one carries its own Oracle-specific SQL, its own driver, and its own release cycle to coordinate with.
- Existing test coverage. With none, the test suite is built before the migration can be verified, and that work is often larger than the conversion.
- Downtime tolerance. A maintenance window permits a one-time cutover. A zero-downtime requirement adds CDC, reverse replication and rehearsal.
- Data volume and LOB share. This sets the cutover mechanics rather than the project length, but it decides whether a one-time load is viable at all.
The assessment in Step 1 is what turns these into a number. Anyone quoting a timeline before seeing object counts is quoting a different project.
Oracle to PostgreSQL migration checklist
- Inventory every object type, including synonyms, DB links and scheduler jobs
- Flag Oracle-only features: RAC, Advanced Queuing, Spatial, Text, VPD
- Run an assessment report and calibrate its cost units against a sample conversion
- Choose the target platform and PostgreSQL major version
- Choose the cutover pattern: one-time, or phased with CDC
- Decide the identifier case rule before generating any schema
- Review every NUMBER, DATE and VARCHAR2 mapping against actual data
- Install orafce early and re-measure what still needs rewriting
- Decide the package-state pattern once, then apply it to every package
- Inventory Oracle-specific SQL in every application, as well as in the database
- Load data first, build secondary indexes and enable constraints after
- Reset every sequence above its column’s maximum value
- Plan LOB columns as a separate load pass
- Run ANALYZE, then capture a performance baseline against Oracle
- Put PgBouncer in front before any load test
- Validate with row counts, column checksums and query-result comparison
- Build and test the reverse replication task before cutover
- Keep Oracle read-only through at least one full month-end
Planning an Oracle to PostgreSQL migration?
Tell us the Oracle version, roughly how large the database is and where you want to land. You will talk to a database architect, not an account manager.