NTech IncSnowflake Partner

Home / Oracle DBA / Oracle to PostgreSQL

Database migration

Oracle to PostgreSQL migration services, planned by senior Oracle DBAs

For CTOs, IT directors and DBA leads moving Oracle databases to PostgreSQL, whether self-managed, Amazon RDS or Aurora, Google Cloud SQL or AlloyDB, or Azure Database for PostgreSQL. NTech delivers the assessment report, schema and PL/SQL-to-PL/pgSQL conversion, data migration with change data capture, validation evidence and a rehearsed cutover. Work is led by a senior Oracle DBA with 20+ years in production and run by engineers in India who overlap 4.5 hours with the US Eastern day.

Quick answer

What do Oracle to PostgreSQL migration services include?

Oracle to PostgreSQL migration services cover six phases: assessment with Ora2Pg or AWS Schema Conversion Tool, schema conversion, PL/SQL to PL/pgSQL code conversion, data movement with a bulk load plus change data capture (AWS DMS or Google Database Migration Service), validation with row counts, checksums and application tests, and a rehearsed cutover with a fallback. Packages, autonomous transactions, NULL handling and DATE semantics need the most manual work.

Last updated · NTech Inc

Led by a senior Oracle DBA with 20+ years in production. Every NTech Oracle engagement, including PostgreSQL migrations, has a senior Oracle DBA accountable for the plan, the conversion review and the cutover decision.

Who it's for

Who this service is for

We assess what will convert cleanly, what needs rewriting, and whether moving is worth it at all.

  • Companies with custom-built applications on Oracle Database that want to stop paying Oracle license and support fees
  • Teams moving to AWS, Google Cloud or Azure that want managed PostgreSQL
  • DBA leads who have run an Ora2Pg or AWS SCT report and need people to do the hard 20% the tools flag
  • Organizations facing an Oracle upgrade or hardware refresh

Tooling

Oracle to PostgreSQL migration tools compared

No single tool does the whole job. Most projects use one tool to convert schema and code, another to move data with change data capture, and people for everything the tools mark as manual.

ToolWhat it doesData movement and CDCBest fitWatch out for
Ora2PgFree, open-source (GPL) tool. Assessment report with an effort estimate; exports schema, PL/SQL and data.Bulk export and load (COPY or INSERT). No built-in change data capture.Any PostgreSQL target, on-premises or cloud.Configuration-file driven; converted code still needs review.
AWS SCT + AWS DMSAWS Schema Conversion Tool (desktop) or DMS Schema Conversion (console) converts schema and code; AWS DMS moves the data.Full load plus ongoing replication (CDC) from Oracle redo logs.Amazon RDS for PostgreSQL and Aurora PostgreSQL.DMS moves tables and rows; indexes, constraints and code come from the converted schema.
Google Database Migration ServiceConversion workspaces for schema and code, with Gemini-assisted fixes.Full dump, then CDC from Oracle log files until you promote the target.Cloud SQL for PostgreSQL and AlloyDB for PostgreSQL.Check Google's supported source versions; Oracle Autonomous Database is not supported.
Azure optionsAI-assisted schema conversion in Microsoft's PostgreSQL extension for VS Code; Microsoft also documents Ora2Pg.Data typically moves with Ora2Pg or a separate CDC tool.Azure Database for PostgreSQL flexible server.Microsoft notes a "Converted" status does not guarantee equivalent runtime behavior.
Manual and supporting toolsHand conversion, the orafce extension for Oracle-compatible functions, oracle_fdw for direct reads.Oracle GoldenGate or another CDC product where a cloud service is not used.Complex packages and business-critical logic.Slowest per object and depends on good test coverage.

We pick tools by target platform and by what the assessment shows. On cloud targets, Ora2Pg can be run alongside the provider's tool because the reports catch different issues.

What changes

PL/SQL to PL/pgSQL conversion issues that need people

Automated tools convert most procedural code. The problems sit in behavior that compiles on PostgreSQL but runs differently. We review these items by hand on every project.

