Thursday, September 21, 2017
Home > Technology > The Simplest Way To Migrate Oracle Database To PostgreSQL

The Simplest Way To Migrate Oracle Database To PostgreSQL

Oracle is a fantastic object-relational DBMS suitable for taking care of large enterprise scale databases. On the other hand, it is costly to acquire the ownership. It also includes a tough licensing policy. This drawback forces some companies and businesses to transfer their databases from Oracle to yet another DBMS.

Why you should Consider PostgreSQL?

Database administrator as well as other person in charge of database movement need to look forward to that new system that provides equivalent set of services when compared to the primary DBMS. When talking about Oracle database, it’s apparent that no other relational database management systems is so filled with powerful properties including:

  • extensive backup
  • multi-level compression
  • flexible storage customization

As a powerful and standard-certified RDBMS that incorporates object-oriented and relational database functions, PostgreSQL becomes a lot closer to Oracle than other programs. Here is a short list of its advanced features:

  • asynchronous replication
  • multi-version concurrency control
  • nested transactions
  • point-in-time recovery
  • sophisticated locking mechanism

Those advantages make PostgreSQL a fantastic solution for complex projects demanding high dependability and data integrity i.e. the best option to Oracle database.

Oracle to PostgreSQL Migration

The process of converting Oracle to PostgreSQL includes all of the following instructions:

  • export Oracle table definitions into “CREATE TABLE” statements
  • make these SQL-instructions conformed with PostgreSQL format and load to the target server
  • export Oracle data into intermediate storage such as CSV files
  • convert it into the target format (if necessary) and import in PostgreSQL database
  • export Oracle views, triggers, stored procedures and functions into SQL statements and plain text source code
  • transform these statements and code according to PostgreSQL syntax and load to the target server

Table classifications

Underneath this category, we take into account the basic procedures require to carry out this process. Anywhere below SQL*Plus is used as default Oracle client application. This is the command line to connect to the database via SQL*Plus:

sqlplus username/password@database

First, it is necessary to get list of all tables:

SQL> select table_name from user_tables;

Here is the query to extract definition (DDL) of the particular Oracle table:

SQL> set long 1000

SQL> set pagesize 0

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

The resulting script must be corrected before loading to PostgreSQL as follows:

  • remove Oracle specific statements at the end of table DDL (starting from “USING INDEX PCTFREE…”)
  • convert all data types into PostgreSQL equivalents according to this table

Data

Next step is to export Oracle data into CSV format. It can be done via the following sequence of commands:

SQL> set heading off

SQL> spool filename.csv

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

SQL> set colsep ‘,’

SQL> select * from my_table;

SQL> spool off;

The resulting CSV file can be imported into PostgreSQL table via “COPY” command:

COPY <table name> FROM <path to csv file> DELIMITER ‘,’ CSV;

If there is “Permission denied” error after running this statement, try to use “\COPY” command instead.

Indexes

Let’s get list of all indexes that belong to table “mytable”:

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

Note, that Oracle stores all database object names as upper case by default unless lower case was specified directly by enclosing table name in quotes in “CREATE TABLE” statement.

And this is how definition of particular indexes can be extracted:

SQL> set long 1000

SQL> set pagesize 0

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

Oracle to PostgreSQL Transformation Tools

The techniques described above indicate that moving Oracle database to PostgreSQL is a complex process. Manual migration needs a great deal of work and can also cause data loss or corruption as a result of human factor. Nevertheless, It is justifiable to take some special migration tools into consideration to compliment the migration of database from Oracle to PostgreSQL with only a few clicks of the mouse. Intelligent Converters made the tool for same purpose – Oracle to PostgreSQL converter. It can automate movement of the following database objects:

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

Leave a Reply

Your email address will not be published. Required fields are marked *