Migrate from Oracle to PostgreSQL

Posted on by Munaf Chatterjee

Every database specialist can recognize Oracle and PostgreSQL as very popular database management systems supplied with wide range of features and tools. However, when compare these two DBMS it comes that Oracle has more strict licensing terms and high total cost of ownership while PostgreSQL goes under liberal license similar to BSD or MIT and it is open source. This is the reason why many companies plan to migrate their databases from Oracle to PostgreSQL.

The process of Oracle to PostgreSQL database migration may be considered as sequence of these steps:

  • Export Oracle table definitions in form of data definition language (DDL) statements
  • Convert DDL statements into PostgreSQL format including correct type mapping, substitution of Oracle embedded functions that are not supported in PostgreSQL, etc
  • Load the convertedtable definitions into PostgreSQL database
  • Export Oracle data from the source database into an intermediate storage in comma separated values (CSV) format
  • Convert the data in order to comply with PostgreSQL format providing necessary transformation of binary data, dates and escaping special symbols in text fields when it is required.
  • Import the resulting CSV files into the target database
  • Export Oracle views, stored procedures and triggers in form of SQL statements and source code
  • Transform these statements and source code according to the destination forma
  • Import it into the PostgreSQLdatabase

The steps listed above are proving that it is not easy to convert database from Oracle to PostgreSQL manually. This tedious procedure takes many efforts and the human factor may cause data loss or corruption. In order to automate database migration and avoid the related risks,special software may be used.

There are few software companies providing solution to automate database migration from Oracle to PostgreSQL. One of such solutions is Oracle to PostgreSQL conversion tool developed by Intelligent Converters, a software company working in database migration field since 2001.

Product Features

  1. The following database objects are converted: table definitions, data, constraints, indexes, foreign keys
  2. The converter implements direct connection and bulk insert techniques in order to reach high performance of the database conversion process
  3. It supports all versions of Oracle and PostgreSQL including “software as a service” (SAAS) variations of these database management systems
  4. For purpose of automation Oracle to PostgreSQL converter provides command line support
  5. The converter allows to merge and to synchronize existing PostgreSQL database with Oracle data
  6. Conversion settings are stored into profile

Besides these basic features Oracle to PostgreSQL converter provides such flexible and powerful option as filtering data via SELECT-queries. This feature allows to select columns and records for conversion or even to make some transformations of the data before converting it into PostgreSQL format. See below a few examples of using SELECT-queries for different purposes.

  • Filtering records:

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

  • Select and rename particular columns:

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

  • Skip NULL values: SELECT * FROM MyTable WHERE Comments IS NOT NULL
  • Merge two tables:

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

FROM Products T1

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

As every product of Intelligent Converters, Oracle to PostgreSQL database migration tool is supplied with unlimited support and 1-year subscription for updates.

 

Related Posts