How to Migrate from On-Premise to Oracle in PostgreSQL

How do I migrate an on-premise PostgreSQL database to Oracle?

The migrate command copies schema and data from an on-premise PostgreSQL database to an Oracle instance with minimal downtime.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What does the migrate command do?

migrate extracts tables, converts PostgreSQL types to Oracle equivalents, and loads the transformed data into the target Oracle database. It supports full loads, incremental syncs, and schema-only moves.

Which prerequisites must be met?

Create network access between the source host and Oracle. Ensure the Oracle user has CREATE SESSION, CREATE TABLE, and INSERT privileges. Confirm the pg_dump and SQL*Loader binaries are installed on the jump box running migrate.

How do I run a full migration?

Invoke migrate with source and target connection strings, the list of tables, and optional flags for chunking or dry-run validation.

migrate \ --source pgsql://admin:pw@10.0.0.5:5432/ecommerce \ --target oracle://app:pw@adb.us-phoenix-1.oraclecloud.com:1521/ORCLPDB1 \ --tables Customers,Orders,Products,OrderItems \ --chunk-size 50000

How can I verify results?

After completion, compare row counts and sample records.

SELECT COUNT(*) FROM Customers@on_prem; SELECT COUNT(*) FROM Customers@oracle;

When should I use --schema-only or --data-only?

Use --schema-only for dry runs that validate DDL conversions without copying rows. Use --data-only for refreshing data when schema already exists on Oracle.

Best practices for large tables?

Enable chunking, disable indexes during load, and parallelize tables to shorten downtime. Always test in staging before production cut-over.

Why How to Migrate from On-Premise to Oracle in PostgreSQL is important

How to Migrate from On-Premise to Oracle in PostgreSQL Example Usage


--Check migrated totals
SELECT COUNT(*) AS on_prem_rows FROM Customers;      -- on-premise
SELECT COUNT(*) AS oracle_rows  FROM Customers@oracle; -- Oracle DB link

How to Migrate from On-Premise to Oracle in PostgreSQL Syntax


migrate \
  --source pgsql://<user>:<pwd>@<host>:<port>/<db> \
  --target oracle://<user>:<pwd>@<host>:<port>/<service> \
  --tables Customers,Orders,Products,OrderItems \
  [--schema-only]     # move DDL only
  [--data-only]       # move data only
  [--map-types jsonb=CLOB,uuid=RAW]  # custom type mapping
  [--chunk-size N]    # rows per batch (default 10000)
  [--parallel T]      # concurrent table loaders
  [--resume]          # continue from last checkpoint
  [--dry-run]         # validate without executing
  [--log-file path]   # write detailed log

Common Mistakes

Ignoring data-type mismatches: TEXT vs. CLOB, JSONB vs. BLOB. Fix by using --map-types to declare explicit conversions.
Running full loads during peak traffic: the lock on large tables causes downtime. Fix by scheduling migration windows and using --chunk-size with --parallel.