How to Migrate Oracle to PostgreSQL: A Complete Technical Guide

Migrating from Oracle to PostgreSQL is one of the most technically demanding database transitions an engineering team can undertake. Oracle and PostgreSQL share relational foundations but diverge significantly in proprietary features, procedural language syntax, data type behavior, and licensing architecture.

This guide covers the full technical path: pre-migration assessment, data type mapping, handling Oracle-specific features that have no direct PostgreSQL equivalent, and three migration methods suited to different database sizes and downtime tolerances.

Should You Migrate from Oracle to PostgreSQL?

An Oracle-to-PostgreSQL migration makes the most sense when it solves a specific business or technical constraint, such as rising Oracle licensing costs, limited cloud portability, or the need to standardize on an open-source database ecosystem.

The migration is more than a database swap. Data may transfer relatively cleanly, but Oracle-specific application behavior, PL/SQL packages, operational processes, and third-party software compatibility can determine whether the project is practical.

Choose the PostgreSQL deployment target before selecting migration tooling. AWS, Azure, Google Cloud, and self-managed PostgreSQL offer different schema-conversion, networking, security, and migration-service options.

Which Migration Method Fits Your Situation

Choose your approach based on three factors: downtime tolerance, database size, and whether you need ongoing sync during the cutover period.

Pre-Migration Assessment: What to Inventory First

Oracle to PostgreSQL migrations can fail or exceed their planned timelines when teams underestimate hidden dependencies. Complete this assessment before writing a single line of DDL.

Database object inventory

Run this query in Oracle to count every object type you need to migrate:

sql
SELECT object_type, COUNT(*) AS object_count
FROM all_objects
WHERE owner = 'YOUR_SCHEMA'
GROUP BY object_type
ORDER BY object_count DESC;

Pay close attention to the counts for: TABLE, VIEW, PROCEDURE, FUNCTION, PACKAGE, PACKAGE BODY, TRIGGER, SEQUENCE, TYPE, and SYNONYM. Each object type has a different migration path and effort level.

Identify Oracle-specific blockers

These items commonly require manual review, code changes, or application redesign even when automated conversion tools are used:

• Packages: Oracle groups related procedures and functions into Packages. PostgreSQL has no Package concept. Each package must be decomposed into individual functions and procedures within a schema.
• Autonomous Transactions: Oracle allows a transaction to commit independently of its parent transaction using PRAGMA AUTONOMOUS_TRANSACTION. PostgreSQL has no native equivalent; the workaround requires dblink or the pg_background extension.
• Global Temporary Tables: In Oracle, the GTT definition is permanent, while its rows are transaction- or session-specific depending on the ON COMMIT setting. In PostgreSQL, both the temporary-table definition and its data are session-scoped by default. Recreate or redesign this behavior based on how the application creates, populates, and reuses temporary data.
• Hierarchical queries (CONNECT BY): Must be rewritten as recursive CTEs in PostgreSQL.
• Oracle-specific SQL functions:NVL, DECODE, SYSDATE, ROWNUM, DUAL table references, and TO_DATE with Oracle-specific format masks all require rewriting.
• Deferred constraints: Oracle and PostgreSQL handle these differently; audit all DEFERRABLE constraints carefully.

Cutover strategy decision

Decide before you start:

• Big bang: migrate the schema and data once, validate the target, and switch the application during a maintenance window. This is simpler operationally but creates a longer write freeze and a more concentrated rollback risk. Use it only after testing that the complete export, transfer, import, validation, and cutover process fits within the available downtime.
• Phased with CDC: keep Oracle and PostgreSQL synchronized during a validation period, then cut over by switching the application connection string. Required for production systems that cannot go offline.

Rollback plan

Define explicitly: what triggers a rollback, how long Oracle stays live as a fallback after cutover, and who owns the decision.

Oracle to PostgreSQL Data Type Mapping

This is the most common source of silent data corruption and failed imports. Map every column type before migrating.

The NUMBER performance trap in detail:

Oracle's NUMBER without precision or scale is used loosely across many schemas, sometimes for columns that only ever hold small integers. If you run a global NUMBER to NUMERIC mapping, every arithmetic operation on those columns forces PostgreSQL's arbitrary-precision engine, bypassing the faster native integer arithmetic. For tables with millions of rows and frequent aggregation queries, this is measurable. Run this before mapping:

sql
-- Oracle: check actual max values in NUMBER columns to determine correct Postgres type
SELECT column_name,
       MAX(LENGTH(TO_CHAR(ABS(column_value)))) AS max_digits,
       MAX(column_value) AS max_value,
       MIN(column_value) AS min_value,
       SUM(CASE WHEN column_value != TRUNC(column_value) THEN 1 ELSE 0 END) AS decimal_count
