Technology

Guide on Oracle to PostgreSQL Migration

Antonio Carter Emily September 7, 2023

In the world of databases Oracle and PostgreSQL are known as the most powerful and popular object-relational DBMS. Both of them are supplied with a lot of advanced features and wide range of administration and development tools. At the same time, PostgreSQL has important advantage towards Oracle since it is open source and has more liberal license than another database management system. In view of this fact many companies migrate their databases from Oracle to PostgreSQL to reduce total cost of the ownership.

Manual migration runs according to extract-transform-load technique. According to this approach, Oracle to PostgreSQL migration is a sequence of following steps: 

  • Export Oracle schemas, indexes and constraints with all necessary attributes in form of data definition language (DDL) statements
  • Convert those statements into PostgreSQL format with respect to type mapping, replacing Oracle built-in functions that are not supported in PostgreSQL, etc
  • Import the resulting CREATE-statements into the destination database
  • Oracle data is exported into comma separates values files (one file per table)
  • Make the data conformed with PostgreSQL format that includes correct processing of binary data and dates, escaping special symbols when it is necessary
  • Load those CSV files into existing (created) PostgreSQL tables
  • Extract views, stored procedures and triggers from Oracle database in form of SELECT-queries and PL/SQL source code
  • Transform these queries and source code according to PostgreSQL dialect and load it into the destination database

The steps of manual Oracle to PostgreSQL migration listed above proves that it is not an easy task taking many efforts. There is high risk of losing data integrity and breaking semantic because of human errors. In order to avoid those risks and to automate database migration, some dedicated software solutions should be used. 

One of such database migration tool is Oracle to PostgreSQL converter. It has been developed by Intelligent Converters, a software vendor focused on database conversion and synchronization for more than 10 years. The product supports all versions of Oracle and PostgreSQL and has outstanding performance due to direct reading and writing data (no middleware components are used). Other features:

  1. Converts schemas, data, indexes, constraints, foreign keys and views
  2. For scripting and scheduling the Oracle to PostgreSQL migration, converter is supplied with command line version
  3. Merges and to synchronizes existing PostgreSQL database with Oracle data
  4. Converts data obtained as result of SELECT-queries

The fourth feature is very powerful and can be used for multiple purposes, for example:

1. Filtering rows: 

SELECT * FROM Orders WHERE StartDate BETWEEN to_date(’15-JAN-16’) AND to_date(’15-FEB-16’)

2. Select and rename particular columns: 

SELECT fname AS FirstName, lname AS LastName, addr as Address FROM People

3. Skip NULL values: SELECT * FROM Employees WHERE Comments IS NOT NULL

4. Merge two tables: 

SELECT T1.code, T1.name, T1.description, T2.image 

FROM Products T1

INNER JOIN ProductLines T2 ON T1.productline = T2.productline;

For more information about Oracle to PostgreSQL migration tool visit the official site of the Intelligent Converters company.