Data platform · Oracle to PostgreSQL

Oracle to PostgreSQL migration: what changes, what it takes, and how to switch without a long outage

Move from Oracle to PostgreSQL with both databases kept in sync until you switch. Interkey scopes the rework and moves the data with iblync.

  • Built and supported by Interkey An Interkey product, delivered and supported by Interkey
  • One platform for migration and continuous sync The move and the replication that follows it
  • Written quote after one meeting The price is discussed in a meeting about your systems

An Oracle to PostgreSQL migration has two parts. The schema and the code inside the database need rework, because the two engines differ in data types and procedural language. The data itself can be loaded and then kept in sync, so Oracle stays in production until the new system is proven. Interkey delivers both parts in Saudi Arabia.

At a glance

The main facts of an Oracle to PostgreSQL migration with Interkey
QuestionShort answer
What moves as it isThe data, and plain tables, keys and constraints once their types are mapped
What needs reworkStored code (PL/SQL procedures, packages and triggers), some data types, Oracle-specific SQL in the applications, and scheduled jobs
How the data movesAn initial load, then continuous sync with iblync while Oracle stays in production
Who does the workInterkey: assessment, conversion, data move and cutover, with your database and application teams
What sets the timelineMostly the amount of stored code and the number of connected applications, then data volume and the downtime window

Why teams move from Oracle to PostgreSQL

Teams that move from Oracle to PostgreSQL usually give one of three reasons.

  • Licence cost. PostgreSQL is open source and carries no licence fee, so a database that moves and is retired no longer adds to the cost of Oracle licences.
  • Freedom over where the database runs. PostgreSQL runs in your own data centre or with a cloud provider, and that choice can change later without changing the engine.
  • One engine to support. Organisations that already run PostgreSQL elsewhere want fewer engines to patch, monitor and staff.

None of this makes Oracle a poor database. It means the reasons to leave are weighed against the work of leaving, and most of that work is in the code, as the next section shows.

What needs rework

Seven differences that come up on almost every Oracle source.

Oracle features, their PostgreSQL counterparts and the work each one needs
In OracleIn PostgreSQLWhat the work is
NUMBER and DATEnumeric or an integer type; timestampEach NUMBER column is mapped by the values it holds. An Oracle DATE also holds a time of day, so it usually becomes timestamp, not date.
Sequences, called with seq.NEXTVALSequences or identity columns, called with nextval('seq')The calls are rewritten, then each sequence is set past the highest value loaded.
PL/SQL procedures and packagesPL/pgSQL functions and procedures; no packagesThe code is rewritten. Functions are grouped in schemas, and package state is held another way, for example in temporary tables.
Triggers with their code written inside themA trigger that calls a separate trigger functionEach trigger body moves into its own function, and the trigger is recreated to call it.
An empty string is treated as NULLAn empty string and NULL are different valuesComparisons, constraints and application code that relied on the Oracle behaviour are reviewed.
B-tree, function-based and bitmap indexesB-tree and expression indexes; no stored bitmap indexB-tree indexes are recreated, function-based ones become expression indexes, and queries that used bitmap indexes are reviewed.
Scheduler jobs inside the databaseNo built-in job schedulerEach job moves to an external scheduler or a PostgreSQL extension, keeping the same timing.

Each row follows the PostgreSQL documentation and, for empty strings and DATE, Oracle's documentation. Conversion tools can draft part of this work; the assessment reviews the rest by hand.

Moving the data while Oracle stays in production

Oracle keeps serving production through the first three steps. How changes are read from Oracle is on our Oracle CDC page, and the parallel run is on parallel run and cutover.

  1. First

    Initial load

    The existing data is copied to PostgreSQL once, at a limited speed so production on Oracle is not slowed.

  2. Next

    Continuous sync with iblync

    iblync applies every change made in Oracle after the load to PostgreSQL. If PostgreSQL is unavailable, changes wait in a queue and are applied when it returns.

  3. Before the switch

    Comparison

    The data in both databases is compared against checks agreed in advance, and the applications are tested on PostgreSQL.

  4. The switch

    Cutover

    A final sync, then application connections move to PostgreSQL in a short pause whose length is agreed in advance.

When an Oracle to PostgreSQL migration is the wrong move

