Transfer Database From Oracle To Postgresql

Philip Williams

Oracle and PostgreSQL database can be identified by all professional database administrator as one of the popular database administrative programs provided with great deal of features and tools. On the other hand, when the two DBMS are compared, the Oracle stands out as the DBMS that possesses tight licensing terms and also, the cost of acquiring the license ownership is very high. The PostgreSQL on the other hand goes under generous license rights just like the BSD or MIT which is open source. Because of this, many companies plan to transfer their databases from Oracle to PostgreSQL.

The process of transferring Oracle to PostgreSQL database can be are as follows: 

  • Export Oracle table information as 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
  • Import the resulting table definitions into the target 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 PostgreSQL database

The steps mentioned above indicates that it is a bit difficult to manually transform Oracle database into PostgreSQL format. This difficult process requires a lot of work also, the human factor can result into loss of data or data corruption. However, to avoid issues when automating database migration, exclusive software can be employed. 

Companies that provide solutions in the area of database migration from Oracle to PostgreSQL are very few. The Intelligent Converters happen to be one of those organizations that provides conversion tools that handles the migration of Oracle to PostgreSQL, and has since being in business since 2001.

The Oracle to PostgreSQL Conversion Tools Product Features Includes:

  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

Apart from these primary features, Oracle to PostgreSQL converter offers this kind of convenient and robust option as filtering data via SELECT-queries. This feature enables administrators to select columns and records for conversion or perhaps to carry out a modification of the data prior to converting it into PostgreSQL format. A few examples on how the SELECT-queries can be used to achieve different goals are listed below:

  1. Filtering records: SELECT * FROM TheTable WHERE UniqueIDColumn > 1000

  1. Select and rename particular columns: SELECT clmn1 AS Name, clmn2 as Phone FROM MyTable

  1. Skip NULL values: SELECT * FROM TheTable WHERE Address2 IS NOT NULL

  1. Merge two tables: SELECT code,  title, properties FROM T1 Products INNER JOIN T2 ProductLines ON T1.line_id = T2.id;

As with other products developed by Intelligent Converters, the Oracle to PostgreSQL database tool comes with one (1) year subscription for updates and unlimited support.

Related Posts