Oracle to Postgres Migration


Oracle and PostgreSQL are recognized by every database specialist as very popular database management system supplied with a wide range of tools and features. PostgreSQL is open source and goes under a liberal license similar to BSD or MIT when comparing this two DBMS, it comes that Oracle has stricter licensing terms and higher total cost of ownership.

This is why many companies intend to launch Oracle to Postgres migration.

Considering the process of Oracle to Postgres migration, the following sequence and steps are considered:

  • Export Oracle table definition table to be in form of statements written in data definition language (DDL)
  • DDL statements should be converted into PostgreSQL format including the substitution of Oracle embedded functions which are not supported in PostgreSQL, correct type mapping etc.
  • Using a target PostgreSQL database, import the resulting table definitions.
  • While working on comma-separated values (CSV) format, export the Oracle data from the source database into an intermediate storage.
  • In order to comply with PostgreSQL format, dates and escaping special symbols in text fields when it is required, providing necessary transformation of binary data, convert the data.
  • Import the CSV files right into the target database.
  • Export Oracle stored procedures, views, and triggers in the form of SQL statement and source code.
  • Transform source codes according to its destination.
  • Import the database into PostgreSQL database.

The above steps prove that it is not easy to run Oracle to Postgres migration manually. It may cause human data loss or corruption as the tedious procedure takes many efforts and human factor. Special software may be used in order to automate database migration and avoiding any related risk.

Fortunately, there are few software companies which provide a solution for automating database migration. Oracle to Postgres migration tool is one of such solution, developed by Intelligent Converters, a database migration field software company since 2001.

Product Features:

  1. Table definitions, constraints, data, indexes and foreign keys are converted as database objects.
  2.   To reach a high performance of the database conversion process, the converter implements direct connection and bulk insert technique.
  3. All versions of Oracle and PostgreSQL are supported including the variation of these database management systems and “software as a service” (SAAS).
  4. Oracle to Postgres migration tool provides command and line support for the purpose of automation.
  5. Existing PostgreSQL database with Oracle base is done by the converter which allows to merge and synchronize.
  6. Settings of conversion are stored in the profile.

Notwithstanding, the core feature provided by Oracle to Postgres migration tool which will definitely makes database migration more flexible and powerful is filtering data via SELECT-queries. Before converting it into PostgreSQL format, this feature allows you to select column and records for transformation or even for conversion.

  • Filter records:

select title, address from vendors where status=1

  • Skip NULL Value(s):

select first_name, last_name from people where birthday IS NOT NULL

  • Select and rename particular columns:

select f109 as bulk_price, f112 as retail_price from sales

  • Merge two tables:

select * from Students S INNER JOIN Advisors A ON S.Advisor_ID=A.Advisor_ID

Visit official product page to learn more about Oracle to Postgres migration tool provided by Intelligent Converters.