Migrate Oracle Database to PostgreSQL: 2 Easy Methods

Most modern businesses today make use of a wide variety of databases and Data Warehouses to run their day-to-day operations. Companies feel the need to use multiple databases because there is no single database that can fulfill all their operational and analytical requirements. Two of the most widely used databases are Oracle and PostgreSQL.

Oracle is used by businesses to run their operations, and PostgreSQL is primarily used for analytical purposes. This article will provide you with a comprehensive understanding of how you can set up Oracle to PostgreSQL Migration to analyze the operational data of your business seamlessly.

What is Oracle Database?

Oracle Database, often called Oracle DBMS or Oracle Autonomous Database, is a robust and widely adopted relational database management system (RDBMS) developed and licensed by Oracle Corporation.

As a multi-model database system, Oracle Database excels in storing, organizing, and retrieving data efficiently. It is extensively utilized for online transaction processing (OLTP), data warehousing (DW), and handling mixed workloads that combine OLTP and DW operations. Oracle Database is renowned for its reliability, scalability, and advanced feature set, making it one of the most widely used relational database engines globally.

What is PostgreSQL?

PostgreSQL, often shortened to Postgres, is a powerful open-source database management system known for its robust features, reliability, and scalability.

What sets PostgreSQL apart is its object-relational nature. It combines the strengths of both relational databases, which organize data in structured tables, and object-oriented programming, which allows modeling data using objects and classes. This unique approach provides greater flexibility and enables users to model complex data relationships more efficiently.

What is the Need for Oracle to Postgres Migration?

With its features, PostgreSQL provides some compelling benefits over Oracle. Below are the reasons why you should consider an Oracle to PostgreSQL migration.

1. PostgreSQL saves data costs: Postgres can save on data costs as it is open-source and free to install and use. Features like data partitioning and high availability, which may require additional payments in Oracle, come with Postgres at no extra cost.
2. PostgreSQL avoids vendor lock-in: Unlike Oracle, which is a proprietary database, Postgres’s open-source licensing and support from various public cloud providers eliminate the risk of vendor lock-in.
3. Avoid Expensive Oracle Licensing and Support: Oracle’s commercial database involves heavy licensing costs and paid support. In contrast, Postgres has an active developer community, providing free tweaks, patches, and updates. Commercial support for Postgres is also generally more affordable than Oracle support.
4. Free extensions from Postgres: Postgres offers thousands of free extensions and add-ons, enhancing database performance without incurring additional costs, unlike Oracle, where similar functionalities can quickly accumulate expenses.
5. Easy Application Programming with Postgres: Postgres, being open-source, allows developers to include header files in projects for direct access to its components, providing flexibility and ease in application programming.
6. Flexible Host-based Authentication with PostgreSQL: Postgres supports various authentication methods, offering more flexibility than Oracle’s integrated authentication system.
7. Postgres’ High Compatibility: Postgres is compatible with most RDBMS, including Oracle, making migration seamless. It also boasts excellent compatibility with various operating systems.
8. High Performance at Low Cost: Postgres allows the creation of an unlimited number of nodes in a read cluster, reducing the cost of reads to near zero. In contrast, Oracle incurs additional costs per node.
9. Unlimited Scalability: Postgres can scale almost infinitely, creating a virtually unlimited number of nodes in a read cluster, offering superior scalability compared to Oracle.
10. Frequent Updates and Bug Fixes: Unlike Oracle’s major releases every 2 to 4 years, Postgres releases updates and bug fixes approximately every three months, ensuring continuous improvement and addressing issues promptly.
11. Data Security: Postgres provides data security through role-based access control and free security authentications, while Oracle’s advanced security tools cost extra.

What is Database Migration?

Database migration involves relocating an organization’s data from one platform or location to another, including transitions from on-premise to the Cloud. This process encompasses moving data, stored procedures, and making necessary application changes. Key stages include data selection, preparation, extraction, ingestion, and transformation.

Organizations opt for database migration for several reasons:

1. Scalability and Efficiency: Migrating to the Cloud offers unlimited scalability and efficiency, reducing infrastructure and staffing expenses, and addresses increased data volumes that strain existing implementations.
2. Adapting to Change: When organizational requirements evolve, necessitating new operational use cases that the current database cannot efficiently handle, migration becomes crucial.
3. Legacy System Upgrade: Migrating from outdated legacy systems to modern platforms enhances software capabilities and ensures compatibility with the latest technologies.
4. Data Consolidation: Data migration enables the consolidation of multi-source data, making it accessible to various teams for analytics and data science purposes.
5. Continuous Improvement: Companies may migrate from a current database over time to cut expenses, enhance dependability, achieve scalability, or pursue other strategic goals.

