blog
Migrating From Oracle to PostgreSQL – What You Should Know
Whether migrating a database or an application from Oracle to PostgreSQL with only one type of database knowledge, there are few things to know about the differences between the two database systems.
PostgreSQL is the world’s most advanced open source database. PostgreSQL community is very strong and they are continuously improving existing PostgreSQL features and also add new features. As per the db-engines.com, PostgreSQL is the DBMS of the year 2017.
There are some incompatibilities in Oracle and PostgreSQL. The behaviour of some functions is different between Oracle and PostgreSQL.
Why Migrate from Oracle to PostgreSQL
- Cost: As you may know Oracle licence cost is very expensive and there is additional cost for some features like partitioning and high availability. So overall it’s very expensive.
- Flexible open source licensing and easy availability from public cloud providers like AWS.
- Benefit from open source add-ons to improve performance.
Preliminary Check
As you may know migration from Oracle to PostgreSQL is a costly and time consuming task. It is important to understand which part is to migrate. Do not waste time for migrating objects that are no longer required. Also, check whether there is any historical data required or not. Do not waste time replicating data that you don’t need, for example backup data and temporary table from past maintenance.
Migration Assessment
After preliminary check, the first step of migration is to analyze the application and database object, find out the incompatibilities between both the databases and estimate the time and cost required for migration.
Ora2pg tool is very helpful for migration assessment. It connects to the Oracle database, scan it automatically and extracts the data, generating the database migration report. You can check a sample report in Ora2pg.
What You Should Know
Understand the differences between Oracle and PostgreSQL and convert it using any tool. There is no any tool that can convert 100% Oracle database into PostgreSQL, some manual changes are required. Please check below some of the important differences you should know before migrating.
Data Type Mapping
PostgreSQL has rich set of data types. Some of the important Data type conversion between Oracle and PostgreSQL is as follow.
| Oracle | PostgreSQL | Comment |
|---|---|---|
| VARCHAR2(n) | VARCHAR(n) | In Oracle ‘n’ is number of bytes whereas in PostgreSQL ‘n’ is number of characters |
| CHAR(n) | CHAR(n) | In Oracle ‘n’ is number of bytes whereas in PostgreSQL ‘n’ is number of characters |
| NUMBER(n,m) | NUMERIC(n,m) | NUMBER type can be converted to NUMERIC but if you use SMALLINT, INT and BIGINT then performance would be better. |
| NUMBER(4) | SMALLINT | |
| NUMBER(9) | INT | |
| NUMBER(18) | BIGINT | |
| NUMBER(n) | NUMERIC(n) | NUMERIC(n) ,If n>=19 |
| DATE | TIMESTAMP(0) | Both databases has DATE type but Oracle DATE type returns date and time whereas PostgreSQL DATE type return only date no time. |
| TIMESTAMP WITH LOCAL TIME ZONE | TIMESTAMPTZ | The PostgreSQL type Timestamptz(Timestamp with time zone) is different from the Oracle Timestamp with time zone. It is equivalent to Oracle’s Timestamp with local time zone, but this small difference can cause performance issue or application bug. |
| CLOB | TEXT | PostgreSQL TEXT type can store up to 1 GB of text. |
| BLOB RAW(n) |
BYTEA(1 GB limit) Large object |
In Oracle, BLOB datatype stores unstructured binary data in the database. BLOB type can store up to 128 terabytes of binary data. PostgreSQL BYTEA stores binary data but only upto 1 GB. If the data if above 1 GB then use Large object. |
Transactions
Oracle database always uses transactions but in PostgreSQL you have to activate that. In Oracle, the transaction starts when executing any statement and ends when COMMIT statement executed. In PostgreSQL, transaction starts when execute BEGIN and end when COMMIT statement executed. Even the isolation levels also have no problem. PostgreSQL database knows all the isolation levels that Oracle database knows. The default isolation level of PostgreSQL is Read committed.
Example:
Oracle:
DELETE FROM table_name WHERE id = 120;
COMMIT;PostgreSQL:
BEGIN;
DELETE FROM table_name WHERE id = 120;
COMMIT;Dual Table
In Oracle FROM clause is mandatory for every SELECT statement so Oracle database uses DUAL table for SELECT statement where table name is not required. In PostgreSQL, FROM clause is not mandatory so DUAL table is not necessary. The Dual table can be created in PostgreSQL as a view to eliminate the porting problem. Orafce tool have implemented this so you can use Orafce also.
Example:
postgres=# SELECT CURRENT_TIMESTAMP FROM DUAL;
ERROR: relation "dual" does not exist
LINE 1: SELECT CURRENT_TIMESTAMP FROM DUAL;
^
postgres=# SELECT CURRENT_TIMESTAMP;
current_timestamp
-------------------------------
2018-03-16 09:36:01.205925+00
(1 row)After installing Orafce module:
postgres=# SELECT CURRENT_TIMESTAMP FROM DUAL;
current_timestamp
-------------------------------
2018-03-16 09:36:01.205925+00
(1 row)SYSDATE
Oracle’s SYSDATE function returns date and time. The behaviour of SYSDATE function is different in different places. PostgreSQL does not have any function corresponding to SYSDATE function. In PostgreSQL there are multiple methods to get the date and time and it is based on the application purpose.
| Time retrieval method | Function to be used |
|---|---|
| SQL start time | Statement_timestamp() |
| Transaction start time | now() or
Transaction_timestamp() |
| Time when the function is implemented | Clock_timestamp() |
In the below example clock_timestamp() returns the time when actual function is executed and other statement_timestamp() returns the time when the SQL statement started it’s execution.
postgres=# SELECT now(), statement_timestamp(), current_timestamp, transaction_timestamp(), clock_timestamp();
now | statement_timestamp | current_timestamp | transaction_timestamp | clock_timestamp
-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------
2018-03-16 09:27:56.163154+00 | 2018-03-16 09:27:56.163154+00 | 2018-03-16 09:27:56.163154+00 | 2018-03-16 09:27:56.163154+00 | 2018-03-16 09:27:56.163281+00
(1 row)TO_DATE(two argument)
Oracle’s TO_DATE function return DATE type value(year, month, day, hour, minute, second) while PostgreSQL’s TO_DATE(two_argument) return DATE type value(year, month, day).
The solution for this incompatibility is to convert TO_DATE() to TO_TIMESTAMP(). If you use Orafce tool then not necessary to change anything because Orafce implemented this function so we get the same result sa Oracle.
Oracle:
SELECT TO_DATE ('20180314121212','yyyymmddhh24miss') FROM dual;PostgreSQL:
SELECT TO_TIMESTAMP ('20180314121212','yyyymmddhh24miss')::TIMESTAMP(0);SYNONYM
CREATE SYNONYM is not supported in PostgreSQL. In Oracle CREATE SYNONYM is used to access remote objects while in PostgreSQL we can use SET search_path to include the remote definition.
Oracle:
CREATE SYNONYM abc.table_name FOR pqr.table_name;PostgreSQL:
SET search_path TO 'abc.table_name';Behaviour of Empty String and NULL
In Oracle, empty strings and NULL values in string context are the same. The concatenation of NULL and string obtain string as a result. In PostgreSQL the concatenation result is null in this case. In Oracle IS NULL operator is used to check whether string is empty or not but in PostgreSQL result is FALSE for empty string and TRUE for NULL.
Sequences
There is a slight difference in the syntax of sequence in Oracle and PostgreSQL.
Oracle:
Sequence_name.nextvalPostgreSQL:
Nextval(‘sequence_name’)To change this syntax you can create a script or you can change it manually.
SUBSTR
The behaviour of SUBSTR function in Oracle and PostgreSQL is different. The SUBSTR function works in PostgreSQL without error but returns a different result. This difference can cause application bugs.
Oracle:
SELECT SUBSTR(‘ABC’,-1) FROM DUAL;
Returns ‘C’PostgreSQL:
postgres=# SELECT SUBSTR('ABC',-1);
substr
--------
ABC
(1 row)The solution for this is to use Orafce SUBSTR function which returns the same result as Oracle in PostgreSQL.
DELETE Statement
In Oracle, DELETE statement can work without FROM clause but in PostgreSQL it is not supported. We need to add FROM clause in PostgreSQL DELETE statement manually.
Oracle:
DELETE table_name WHERE column_name = 'Col_value';PostgreSQL:
DELETE FROM table_name WHERE column_name = 'Col_value';External Coupling +
Oracle uses + operator for left and right join but PostgreSQL does not use it.
Oracle:
SELECT a1.name1, a2.name2
FROM a1, a2
WHERE a1.code = a2.code (+);PostgreSQL:
SELECT a1.name1, a2.name2
FROM a1
LEFT OUTER JOIN a2 ON a1.code = a2.code;START WITH..CONNECT BY
Oracle uses START WITH..CONNECT BY for hierarchical queries. PostgreSQL does not support START WITH..CONNECT BY statement. PostgreSQL have WITH RECURSIVE for hierarchical queries so translate CONNECT BY statement into WITH RECURSIVE statement.
Oracle:
SELECT
restaurant_name,
city_name
FROM
restaurants rs
START WITH rs.city_name = 'TOKYO'
CONNECT BY PRIOR rs.restaurant_name = rs.city_name;PostgreSQL:
WITH RECURSIVE tmp AS (SELECT restaurant_name, city_name
FROM restaurants
WHERE city_name = 'TOKYO'
UNION
SELECT m.restaurant_name, m.city_name
FROM restaurants m
JOIN tmp ON tmp.restaurant_name = m.city_name)
SELECT restaurant_name, city_name FROM tmp;PLSQL to PLPGSQL Conversion
PostgreSQL’s PL/pgSQL language is similar to Oracle’s PL/SQL language in many aspects. It is a block-structured, imperative language, and all variables have to be declared. In both the databases assignments, loops, conditionals are similar.
The main differences you should keep in mind when porting from Oracle’s PL/SQL to PostgreSQL’s PL/pgSQL
Migration Tools
There are some tools which are very helpful for an Oracle to PostgreSQL migration. You can also create your own tool as an extension and use it inside PostgreSQL.
Orafce
Oracle compatible functions, data type and packages can be use as it is in PostgreSQL. This is an open source tool with BSD licence so anyone can use this tool.
Most of the major functions are covered in Orafce.
Applications usually use those functions with multiple occurrences. You can reduce the modification cost of SQL by using this tool.
All the functions and packages are implemented correctly and it is well tested.
Some of the functions:
- Dbms_output
- dbms_random
- utl_file – filesystem related functions
- Dbms_pipe and dbms_alert
- PLVdate,PLVstr, PLVchr
- Oracle compatible DATE data type and functions like ADD_MONTHS, LAST_DAY,NEXT_DAY and so on.
- NVL function
- SUBSTR and SUBSTRB function
- VARCHAR2 and NVARCHAR2 support
- TO_DATE()
Ora2pg
Ora2Pg is a free tool used to migrate an Oracle database to a PostgreSQL compatible schema.
It connects to the Oracle database, scans it automatically, extracts its structure or data and then generates SQL scripts that you can load into your PostgreSQL database.
The cost estimation in an Oracle to PostgreSQL migration is not easy.
Ora2Pg inspects all database objects, all functions and stored procedures to detect if there’s still some objects and PL/SQL code that cannot be automatically converted by Ora2Pg.
This tool is very helpful for the following conversions:
- Schema conversion
- PLSQL to PLPGSQL conversion
Testing
Testing the whole application and the migrated database is very important because some of the functions are the same in both databases, however the behaviour is different.
- Some common scenarios need to be checked:
- Check whether all the objects are correctly converted or not.
- Check whether all the DMLS’s are working correctly or not.
- Load some sample data in both databases and check the result. The result of SQL from both database should be same.
- Check the performance of the DML and improve it if necessary.
