Migrate database from Oracle to PostgreSQL

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 migrating Oracle database to PostgreSQL server basically consists of these steps according to ETL approach: 

• • Structure of every Oracle table is extracted in form of data definition language (DDL) statements
  • Those DDL statements are translated according to PostgreSQL syntax with the appropriate type mapping, conversion of Oracle embedded functions that are not supported in PostgreSQL, and then are imported into the target database
  • Oracle data is exported into comma separated values (CSV) format as an intermediate storage
  • Make necessary transformations over CSV files to make the data complied with the target format and load it into PostgreSQL database

 

• Oracle views, stored procedures and triggers must be extracted in form of SQL CREATE-statements and source code, translated into PostgreSQL format and loaded into the target database

 

The steps listed above are proving that it is not easy to convert Oracle database into PostgreSQL format 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 developed by Intelligent Converters, a software vendor working in database migration field since 2001. 

Product Features

1. Table definitions, data, constraints, indexes, foreign keys and simple views are converted
2. Direct connection to source and destination databases and bulk insert technique guarantee high performance of the migration process
3. All versions of Oracle and PostgreSQL are supported including cloud variations and such fork as Heroku
4. Command line version of the application allows automate Oracle to PostgreSQL migration 
5. The converter provides overwrite, merge and synchronize modes to process existing PostgreSQL database 
6. Conversion settings are stored into profile
7. Multi-byte codepages are supported 

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.

1. Filtering records: 

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

1. Select and rename particular columns: 

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

1. Skip NULL values: SELECT * FROM MyTable WHERE Comments IS NOT NULL
2. 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.