Case Study: Migrating from Oracle 19c to PostgreSQL 16 using Ora2PG

Objective

To migrate selected applications from Oracle 19c to PostgreSQL 16 using Ora2PG, while ensuring:

• Minimal downtime
• Compatibility with existing data structures
• Equivalent or improved performance
• Maintainability and observability
  
  

Tools and Technologies

• Source: Oracle 19c (multi-schema, pluggable database)
• Target: PostgreSQL 16 (on-prem / cloud)
• Migration Tool: Ora2PG
• OS: RHEL 8 / Rocky Linux 9
• Monitoring: pg_stat_statements, Oracle AWR (for pre-migration benchmarking)
  
  

Why PostgreSQL?

We do not consider this as a 'replacement' because Oracle is lacking — rather, we chose PostgreSQL where it made strategic and technical sense. Here are some comparative advantages observed during evaluation:

Migration Process (Using Ora2PG)

1. Assessment: Used `ora2pg -t SHOW_REPORT` to assess schema complexity and estimated effort. Identified object types, incompatible PL/SQL constructs, and migration blockers.
2. Schema Migration: Converted tables, indexes, constraints using Ora2PG with customized configuration. Used TYPE, DATA_TYPE, and DATA_EXPORT modes. Manual intervention for packages, triggers, and ref cursors.
3. Data Migration: Used Ora2PG with DATA mode to export and load data in parallel. Large tables were handled using COPY and parallel jobs.
4. Validation: Row count comparison, checksum validation, and SQL query benchmarking. Performance tuning using PostgreSQL’s EXPLAIN ANALYZE and pg_stat_statements.
5. Cutover: Performed during a scheduled maintenance window. Read-only Oracle sync using Oracle FDW for fallback testing.
   
   

Results & Benefits

Challenges Faced

• PL/SQL to PL/pgSQL translation required manual refactoring
• Oracle-specific features like CONNECT BY, MERGE, DBMS_OUTPUT needed alternatives
• Tuning PostgreSQL autovacuum and memory parameters post-migration
  
  

Where Oracle Still Excels

• Advanced clustering via RAC
• Built-in Data Guard for replication
• Enterprise support and SLAs
• Comprehensive tooling and diagnostics (OEM, AWR)
  
  

Learnings and Recommendations

• Start with a detailed assessment using Ora2PG reports.
• Refactor your PL/SQL early and involve developers.
• Benchmark queries pre and post-migration.
• PostgreSQL gives more control, but you must manage tuning and scaling.
  
  

Superior PostgreSQL Features That Attract Enterprises

Zero Licensing Cost

Fully open-source with no license fees. PostgreSQL can scale horizontally and vertically without increasing costs. Ideal for test/dev, DR, multi-region, and containerized deployments.

Advanced JSON & Document Store Capabilities

JSONB data type allows efficient querying, indexing, and storage of JSON data. Supports document-store style use cases with full SQL power.

Extensibility Architecture

Users can create custom data types, functions (in PL/pgSQL, Python, SQL, C), aggregates, and index types. Includes extensions like PostGIS, TimescaleDB, pg_partman, and pg_stat_statements.

Parallelism & Performance (PostgreSQL 16+)

Parallel query execution, sort, index creation, and vacuum operations. Enhanced GROUP BY and incremental sort support.

Powerful Indexing Options

Supports B-Tree, Hash, GIN, GiST, SP-GiST, and BRIN indexes. Partial, expression, and covering indexes are available. Advanced text search with pg_trgm.

Declarative Partitioning

Native partitioning with range, list, and hash types. Enhanced partition pruning and parallelism in PG16. Improved MERGE statement support.

Logical Replication & Bidirectional Sync

Supports native logical replication with PostgreSQL 16 enabling built-in bidirectional sync. Great for active-active or migration scenarios.

Security & Role Management

Supports row-level security, column-level privileges, SSL/TLS, LDAP, SCRAM-SHA-256, and certificate-based auth.

Observability & Query Tuning

pg_stat_statements and auto_explain for live query tracking. Rich diagnostics with EXPLAIN ANALYZE, pg_wait_sampling, and pg_stat_kcache.

Cloud-Native and DevOps Ready

Official Docker images, Kubernetes Operators (Zalando, Crunchy), and compatibility with Ansible, Terraform, etc. Backed by many managed cloud offerings.

Transactional DDL & MVCC

Supports transactional DDL for safer deployments and MVCC for high concurrency without table-level locking.

Materialized Views

Supports materialized views with refresh options, useful for reporting and analytics workloads.

Foreign Data Wrappers (FDWs)

Connect and query external data sources like Oracle, MySQL, MongoDB, CSV, REST APIs through FDWs.

Community & Release Cycle

Strong global community, yearly stable releases, rich ecosystem of extensions and tools.

Conclusion

This migration was not an anti-Oracle decision, but a pro-PostgreSQL one — focused on modernization, openness, and cost-efficiency. PostgreSQL 16 has matured significantly and, when paired with the right strategy and tools, can deliver enterprise-grade performance with greater flexibility.

Ready to Start Your PostgreSQL Migration?

If you're exploring ways to reduce Oracle licensing costs, embrace open-source solutions, or future-proof your infrastructure, we’re here to help. Our experienced team ensures a smooth, secure, and performance-optimized migration to PostgreSQL using proven tools like Ora2PG.

Email: info@datapatroltech.com

Call/WhatsApp: +91 84848 39896

Let’s accelerate your database transformation journey—get in touch today!