Oracle to Postgres Migration

Being very popular database management systems, both Oracle and PostgreSQL provide wide range of capabilities, APIs and development tools. However, Oracle has more strict licensing terms and high total cost of ownership (TCO) while PostgreSQL is open-source software coming under liberal license similar to BSD or MIT. This is the main reason why many businesses migrate their databases from Oracle to PostgreSQL.

In general, database migration is implemented according to the method known as extract-transform-load or ETL. For Oracle to Postgres migration this approach includes the following steps:

• Export definitions of Oracle objects in form of CREATE-statements
• Convert those statements into PostgreSQL format including correct type mapping, substitution of Oracle specific keywords and embedded functions, etc
• Import the modified definition statements into PostgreSQL database
• Export Oracle data into comma separated values (CSV) format as one file per table
• Transform extracted data with respect to PostgreSQL format handling properly binary data, dates and escaping special symbols in text fields when it is required
• Load the transformed data from CSV files into the PostgreSQL database

The description of ETL approach given above proves it is not easy to migrate database from Oracle to PostgreSQL manually. This procedure may take many efforts and leads to data loss or corruption due to the human factor. That’s why database professionals use special software tools to simplify database migration and avoid the related risks.

There are some software solutions to automate database migration from Oracle to PostgreSQL on the market. One of such solutions for Oracle to Postgres migration has been developed by Intelligent Converters, a software company working in database migration and synchronization field for more than twenty years.

Features of Oracle to PostgreSQL converter

1. Table definitions, data, constraints, indexes, foreign keys and views are migrated
2. Direct connection and bulk insert techniques allow to reach high performance of database migration
3. All versions of Oracle and PostgreSQL including “software as a service” (SAAS) variations of these DBMS such as Azure and Amazon RDS
4. Command line support for purpose of automation Oracle to PostgreSQL migration
5. Merge and synchronize methods of processing existing PostgreSQL database
6. Conversion settings are stored into profile to simplify next runs

Besides generic capabilities Oracle to PostgreSQL converter offers filtering data via SELECT-queries. This advanced feature can be used for pre-processing of data source before migration such as selecting particular columns and records, merging multiple tables and others. Here are few examples of using SELECT-queries feature:

• Filtering rows:

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

• Filtering and renaming columns:

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

• Skipping NULLs: SELECT * FROM MyTable WHERE Comments IS NOT NULL
• Merging two tables into a single one:

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

FROM Products T1

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

As every software product made by Intelligent Converters, Oracle to PostgreSQL database migration tool comes with free demo version for evaluation purpose, unlimited support and 1 year subscription for updates.