Oracle to PostgreSQL Migration

Oracle is widely recognized as a powerful object-relational database management system capable of handling large and complex enterprise-scale data warehouses and applications. However, it comes with certain disadvantages such as high total cost of ownership and strict licensing policies. These drawbacks have led many companies and organizations to consider migrating from Oracle to alternative systems and platforms.

Why choose PostgreSQL?

When selecting a new database system for migration, it is important to ensure that it offers a similar range of features compared to the original DBMS. While no other database can match Oracle’s extensive feature set, PostgreSQL comes closest as a powerful, standards-compliant database management system that combines object-oriented and relational database functionality. Here are some of the superior features offered by PostgreSQL:

Asynchronous replication
Multi-version concurrency control
Nested transactions
Point-in-time recovery
Sophisticated locking mechanism

These advantages make PostgreSQL an excellent solution for projects that require high power, reliability, and data integrity, making it a compelling replacement for Oracle.

Oracle to PostgreSQL Migration Process

The process of Oracle to PostgreSQL migration typically involves the following steps:

Exporting Oracle table definitions as “CREATE TABLE” SQL commands.
Modifying these SQL commands to fit the PostgreSQL format and then loading them into the target server.
Exporting Oracle data into an intermediate storage format, such as CSV files.
Converting the CSV files into the target format (if necessary) and importing them into the PostgreSQL database.
Exporting Oracle views, triggers, stored procedures, and processes into the appropriate SQL statements and plain text source code.
Transforming these statements and code according to PostgreSQL syntax and loading them into the target server.

Table Definitions. The process begins by extracting the Oracle table definitions using SQLPlus, the default Oracle client application. Here is an example command to connect to the database via SQLPlus:

sqlplus username/password@database

To obtain a list of all tables, execute the following SQL query:

SQL> select table_name from user_tables;

To extract the definition of a specific Oracle table, use the following SQL query:

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 it into PostgreSQL. This involves removing Oracle-specific statements at the end of the table DDL and converting all data types into their PostgreSQL equivalents.

Data. Oracle data needs to be exported into CSV format using SQL queries. For example:

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;

PostgreSQL can import the resulting CSV file into a table using the “COPY” command:

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

Indexes and Views. To extract the list of all indexes belonging to a specific table in Oracle, execute the following SQL query:

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

For extracting the definition of particular indexes, use the following SQL query:

SQL> set long 1000 SQL> set pagesize 0 SQL> select DBMS_METADATA.GET_DDL(‘INDEX’,'<INDEX NAME>’) from DUAL;

To obtain a list of all views in the Oracle database, use the following query:

SELECT VIEW_NAME, TEXT from SYS.USER_VIEWS;

The resulting queries and views must be converted to the destination format. This includes removing specific Oracle keywords that are not supported by PostgreSQL and replacing any embedded Oracle functions with their PostgreSQL equivalents.

Oracle to PostgreSQL Migration Tools

The steps explained above can indicate that Oracle to PostgreSQL migration is really a hard and tedious procedure. Running the migration manually requires must time and efforts, also there is risk of data loss or corruption as a result of human errors. It’s reasonable to consider special Oracle to PostgreSQL migration tools to simplify and automate it. Below two popular solutions for automating Oracle to PostgreSQL migration are introduced.

Ora2Pg

Ora2Pg is a free open-source Perl script designed to facilitate the migration of Oracle DDL statements to PostgreSQL format. This tool offers the capability to export the entire database schema, encompassing tables, views, sequences, and indexes, while preserving the relevant attributes. Ora2Pg efficiently converts Oracle BLOBs into PostgreSQL bytea format and seamlessly transforms Oracle views into regular PostgreSQL tables. Furthermore, it effectively manages sequences and user-defined types, ensuring a smooth and comprehensive migration process.

Pros of Ora2Pg

Free and Open Source: Ora2Pg is freely available and open-source software, which means it can be accessed and used without any licensing costs.
Comprehensive Migration: Ora2Pg supports the migration of various database objects, including tables, views, sequences, indexes, BLOBs, and user-defined types. It offers a comprehensive solution for transferring the database schema from Oracle to PostgreSQL.
Schema Customization: The tool allows for customization during the migration process. Users can modify the schema objects, attributes, and settings to align with their specific requirements in the target PostgreSQL database.
Performance Optimization: Ora2Pg offers optimization options to enhance the performance of the migration process. Users can configure parameters and settings to maximize the efficiency and speed of the data transfer.
Active Community: Ora2Pg benefits from an active and supportive community of users and developers. This means that users can find help, resources, and updates through community forums, discussions, and documentation.

Cons of Ora2Pg

Complexity for Non-Technical Users: Ora2Pg is a command-line tool that operates through a Perl script. It requires a certain level of technical knowledge and familiarity with command-line interfaces, which might be challenging for non-technical users or those less experienced with database migrations.
Potential for Data Loss or Corruption: As with any database migration tool, there is a risk of data loss or corruption during the migration process. It is crucial to thoroughly test and validate the migrated data to ensure its integrity and accuracy.
Limited Support for Advanced Oracle Features: While Ora2Pg covers a wide range of Oracle database features, it may not fully support some advanced or proprietary Oracle functionalities. Users migrating from Oracle to PostgreSQL should carefully assess the compatibility of their specific Oracle features with Ora2Pg.
Learning Curve: Due to its command-line nature and configuration options, there may be a learning curve associated with using Ora2Pg effectively. Users might need to invest time in understanding the tool’s functionalities and options to optimize the migration process.
Lack of Graphical User Interface (GUI): Ora2Pg primarily operates through the command-line interface, which may not be as user-friendly for those who prefer a graphical user interface (GUI) for managing database migrations. However, there are some third-party GUI tools available that integrate with Ora2Pg to provide a more visual experience.

Commercial tools for Oracle to PostgreSQL Migration

For those cases where Ora2Pg capabilities are not enough, users should consider commercial tools designed to automate Oracle to PostgreSQL migration with only a couple of clicks of mouse button. Intelligent Converters has developed a tool called Oracle to PostgreSQL converter, which provides a convenient solution when Ora2Pg’s capabilities are insufficient or when a direct migration from the source database server to the target server is required. This tool offers the following features for Oracle to PostgreSQL migration:

The tool migrates the structure and definitions of tables from Oracle to PostgreSQL, ensuring the integrity of the database schema.
It efficiently transfers the data stored in Oracle tables to corresponding tables in PostgreSQL, ensuring the accuracy of the data during the migration.
The tool migrates indexes and constraints defined in Oracle tables, ensuring the consistency and integrity of the data in PostgreSQL.
It handles the migration of foreign key relationships between tables, maintaining data relationships in the target PostgreSQL database.
The tool can migrate Oracle views as regular PostgreSQL tables, preserving the logic and functionality of the original views.

Additionally, the converter provides the capability to migrate the result of a SELECT-query as a regular table. This feature allows users to filter data, rename columns or tables, or merge multiple tables into a single table in the destination database during Oracle to PostgreSQL migration.

The tool also offers basic synchronization capabilities. It combines the insertion of Oracle records that are missing in the PostgreSQL table with the update of existing PostgreSQL records using Oracle data. To enable synchronization, it is necessary for the source and destination tables to have matching structures and have a primary key or unique index defined for synchronization purposes.

By utilizing the converter, users can streamline and automate the Oracle to PostgreSQL migration, as well as reducing manual efforts and ensuring data accuracy in the target database.