FROM your_table
UNPIVOT (column_value FOR column_name IN (col1, col2, col3))
GROUP BY column_name;

Handling Oracle-Specific Features

These Oracle features require manual rewriting; no automated tool reliably handles them.

Oracle feature compatibility matrix

Autonomous Transaction workaround

Oracle's Autonomous Transaction lets a procedure commit independently of its parent transaction. The most common use case is audit logging, where the log entry should persist even if the parent transaction rolls back.

Oracle syntax:

sql
CREATE OR REPLACE PROCEDURE log_action (msg VARCHAR2) AS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO audit_logs VALUES (msg, SYSDATE);
  COMMIT;
END;

PostgreSQL workaround using dblink:

sql
-- First install the extension
CREATE EXTENSION IF NOT EXISTS dblink;

CREATE OR REPLACE PROCEDURE log_action(msg TEXT) AS $$
BEGIN
  PERFORM dblink_connect('log_conn', 'dbname=target_db user=migration_user');
  PERFORM dblink_exec('log_conn',
    format('INSERT INTO audit_logs VALUES (%L, NOW())', msg));
  PERFORM dblink_disconnect('log_conn');
END;
$$ LANGUAGE plpgsql;

The dblink call opens a separate database connection, inserts and commits within that connection, then closes it. The insert commits independently of the calling transaction, replicating the Autonomous Transaction behavior.

Rewriting CONNECT BY hierarchical queries

Oracle's CONNECT BY syntax is commonly used for organizational hierarchies, bill-of-materials, and tree structures.

Oracle syntax:

sql
SELECT employee_id, manager_id, name, LEVEL
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

PostgreSQL equivalent using recursive CTE:

sql
WITH RECURSIVE org_hierarchy AS (
  -- Anchor: top-level rows
  SELECT employee_id, manager_id, name, 1 AS level
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive: join children to parents
  SELECT e.employee_id, e.manager_id, e.name, oh.level + 1
  FROM employees e
  INNER JOIN org_hierarchy oh ON e.manager_id = oh.employee_id
)
SELECT employee_id, manager_id, name, level
FROM org_hierarchy
ORDER BY level, employee_id;

Oracle to PostgreSQL Migration Tools Compared

No single tool handles the full Oracle to PostgreSQL migration end-to-end. Most teams combine two or three tools: one for schema conversion, one for data transfer, and optionally one for ongoing sync during validation. Here is how the major options compare.

Recommended combinations by scenario

• Small database, one-time move: Ora2Pg for schema and data export, PostgreSQL COPY for import.
• Medium database, AWS target: AWS SCT for schema conversion review, AWS DMS for data migration and initial CDC.
• Enterprise database, supported PostgreSQL target, minimal downtime: Use Ora2Pg to assess Oracle-specific objects and guide the destination design. Use Estuary to create connector-managed PostgreSQL tables and continuously synchronize data during validation. Confirm that the generated table structure, keys, data types, indexes, constraints, and delete behavior meet the application’s requirements before cutover.
• Phased application rewrite:oracle_fdw to bridge Oracle and PostgreSQL while services are rewritten incrementally, then Estuary CDC when ready for full cutover.

Method 1: Near-Zero Downtime Migration with Estuary CDC

Estuary reads Oracle's Redo Logs via LogMiner and streams every insert, update, and delete into PostgreSQL continuously. Oracle and PostgreSQL stay synchronized during the validation period, allowing the cutover to be reduced to a short application switchover rather than a long write freeze.

Note on "zero downtime": Even with CDC, production cutover involves application connection draining, final validation, rollback readiness, and sometimes a brief write pause depending on application architecture. CDC minimizes this window significantly but does not eliminate coordination overhead entirely.

When to use: live production databases, enterprise systems, large databases where extended downtime is not acceptable.

Prerequisites

• Oracle 11g or later
• An Estuary account at dashboard.estuary.dev
• PostgreSQL target database with a user that has CREATE TABLE privileges
• Network access from Estuary to Oracle (IP allowlist or SSH tunnel)

Step 1: Create the Estuary user in Oracle

Run these commands against your Oracle database. The exact SQL depends on whether you are using a standard database or a Container Database (CDB/PDB).

For standard (non-container) databases:

sql
-- Create the user
CREATE USER estuary_flow_user IDENTIFIED BY <strong_password>;
GRANT CREATE SESSION TO estuary_flow_user;

-- Grant read access to source tables
GRANT SELECT ANY TABLE TO estuary_flow_user;

