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 systemishigh cost of theownership due to tough licensing terms. This disadvantage leads many clients to database migrationfrom 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 powerfulfeatures of Oracle database such as:
- An extensivebackup
- A multi-levelcompression
- A totally flexiblestoragecustomisation
However, capabilities of PostgreSQL are much closer to Oracle than other DBMS since it also combines object-oriented and relational features and providessome other advanced features:
- asynchronousreplication
- multi-version concurrency control system
- nestedtransactions
- point-in-timerecovery
- extremely sophisticatedlockingmechanism
The considerations abovemake PostgreSQL ideal choicefor 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
Databasemigration from Oracle to PostgreSQL is usually occurred according to extract-transform-load (ETL) approach that involves the followingsteps:
- Export Oracle database table definitions to “CREATE TABLE” statements
- Ensure that the SQL-statements are compatible with PostgreSQL format and import itto the destination server
- Export the Oracle data into an intermediate storage like CSV files
- Convert it to the target format (if needed) and then import in PostgreSQL
- Export the Oracle views, triggers, stored procedures and the functions into SQL statements and plain text source code
- Transform all the statements and code as per PostgreSQL syntax and then load to the target server
Table Definitions
Use this statement to connect the Oracle database via SQL*Plus:-
sqlplus username/password@database
Now we need to get list of every tables as follows:
SQL> select table_name from user_tables;
The definition of particular Oracle table can be extracted through sequence of SQL statements as follows:
SQL> set long 1000
SQL> set pagesize 0
SQL> select DBMS_METADATA.GET_DDL(‘TABLE’,'<TABLE NAME>'[,’SCHEMA’]) from DUAL
Before loading into PostgreSQLdatabase, the resulting data definition language (DDL) script mustbe 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 allindexes thatbelongs to Oracletable “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 Oracleindex 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 migrateyour database from Oracle to PostgreSQL with a click of a few mouse buttons. Make sure the migration tool you choosesupports generic database objects:
- Tabledefinitions
- Data
- Indexesandconstraints
- Foreign keys
- Views
