< Back to blog home Fujitsu PostgreSQL blog

Oracle to PostgreSQL migration: An enterprise playbook

Fujitsu | September 24, 2026

Choosing between Oracle and PostgreSQL is about understanding where each platform fits, and whether the cost, complexity, and vendor dependency of your database still make sense for your workload

What an Oracle to PostgreSQL migration involves

An Oracle to PostgreSQL migration is a heterogeneous schema migration that covers schema conversion, PL/SQL to PL/pgSQL conversion, data movement, application remediation, and cutover. Data transfer is often the quickest stage. Planning, conversion, testing, and validation usually account for most of the project timeline, especially in enterprise environments.

The biggest effort drivers are Oracle-specific data types, PL/SQL volume, proprietary features such as partitioning syntax, CONNECT BY, packages, and how tightly the application layer is coupled to Oracle-specific behaviour. Teams moving from Oracle to PostgreSQL often discover that application coupling, rather than the database engine itself, is what extends the project and makes accurate planning more difficult.

Choosing an Oracle to PostgreSQL migration strategy

Before planning the migration itself, decide what kind of migration you're trying to deliver. The strategy determines how much changes, how much moves at once, how much downtime the business accepts, and how risk is managed throughout the project.

There's no universally correct choice among these. The right strategy depends on application dependencies, acceptable downtime, business priorities, and how quickly leadership expects to reduce Oracle licensing costs.

Once the migration strategy is clear, the next step is understanding what needs to be assessed before any code or data moves.

Phase 1: Assessment and migration planning

A successful Oracle to PostgreSQL migration starts with a thorough assessment. Before any schema conversion, code translation, or data movement begins, the team needs a clear picture of the existing environment, the complexity of the Oracle schema, and the risks that could affect the project. This is where uncertainty gets replaced with evidence. A credible assessment produces outputs the team can act on, rather than a generic checklist copied from the last project.

A credible assessment should include:

• Dependency discovery
  Identifying every application, integration, and reporting tool that touches the database.
• Application inventory
  Documenting the Oracle schema, key database objects, and how applications depend on them.
• SQL compatibility analysis
  Identifying which queries, SQL scripts, PL/SQL code, and Oracle-specific features translate cleanly and which require remediation.
• Performance baseline capture
  Documenting current throughput and query performance for comparison after migration.
• Operational readiness
  Assessing whether the team is staffed and trained to run PostgreSQL in production.
• Rollback planning
  Defining a documented path back if cutover does not go as planned.
• Migration prioritization
  Creating a workload-by-workload sequencing plan that determines which databases move first.

Assessment is worth getting right. An analysis by Cintra of 21,772 Oracle databases found that 54.12% were classified as Low or Medium migration complexity for Oracle-to-PostgreSQL migration. That highlights why understanding application dependencies, procedural code, and overall migration complexity early has such a significant impact on project planning.

For organizations without in-house experience of heterogeneous database migrations, structured assessment services can help identify technical risks, dependencies, and migration sequencing before delivery begins.

Phase 2: Schema and code conversion