-- Create the watermarks table (required by the connector)
CREATE TABLE estuary_flow_user.FLOW_WATERMARKS(
  SLOT VARCHAR(1000) PRIMARY KEY,
  WATERMARK VARCHAR(4000)
);

-- Grant LogMiner and metadata permissions
GRANT SELECT_CATALOG_ROLE TO estuary_flow_user;
GRANT EXECUTE_CATALOG_ROLE TO estuary_flow_user;
GRANT SELECT ON V$DATABASE TO estuary_flow_user;
GRANT SELECT ON V$LOG TO estuary_flow_user;
GRANT LOGMINING TO estuary_flow_user;
GRANT INSERT, UPDATE ON estuary_flow_user.FLOW_WATERMARKS TO estuary_flow_user;

-- Ensure quota on USERS tablespace
ALTER USER estuary_flow_user QUOTA UNLIMITED ON USERS;

-- Enable supplemental logging (required for LogMiner CDC)
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

For Amazon RDS Oracle instances, replace the supplemental logging command with:

sql
BEGIN
  rdsadmin.rdsadmin_util.alter_supplemental_logging(
    p_action => 'ADD',
    p_type   => 'ALL'
  );
END;

For Container Databases (CDB/PDB): the user must have the c## prefix and CONTAINER=ALL on all grants. Note that Amazon RDS Oracle does not support access to the root container and therefore does not work with CDB multi-tenant architecture for CDC.

sql
CREATE USER c##estuary_flow_user IDENTIFIED BY <strong_password> CONTAINER=ALL;
GRANT CREATE SESSION TO c##estuary_flow_user CONTAINER=ALL;
GRANT SELECT ANY TABLE TO c##estuary_flow_user CONTAINER=ALL;

CREATE TABLE c##estuary_flow_user.FLOW_WATERMARKS(
  SLOT VARCHAR(1000) PRIMARY KEY,
  WATERMARK VARCHAR(4000)
);
GRANT INSERT, UPDATE ON c##estuary_flow_user.FLOW_WATERMARKS
  TO c##estuary_flow_user CONTAINER=ALL;

GRANT SELECT_CATALOG_ROLE TO c##estuary_flow_user CONTAINER=ALL;
GRANT EXECUTE_CATALOG_ROLE TO c##estuary_flow_user CONTAINER=ALL;
GRANT LOGMINING TO c##estuary_flow_user CONTAINER=ALL;
GRANT ALTER SESSION TO c##estuary_flow_user CONTAINER=ALL;
GRANT SET CONTAINER TO c##estuary_flow_user CONTAINER=ALL;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

Common failure point: If the capture connects but CDC fails immediately after, common causes include: supplemental logging not enabled, missing watermarks table, or missing LOGMINING grant. Check all three before investigating further.

Step 2: Create the Oracle capture in Estuary

