Migration from Oracle Database to PostgreSQL Database
 

Disclaimer:  Oracle and PostgreSQL are registered trademarks that are not owned by Altnix and belong to respective trademark owners.

Many organizations are adopting open source  RDBMS databases with a goal of minimizing license costs associated with proprietary databases such as Oracle Database. PostgreSQL Database 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 Database to PostgreSQL  Database is not a trivial task and offers challenges that need to be carefully solved.

 

 

Typical Challenges in Database Migration
 

While migrating from Oracle Database to PostgreSQL Database, 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 Database must be retained after migrating to PostgreSQL Database. 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 database
  • Performance impact – it is possible that performance of existing Oracle database has been optimized over many years of operations. Performance of PostgreSQL database after the migration must be within 10 per cent of Oracle database. Performance tuning for PostgreSQL database can consume lot of effort
  • Report Migration – Oracle Database may be using reports based on complex querying created over a period of time by different applications. Such queries and reports must be migrated by creating equivalent reports and queries in PostGreSQL Database

Approach to Data Migration


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

  • Schema and Table Structure Migration – study the database structure of Oracle database in details. Then we prepare a strategy for the migration. We create equivalent database structures in PostgreSQL database with a combination of tools and manual programming. Many times, we may need to manually create database structure in PostgreSQL database that matches existing Oracle Databases.
  • Data Copy – next step would be copy the data records from Oracle database to PostgreSQL database. This is done using a combination of tools and manual scripting.
  • Data Validation -  Final data in PostgreSQLdatabase 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 database must be migrated with equivalent objects in PostgreSQL database. While tools help to some extent, it is inevitable that we should create equivalent objects, procedures, queries and functions in PostgreSQLdatabase 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 variables 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 database. 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 database. 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 database to PostgreSQL database 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.