How to Migrate an Oracle Database to PostgreSQL

 

---

Introduction

In this article, we demonstrate how to migrate an Oracle Database to PostgreSQL.
Why would you want to do that? A common motivation is to make your application database-agnostic, allowing it to run on multiple database systems and thereby increasing your potential customer base.

There are several tools on the market that claim to automate this migration. KeepTool is one of them—easy to use and designed to automate migration tasks without requiring you to hand over full control to a “black-box” tool.

Over the past year, Hora’s built-in migration components have been refined through numerous real-world Oracle-to-PostgreSQL migration projects. This article explains how to use these capabilities efficiently.

Migration Tasks

Migrating an Oracle database to PostgreSQL involves several tasks:

• Creating an equivalent table structure in PostgreSQL, including foreign keys, primary/unique keys, check constraints, and comments

• Re-creating views using ANSI SQL and addressing differences in SQL syntax

• Copying data from Oracle to PostgreSQL

• Re-implementing PL/SQL logic using PL/pgSQL

We cover each of these steps in the following sections.

Creating a Table Structure in PostgreSQL

Before starting, review your Oracle schema and decide which tables actually need to be migrated. Some tables—such as temporary backup tables—may not be relevant in the target environment.

Hora’s flagging feature helps you mark the tables you want to migrate. In our example, we use a yellow bullet to identify migration candidates.

Once your tables are flagged, select one or more of them and choose DDL for PostgreSQL DB. To understand the process, let’s begin with a single table.

Hora displays a dialog with PostgreSQL-compatible DDL. It starts by setting the PostgreSQL search_path, which plays a role similar to—but not identical with—Oracle’s current schema.

set search_path = hr;

Next, Hora generates a CREATE TABLE statement. For each Oracle column, Hora selects the closest PostgreSQL data type. While similar, there are important differences, which Hora handles automatically.

Example: PostgreSQL Table Definition

DROP TABLE IF EXISTS jobs;

CREATE TABLE jobs
(
   job_id varchar(10) NOT NULL,
   job_title varchar(35) NOT NULL,
   min_salary int4,
   max_salary int4
);

Hora also includes table and column comments if they exist in the Oracle schema:

COMMENT ON TABLE jobs IS
  'jobs table with job titles and salary ranges. Contains 19 rows.
  References with employees and job_history table.';

COMMENT ON COLUMN jobs.job_id IS
  'Primary key of jobs table.';

COMMENT ON COLUMN jobs.job_title IS
  'A not null column that shows job title, e.g. AD_VP, FI_ACCOUNTANT';

Constraints are generated next, each with its own comment:

ALTER TABLE jobs ADD CONSTRAINT job_id_pk PRIMARY KEY ( job_id)
NOT DEFERRABLE INITIALLY IMMEDIATE;

COMMENT ON CONSTRAINT job_id_pk ON jobs IS
'The values in the column JOB_ID should be unique for each JOBS data record.';

Creating the Stuctures for Multiple Tables

When generating DDL for multiple tables, the order of statements becomes important. For example, foreign keys referencing a table require that the referenced table’s primary key already exists.

Hora automatically determines the correct order.
Simply select multiple tables and choose DDL for PostgreSQL DB again.

The generated script is organized as follows:

1. DDL for tables, including comments, primary/unique keys, and indexes

2. DDL for all foreign keys

3. Trigger logic

4. Grants

This structured output lets you run the entire script at once without worrying about dependencies.

Migrating Trigger Logic

For each Oracle trigger, Hora creates:

• a PostgreSQL function (containing the logic), and

• a PostgreSQL trigger that calls this function.

Example:

create or replace function departments_bir_fnc()
returns trigger as
$body$
begin
IF coalesce( new.DEPARTMENT_ID,0) = 0 THEN
select nextval('departments_seq') INTO new.DEPARTMENT_ID ;
end IF;
return new;
end;
$body$ language plpgsql;

create or replace trigger departments_bir
before insert
on departments
for each row
execute function departments_bir_fnc();

Creating Views in PostgreSQL

Switch the overview context from tables to views. Any flagged views can also be processed with the DDL for PostgreSQL DB menu option.

Hora generates a complete PostgreSQL-compatible definition, replacing Oracle-specific joins with ANSI-compliant syntax. In complex cases, Hora also adjusts built-in function calls and other Oracle-specific constructs.

