This article discusses the possible complications that can arise when migrating databases from Oracle to PostgreSQL. The steps for migration are covered and issues and their fixes are identified:
- Approach to migration
- Schema migration
- Code migration
- Data migration
When migrating database objects from Oracle to PostgreSQL you may face issues as there are some incompatibilities between Oracle and PostgreSQL. Before performing the migration you need to find out the incompatibilities between these heterogeneous databases. Because of the challenges you may face, you may need to make some manual changes in the syntax of the database objects before you perform a migration between Oracle to PostgreSQL.
For the migration you may use the open source tool Ora2pg, an EDB Migration Toolkit (MTK), or EDB Migration Portal.
Approach to migration
You need to set the approach of migration. Following the steps below can be helpful:
1. Schema migration
2. Code migration
3. Data migration
Schema migration
You need to fetch the Oracle schema (that is, DDLs) first and then insert that schema into PostgreSQL, after making the appropriate changes. You may face several challenges here that require you to make some manual changes in the DDLs before you perform the migration.
Below are some changes to consider:
A) Parameters
Parameters need to be considered for the creation of a table. Oracle has a huge list of parameters that you can provide to create a table, whereas PostgreSQL has comparatively fewer options. Here is a general example showing the differences when creating the same table in Oracle and PostgreSQL:
Oracle Example
SQL> CREATE TABLE "U1"."SC_TENORS"
("TENOR_NAME" VARCHAR2(20) NOT NULL ENABLE,
"TENOR_VALUE" NUMBER(*,0) NOT NULL ENABLE,
"TENOR_DESC" VARCHAR2(30) NOT NULL ENABLE,
CONSTRAINT "SC_TENORS_PK" PRIMARY KEY ("TENOR_NAME") ENABLE
)
ORGANIZATION INDEX NOCOMPRESS PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
TABLESPACE "TBS1"
PCTTHRESHOLD 50 INCLUDING "TENOR_VALUE" OVERFLOW
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING
TABLESPACE "TBS1"
;
NOTE: The table name SC_TENORS in Oracle is case-insensitive.
PostgreSQL Example
postgres=# CREATE TABLE sch1."SC_TENORS" (
"TENOR_NAME" varchar(20) NOT NULL,
"TENOR_VALUE" numeric(38) NOT NULL,
"TENOR_DESC" varchar(30) NOT NULL,
CONSTRAINT "SC_TENORS_PK1" PRIMARY KEY ("TENOR_NAME")
) tablespace tbspc1 ;
CREATE TABLE
postgres=# \dt
List of relations
Schema | Name | Type | Owner
--------+-----------+-------+----------
sch1 | SC_TENORS | table | postgres
(1 rows)
postgres=# CREATE TABLE sch1.SC_TENORS (
TENOR_NAME varchar(20) NOT NULL,
TENOR_VALUE numeric(38) NOT NULL,
TENOR_DESC varchar(30) NOT NULL,
CONSTRAINT "SC_TENORS_PK2" PRIMARY KEY (TENOR_NAME)
) tablespace pg_default ;
CREATE TABLE
postgres=# \dt
List of relations
Schema | Name | Type | Owner
--------+-----------+-------+----------
sch1 | SC_TENORS | table | postgres
sch1 | sc_tenors | table | postgres
(2 rows)
B) Datatypes
The following dataypes require special attention when migrating from Oracle to PostgreSQL:
1. VARCHAR2 is not supported in PostgreSQL and will generate errors.
Workaround: VARCHAR2 can be replaced with VARCHAR.
2. NUMBER is not supported in PostgreSQL.
Workaround: NUMBER can be replaced with INT, SMALLINT, BIGINT, NUMERIC(n).
3. DATE is available in Oracle as well as PostgreSQL. The DATE datatype in Oracle shows both date and time, but the PostgreSQL DATE data type returns only date with no time.
Workaround: If your requirement is date with time, then use the datatype TIMESTAMP.
C) ROWID
Oracle's pseudocolumn ROWID returns the physical address of a table row, PostgreSQL instead has ctid, which serves the same purpose.
D) Virtual Columns
Virtual Columns do not exist in PostgreSQL, You can use view instead.
E) Global indexes
Oracle has global indexes over partitions, they are not supported in PostgreSQL.
F) Global temporary tables
Unlike Oracle, global temporary tables do not exist in PostgreSQL.
G) Object name field limit
Oracle has higher object name field limit than PostgreSQL.
H) UNIQUE
The UNIQUE keyword is currently not supported in PostgreSQL. Users can substitute the functionally equivalent “DISTINCT” keyword.
Usage (ORACLE):
SELECT UNIQUE deptid FROM dept;
Workaround (PostgreSQL):
SELECT DISTINCT deptid FROM dept;
DUAL Table
DUAL table is a dummy table used by Oracle, as for every select statement a FROM clause is mandatory. In PostgreSQL this clause can be omitted and the direct function can be used.
Workaround: When migrating, just remove the "FROM DUAL" clause from the statement. Joins with dual are really rare—and peculiar.
Oracle example
SQL> select current_timestamp from dual;
CURRENT_TIMESTAMP
----------------------------------------------------------------------
17-MAR-20 04.32.32.465956 AM -07:00
PostgreSQL example
postgres=# select current_timestamp ;
current_timestamp
-------------------------------
2020-03-17 04:32:49.218165-07
(1 row)
Code migration
This is the most important and time-consuming task in the migration. Code gets developed based on the business logic and due to a lot of differences in PL/SQL and PL/pgSQL, a major part of the migration requires manual intervention.
These are some of the most common differences:
A) There is no substitution of Oracle’s packages in PostgreSQL. You must create a schema for it instead and use it.
B) To print something, Oracle uses “dbms_output.put_line('Hello World');”, while PostgreSQL uses “raise notice 'Hello World!';”.
An Oracle-style (PL/SQL) procedure for printing “hello world” would look like this:
SQL> create or replace procedure pro1
as
begin
dbms_output.put_line('Hello World');
end;
/
Procedure created.
SQL> exec pro1;
Hello World
PL/SQL procedure successfully completed.
We can convert this into a PostgreSQL-style procedure (PL/pgSQL) like this:
postgres=# create or replace procedure pro1()
language plpgsql
as $$
begin
raise notice 'Hello world';
end; $$;
CREATE PROCEDURE
postgres=# call pro1();
NOTICE: Hello world
CALL
C) Exiting from IF statements looks like this:
In Oracle:
EXIT
WHEN cur_get_objects%NOTFOUND;
In PostgreSQL:
IF NOT FOUND THEN EXIT; END IF;
Along with the above, here are a few additional differences:
—Oracle converts names of schema, tables, columns, and functions to UPPER CASE, unless quoted, and PostgreSQL converts them to lower case, unless quoted. It is safe whether the application quotes the identifiers or not.
—Oracle gives every user their own schema, by default. For PostgreSQL, create a user and schema by the same name. By default the first component in search_path is $user.
You are now connected to database "postgres" as user "arun".
postgres=> show search_path;
search_path
-----------------
"$user", public
(1 row)
postgres=> set search_path=arun;
SET
postgres=> show search_path;
search_path
-------------
arun
(1 row)
—Sequences: In Oracle, to check a sequence value we use “sequence_name.nextval” whereas in Postgres, we use “setval('sequence_name', value)”.
—Compound triggers are not supported in PostgreSQL.
—Oracle uses the function sysdate to get the current date and time in the server's timezone. Postgres uses 'now'::timestamp for current transaction start date and time in the session's timezone.
SYSDATE Function
In Oracle, the SYSDATE function returns date and time. PostgreSQL doesn't have any such function corresponding to the SYSDATE function.
Workaround: To achieve your requirement of returning date with time you can use any of the following functions:
1. now() or transaction_timestamp()
This function will give you the transaction start time.
2. statement_timestamp()
This function will give you the SQL start time.
3. clock_timestamp()
This function will give you the time when the function is implemented.
DELETE Syntax
In Oracle, the DELETE statement has two syntax options. That is, it can be used with a FROM clause and without a FROM clause. However, in PostgreSQL the FROM clause is mandatory.
Workaround: You will need to add a FROM clause in the Oracle DELETE statement.
Oracle example
SQL> delete from t1 where name='arun';
1 rows deleted.
SQL> delete t1 where name='arun';
1 rows deleted.
PostgreSQL example
postgres=# delete from t1 where name='arun';
2 rows deleted.
In PostgreSQL, a DELETE statement without a FROM clause will give you a syntax error:
postgres=# delete t1 where name='arun';
ERROR: syntax error at or near "t1" at character 8
STATEMENT: delete t1 where name='arun';
SUBSTR Function
The SUBSTR Function behaves differently in Oracle and PostgreSQL, which can lead to potential issues.
In Oracle:
SQL> select SUBSTR ('XYZ',-1) from dual;
S
-
Z
In PostgreSQL:
postgres=# select SUBSTR ('XYZ',-1);
substr
--------
XYZ
(1 row)
Workaround: You can use the Oracfe SUBSTR function, which returns the same result in PostgreSQL.
Data migration
Once the Oracle schema and code with the required changes are present in the PostgreSQL, the last step remains to migrate the data. This is a simple task compared the first two steps and requires time for copying data only. Usually, the time required is dependent on the underlying resources.
Tools that can be used for migration
You can use an EnterpriseDB Migration Toolkit (MTK) and the EDB Migration Portal. For more information on these tools you can refer to the EDB user guides:
There are also external tools available for migration:
Ora2pg, which is an open source tool: http://ora2pg.darold.net/
Oracle to Postgres: https://www.convert-in.com/ora2pgs.htm
ora_migrator: https://github.com/cybertec-postgresql/ora_migrator