November 26, 2022

Convert Oracle Database to PostgreSQL

Oracle is a very sophisticated and powerful object-relational DBMS which is capable of handling enormous enterprise-scale databases. However, great weakness of that database management system is high cost of the ownership due to tough licensing terms. This disadvantage leads many clients to database migration from Oracle to other database management systems.

Why you should pick PostgreSQL over Oracle?

DBA or another person responsible for migration should always remember that a new system should provide similar features before moving to other DBMS. Obviously, no other system can have all the powerful features of Oracle database such as:

  1. An extensive backup
  2. A multi-level compression
  3. A totally flexible storage customization

However, the capabilities of PostgreSQL are much closer to Oracle than other DBMSs since it also combines object-oriented and relational features and provides some other advanced features:

  1. asynchronous replication
  2. multi-version concurrency control system
  3. nested transactions
  4. point-in-time recovery
  5. the extremely sophisticated locking mechanism

The considerations above make PostgreSQL ideal choice for most complicated database projects which demand high performance and data integrity. This is the reason why many database specialists consider PostgreSQL as best alternative to Oracle.

Migration from Oracle to PostgreSQL

Database migration from Oracle to PostgreSQL is usually occurred according to the extract-transform-load (ETL) approach that involves the following steps:

  1. Export Oracle database table definitions to “CREATE TABLE” statements
  2. Ensure that the SQL statements are compatible with PostgreSQL format and import it to the destination server
  3. Export the Oracle data into intermediate storage like CSV files
  4. Convert it to the target format (if needed) and then import in PostgreSQL
  5. Export the Oracle views, triggers, stored procedures and the functions into SQL statements and plain text source code
  6. Transform all the statements and code as per PostgreSQL syntax and then load to the target server

Table Definitions

For all examples of Oracle statements and queries SQL*Plus is used as default SQL command line client. It is connected to the Oracle database as follows:

sqlplus username/password@database

All Oracle tables available for the specified user are listed as follows:

SQL> select table_name from user_tables;

Oracle table definition is explored via these SQL statements:

SQL> set long 1000

SQL> set pagesize 0

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

Before loading into PostgreSQL database, the resulting data definition language (DDL) script must be corrected as follows:

  • Oracle specific statements at the end of table DDL must be removed (starting straight from “USING INDEX PCTFREE…”) as it is not supported by PostgreSQL
  • Convert every data types into PostgreSQL equivalents. You can find safe mappings for basic¬†types below
Oracle PostgreSQL
BFILE VARCHAR(255)
BINARY_FLOAT REAL
BINARY_DOUBLE DOUBLE PRECISION
BLOB BYTEA
CHAR(n), CHARACTER(n) CHAR(n), CHARACTER(n)
CLOB TEXT
DATE TIMESTAMP
DECIMAL(p,s), DEC(p,s) DECIMAL(p,s), DEC(p,s)
DOUBLE PRECISION DOUBLE PRECISION
FLOAT(p) DOUBLE PRECISION
INT, INTEGER INT, INTEGER
LONG TEXT
LONG RAW BYTEA
NCHAR(n) CHAR(n)
NCHAR VARYING(n) VARCHAR(n)
NCLOB TEXT
NUMBER(p,0), NUMBER(p), 1 <= p < 5 SMALLINT
NUMBER(p,0), NUMBER(p), 5 <= p < 9 INT
NUMBER(p,0), NUMBER(p), 9 <= p < 19 BIGINT
NUMBER(p,0), NUMBER(p), p >= 19 DECIMAL(p)
NUMBER(p,s) DECIMAL(p,s)
NUMBER, NUMBER(*) DOUBLE PRECISION
NUMERIC(p,s) NUMERIC(p,s)
NVARCHAR2(n) VARCHAR(n)
RAW(n) BYTEA
REAL DOUBLE PRECISION
ROWID CHAR(10)
SMALLINT SMALLINT
TIMESTAMP(p) TIMESTAMP(p)
TIMESTAMP(p) WITH TIME ZONE TIMESTAMP(p) WITH TIME ZONE
VARCHAR(n) VARCHAR(n)
VARCHAR2(n) VARCHAR(n)
XMLTYPE XML

Data export

The source Oracle data can be exported into a comma separate values (CSV) format via the following sequence of SQL 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 loaded into the destination PostgreSQL table via the “COPY” statement:

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

If you encounter “Permission denied” error after you ran this statement, you can try out this common “\COPY”.

The Indexes

Use the following SQL statement to get all indexes that belongs to Oracle table “mytable”:

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

Do not forget that Oracle treats all database object names in upper case by default. Lower case and other case sensitive options may be required by enclosing object name in quotes in SQL statements.

Definition of particular Oracle index can be extracted via the following SQL statements:

SQL> set long 1000

SQL> set pagesize 0

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

Conversion Tools for Oracle to PostgreSQL Migration

From this brief description of database migration from Oracle to PostgreSQL you may see that it is a very sophisticated procedure. Manual migration requires a lot of efforts and it is connected with risk of data loss or corruption due to human factor. Another approach eliminating all specified difficulties and risks is to use special software tools that can smoothly migrate your database from Oracle to PostgreSQL with a click of a few mouse buttons. Make sure the migration tool you choose supports generic database objects:

  1. Table definitions
  2. Data
  3. Indexes and constraints
  4. Foreign keys
  5. Views

Leave a Reply