White paper

Re-platforming Oracle Databases to PostgreSQL

PostgreSQL is a powerful open-source database with a large ecosystem of managed cloud services. This paper explains how to assess, convert and migrate Oracle workloads to PostgreSQL while keeping performance and functionality.

Database9 min read Published August 9, 2023HNR Solutions

1Challenge

  • License and support costs that keep rising while budgets stay flat.
  • Vendor lock-in that limits cloud options and bargaining power.
  • Feature-gap uncertainty: teams aren't sure how packages, hierarchical queries, partitioning or Oracle-specific SQL will translate.
  • Skills: DBAs and developers need help moving from Oracle to PostgreSQL ways of working.

2Solution

  1. Assess & score

    Use ora2pg (or AWS SCT) reports to score migration effort per schema, and flag unsupported features and application SQL that needs changes.

  2. Convert schema & PL/SQL

    Convert to PostgreSQL DDL and PL/pgSQL. Packages become schemas with functions/procedures; CONNECT BY becomes recursive CTEs; NVL/DECODE become COALESCE/CASE; ROWNUM becomes LIMIT or window functions.

  3. Migrate & synchronize data

    Bulk-load the data, then use CDC to keep source and target in sync until cut-over.

  4. Validate & tune

    Compare data and results, review execution plans, tune indexes, autovacuum and memory settings.

  5. Operate

    Run on self-managed PostgreSQL with Patroni HA and pgBackRest, or on a managed service (Amazon RDS/Aurora, Azure Database for PostgreSQL, Google Cloud SQL / AlloyDB, OCI Database with PostgreSQL).

OraclePostgreSQL
NUMBER(p,s)NUMERIC(p,s), INTEGER, BIGINT
VARCHAR2(n)VARCHAR(n) / TEXT
DATETIMESTAMP(0)
CLOB / BLOBTEXT / BYTEA
CONNECT BYWITH RECURSIVE
Data GuardStreaming replication (Patroni)
RMANpgBackRest / Barman

3Results

No license fees

Open-source licensing removes per-core database license and support charges.

Cloud portability

Every major cloud offers managed PostgreSQL, so you can choose freely.

Rich extensions

PostGIS, pgvector, partitioning and more extend capability without extra licensing.

Skilled teams

Built-in knowledge transfer helps your team run PostgreSQL with confidence.

This white paper is general, educational guidance. Results depend on each organization's environment, contracts and scope. Product names are trademarks of their respective owners and are used for identification only.

Want to apply this to your environment?

We'll review your situation and outline a tailored plan, with no obligation.

Start a conversation