Oracle featurePostgreSQL approachWhy it matters
Packages and package variablesA schema per package with functions and procedures; session state held in custom settings (set_config / current_setting) or temporary tablesNo packages in PostgreSQL; initialization blocks and state need redesign. Usually the highest-effort item.
Autonomous transactionsNo native equivalent. Loopback through the dblink or pg_background extensions, or redesign the logicCommon in audit and error logging that must survive a rollback.
CONNECT BY hierarchical queriesWITH RECURSIVE common table expressions; SEARCH and CYCLE clauses in PostgreSQL 14 and laterLEVEL, SYS_CONNECT_BY_PATH, ordering and cycle detection need rewriting.
Sequencesnextval('seq') instead of seq.NEXTVAL, or identity columnsStart values must be reset after the data load, and cache settings reviewed.
Data typesNUMBER to integer, bigint or numeric; VARCHAR2 to varchar or text; CLOB to text; BLOB and RAW to byteaMapping every unconstrained NUMBER to numeric is safe but slow for keys and joins.
NULL versus empty stringPostgreSQL treats '' as a real value, not NULLOracle treats an empty string as NULL. IS NULL checks, string concatenation with NULLs (use concat() or coalesce) and unique constraints behave differently.
DATE semanticsOracle DATE includes time to the second, so it maps to timestamp(0), not dateSYSDATE, date arithmetic such as date + 1, TRUNC and TO_DATE formats all need checking. now() returns the transaction start time.
Oracle-only syntaxANSI joins for (+), CASE for DECODE, coalesce for NVL, LIMIT or row_number() for ROWNUM; drop DUAL and optimizer hintsMostly automated, except SQL embedded in application code.
Transactions and errorsAn error aborts the PostgreSQL transaction until rollback; exception blocks use subtransactions; COMMIT works in procedures (PostgreSQL 11+), not functionsBatch code that commits in loops needs restructuring.
Identifiers, synonyms, DB links, temp tablesLower-case identifiers; search_path or views for synonyms; postgres_fdw or oracle_fdw for links; session temporary tablesUpper- versus lower-case folding breaks quoted names in application SQL.
RAC and Data GuardStreaming replication with Patroni, or the high availability of a managed cloud serviceAn architecture decision; agree recovery targets first.

Approach

A six-phase Oracle to PostgreSQL migration plan

  1. Assess

    Run Ora2Pg and, on AWS or Google Cloud, the provider's assessment. Inventory PL/SQL, application SQL, interfaces and jobs. Output: an object-by-object report, an effort range and a go or no-go recommendation.

  2. Convert the schema

    Convert tables, types, constraints, indexes, sequences, views and partitions, choosing type mappings deliberately. Output: versioned DDL that builds the target from scratch.

  3. Convert the code

    Automated PL/SQL to PL/pgSQL conversion, then senior review of every package, procedure and trigger, with manual rewrites where needed. Application SQL is fixed in the same phase. Output: compiled code with unit tests.

  4. Move the data

    Bulk load with Ora2Pg, AWS DMS or Google Database Migration Service, then change data capture keeps the target current. Output: a repeatable, rehearsed load.

  5. Validate

    Row counts and checksums per table, application regression tests and performance tests on production-like volumes. Output: a signed-off validation report.

  6. Cut over

    A full rehearsal, then a short final sync, application switch and a written fallback with a clear decision point. Output: a live system and an operations runbook.

Deliverables

What you get

Everything is handed over, so your team can run PostgreSQL after we leave.

Migration deliverables

  • Assessment report with object counts, conversion issues and an effort range
  • Target architecture: platform, version, high availability, backup and monitoring
  • Converted DDL and PL/pgSQL code in version control
  • List of application SQL changes, by file and statement
  • Data migration and CDC runbook, with a rehearsal planned before cutover
  • Validation report: row counts, checksums, test results and performance comparison
  • Cutover and fallback plan with named owners and decision points
  • Operations handover: backups, restore tests, failover and monitoring on PostgreSQL

Effort

What drives migration effort

Effort depends far more on code and features than on database size. These are the drivers the assessment measures.

DriverLower effortHigher effort
Amount of PL/SQLLogic mostly in the applicationLarge package libraries holding business rules
Oracle-specific featuresStandard tables, views and simple triggersAutonomous transactions, object types, Advanced Queuing, Oracle Spatial or Text
SQL in application codeAn ORM or a data access layerHand-written SQL spread across services and reports
Downtime windowA weekend outage is acceptableNear-zero downtime, which needs CDC and parallel running
IntegrationsOne application, few interfacesMany systems reading and writing through DB links or ETL
Test coverageAutomated regression tests existTesting is manual or undocumented
High availabilitySingle instance with backupsRAC and Data Guard designs to replace

Business case

How to frame the license savings

The main financial reason to move is Oracle license and annual support fees, plus paid options such as Partitioning or the Diagnostics and Tuning Packs. PostgreSQL has no license fee. We will not quote a saving before seeing your contracts and code.

Set recurring savings against one-time costs (conversion, testing, retraining, a parallel-run period) and costs that remain (hosting, any commercial PostgreSQL support, DBA time). Before reducing Oracle licenses, check how your contract treats partial termination of support, because it can reprice what remains. The assessment gives you the cost side; the decision is made on your numbers.