What are the Methods to Set up Oracle to PostgreSQL Migration?

The two methods that can be used to set up Oracle to PostgreSQL Migration are as follows:

• Method 1: Using Hevo to Set up Oracle to PostgreSQL Migration
• Method 2: Manual Oracle to PostgreSQL Migration

Method 1: Using Hevo to Set up Oracle to PostgreSQL Migration

With integration with 150+ Data Sources (40+ free sources), Hevo helps you directly transfer data from Oracle and various other sources to PostgreSQL, Business Intelligence tools, Data Warehouses, or a destination of your choice in a completely hassle-free & automated manner.

Learn more about Hevo

Hevo is fully managed and completely automates the process of not only loading data from your desired source but also enriching the data and transforming it into an analysis-ready form without having to write a single line of code. Its fault-tolerant architecture ensures that the data is handled in a secure, consistent manner with zero data loss.

The following steps can be implemented to set up Oracle to PostgreSQL Migration using Hevo:

Step 1: Configure Oracle as your source

• Click PIPELINES in the Navigation Bar.
• Click + CREATE in the Pipelines List View.
• In the Select Source Type page, select Oracle as your source.
• Connect Hevo Data with Oracle by providing a unique name for your Pipeline along with information about your Oracle database such as its name, IP Address, Port Number, Username, Password, etc.

Step 2: Start Data Migration with PostgreSQL as your Destination

• Click DESTINATIONS in the Navigation Bar.
• Click + CREATE in the Destinations List View.
• In the Add Destination page, select PostgreSQL.
• Complete Oracle to PostgreSQL Migration by providing your PostgreSQL database credentials such as your authorized Username and Password, along with information about your Host IP Address and Port Number value. You will also need to provide a name for your database and a unique name for this destination.

SIGN UP HERE FOR A 14-DAY FREE TRIAL

Method 2: Manual Oracle to PostgreSQL Migration

The following steps can be implemented to manually set up Oracle to PostgreSQL Migration:

Step 1: The first step is to install the PostgreSQL ODBC driver in the system in which Oracle is installed.  In Linux, you can do this by executing the following command:

yum install postgresql93-odbc

The command will differ according to the flavor of your Linux. 

Create a file named odbc.ini and place it in your user folder. This file should contain the details of the target PostgreSQL database.

[ODBC Data Sources]
  TARGET_PG_LINK = PostgreSQL
[TARGET_PG_LINK]
  Debug = 1
  CommLog = 1
  ReadOnly = no
  Driver = /usr/pgsql-9.3/lib/psqlodbc.so
  Servername = <PostgreSQL_SERVER_IP>
  FetchBufferSize = 99
  Username = pg_user
  Password = pg_pass
  Port = 5432
  Database = pgdb
[Default]
  Driver = /usr/lib64/liboplodbcS.so.1

Step 2: You will now configure the heterogeneous services in Oracle. For this, you need to create three files with the extension .ora. You will begin by creating the first while which initializes the heterogeneous services. Navigate to /hs/admin in your Oracle installation directory and create a file named initdblink.ora with the following content:

 HS_FDS_CONNECT_INFO = TARGET_PG_LINK
 HS_FDS_TRACE_LEVEL = 0
 HS_FDS_SHAREABLE_NAME=/usr/pgsql-9.3/lib/psqlodbc.so
 set ODBCINI=/PATH_TO_ODBC_FILE/.odbc.ini

The important section is the part where you need to mention the ODBC information for the destination PostgreSQL installation. Ensure that you specify the same name in HS_FDS_CONNECT_INFO.

Step 3: The next step is to modify a file located in the /network/admin directory of your Oracle installation. Find the file tnsnames.ora and add details about the destination PostgreSQL instance.

TARGET_PG_LINK  =
  (DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=<ORACLE_HOSTNAME>)(PORT=<ORACLE_PORT>))
	(CONNECT_DATA=(SID=TARGET_PG_LINK))
	(HS=OK)
  )

Step 4: Locate the listener.ora file in the same directory and add the following details:

SID_LIST_<your_LISTENER_NAME>=
  (SID_LIST=
  	(SID_DESC=
     	(SID_NAME=TARGET_PG_LINK)
     	(ORACLE_HOME=<ORACLE_HOME_DIRECTORY>)
     	(PROGRAM=dg4odbc)
  	)
  )

