Migration from Oracle to PostgreSQL Database
 

Many organizations are adopting open source  RDBMS databases with a goal of minimizing license costs associated with proprietary databases such as Oracle. PostgreSQL is a very popular open source database that also provides an enterprise version for customers needing such features and support. Such organizations may have incumbent deployments of Oracle databases. Migrating from Oracle to PostgreSQL is not a trivial task and offers challenges that need to be carefully solved.

 

 

Typical Challenges in Database Migration
 

While migrating from Oracle to PostgreSQL, the following challenges are typically seen

  • Data integrity – for any migration to work, there should be zero loss of data. This means that all schemas, table structures and data records in Oracle must be retained after migrating to PostgreSQL. Maintaining data integrity is the first critical requirement for any such migration project
  • Objects, Procedures, Functions and Queries must work – quite often, queries, procedures and functions are written specific to an end application that is using the Oracle database. It is vital that we replace with equivalent constructs and syntax in PostgreSQL
  • Performance impact – it is possible that performance of existing Oracle database has been optimized over many years of operations. Performance of PostgreSQL after the migration must be within 10 per cent of Oracle database. Performance tuning for PostgreSQL can consume lot of effort
  • Report Migration – Oracle may be using reports based on complex querying created over a period of time by different applications. Such queries and repots must be migrated by creating equivalent reports and queries in PostGreSQL

Approach to Data Migration


We use the following approach to migration for any project that needs PostgreSQL as the final target database.

  • Schema and Table Structure Migration – study the database structure of Oracle databse in details. Then we prepare a strategy for the migration. We create equivalent database strcutures in PostgreSQL with a combination of tools and manual programming. Many times, we may need to manually create database structure in PostgreSQL that matches existing Oracle Databases.
  • Data Copy – next step would be copy the data records from Oracle to PostgreSQL. This is done using a combination of tools and manual scripting.
  • Data Validation -  Final data in PostgreSQL is validated using tools such as DBeaver and additional scripting that will depend on individual customer database installation. Sometimes, data may get copied over with incorrect data types and formats which need to be validated and corrected as needed.

Migration of Objects, Procedures, Queries and Functions
 

Native objects such as packages, procedures, functions and queries in Oracle must be migrated with equivalent objects in PostgreSQL. While tools help to some extent, it is inevitable that we should create equivalent objects, procedures, queries and functions in PostgreSQL by rewriting them manually using database programming. This can become a time- consuming task and must be planned in advance as part of your project.

Performance tuning and Optimization


Usually the performance of PostgreSQL will be different from Oracle database after the migration. The project team must then investigate on reasons for the divergence in performance. Performance in PostgreSQL must be tuned by choosing appropriate configuration varailbes in PostgreSQL and in some cases, rewrite complex, slow running queries with faster queries that fix the performance issues that might be seen. This is a very important step to ensure customer satisfaction after migration.

Application Specific Code Rewrite


Most Applications would need to rewrite basic sections of their code meant for accessing the database, retrieving and updating records in the Oracle database to ensure that application code can work correctly with PostgreSQL. Some part of this exercise can be automated using scripts.

Training End Users and Admins
 

Adopting a new database will obviously need training to database admins and database end users on PostgreSQL. It is advisable to plan training sessions in advance before rolling out PostgreSQL database at your organizations.

Altnix Advantage

Altnix has a team of experts that has done many migrations from Oracle to PostgreSQL and other open source community databases. Reach out to us at This email address is being protected from spambots. You need JavaScript enabled to view it. for further information.