Migrate database from Oracle to PostgreSQL

0
777

When comparing Oracle and PostgreSQL, it comes that the first database management system has more strict licensing terms and high total cost of ownership while PostgreSQL comes with liberal license and it is open source. This is the main reason why many organizations migrate their databases from Oracle to PostgreSQL.

Oracle to PostgreSQL database migration process is usually organized according to extract-transform-load (ETL) concept and could be considered as sequence of the following steps:

  • Oracle schemas are exported in form of data definition language (DDL) statements
  • Those DDL statements are converted into PostgreSQL format with correct type mapping, replacement of Oracle specific functions and operators that are not supported in PostgreSQL
  • The resulting script is imported into the target database
  • Oracle data is exported into comma separated values (CSV) format as an intermediate storage
  • The data is converted according to PostgreSQL format with respect to transformation of binary data, dates and escaping special symbols in text fields
  • The resulting CSV files are imported into PostgreSQL database
  • Oracle views, stored procedures and triggers are exported into SQL statements and source code
  • These statements and source code must be transformed according to PostgreSQL format and imported into the destination database

The algorithm specified above proves that Oracle to PostgreSQL database migration is a complicated tedious task that can be hardly implemented manually. This procedure takes many efforts especially for huge data warehouses and highly probable human errors may cause data loss or corruption. It is reasonable to use special software solutions to simplify database migration and eliminate the related risks.

There are multiple products to automate migration of Oracle database to PostgreSQL available on the market. One of such solutions is Oracle to PostgreSQL tool provided by Intelligent Converters, a company specializing on database migration since 2001.

Product Features

  1. The product migrates table definitions, data, constraints, indexes, foreign keys with all necessary attributes
  2. High performance of the database migration process due to direct low-level connection to Oracle and PostgreSQL servers and bulk insert techniques
  3. All modern versions of Oracle and PostgreSQL are supported including forks and SAAS variations of both database management systems
  4. Oracle to PostgreSQL converter supports command line for automation purpose
  5. Existing PostgreSQL database can be overwritten, merged or synchronized with Oracle data
  6. The product stores conversion settings into profile to simplify next runs

As extra advantage Oracle to PostgreSQL migration tool offers filtering data via SELECT-queries. This powerful and flexible feature allows to select particular columns and rows for migration and to transform the data before importing it into PostgreSQL format. Here are examples of using SELECT-queries for miscellaneous purposes.

  1. Filtering records: SELECT id, fname, lname FROM people WHERE birthdate > to_date(’01-JAN-90’,’DD-MON-YY’)
  2. Select and rename particular columns: SELECT f1 as “First Name” FROM tbl1
  3. Skip NULL values: SELECT first_name, last_name FROM employee WHERE company IS NOT NULL
  4. Merge two tables:

SELECT suppliers.id, suppliers.name, orders.date

FROM suppliers INNER JOIN orders ON suppliers.id = orders.supplier_id;

Just like any software product provided by Intelligent Converters, Oracle to PostgreSQL is supplied with unlimited support and 1-year subscription for updates.

Leave a reply