Oracle listeners are responsible for listening for connections to other databases and handling them appropriately. The name of the configured listener can be found out from the listener.ora file. After changing the file, execute the below command to restart the listener.

lsnrctl reload <your_LISTENER_NAME>

Step 5: The final step is to start PostgreSQL and type the following statement to establish the connection:

Create database link TARGET_PG_LINK connect to "pg_user" identified by "pg_pass" using ‘TARGET_PG_LINK’;

If everything went well, you should now be able to execute queries in PostgreSQL like the one below.

select * from "target_table_name"@TARGET_PG_LINK;

What are the Limitations of Manual Oracle to PostgreSQL Migration?

The limitations of manually setting up Oracle to PostgreSQL Migration are as follows:

• Even though the user only has to make a few configuration changes in order to set up Oracle to PostgreSQL Migration, it is difficult to get this integration right without deep Oracle administration knowledge. This might lead to Oracle throwing errors because of multiple configuration parameters that vary across different versions.
• Even after getting the Oracle to PostgreSQL Migration right, the connection, in reality, has many constraints and rough edges with respect to the SQL queries that can be executed. You will run across problems with functions and data types that are not supported at source and target databases.

Related: If you’re looking to migrate your data from Oracle to MongoDB, discover our comprehensive guide on Oracle to MongoDB replication. Additionally, explore RDS Oracle PostgreSQL integration to learn efficient ways to integrate Oracle and PostgreSQL in your database environment.

Oracle to Postgres Migration Approaches

Oracle to PostgreSQL migration can be approached in three different ways. Let us look at them one by one.

Snapshot Replication

In snapshot replication, a copy of all the data and objects(full snapshot) is created and applied to the target database. During snapshot replication, write operations are not allowed on the source database. This approach to database replication is mostly useful when the data volume is small, the latest data copy source is not required, and the data change frequency is low. This method serves as the initial full data refresh for transactional and merge processes.

Advantages of Snapshot Replication

1. Data movement occurs in a single operation.
2. No challenges with Data Type.
3. Application access to the target database is immediate after the snapshot.
4. Easy management with no need for special settings.
5. READ ONLY users may access the source database in certain cases.

Disadvantages of Snapshot Replication

1. Application shutdown is necessary during the snapshot.
2. Reinitialization is required if the snapshot process is interrupted.

Snapshot – Parallel in ChunksReplication

The Parallel in Chunks snapshot approach involves dividing data objects into chunks and concurrently creating snapshots. Most tools support parallel snapshots, enabling processes to run simultaneously. Block-by-block snapshots can be generated in two ways: either per table or by partitioning a large table into smaller sets based on primary keys or unique row IDs. Implementing the parallel in chunks approach can significantly reduce the duration of snapshots and minimize downtime windows.

Advantages of Snapshot – Parallel in Chunks Replication:

1. Simultaneous data movement with reduced downtime.
2. Parallel loading of data, whether per table or a large table partitioned into smaller sets.

Disadvantages of Snapshot – Parallel in Chunks Replication:

1. Application downtime is still required, albeit less than the first approach.
2. Splitting large tables into smaller rows necessitate a mandatory primary key or unique row identifier.
3. Scripting is necessary to adopt the parallel approach.
4. Reinitialization is necessary in case the snapshot process is interrupted.

Change Data Capture (CDC) Replication

Change Data Capture (CDC) is a replication process designed to capture and transfer every change occurring in the source database to the target database in real time, ensuring data synchronization. This method is highly efficient and resource-efficient as it replicates only the altered rows. CDC is particularly well-suited for modern Cloud architectures, facilitating real-time analytics and data science applications.

6 Simple Stages for Oracle to PostgreSQL Migration

Oracle to PostgreSQL Migration consists of many stages, including managing indexes, structured and unstructured data, and stored manuals. The following steps explaining the different stages for Oracle to PostgreSQL Migration are listed below.

1) Basic Preparation 

It is essential to inform your team about the transition from Oracle to PostgreSQL Migration. Prepare the team, equip them with the required technical knowledge, and educate them about the benefits of PostgreSQL and its usage for collaboration, data governance, and seamless business continuity.

2) Assessment of the Environment 

Many times, companies decide to fasten the Oracle to PostgreSQL Migration and shift all the critical modules of the application, ignoring checking the compatibility and capabilities and assessing the environment. 

