Oracle to PostgreSQL database migration

When comparing Oracle and PostgreSQL, every specialist recognizes both DBMS as powerful object-relational database systems enriched with wide range of development and administration frameworks. Nevertheless,many organizations migrate their databases from Oracle to PostgreSQL in order to reduce total cost of ownership and to avoid strict licensing policy.

PostgreSQL is considered as best alternative to Oracle due to similar set of advanced features that are not fully supported by other DBMS:

  • sophisticated mechanism of locking rows
  • point-in-time recovery
  • nested transactions
  • both synchronous or asynchronous replication
  • multi-version concurrency control

Due to this fact migration from Oracle to PostgreSQL is reasonable especially for complicated projects that require high reliability, security and efficiency. In most cases the following database objects must be process during migration process: schemas, data, indexes, constraints, permissions, triggers, views, stored procedures and functions. Corporate-scale data warehouses may also require extra migration efforts for external applications.

Oracle to PostgreSQL database migration includes these generic steps conforming with famous extract-transform-load (ETL) approach:

  • export Oracle schemas, table definitions, indexes, constraints, user-defined types and other related database entities into the appropriate SQL-statements
  • modify these SQL-statements in order to comply with PostgreSQL format and load to the destination database
  • export Oracle data into comma separated values files as the most suitable intermediate storage
  • process CSV files if it is necessary and import into previously created PostgreSQL tables
  • export Oracle stored procedures, functions, triggers and views into SQL code
  • rewrite the code according to PostgreSQL syntax and load it to the destination database

The steps listed above prove that database migration from Oracle to PostgreSQL is quite sophisticated process requiring lot of resources. Doing it manually may cause errors or data loss due to human factor. That’s why experienced specialists use special tools to migrate database from Oracle to PostgreSQL in automated mode and to get error free result.

One of such tools is provided by Intelligent Converters, a leading company in database migration and synchronization field. Their Oracle to PostgreSQL converter supports all versions of source and destination DBMS.The following database objects are processed:

  • Table definitions
  • Data
  • Indexes and constraints
  • Foreign keys
  • Views

Command line version of the product can be used for scripting and scheduling the migration procedure. Oracle to PostgreSQL converter can export the source database into SQL-script instead of migrating to the target server directly. This feature can assist in those cases when direct connection to PostgreSQL server is not available. For these cases, database administrator should migrate Oracle database into script containing DDL and DML statements compatible with PostgreSQL, transfer the resulting file to the target server via SSH client or similar tools and then import it into PostgreSQL database locally.

Visit official product page to learn more about Oracle to PostgreSQL converter: https://www.convert-in.com/ora2pgs.htm