This phase covers data type mapping (Oracle's NUMBER, VARCHAR2, DATE, TIMESTAMP, and RAW types to their PostgreSQL equivalents), identifier case-folding behavior, and PL/SQL to PL/pgSQL conversion, which is the dominant effort driver in most Oracle migrations.

Established tooling, such as Ora2Pg and equivalent conversion tools, handles much of this work automatically. However, AWS notes in its Migrating Oracle Database Workloads guidance that database code conversion is typically the most complex part of a heterogeneous migration, making manual review and refinement an important part of the project. Oracle-specific features that have no direct PostgreSQL equivalent still require manual remediation, and that remaining work is often more substantial than the automated pass suggests.

Data type and schema mapping

Oracle and PostgreSQL differ in more than data type names, and the differences that cause production issues are rarely the ones teams expect. Case-folding behavior for unquoted identifiers differs between the two platforms, and getting this wrong silently breaks queries that assume Oracle's default behavior. No error is raised. Instead, the query returns incorrect results, making these issues difficult to detect during migration testing.

Schema mapping should also account for sequences, triggers, synonyms, constraints, foreign keys, stored procedures, and supporting database objects, which don't always map one-to-one and may need to be restructured rather than directly translated.

Converting PL/SQL to PL/pgSQL

PL/SQL to PL/pgSQL conversion often handles standard procedural logic well, and that's usually where confidence in the timeline comes from. Materialized views, scheduler jobs, partitioning syntax, and optimizer hints are common sticking points, since PostgreSQL implements each of these differently and a literal translation rarely produces working, performant code on the first pass.

Handling Oracle-only features

Some Oracle constructs have no direct PostgreSQL equivalent and require redesign rather than conversion. Packages, SYSDATE, CONNECT BY queries, Oracle-specific partitioning syntax, and optimizer hints all need to be evaluated individually, while indexes often need to be retuned for PostgreSQL's query planner rather than carried across unchanged.

Phase 3: Data migration and synchronization

Two migration strategies cover most Oracle to PostgreSQL migrations. The right choice depends on database size, write volume, and downtime tolerance rather than the migration tool itself.

Snapshot, or big-bang, migration is simpler and requires a downtime window, which suits smaller or less critical databases. Change data capture (CDC) and logical replication keep the source and target databases in near-continuous sync, making them a better fit for business-critical systems that require near-zero downtime cutover.

Whichever strategy fits, the migration needs validation built in from the start, not bolted on after it’s complete. That means checksum verification and row-level reconciliation to confirm data arrived intact, explicit handling for CLOB, BLOB, and PostgreSQL's BYTEA data type, which many tools treat as an afterthought, and incremental synchronization to catch changes made on the source after the initial transfer.

Phase 4: Testing, validation, and cutover

Testing covers parallel-run validation, performance benchmarking against pre-migration baselines, and application regression testing across every dependent system identified during assessment. The cutover plan should define clear rollback criteria before production migration begins. A rollback plan is a sign of a mature migration, not a lack of confidence. It reduces risk if unexpected issues arise during cutover.

Security and compliance validation should also be part of cutover planning, and it's an area where enterprise PostgreSQL platforms can provide significant operational advantages. Permissions should be reverified rather than assumed to carry over, data encryption should be confirmed on the target environment, and PostgreSQL security best practices should include validating continuous audit logging throughout the cutover window. Backup and recovery procedures should also be validated with a successful restore test, not just a completed backup job.

Phase 5: Post-migration operations

Day-one operations extend beyond monitoring. They include backup and recovery procedures, patching, lifecycle management, PostgreSQL upgrades, high-availability monitoring, ongoing performance tuning, and a clearly defined support model.

Patching, lifecycle management, and PostgreSQL version upgrades all need defined ownership and a regular cadence. Operational runbooks should be in place before the first incident occurs, not written in response to one. Backup and recovery procedures, high-availability monitoring, and ongoing performance tuning become continuous operational responsibilities rather than one-time migration tasks.

This is where the enterprise platform question comes into focus. Community PostgreSQL places responsibility for operational assurance on the internal team. An enterprise distribution provides support SLAs, security features, and lifecycle management as part of the platform, which is particularly valuable for organizations operating hybrid cloud environments in regulated industries where infrastructure and compliance requirements are shared across multiple environments.

Where Fujitsu Enterprise Postgres fits in an Oracle migration

Earlier phases focused on assessment, conversion, migration, testing, and operational readiness. Fujitsu Enterprise Postgres supports each stage with migration expertise, enterprise PostgreSQL capabilities, and long-term operational support.

FEP is a 100% PostgreSQL-compatible target with enterprise security built in, including transparent data encryption, data masking, and dedicated audit logging, backed by 24/7 global support. These are the kinds of enterprise database features regulated industries prioritize.

It deploys across on-premises, cloud, and hybrid environments, including IBM Power, giving organizations the flexibility to support a wide range of enterprise infrastructure requirements. A global superannuation platform used this approach for an Oracle migration. Following a structured architecture review and migration assessment, the organization completed production cutover during a planned weekend maintenance window and significantly reduced Oracle licensing costs. The customer isn't named, and the timeline reflects that specific engagement rather than a general promise.

Talk to Fujitsu's migration specialists about mapping your Oracle environment's complexity and sequencing before your team commits to a timeline.

Frequently asked questions about Oracle to PostgreSQL migration