Inputs for your business case

  • Current Oracle license, support and option fees
  • Renewal dates and contract terms on reducing support
  • Migration effort range from the assessment
  • Target hosting cost for PostgreSQL
  • Parallel-run period and its cost
  • Training or hiring for PostgreSQL skills

Case study

The phased, parallel-run method in practice

Financial services

Core banking platform modernization

Duration
18 months
Team
12 senior engineers

This project moved a mainframe core banking system to a cloud-native architecture, not Oracle to PostgreSQL, but used the method we apply to database migrations: assess risk first, migrate in phases, and run old and new in parallel until results match.

Approach

  • Technical due diligence and risk assessment
  • Phased migration with parallel run
  • Compliance controls designed in from the start

Outcomes

  • Zero-downtime migration
  • Regulatory audit completed with no findings

Client names are withheld; full versions are available under NDA. See all case studies.

Honest advice

How to decide: migrate, upgrade or stay

An assessment can end with "don't move yet". These are the cases where we say so.

  • Packaged applications that are only certified on Oracle, such as E-Business Suite, should stay on Oracle
  • Heavy use of Oracle-only features with no clean PostgreSQL equivalent can make the rewrite cost more than the savings
  • When license savings are smaller than the cost of rewriting and retesting the code, an Oracle upgrade is usually the better step
  • When moving to the cloud solves the real problem, see Oracle cloud migration
  • If the real pain is slow queries rather than cost, start with Oracle performance tuning
  • For analytics and reporting workloads, Snowflake may fit better than PostgreSQL

Validation

How we prove the migrated database is right

A migration is finished when the business trusts the new database. We compare every table, run the application's own tests against PostgreSQL, and measure performance on production-like volumes, because plans fast on Oracle can be slow on PostgreSQL until indexes and queries are adjusted.

The team works 14:00 to 23:00 IST, overlapping 09:00 to 13:30 US Eastern, with a daily standup and a written end-of-day handoff. The assessment and the migration can each run as a fixed-scope project or with a dedicated team. See engagement models.

Validation checklist

  • Row counts and checksums per table
  • Spot checks on dates, numbers, empty strings and text encoding
  • Converted procedures tested with real inputs
  • Application regression tests passed
  • Performance tested under realistic load
  • Backup, restore and failover tested on the new system

Contact us

Assess your Oracle to PostgreSQL migration

Tell us roughly how much PL/SQL you have and why you want to move. We'll reply with how an assessment works.

  • A senior engineer reads every message
  • Reply within one business day
  • No obligation, and your details are used only to reply

Prefer the full form? Go to the contact page.

FAQ

Questions buyers ask us

How long does an Oracle to PostgreSQL migration take?

It depends mostly on the amount of PL/SQL, application SQL and Oracle-specific features, not on database size. An assessment with Ora2Pg or AWS SCT gives an object-by-object effort range before you commit.

What tools are used to migrate Oracle to PostgreSQL?

Ora2Pg, AWS SCT or DMS Schema Conversion, Google Database Migration Service and Microsoft's PostgreSQL extension for VS Code convert schema and code. AWS DMS, Google Database Migration Service, Ora2Pg or Oracle GoldenGate move the data.

Is Ora2Pg or AWS SCT better?

Neither is better in every case. Ora2Pg is open source and works for any PostgreSQL target. AWS SCT and DMS fit best when the target is Amazon RDS or Aurora and you want managed change data capture. On cloud targets we often run both, because their reports catch different issues.

Can PL/SQL packages be converted automatically?

Partly. Tools convert most procedural code, but packages with session state, initialization blocks, autonomous transactions or Oracle-only built-ins need manual redesign, because PostgreSQL has no package construct.

Can we migrate from Oracle to PostgreSQL with minimal downtime?

Yes, in most cases. Load the bulk of the data first, keep PostgreSQL current with change data capture, then cut over after a short final sync, with a rehearsed fallback plan.

Do we need to change application code?

Usually some. Embedded SQL may use ROWNUM, the (+) outer join, DECODE or NVL, or rely on empty strings being NULL. The assessment lists these changes by file and statement.

Is PostgreSQL as reliable as Oracle?

PostgreSQL runs many large production systems. High availability is built differently, with streaming replication and tools such as Patroni or a managed cloud service instead of RAC and Data Guard.

How much does Oracle to PostgreSQL migration save?

The main saving is Oracle license and support fees. Set that against the cost of converting and retesting code, parallel running and retraining; an assessment shows both before you decide.

Next step

Tell us what you need to build or who you need to hire.

A 30-minute call with a senior architect. You leave with a scoped plan or a role profile, whether or not you work with us.