Architecture
Oracle to PostgreSQL Migration: A Step-by-Step Guide
How an Oracle to PostgreSQL migration actually runs: assessment with ora2pg, deciding what moves, schema and data type mapping, converting PL/SQL, the syntax differences that bite, data validation, performance work, operations and cutover patterns.
Need a hand with this? Talk to a senior database DBA ↓By RadixTrie3 September 20264 min read
Organisations consider moving from Oracle to PostgreSQL for familiar reasons: licence cost, vendor independence, cloud portability and the maturity PostgreSQL has reached. The engine is rarely the obstacle. PostgreSQL handles the workloads. What consumes the time is everything around the engine: procedural code, application assumptions, operational habits and the testing needed to prove that nothing important changed. This guide sets out the migration step by step, in the order the work usually happens.
Step 1: Assess what you have
Inventory before you plan: schemas, object counts, lines of PL/SQL in packages, procedures, functions and triggers, use of Oracle-specific features such as materialised views, advanced queuing, partitioning options, external tables and proprietary data types, and how each application talks to the database. Open-source tooling such as ora2pg produces an assessment report that scores conversion effort per object type; AWS Schema Conversion Tool and EDB Migration Toolkit do similar work in their ecosystems. Treat the report as a map of where the work is, not as a schedule.
Step 2: Decide what moves
Not every workload should move, and the assessment says which. A reporting database with plain SQL and few stored procedures is a low-risk candidate. A core system with tens of thousands of lines of PL/SQL, vendor-supplied schema and an application certified only on Oracle may cost more to move than it will ever save. Rank the candidates by business value of moving against conversion effort and risk, migrate the easy wins first, and let the hard ones wait for the skills and confidence the early ones build.
Step 3: Convert the schema and data types
Most tables convert cleanly. The care goes into the types and the semantics behind them.
- NUMBER maps to numeric, or to integer and bigint where the precision allows; getting this right decides both correctness and performance.
- VARCHAR2 maps to varchar or text. Oracle treats an empty string as NULL; PostgreSQL does not, and application logic that relied on that behaviour will change.
- Oracle DATE carries a time component, so it maps to timestamp, not to the PostgreSQL date type. TIMESTAMP WITH TIME ZONE needs a deliberate decision about session time zones.
- CLOB maps to text; BLOB and RAW map to bytea in most cases, with large objects as the alternative for very large binaries.
- Sequences and identity columns need their current values carried across, and triggers that populated keys usually give way to identity or default expressions.
- Constraints enforced in triggers rather than declared should be declared on the way across. Unquoted identifiers fold to upper case in Oracle and lower case in PostgreSQL, which matters for anything that quotes names.
Step 4: Convert the procedural code
PL/SQL to PL/pgSQL is the largest single effort in most migrations. Packages have no direct equivalent and are usually restructured into schemas of functions with state held in tables or session settings. Autonomous transactions do not exist and need a redesign, often through a separate connection. Exception names, cursor handling, bulk operations and Oracle-specific built-in functions all need deliberate translation, and every converted routine needs a test that proves it produces the same result on the same data.
Step 5: Fix the SQL in the application
SQL embedded in applications carries Oracle idioms. Object-relational mappers hide many of them; hand-written SQL needs review. These are the differences that come up in almost every migration:
- Outer join syntax using (+) becomes ANSI LEFT and RIGHT JOIN.
- ROWNUM becomes LIMIT and OFFSET, or the row_number() window function where ordering matters.
- SELECT ... FROM DUAL loses the FROM DUAL; PostgreSQL allows a SELECT without a table.
- NVL becomes COALESCE; DECODE becomes CASE; SYSDATE becomes now() or current_timestamp with attention to time zones.
- CONNECT BY hierarchies become recursive common table expressions.
- MERGE exists in PostgreSQL from version 15; on older versions INSERT ... ON CONFLICT covers the common upsert case.
- Optimiser hints have no effect in PostgreSQL. Performance is managed through indexing, statistics and configuration instead.
- Date arithmetic and string functions differ in small ways that only tests catch: TO_CHAR format models, TRUNC on dates, string concatenation with NULLs.
The single best predictor of a smooth migration is how many full rehearsals you run against production-like data before the real cutover. Two is a minimum for anything that matters.
Step 6: Migrate and validate the data
Moving the data is straightforward; proving it arrived intact is the work. ora2pg can perform the initial load, and change-data-capture tools such as AWS Database Migration Service keep the target in step with the source while validation runs. Validate with row counts per table, checksums on key columns, referential integrity checks and business-level reconciliation reports that the application owners recognise, and repeat the whole set on every rehearsal so the cutover-night checks are routine.
Step 7: Validate performance
PostgreSQL's planner and indexing model differ from Oracle's. Expect a set of queries that need new indexes, rewritten predicates or configuration changes. Run ANALYZE after every load so statistics are current, capture the heaviest statements with pg_stat_statements, compare against the Oracle baseline on production-like data, and size memory and parallelism settings for the real workload rather than the defaults. Plan the time to find these queries; they exist in every migration.
Step 8: Prepare operations before cutover
Backups and point-in-time recovery, replication and failover, monitoring, vacuum management and security all work differently in PostgreSQL. The team that will run it needs backups they have restored, a standby they have promoted and alerts they have seen fire before the first production day, not after. Our note on reskilling to PostgreSQL covers what changes for experienced Oracle DBAs and how to approach the transition alongside the technical work.
Step 9: Choose the cutover pattern
Small systems can move in a single maintenance window: freeze, migrate, validate, switch. Larger or busier systems usually need a period of change-data-capture or dual running, where Oracle stays the system of record while PostgreSQL is fed and validated in parallel, followed by a short final switch. The right pattern depends on how long the business can pause writes and how quickly you need to be able to roll back. Whatever the pattern, keep the Oracle instance intact and unmodified until PostgreSQL has proven itself through at least one full business cycle.
How RadixTrie approaches it
RadixTrie supports both Oracle and PostgreSQL in production for South African organisations, so our advice is about the workload rather than the licence: some systems should move, some should stay, and the assessment says which. When a migration goes ahead, senior engineers run the assessment, conversion, rehearsals and cutover, and then support the estate afterwards. Our PostgreSQL support services and Oracle database support services pages describe what that cover looks like on each side of the move.
Need a hand with this?
Tell us what you run today and when it has to move. A senior database DBA will reply directly, usually within one working day. Prefer email or phone? Write to expert@radixtrie.com or call +27 10 143 0304, or use the contact page to book a 30-minute call.
TAGSOraclePostgreSQLMigrationArchitectureSouth Africa