Oracle to PostgreSQL Data Migration Using Ora2Pg: Full Load

Introduction

In this blog, I’ll share my experience in performing the data migration of an entire Oracle database to Postgres using Ora2Pg. While the demonstration uses the Oracle HR schema for simplicity, the same approach and practices can be applied in a real-time project too.

Migrating data from Oracle to PostgreSQL can be complex, particularly when handling dependencies, constraints, and performance considerations. This blog outlines the configuration, execution, and validation strategies I used to complete the migration, along with key insights and challenges encountered during the process.

Prerequisites

Before starting the migration, ensure:

Ora2Pg is installed and configured properly
Oracle client libraries are installed (ORACLE_HOME set)
PostgreSQL database is up and reachable
Required Perl modules are installed

Pre-Migration Checklist (Important but often missed)

Before running Ora2Pg:

Identify large tables and estimate migration time
Check unsupported Oracle features:
- Packages
- Materialized views
- Synonyms
Review data types that require conversion (`NUMBER, DATE, CLOB`, etc.).
Check invalid objects in Oracle:

SELECT object_name, status FROM user_objects WHERE status=’INVALID’;

Ensure sufficient disk space on the target server

Ora2Pg Configuration (Full Load)


####################  Ora2Pg FULL SCHEMA Configuration   ####################
#------------------------------------------------------------------------------
# INPUT SECTION (Oracle connection)
#------------------------------------------------------------------------------
ORACLE_HOME     /opt/oracle/product/21c/dbhome_1
ORACLE_DSN      dbi:Oracle:host=13.12.197.9;service_name=ORCLPDB1;port=1521
ORACLE_USER     hr
ORACLE_PWD      oracle123

# FULL SCHEMA Migration Settings
SCHEMA          HR
COMPILE_SCHEMA  1
EXPORT_SCHEMA   1
TYPE            COPY

# AUTO-DEPENDENCY HANDLING (No manual table list needed!)
RUN_ORDER_BY_DEPS 1

# Foreign Key & Constraint Management
DROP_FKEY              1
RESTORE_FKEY           1

# Data Loading Optimization
TRUNCATE_TABLE         1
DISABLE_TRIGGERS       1
DISABLE_FKEY           1

# Performance Settings
PARALLEL_TABLES        0  # Sequential for dependencies
MAX_PARALLEL_WORKERS   1

# Include ALL Schema Objects
EXPORT_TABLESPACES     0
EXPORT_PARTITIONS      0
EXPORT_INDEXES         1
EXPORT_TRIGGERS        1
EXPORT_CONSTRAINTS     1
EXPORT_COMMENTS        1
EXPORT_GRANTS          1
EXPORT_REFRESH_MV      0

# Debug & Validation
DEBUG                  1

#------------------------------------------------------------------------------
# OUTPUT SECTION (PostgreSQL connection)
#------------------------------------------------------------------------------
PG_DSN           dbi:Pg:dbname=cdc_target;host=172.1.7.16;port=5432
PG_USER         postgres
PG_PWD           postgres
PG_VERSION      17
PG_SCHEMA       hr
#65-0-87-65
# Schema Management
DROP_SCHEMA_IF_EXISTS  0
DROP_IF_EXISTS         0
USER_GRANTS            1
NULL_EQUAL_EMPTY       1

# Data Type Mappings
DATA_TYPE       TIMESTAMP => TIMESTAMP
DATA_TYPE       DATE => TIMESTAMP
DATA_TYPE       NUMBER(38,0) => BIGINT
DATA_TYPE       CLOB => TEXT

Key Highlights:

TYPE COPY → Faster bulk loading using PostgreSQL COPY
RUN_ORDER_BY_DEPS → Automatically resolves table dependencies
DISABLE_TRIGGERS & DISABLE_FKEY → Improves load performance
PARALLEL_TABLES = 0 → Ensures dependency-safe sequential load

Schema Conversion Strategy

Ora2Pg automatically converts most schema objects, but some require attention:

Important Conversions:

Oracle	PostgreSQL
NUMBER	BIGINT / NUMERIC
DATE	TIMESTAMP
CLOB	TEXT
SEQUENCE	SERIAL / SEQUENCE

Execution Steps

Test Oracle Connection:

ubuntu@ip-172-31-7-166:~/Hr_schema_migration$ ora2pg -t SHOW_VERSION -c ora2pg_data.conf
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0

Load the database:

