Oracle to Postgres Migration

Willing to decrease expenses of IT sector, many companies consider migrating their Oracle databases to Postgres. The main reason for this decision is that unlike Oracle PostgreSQL goes under generous license rights just like the BSD or MIT which is open source.

The process of relational database migration can be arranged through the following phases:

• Export Oracle tables,indexes, constraints and sequences as data definition language (DDL) statements
• Process those DDL statementswith correct type mapping, substitution of Oracle embedded functions that are not supported in PostgreSQL, etc
• Load the converted tabledefinitions into the target PostgreSQL database
• Extract datafrom the source Oracle database into CSV files
• Convert the dataproviding necessary transformation of binary, dates and escaping special symbols in text fields when it is required.
• Import theresulting CSV files into previously created PostgreSQL tables
• Export Oracle views, packages, storedprocedures, functions and triggers as SQL statements and source code
• Transform thesestatements and source code to comply with the PostgreSQL dialect of SQL
• Import itinto the destination database

The method briefly described above proves that Oracle to Postgres migration is quite difficult procedure requiring a lot of work. The worst thing is that the human errors can result into loss of data or data corruption when migrate manually. Therefore, it is reasonable to use exclusive software to avoid issues and automate the database migration.

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

Their tool for Oracle to Postgres migration provides the following generic features:

1. All primarydatabase entries are processed including table definitions, data, constraints, indexes, foreign keys
2. The converterimplements direct connection and bulk insert techniques in order to reach high performance of the database conversion process
3. It supportsall versions of Oracle and PostgreSQL including “software as a service” (SAAS) variations of these database management systems
4. For purpose ofautomation the converter provides command line support
5. The converterallows to merge and to synchronize existing PostgreSQL database with Oracle data
6. Conversion settingsare stored into profile

Apart from these primary features, this Oracle to Postgres migration tool offers this kind of convenient and robust option as filtering data via SELECT-queries. This capability allows administrators to specify individual columns and records for migration and carry out a modification of the data prior to transferring it into PostgreSQL format. A few examples on how the SELECT-queries can be used to achieve different goals are listed below:

• Filtering records: SELECT * FROM TheTable WHERE UniqueIDColumn > 1000
• Select andrename particular columns: SELECT clmn1 AS Name, clmn2 as Phone FROM MyTable
• Skip NULLvalues: SELECT * FROM TheTable WHERE Address2 IS NOT NULL
• Merge twotables: 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 Postgres migration tool comes with one-year subscription for updates and unlimited support.