teachera

Oracle to PostgreSQL: introduction to migration

Being powerful object-relational database management system, Oraclecanmanageverysophisticatedenterprise-scaledata warehouses. It is also supplied with huge collection of administration tools and developer frameworks. However, such disadvantages of the DBMS arehigh total cost ofownershipandstrictlicensingpolicyforcesmanyorganizationsto migrate Oracle databases to differentsystems and platforms. 

Why choose PostgreSQL?

When planning migration, the person who manages the procedureexpectsthatnewsystemhascapabilitiessimilarto the original DBMS. It is hard to find an alternative toOracle database due to its unique powerfulfeatures.Among all advanced database management systems PostgreSQL with its mixes object-oriented and relational databasefunctionalitygetsmuchcloser to Oracle than others. Here are the main of PostgreSQLpowerful features: 

  • full compliance with ANSI SQL standard 
  • multi-version concurrency control
  • asynchronous replication
  • nested transactions
  • point-in-time recovery
  • sophisticated locking mechanism

Thoseadvanced benefits make PostgreSQLideal choiceformanaging powerful sophisticated databasesi.e. the most effectivealternative for Oracle. 

Migrate from Oracle to PostgreSQL

The procedure of Oracle to PostgreSQL database migration consists of the following steps: 

  • extract Oracle tabledefinitionsas “CREATE TABLE” SQL commands, convert these statement into PostgreSQL formatand importto thetargetdatabase
  • export Oracle dataintointermediate CSV files, convert itinto thetargetformat (when required) andload to PostgreSQL database
  • extract Oracle views, triggers, storedprocedures as SQL statementsandsourcecode; transform it according to PostgreSQL syntaxand loadto thetargetdatabase

Let us explore the steps listed above in details.

TableDefinitions

This is how to get list of all tables in Oracle: 

SQL> select table_name from user_tables;

The definition of particular Oracle table is extracted through these statements:

SQL> set long 1000

SQL> set pagesize 0

SQL> select DBMS_METADATA.GET_DDL(‘TABLE’,'<TABLE NAME>'[,’SCHEMA’]) from DUAL

The outputdata definition (DDL) script must be manually processed before loading to PostgreSQL: 

  • Oracle specific statements at the end of each table definitionmust be removed
  • Oracle data types must be translated into PostgreSQL equivalents

Data

This is how to export Oracle data into CSV format: 

SQL> set heading off

SQL> spool filename.csv

SQL> select column1 || ‘,’ || column2 || … from table1;

SQL> set colsep ‘,’

SQL> select * from table1;

SQL> spool off;

PostgreSQL can load the output CSV file via “COPY” command: 

COPY <table_name> FROM csv_file> DELIMITER ‘,’ CSV;

If you experience “Permission denied” error, try using “\COPY” command. 

Indexes

Oracle explores all indexes that belong to particular table through this query: 

SQL> select * from all_indexes where table_name = ‘<TABLE NAME>’;

Keep in mind that Oracle table are case sensitive. The definition of particular index can be extracted via these statements: 

SQL> set long 1000

SQL> set pagesize 0

SQL> select DBMS_METADATA.GET_DDL(‘INDEX’,'<INDEX NAME>’) from DUAL;

Views

Oracle exposes the list of all viewsthrough this query:

select VIEW_NAME, TEXT from SYS.USER_VIEWS;

Then definition (SQL query) of every view must be converted in PostgreSQL format by removing specific Oracle keywords that are not supported by the target DBMS and replacing all built-in functionsby the appropriate equivalents.

Oracle to PostgreSQL Conversion Tools

The steps described aboveillustrate thatdatabase migration from Oracle to PostgreSQL is quite sophisticatedprocedure. Running migration manually requires alot ofeffortsand also implies high risk of losing data integrity or database logic due to humanerrors. This is the reason why usingspecialtoolsto automate migration process makes sense. 

Intelligent Converters, a software company focusing database migration and synchronization, provides one of suchtools-Oracle to PostgreSQL converter. The productautomates migration of maindatabaseentries: 

  • Schema
  • Data
  • Indexes andforeign keys
  • Views