For smooth Database Migration, one should first stop using third-party applications offered by Oracle Database because they will not support open-source alternatives. Also, try to analyze the number of functions that you want to migrate from the Database and which need any update or correction to stay compatible with the new Database.

3) Planning and Solutions

This is a critical phase of Oracle to PostgreSQL Migration because many strategies are discussed, and decisions need to be finalized. Companies should consider all possible conflicts that may arise and find solutions to avoid any risks later. 

Companies should have detailed plans for PostgreSQL deployment options, bandwidth requirements, Data Migration strategies, and data transfer. Also, keep in mind that there should be as little downtime as possible. 

4) Picking the Suitable Tool

Manually completing the Oracle to PostgreSQL Migration can be tedious if it involves many Databases and complex data. Leveraging automation tools will significantly reduce the time needed to complete the task and help maintain the Database after deployment.

5) Migration process

With the help of specialized tools, you can connect to the Oracle Database so that you can scan and extract the structured data and generate scripts from it. These scripts can then be fed to PostgreSQL. This Oracle to PostgreSQL Migration process involves executing tasks, schema migration, SQL migration, and identifying functions equivalent to Oracle.

• Schema Migration: It enables users to create many independent schemas without the need for separate users and create objects in them. 
• Functional Testing: After a successful schema conversion and performing an Oracle to PostgreSQL Migration, functional testing is required. You can also test the schema in both Databases and compare the results for similarities.
• Performance Testing: The transactions and features of Oracle and PostgreSQL are different that require performing performance testing so that the differences can be tracked, locked, and fixed.
• Data Migration: With the help of the right tool and strategy used to perform Data Migration can solve and avoid many challenges.

6) Expect Business Effects

You need to make sure that Oracle to PostgreSQL Migration process doesn’t disrupt the business activities hence minimizing the time in migrating the data. Also, it is essential to estimate the downtime tolerance during migration and track the operational changes after integrating PostgreSQL with other applications, features, etc.

Oracle to Postgres Safe Type Mapping

The following table demonstrates Oracle to Postgres safe type mapping:

Oracle	PostgreSQL
BINARY_FLOAT	REAL
BINARY_INTEGER	INTEGER
BINARY_DOUBLE	DOUBLE PRECISON
BLOB, RAW(n), LONG RAW	BYTEA (1GB limit)
CLOB, LONG	TEXT (1GB limit)
DATE	TIMESTAMP
NUMBER, NUMBER(*)	DOUBLE PRECISION or BIGINT if it is a part of Primary Key
NUMBER(n,0), NUMBER(n)	n<5 – SMALLINT5<=n<9 – INT9<=n<19 – BIGINTn>=19 – DECIMAL(n)
NUMBER(p,s)	DECIMAL(p,s)
REAL	DOUBLE PRECISION

It is critical to understand the scope of use of numeric types in databases. If accuracy is important, Oracle numeric types must be mapped in PostgreSQL NUMERIC. If calculating speed is the most important factor, the optimal mapping options are REAL or DOUBLE PRECISION.

Challenges of PL/SQL Migration

This section looks at the challenges faced during migrating Oracle packages, store procedures, functions, and triggers to PostgreSQL.

• Packages: Postgres does not support packages. This missing feature can be simulated by grouping all entries from a single package with the same name within the Postgres schema. Global variables can be saved in a service table.
• Empty strings: In contrast to Oracle, PostgreSQL does not treat empty strings as NULL.
• Types casting: Postgres requires tight types casting when executing functions and operators and when using INSERT and UPDATE statements to insert expression results into tables.
• Autonomous Transactions: In Oracle, a subprogram can commit or roll back SQL queries without affecting the primary transaction. For example, suppose data is added to a table as part of an insert trigger. These transactions aren’t supported natively in PostgreSQL.

Conclusion

This article provided you with a step-by-step guide on how you can set up Oracle to PostgreSQL Migration manually or using Hevo. However, there are certain limitations associated with the manual method. If those limitations are not a concern to your operations, then using it is the best option but if it is, then you should consider using automated Data Integration platforms like Hevo.

Hevo helps you directly transfer data from a source of your choice to a Data Warehouse, Business Intelligence, or desired destination in a fully automated and secure manner without having to write the code. It will make your life easier and make data migration hassle-free. It is User-Friendly, Reliable, and Secure.

Want to take Hevo for a ride? Sign Up for a 14-day free trial and simplify your Data Integration process. Do check out the pricing details to understand which plan meets all your business needs.

Tell us in the comments about your experience of connecting Oracle to PostgreSQL!