Example output:

set search_path = hr;

-- VIEW: emp_details_view
drop view if exists emp_details_view;

create or replace view emp_details_view
(
   employee_id, job_id, manager_id, department_id, first_name, last_name,
   salary, commission_pct, location_id, department_name, country_id, city,
   state_province, job_title, country_name, region_name
)
AS
select
   E.EMPLOYEE_ID, E.JOB_ID, E.MANAGER_ID, E.DEPARTMENT_ID,
   E.FIRST_NAME, E.LAST_NAME, E.SALARY, E.COMMISSION_PCT,
   D.LOCATION_ID, D.DEPARTMENT_NAME,
   L.COUNTRY_ID, L.CITY, L.STATE_PROVINCE,
   J.JOB_TITLE,
   C.COUNTRY_NAME,
   R.REGION_NAME
from EMPLOYEES E
join DEPARTMENTS D on E.DEPARTMENT_ID = D.DEPARTMENT_ID
join LOCATIONS L on D.LOCATION_ID = L.LOCATION_ID
join COUNTRIES C on L.COUNTRY_ID = C.COUNTRY_ID
join REGIONS R on C.REGION_ID = R.REGION_ID
join JOBS J on J.JOB_ID = E.JOB_ID;

COMMENT ON VIEW emp_details_view IS
  'Employees details view';

-- Grant privileges
grant read on emp_details_view to keeptool;

grant select on emp_details_view to keeptool;

You may also select multiple views to generate all definitions in a single script.

Copying Data from Oracle to PostgreSQL

Hora makes data migration straightforward by generating PostgreSQL-compatible INSERT scripts.

Start by opening the table in Tables → Data Content (or the Data Content Browser).
Right-click the grid and select Create INSERT Script.

In the dialog that appears, deselect all Oracle-specific options. Ensure that Use timestamp literals remains enabled. These settings produce PostgreSQL-compatible output.

Example excerpt:

INSERT INTO JOBS( JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY)
VALUES( 'AD_PRES', 'President', 20080, 40000);
INSERT INTO JOBS( JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY)
VALUES( 'AD_VP', 'Administration Vice President', 15000, 30000);
INSERT INTO JOBS( JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY)

...
COMMIT;

This method can be applied to any flagged table.

Implementing Oracle PL/SQL Logic using PL/pgSQL

We’ve already seen how Hora migrates trigger logic. Most Oracle databases also contain stored procedures, functions, and packages. Migrating these requires a deeper analysis of your PL/SQL code.

A follow-up blog post will cover how to migrate PL/SQL logic to PL/pgSQL, including common patterns, differences in exception handling, and best practices.

Rewriting SQL Queries for PostgreSQL

Hora provides a built-in tool for automatically converting Oracle SQL queries to PostgreSQL, including adjustments for ANSI compliance. This feature is useful in several situations:

• Queries inside PL/SQL code that are not automatically transformed when converting to PL/pgSQL.
  These need to be converted individually using our tool.

• Queries used in applications that are also being migrated from Oracle to PostgreSQL.

In all cases, the workflow is straightforward:
Select any query inside any SQL editor window, right-click, and choose “Rewrite SQL for PostgreSQL.”

Example

Once selected, KeepTool attempts to convert the query automatically.
For example, the following rewritten query is produced:

select 
   D.DEPARTMENT_NAME, D.DEPARTMENT_ID,
   E.EMPLOYEE_ID, E.FIRST_NAME, E.LAST_NAME, E.DEPARTMENT_ID
from DEPARTMENTS D
right join EMPLOYEES E on D.DEPARTMENT_ID = E.DEPARTMENT_ID 

The algorithm handles most queries reliably.
However, very complex SQL statements may not be fully convertible in a single step.
In such cases, try converting subqueries, UNION blocks, or other components separately to achieve the best results.

---

Summary

Migrating an Oracle database to PostgreSQL involves schema conversion, view translation, data transfer, and procedural logic migration. KeepTool’s Hora simplifies all these steps while giving you complete control over the generated SQL.

Stay tuned for our next article, which will dive into converting complex PL/SQL code to PostgreSQL’s PL/pgSQL.

---