Why and how to migrate from Oracle to PostgreSQL

As companies look to modernize their applications and infrastructure, CIOs and IT stakeholders around the globe are considering how and where to leverage open source technologies.   One area that is picking up a lot of momentum is deploying open source database technologies over mainstream commercial databases such as Oracle and Microsoft SQL Server.  This blog will discuss why and how to migrate from Oracle to PostgreSQL by focusing on the benefits and generic migration strategy.

Oracle is the top relational database management system (RDBMS) in the world and heavily deployed across traditional enterprise applications.  With Oracle you pay significant licensing and support costs and additional license fees for advanced database features such as high availability and security. On the other hand, PostgreSQL is an open source technology. Hence it is a free database! Many organizations are already moving and or planning to move to PostgreSQL to save on database licensing costs. The database solutions of PostgreSQL are suitable for any size of business.

Let’s discuss what PostgreSQL is first.

It is an open-source object-relational database with a combination of SQL language. PostgreSQL can store and scale the most complicated workload with no hassle. It was part of the POSTGRES project at the University of California in 1986 and has been under active development, feature updates, and support for 30+ years. PostgreSQL is ACID-compliant, reliable, integrated, and compatible with all major OS.

It also extends the popular PostGIS geospatial database’s support, and no surprise why so many organizations select PostgreSQL.

Why companies are considering migrating from Oracle to PostgreSQL?

Oracle database is the most popular and largest RDBMS in the world. But with the rise of open source databases, Oracle database popularity has been declining steadily, around 18% since 2013. Still, Oracle is suitable for huge data and workload. Here, we explain the key reasons why companies are considering migrating from Oracle to PostgreSQL:

1. Scalability: You can use Oracle for a high workload. But scaling the Oracle database is costly. You can scale to millions of transactions per second with PostgreSQL.
2. Updates: You get major Oracle versions every 2-4 years. PostgreSQL releases major updates and bug fixes every three months.
3. Security: Oracle’s advanced security packages are available as costly add-ons. PostgreSQL offers free security authentications like Host, LDAP, PAM, etc., and role-based access control. You can get data encryption with advanced security plugins.
4. Replication: You need Oracle GoldenGate with a separate license for all data replication. But PostgreSQL supports logical and native streaming replication.
5. Cloud Development: You can use PostgreSQL in all clouds. But in Oracle, you need to bring your own license (BYOL) or use on-demand licensing.
6. Compatibility: PostgreSQL is compatible with other open-source RDBMS.
7. Extension: The open-source extensions of PostgreSQL are free, but not in Oracle.

How to migrate from Oracle to PostgreSQL?

Database migration is a challenge. Most CIOs and IT leaders wonder how a migration will affect the current operation. Unpredictable downtime is also a significant concern during migration. The migration strategy always depends on the goals you want to achieve after moving onto a new platform. However, here we are mentioning some generic migration strategies that will smoothen your journey from Oracle to PostgreSQL.

1. Assessment: You need to evaluate the requirements before migration. It helps you analyze the difficulty and the level of migration, and accordingly, you can plan your strategy. You can conduct a thorough analysis of the technology-related issues. You should also assess the compatibility of the application server, client, data access, datatype conversion, code conversion, and database features.

    

   • • Compatibility assessment: Post verifying the target database and source compatibility; you should meet the following prerequisites for migration:
       1. Server Resources like memory, network ports open between source and destination.
       2. Operating System
       3. Data migration software and related drivers installed & configured.
   • The target server resources of PostgreSQL where you are migrating from Oracle should be larger enough to fit and scale your data. If your data volume is not huge, you can pick up an online migration strategy with an export-import approach, otherwise divide your whole migration into multiple small parts.
   • • Application code assessment: This is an important stage of migration to PostgreSQL. If your application code is Oracle-specific, your migration will be more intricate. The adjustments you need to make rely on your database connection layer and application architecture. For example, if your code is Java that uses generic JDBC classes instead of Oracle-specific, the conversion would be more straightforward. Postgres does not have a concept of packages that Oracle has, so your migration strategy should include converting Oracle packages into Postgres procedures and functions. Implementation of sequences is different between Oracle and Postgres, so the migration strategy should take into consideration the calls to sequences in the application code.
   • • Architecture assessment and cleanup: You can take this migration as an opportunity to clean up your database and architecture from the objects you are not using anymore. When you move from Oracle to PostgreSQL, you can separate analytics and online transaction processing (OLTP) into different warehouses to enhance their responsiveness and capability.
   • • Schema conversion: In Oracle a schema and a user have a one-to-one relationship and there is no real distinction between a user and a schema. In PostgreSQL the situation is different: All the objects a user is creating are created in a specific schema (or namespace). Other users may or may not have permissions to work with these objects or even to create new objects in a specific schema. Once a role and schema and their association are defined in Postgres, the next step is to generate a schema conversion report that will provide details on objects that can be converted seamlessly and others that need manual intervention. Analyze the report and perform necessary manual conversions that is compatible with application.

    

2. Schema Migration: In Oracle, you need to define each schema. But in PostgreSQL, the schema goes to the public schema by default, even if you don’t specify it. The name of any schema is called ‘User’ in Oracle. A single user can build multiple schemas without creating separate users in PostgreSQL. You can give permissions for creating various objects in the schemas. So once the conversion report is analyzed and necessary steps have been undertaken to convert non-conforming objects, the next step is to execute the sql script in Postgres to create an empty schema.
3. Functional Testing: It is critical to test the migrated schema on a sample dataset. You can load sample data into PostgreSQL from a destination database/testing environment and create an application connection using the right drivers or data access. Once you connect the application to the database, it allows you to complete functional testing on the converted objects with DMLs. Load the exact sample dataset in both PostgreSQL and Oracle and ensure that SQL results are identical.
   
4. Performance Testing: The Oracle built-in transactions are different in PostgreSQL. Hence, performance testing is paramount here. You can capture all the differences and fix the same in the app, drivers, and database level with the right tuning.
5. Data Migration: Data migration is a time-consuming process between Oracle and PostgreSQL for the heterogeneous structure. The data migration strategy depends on your goals and requirements. There are three key data migration strategies that can also be used in conjunction with each other:  
   1. 1. Snapshot: Move data at once
      2. Snapshot in Parallel: Move data in chunks (schema or table)
      3. Change Data Capture (Replication): Load data continuously.

   You need application downtime for the first two approaches. But you need a smaller downtime window in the third approach. You can pick the right approach as per your downtime window.

Migrate your Oracle database to PostgreSQL with MiCORE Solutions

At MiCORE, we provide you with specialized database consulting services, database managed services and cloud solutions with migration, 24/7 support, and monitoring. We help you migrate your Oracle database to PostgreSQL with architecture and design support along with on-going operational support. With our years of expertise, we assist in providing best practices to meet your availability and performance requirements. Combining our expertise of Oracle and PostgreSQL, we work with you to build a workable PostgreSQL migration strategy without disturbing your current operations.    

Talk to our experts today to know more about migrating your Oracle databases to PostgreSQL.