ubuntu@ip-172-31-7-166:~/Hr_schema_migration$ ora2pg -c ora2pg_data.conf
[2026-02-11 08:59:44] Ora2Pg version: 24.3
[2026-02-11 08:59:44] Export type: COPY
[2026-02-11 08:59:44] Geometry export type: INTERNAL
[2026-02-11 08:59:44] ORACLE_HOME = /opt/oracle/product/21c/dbhome_1
[2026-02-11 08:59:44] NLS_LANG = AMERICAN_AMERICA.AL32UTF8
[2026-02-11 08:59:44] NLS_NCHAR = AL32UTF8
[2026-02-11 08:59:44] Trying to connect to database:
…
NOTICE:  relation "locations_seq" does not exist, skipping
[2026-02-11 09:00:37] Total time to export data from 13 tables (0 partitions, 0 sub-partitions) and 216 total rows: 46 wallclock secs ( 0.13 usr +  0.01 sys =  0.14 CPU)
[2026-02-11 09:00:37] Speed average: 4.70 rows/sec
[2026-02-11 09:00:37] Fixing function calls in output files...

Performance Optimization Tips (Very Important Addition)

For large datasets:

Increase parallelism:

PARALLEL_TABLES 4
MAX_PARALLEL_WORKERS 4
Use COPY instead of INSERT
Disable indexes before load (optional for huge tables)
Run VACUUM ANALYZE after load:

postgres=#VACUUM ANALYZE;

Post-Migration Validation:

Modify the ora2pg.config file according to your requirements.

psql (18.1 (Ubuntu 18.1-1.pgdg24.04+2), server 17.7 (Ubuntu 17.7-3.pgdg24.04+1))
Type "help" for help.

postgres=# \l
                                                  List of databases
    Name    |  Owner   | Encoding | Locale Provider | Collate |  Ctype  | Locale | ICU Rules |   Access privileges
------------+----------+----------+-----------------+---------+---------+--------+-----------+-----------------------
 cdc_target | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |        |           |
 postgres   | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |        |           |
 template0  | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |        |           | =c/postgres          +
            |          |          |                 |         |         |        |           | postgres=CTc/postgres
 template1  | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |        |           | =c/postgres          +
            |          |          |                 |         |         |        |           | postgres=CTc/postgres
(4 rows)

postgres=# \c cdc_target
psql (18.1 (Ubuntu 18.1-1.pgdg24.04+2), server 17.7 (Ubuntu 17.7-3.pgdg24.04+1))
You are now connected to database "cdc_target" as user "postgres".
cdc_target=# \dn
      List of schemas
  Name  |       Owner
--------+-------------------
 hr     | postgres
 public | pg_database_owner
(2 rows)

cdc_target=# set search_path to hr;
cdc_target=# \dt
             List of tables
 Schema |    Name     | Type  |  Owner
--------+-------------+-------+----------
 hr     | countries   | table | postgres
 hr     | departments | table | postgres
 hr     | employees   | table | postgres
 hr     | job_history | table | postgres
 hr     | jobs        | table | postgres
 hr     | locations   | table | postgres
 hr     | regions     | table | postgres
(7 rows)

Row Count Validation:

cdc_target=# SELECT COUNT(1) FROM countries;
 count
-------
    25
(1 row)

cdc_target=# SELECT COUNT(1) FROM departments;
 count
-------
    27
(1 row)

cdc_target=# SELECT COUNT(1) FROM employees;
 count
-------
   107
(1 row)

cdc_target=# SELECT COUNT(1) FROM job_history;
 count
-------
    10
(1 row)
                             
cdc_target=# SELECT COUNT(1) FROM jobs;
 count
-------
    19
(1 row)

cdc_target=# SELECT COUNT(1) FROM locations;
 count
-------
    23
(1 row)

cdc_target=# SELECT COUNT(1) FROM regions;
 count
-------
   4     
(1 row)

Data Integrity Validation (Advanced but valuable addition)

For production migrations:

Use checksums:

SELECT md5(string_agg(t::text, '')) FROM table_name t;

Compare source vs target counts and aggregates:

SELECT COUNT(*), SUM(column_name) FROM table_name;

Lessons Learned (Very important section)

Always test with a small schema first
Dependency handling (RUN_ORDER_BY_DEPS) saves time
Sequence synchronization is often overlooked
Parallel execution improves performance but may break dependencies
Validation is as important as migration

Conclusion

Ora2Pg is a powerful tool for migrating from Oracle to PostgreSQL when configured properly. By combining dependency handling, performance tuning, and validation strategies, a full-load migration can be executed efficiently and reliably.

After completing the full-load migration, I implemented CDC using Debezium and Apache Kafka to handle real-time data synchronization.

Refer to my CDC blog for the complete setup:https://opensource-db.com/oracle-to-postgresql-migration-cdc-with-debezium-apache-kafka/
https://opensource-db.com/oracle-to-postgresql-migration-testing-and-validation-of-cdc-with-debezium-apache-kafka/