Sometimes the honest answer is to stay. Three cases come up most:

  • A packaged application whose vendor supports it only on Oracle. Moving the database would leave you running an unsupported system.
  • A large body of stored code with no tests. Every procedure would be rewritten with no way to prove it still does the same thing. Build the tests first, or move later.
  • No real problem today. If the licence, the hosting and the support all suit you, a migration adds risk without a reason.

When one of these applies, the assessment says so in writing.

How Interkey runs the migration

  1. Assessment.

    Schema, stored code, application queries, data volume, rate of change, downtime window and where PostgreSQL will run. The result is a written plan, or advice to stay.

  2. Schema and code conversion.

    Types are mapped, PL/SQL is rewritten in PL/pgSQL, and both are tested against production-shaped data.

  3. Load and sync.

    iblync loads the data, then keeps PostgreSQL in step with Oracle at a speed that does not slow production.

  4. Parallel run.

    Both databases stay live. Applications move service by service, with two-way sync where some already write to PostgreSQL.

  5. Cutover and support.

    A final sync and the agreed checks, then connections move. Support comes from Interkey, in Arabic and English.

What shapes the work and the timeline

We do not publish a fixed duration for this migration, because your environment decides it. Four factors shape the work and the timeline:

  • The amount of stored code: procedures, packages and triggers. Usually the largest factor.
  • The data: its volume and how fast it changes.
  • Connected applications: how many there are, and how much of their SQL was written for Oracle.
  • The downtime window the business accepts at cutover.
Read more

The assessment turns these into a schedule for your project. The price is discussed in a meeting; see iblync pricing to book one. Before you start, the migration checklist helps you gather the details, and the wider route is on our Oracle migration and PostgreSQL migration pages.

Questions buyers ask

Can Oracle be migrated to PostgreSQL without downtime?

Almost. Oracle stays in service during the load and the sync, and a short pause remains when application connections switch, its length agreed with you in advance. Until that moment iblync keeps PostgreSQL in step with Oracle. Our zero downtime migration page explains the pattern.

What is the hardest part of an Oracle to PostgreSQL migration?

The code inside the database: PL/SQL procedures, packages and triggers. PostgreSQL has no packages and writes triggers differently, so this code is rewritten and tested, not copied. Moving the data is easier than moving the code, which is why the assessment reads the stored code before anything else.

Which tool moves the data from Oracle to PostgreSQL?

Interkey uses iblync, its own platform, for the initial load and the continuous sync from Oracle to PostgreSQL. iblync moves data; it does not convert stored code. The schema and the PL/SQL are converted by our engineers, with conversion tools for a first draft where they help.

What decides how long an Oracle to PostgreSQL migration takes?

The amount of stored code decides it more than the volume of data. The number of connected applications and the downtime window you accept come next. The assessment looks at all of these and then gives a schedule for your project.

Do our applications need to change?

Usually yes, in two places: SQL written for Oracle, such as functions and syntax that exist only there, and the connection settings and drivers. How much changes depends on how much SQL lives in each application. The assessment lists the changes per application before the work starts.

Where can PostgreSQL run in Saudi Arabia?

In your own data centre or with a cloud provider. The choice follows your data residency requirements and is made for each project, before design. The same decision covers where iblync runs while the data moves. Our data residency guide lists the questions to settle first.

Next step

Scope an Oracle to PostgreSQL migration

Tell us the Oracle version, roughly how many procedures and packages it holds, the data volume, where PostgreSQL should run, and the downtime window you can accept.

Required field

By sending this form, you agree to our Privacy Notice.

What happens next

  1. We read your request
  2. An engineer contacts you
  3. You get an estimate or a demo
  • Built and supported by Interkey

    An Interkey product, delivered and supported by Interkey

  • One platform for migration and continuous sync

    The move and the replication that follows it

  • Written quote after one meeting

    The price is discussed in a meeting about your systems

Or directly

+966-11-2180999 [email protected]

Tawuniya Towers, North Tower, 7th Floor, King Fahad Highway, Olaya, P.O. Box 56835, Riyadh 11564, Saudi Arabia

or Read the migration checklist

Published by Interkey. Last updated . Interkey is registered in Riyadh, Saudi Arabia under commercial registration 1010156897.