No single tool migrates a production Oracle database to PostgreSQL. Each one covers a part: assessment, schema and code conversion, bulk load, ongoing change capture, or validation. This guide sorts the common options by the job they do.

Assessment and conversion

  • ora2pg. Open source and the usual starting point. It exports schema, attempts PL/SQL conversion, exports data, and produces a migration assessment report that scores objects by difficulty. Run the report first. It gives a defensible first estimate before any commitment.
  • AWS Schema Conversion Tool. Converts schema and code with an itemized report of what it could not convert. Useful as a second opinion next to ora2pg, whatever the target hosting is.
  • Commercial converters. Products such as Ispirer SQLWays convert a larger share of PL/SQL automatically. The output still needs review and tests.

Treat converted PL/SQL as a draft. The differences in missing rows, exceptions and transactions are behavioral, and a converter translates syntax.

Compatibility layers

  • orafce. An open source extension that adds Oracle-style functions and some built-in packages to PostgreSQL. It reduces rewriting for common functions. It does not add packages, autonomous transactions or Oracle's empty-string rule.
  • Oracle-compatible PostgreSQL distributions. EDB Postgres Advanced Server accepts a large part of PL/SQL, including packages. It can shorten the first move. It is a commercial product with its own license, so the decision is about cost and long-term direction as much as effort.

Bulk data load

  • ora2pg data export. Reads Oracle and writes PostgreSQL COPY streams, in parallel. Good for rehearsals and for databases that can take a downtime window.
  • oracle_fdw. A foreign data wrapper that lets PostgreSQL query Oracle tables directly. A table can be copied with one INSERT ... SELECT, and the same connection serves validation.

Ongoing change capture

A migration with a short cutover window needs changes to flow from Oracle to PostgreSQL while both run.

  • AWS Database Migration Service. Full load plus change capture. Widely used when the target is on AWS. Watch large objects, tables without primary keys, and schema changes during the run.
  • Oracle GoldenGate. Oracle's own replication product, with PostgreSQL as a supported target. Mature and licensed separately.
  • Debezium. Open source change capture from Oracle redo through LogMiner into Kafka. Flexible, and it brings a streaming platform that someone has to operate.
  • Commercial replication products. Qlik Replicate and similar tools cover the same job with vendor support.

Choose from the failure modes the replication must survive: tables without keys, large objects, high redo volume, schema changes mid-migration, and a rebuild that must not load the primary.

Validation

  • oracle_fdw again. Reconciliation queries can compare both sides in one statement from PostgreSQL.
  • Your own reconciliation queries. Row counts, aggregates, NULL counts and sampled row comparisons, scheduled and stored. See the data type guide for what to compare.

What no tool covers

  • Who owns production while both platforms run.
  • Which threshold stops a cutover, and who makes that call.
  • A rollback rehearsed after the target has accepted writes.
  • Whether the Oracle license term covers the rollback window.
  • Monitoring, backup and recovery procedures the operations team has practiced on PostgreSQL.

These are the items on the migration checklist that decide whether the migration holds. Tool choice is a week of the plan. The rest is operations.

Where this leads

Datrick operates Oracle and PostgreSQL in production and delivers Oracle to PostgreSQL migration under a written scope, with named engineers accountable for assessment, conversion, validation, cutover and the operation that follows. The service page describes the delivery model: Oracle to PostgreSQL migration. For a first read of risk and effort, see the migration risk assessment.

Related: Oracle to PostgreSQL migration · migration risk assessment · Oracle DBA services · PostgreSQL DBA services · SQL Server migration tools