1. Log in to dashboard.estuary.dev
2. Navigate to Sources and click + New Capture
3. Search for Oracle and select the Oracle Database (Real-time) connector
4. Fill in the endpoint configuration:
   • Address:your-oracle-host:1521
   • Username:estuary_flow_user (or c##estuary_flow_user for CDB)
   • Password: the password set above
   • Database: your Oracle database name (defaults to ORCL; for CDB use the PDB name)
5. Click Next. Estuary tests the connection and discovers available schemas and tables.
6. Select the tables you want to replicate
7. Click Save and Publish. The initial backfill begins immediately.

Dictionary Mode note: The connector defaults to extract mode, which handles schema changes gracefully by extracting the dictionary from the redo logs. Online mode uses less disk but may break if schema changes occur during capture. For production migrations, keep the default extract mode.

Step 3: Create the PostgreSQL materialization

Important: Estuary’s PostgreSQL materialization connector creates and manages the destination tables. Tables created manually in advance are not supported by the standard connector workflow. If the migrated application requires exact table definitions, custom indexes, foreign keys, constraints, or naming conventions, treat those requirements as a separate design and validation workstream. Estuary synchronizes the data but does not reproduce the complete Oracle application schema automatically.

1. After the capture publishes, navigate to Destinations and click + New Materialization
2. Select the PostgreSQL connector
3. Fill in the endpoint configuration:
   • Address:your-postgres-host:5432
   • User: a PostgreSQL user with CREATE TABLE and INSERT privileges
   • Password
   • Database: target database name
   • Database Schema: target schema (default: public)
   • Hard Delete: Enable this when rows deleted in Oracle must also be physically deleted from PostgreSQL. When disabled, which is the default, Estuary retains the row and records its deletion status in _meta/op
4. Click Next. Estuary maps Oracle collections to PostgreSQL tables.
5. Click Save and Publish. Estuary performs the initial full load, then switches to streaming CDC.

From this point, Estuary continuously materializes captured Oracle changes into PostgreSQL. Actual end-to-end latency depends on the source workload, network conditions, backfill status, connector configuration, and destination performance. Verify update and delete behavior before treating the target as a cutover-ready replica. Run your validation queries while both databases are live, then cut over by updating the application connection string.

Testing the Oracle Connector with a Local Docker Instance

If you want to validate the Estuary Oracle connector setup before connecting to a production database, Oracle's free container image lets you spin up a local Oracle instance for testing.

For a full video walkthrough of this Docker setup, see the Estuary Oracle CDC tutorial on YouTube.

The key steps specific to a Docker-based Oracle setup that differ from production:

1. Enable the archive log in Docker compose

The archive log must be explicitly enabled in your compose environment. Add ORACLE_ENABLE_ARCHIVELOG=true (or equivalent for your chosen image) to the environment block before running docker compose up.

2. Retain archived redo logs long enough for CDC recovery

The connector must be able to read archived redo logs after an interruption. Keep the logs long enough to cover the longest expected connector outage or maintenance event. An RMAN backup retention policy alone does not guarantee that archived redo logs will remain available for a specific number of days. Availability also depends on the fast recovery area size, archive destinations, backup jobs, and the configured archived redo-log deletion policy. Review the current configuration before testing:

bash
rman target /

sql
SHOW ALL;

Also verify archive mode and monitor available recovery-area space:

sql
ARCHIVE LOG LIST;

SELECT name, space_limit, space_used
FROM v$recovery_file_dest;

For a local test instance, prevent automated cleanup from removing logs that the connector may still need. For production, work with the DBA to define an archived-log retention and deletion policy that balances CDC recovery requirements with available storage.

3. Oracle FREE database name for container databases

When using Oracle's free edition image (version 21c+), the default PDB name is FREE, not ORCL. In the Estuary connector configuration, set the Database field to FREE rather than the default ORCL.

4. Username format for container databases

As covered in the prerequisites above, CDB users require the c## prefix. In the Estuary dashboard, enter c##estuary_flow_user as the username, not estuary_flow_user.

Once the container is running with supplemental logging enabled, the watermarks table created, and RMAN retention configured, the Estuary capture setup steps are identical to a production Oracle instance.

Method 2: One-Time Migration Using CSV Export

For databases where the complete migration can fit within a tested maintenance window, CSV export from Oracle and import into PostgreSQL can provide a relatively simple one-time migration path. Data volume alone is not enough to determine suitability; network throughput, large objects, indexes, constraints, and validation time also affect the required downtime.

Step 1: Export from Oracle using SQLcl or Ora2Pg

For individual tables and smaller one-time migrations, Oracle SQLcl can produce properly formatted CSV output, including the quoting required for values containing delimiters.

sql
SET SQLFORMAT csv
SET FEEDBACK OFF
SET ECHO OFF

SPOOL /export/your_table.csv

SELECT *
FROM your_schema.your_table;

SPOOL OFF

Before starting the export, stop writes to the source or use a consistent snapshot supported by your chosen export tool. Exporting different tables at different times while writes continue can create referential inconsistencies in the target.

For larger schemas, complex data types, or repeatable exports, use Ora2Pg instead of maintaining separate SQLcl scripts for every table.

Oracle Data Pump (expdp and impdp) produces Oracle-specific dump files. It is useful for Oracle-to-Oracle transfers and backups, but impdp does not directly convert a dump file into PostgreSQL-ready CSV. A separate extraction or conversion step is still required.

Step 2: Create the target schema in PostgreSQL

Apply your DDL, with all Oracle-to-PostgreSQL type mappings applied (see the type mapping table above). Drop indexes before loading data and recreate them after.

Step 3: Import using PostgreSQL COPY

Run the following command from `psql`. The `\copy` command reads the file from the client machine, avoiding the server-side file-access permissions required by PostgreSQL `COPY FROM`.

sql
\copy your_table (col1, col2, col3) FROM '/export/your_table.csv' WITH (FORMAT csv, HEADER true, ENCODING 'UTF8', NULL '');

Test the import first with representative rows containing commas, quotation marks, line breaks, non-ASCII characters, NULL values, timestamps, and large text fields. These values are the most likely to expose formatting or encoding differences.

Step 4: Validate and reset sequences

sql
-- Row count check
SELECT COUNT(*) FROM your_table;

-- Reset sequence to current max ID after data load
SELECT setval(
  'your_table_id_seq',
  COALESCE((SELECT MAX(id) FROM your_table), 1),
  EXISTS (SELECT 1 FROM your_table)
);

Method 3: Phased Migration Using Foreign Data Wrappers

Foreign Data Wrappers let PostgreSQL query Oracle tables directly without copying data. This is not a migration tool but a bridge for teams rewriting their application one service at a time.

sql
-- Install the oracle_fdw extension
CREATE EXTENSION oracle_fdw;

-- Create the foreign server
CREATE SERVER oracle_server
  FOREIGN DATA WRAPPER oracle_fdw
  OPTIONS (dbserver '//oracle-host:1521/ORCL');

-- Create user mapping
CREATE USER MAPPING FOR current_user
  SERVER oracle_server
  OPTIONS (user 'read_only_user', password 'password');

-- Import a table from Oracle
IMPORT FOREIGN SCHEMA "ORACLE_SCHEMA"
  LIMIT TO (your_table)
  FROM SERVER oracle_server
  INTO pg_schema;

PostgreSQL can now query pg_schema.your_table and Oracle serves the data in real time. As you rewrite each service to use native PostgreSQL tables, drop the corresponding foreign table.

Post-Migration Validation

Never close the migration until data integrity is confirmed. Run these checks in PostgreSQL after every method.

Row count comparison

sql
-- Run this in Oracle
SELECT COUNT(*) FROM your_schema.your_table;

-- Run this in PostgreSQL
SELECT COUNT(*) FROM your_table;

MD5 checksum for data integrity

sql
-- PostgreSQL MD5 aggregate validation
-- Compare the output against an equivalent hash from Oracle
SELECT md5(string_agg(row_hash, '' ORDER BY id)) AS table_checksum
FROM (
  SELECT id,
         md5(concat_ws('|', id::text, col1::text, col2::text, col3::text)) AS row_hash
  FROM your_table
) subquery;

Generate hashes from the same columns in both databases and compare them in stable primary-key ranges. Normalize `NULL` values, timestamps, numeric formats, time zones, character encoding, and column order before hashing; otherwise representation differences can produce false mismatches.

A whole-table checksum only confirms whether the two outputs match. To identify the affected records, calculate and compare checksums for smaller primary-key ranges or partitions.

Sequence validation

sql
SELECT schemaname, sequencename, last_value
FROM pg_sequences
WHERE schemaname = 'public';

-- Compare last_value against the actual table maximum
SELECT MAX(id) FROM your_table;

Application query smoke tests

After cutover, run your 10 most critical application queries against PostgreSQL and compare execution plans and row counts against Oracle. Pay special attention to queries that used Oracle-specific functions, any query using ROWNUM, and any query over tables that had NUMBER columns remapped to INTEGER.

Common Oracle to PostgreSQL Migration Errors

Reference Documentation

The following official documentation sources were used in preparing this guide and are recommended reading for anyone implementing an Oracle-to-PostgreSQL migration.

Oracle:

• Oracle LogMiner documentation — official reference for LogMiner setup, supplemental logging, and redo log mining
• Oracle Data Pump (expdp/impdp) — for full schema and data exports
• Oracle supplemental logging — required for CDC-based capture

PostgreSQL:

• PostgreSQL COPY command — official reference for bulk data import
• PostgreSQL recursive CTEs (WITH RECURSIVE) — for rewriting Oracle CONNECT BY queries
• PostgreSQL dblink extension — for implementing Autonomous Transaction workarounds
• PostgreSQL Foreign Data Wrappers — for phased migration bridging

Estuary:

• Estuary Oracle CDC connector documentation — prerequisites, setup, and configuration reference
• Estuary PostgreSQL materialization connector — destination configuration reference

Conclusion

Oracle to PostgreSQL migration has three distinct technical challenges: data type mapping (especially NUMBER and DATE), PL/SQL object conversion (especially Packages and Autonomous Transactions), and choosing a cutover strategy that matches your downtime tolerance.

For production databases where extended downtime is not acceptable, Estuary’s LogMiner-based CDC can keep Oracle and PostgreSQL synchronized during the validation period, reducing the final cutover to a controlled application switchover. For smaller databases where the full process fits within a tested maintenance window, a one-time export and PostgreSQL `COPY` import may be the simpler approach.

CDC moves and synchronizes data; it does not convert Oracle Packages, stored procedures, application queries, or other PL/SQL logic. Plan schema conversion, application remediation, data transfer, validation, and cutover as separate but coordinated workstreams.

The data type mapping table, Oracle feature compatibility matrix, and troubleshooting table above cover the issues responsible for the majority of migration failures.

Start your free Estuary migration or explore related guides:

• Oracle CDC with Estuary
• Oracle to Snowflake
• PostgreSQL Change Data Capture