JOURNEY OF AN ORACLE TO

POSTGRESQL MIGRATION
WITH MIGOPS
Content
Who is MigOps?

Our Team

How we approach Migrations to PostgreSQL

1. Conduct a Manual Assessment
2. List out the Patches to be contributed to PostgreSQL extensions
3. Identify the need for new PostgreSQL extensions
4. Enabling opportunities for a parallel development
5. Migrate Packages/Stored Procedures and other Code objects
6. Verification of the migrated objects
7. Support the application teams during the development
8. Heavy support during the testing
9. Setting up a PostgreSQL Production cluster
10. Creating a strategy for Production database migration
11. Support during the Production Cutover
12. Post-Production Support

Customer Use Cases

Use Case 1
Use Case 2

Additional Resources

Conclusion

How To Approach Oracle to PostgreSQL Migrations

Who is MigOps?
MigOps believes in Open Source database freedom. Our experts at
MigOps are known in the PostgreSQL world for providing
customized and Open Source solutions to eliminate additional
license costs and vendor lock-in while migrating to PostgreSQL.

In addition to PostgreSQL, our experts at MigOps have working

experience ranging between 5 to 20 years in deploying critical
applications on Oracle, SQL Server, DB2 and many more commercial
database technologies.

PostgreSQL is the most loved database by

Oracle database administrators and
developers.

How To Approach Oracle to PostgreSQL Migrations

Our Team

The team at MigOps is known for the contributions we made to

the Open Source ecosystem of PostgreSQL for more than a
decade. Some of the contributions include PostgreSQL, Ora2PG,
pgBadger, pgTT, Orafce, pg_dbms_job, pg_dbms_errlog,
credcheck, and many more PostgreSQL tools and extensions.

Check out our contributions to PostgreSQL in 2021.

While migrating to PostgreSQL, if a need arises to achieve

compatibility to Oracle, SQL Server, DB2, or Sybase, we prefer to
contribute to an existing Open Source extension like Orafce or
build a new Open Source extension with same license as
PostgreSQL. The ultimate goal of MigOps is to simplify the overall
migration process to PostgreSQL.

How To Approach Oracle to PostgreSQL Migrations

How we approach
Migrations to PostgreSQL
With an experience of migrating thousands of Oracle databases,
thousands of terabytes of data and millions of stored procedures
and packages, we have developed a unique strategy for migrations
to PostgreSQL.

How To Approach Oracle to PostgreSQL Migrations

1. Conduct a manual assessment

Majority of mistakes, failures and delays in

migrations from Oracle to PostgreSQL
happen due to lack of assessments or over-
trusting the assessments performed by tools.
For this reason, we approach each migration
through a detailed assessment performed by
humans in addition to the assessment
performed by Ora2Pg and our in-house tools.

Upon completing the database(s) and

application(s) assessment, we draft a detailed
report that includes some of the challenges
mentioned here. Our analyses helps
smoothen the decision making process by
stakeholders and increase confidence for
Engineering and Development teams.

How To Approach Oracle to PostgreSQL Migrations

2. List out the Patches to be contributed to PostgreSQL
extensions

MigOps identifies all the incompatible features during the migration

assessment phase. These incompatibilities can sometimes create a
lot of application rewrite as well as an increased development time.
For this reason, MigOps identifies the list of such incompatibilities
that can be addressed through patches to some of the existing
PostgreSQL extensions.

In the event of a target

environment being a cloud
platform such as AWS or
Azure or GCP, MigOps would
With more than a decade
efficiently select the of experience in
extensions supported on observing thousands of
those platforms and databases and solving a
contribute the compatibility variety of issues, we learn
every day and implement
through those extensions.
the right set of tools and
extensions that perform
well with PostgreSQL.

How To Approach Oracle to PostgreSQL Migrations

3. Identify the need for new PostgreSQL extensions
While migrating databases and corresponding applications, we may
observe some unique Oracle features that have no alternative in
PostgreSQL. For example, some of our customers were using
DBMS_JOB and DBMS_ERRLOG packages extensively in their
Oracle environments. There exists no Open Source solutions that
serve this purpose. As the same has been identified during the
Migration assessment, we contributed to two new extensions:
PG_DBMS_JOB and PG_DBMS_ERRLOG in less than 3 weeks. This
reduced the application migration efforts by several months.

4. Enabling opportunities for a parallel development

It is important to enable opportunities for parallel
development at any stage during the process of a
database migration. For this reason, we start by
identifying the accurate data type for attributes
and migrate all the tables from Oracle to
PostgreSQL. We then migrate the data for these
tables to support the application and testing
teams with their POC and enable parallel
development/testing during migration.

How To Approach Oracle to PostgreSQL Migrations

5. Migrate Packages/Stored Procedures
and other Code objects
Once the tables have been successfully migrated,
MigOps would then complete migrating the other
objects from Oracle to PostgreSQL. During this
phase, we are flexible to migrate the list of objects
that are needed by a specific application module
which is chosen as the first target for migration.

6. Verification of the migration objects

MigOps has built an ocean of in-house tools and
extensions to validate the migrated objects and
data. Using these tools with no additional license
costs, we should be able to validate the objects
and data migrated from Oracle to PostgreSQL.

7. Support the application teams during the

development
Migration from Oracle to PostgreSQL may introduce several
challenges for the application developers. This is because of the
fundamental difference in a lot of behaviours within the two
database engines - Oracle and PostgreSQL. With immense
knowledge in application development and PostgreSQL, MigOps
supports the application developers by providing the best
workarounds or ideal designs for complex applications.

How To Approach Oracle to PostgreSQL Migrations

8. Heavy support during the testing

While comparing the application workload against PostgreSQL with

Oracle, we may initially find a lot of performance degradation. This
can be sorted out with strategic implementation of tuning
parameters, partitioning, indexing, etc. MigOps has multiple in-
house tools to automatically find the appropriate indexes and the
areas to tune. We have always observed a better performing
PostgreSQL database when compared to Oracle, upon our
performance tuning exercises.

How To Approach Oracle to PostgreSQL Migrations

9. Setting up a PostgreSQL Production cluster
MigOps has extensive expertise in building secure, scalable, and
highly available PostgreSQL databases on both Cloud and On-Prem.
To achieve high availability and a good backup strategy, MigOps
chooses the best Open Source tools that are actively being
maintained by both the PostgreSQL community and MigOps. We
identify the need for a server-side connection pooler and a load
balancer and other areas to satisfy the business and security
requirements.

10. Creating a strategy for Production

database migration
Based on the business requirements, MigOps
identifies the right sequence of steps and the tools
to perform OFFLINE or ONLINE migrations from
Oracle to PostgreSQL. We support your team by
performing multiple dry-runs of the
implementation strategy to give you enough
confidence for Production Migration.

11. Support during the Production Cutover

Our team at MigOps is on standby to provide
support during the Production cutover. We will be
available to guide, and resolve any unforeseen
issues during the time of production cutover.

How To Approach Oracle to PostgreSQL Migrations

12. Post-Production Support
MigOps and Percona offer multiple services for 24/7 Support and
Post-production support including Managed Services.

PostgreSQL is feature-rich and completely

community driven so you can add extensions to
serve your unique purpose.

- Avi Vallarapu

How To Approach Oracle to PostgreSQL Migrations

 The fact of the matter is
that most extensions we
think we need to pay for
have already been
contributed, are available,
and free to use when it
comes to PostgreSQL.
-AVI VALLARAPU

How To Approach Oracle to PostgreSQL Migrations

Additional Resources

Oracle to PostgreSQL Schema/Data

migration tool with Validation. MigOps
has added integration to Oracle_FDW
Ora2PG for faster data migration. We are also
going to announce the support for
ONLINE Migrations with the new CDC
feature to Ora2Pg

Provides Oracle compatibility in

Orafce PostgreSQL avoiding a huge rewrite of
application code for PostgreSQL

pg_dbms_job Full compatibility to Oracle DBMS_JOB

How To Approach Oracle to PostgreSQL Migrations

Additional Resources
pgBadger PostgreSQL Log Analyzer

Enforces user and password rules in

credcheck PostgreSQL

pg_dbms_er Provides compatibility to Oracle

rlog DBMS_ERRLOG in PostgreSQL

An extension to provide Oracle style

pgtt Global temporary tables feature in
PostgreSQL.

pg_dumpbin Dumps data of a PostgreSQL database

ary in binary format.

PostgreSQL SQL and PL/pgSQL syntax

pgFormatter formatter

How To Approach Oracle to PostgreSQL Migrations

 Customer Use Cases
In Q4 of 2021, MigOps migrated two of the biggest financial
applications from Oracle to PostgerSQL.

Use Case 1

Migration: Oracle Forms to Java and PostgreSQL for one of the

major financial companies.
Description: This has been one of the most complex migrations ever
performed by MigOps to date. This migration included 54 Oracle
forms and complex Oracle PLSQL code. Our team migrated the
Oracle forms to a new application developed majorly using Java and
React. The forms backend PLSQL and also Oracle database objects
have been migrated to PostgreSQL.
Objects: 780 Stored Procedures, 98 Packages, 550 Tables, and many
other objects.
Patches contributed: To support this migration, MigOps pushed
patches to some of the extensions: Orafce, PGTT as documented in
this article.
Duration: 6 months
Data: 9 TB
Downtime: 4 hours

How To Approach Oracle to PostgreSQL Migrations

 Use Case 2

Migration: Oracle to PostgreSQL migration involving a multi-tenant

financial application.
Description: This migration included multiple incompatible features
in PostgreSQL. With more than 7000 tenants (1 schema per each
tenant), it added complexity while matching performance with
Oracle. This is because the increase in catalog size with increased
metadata introduced more challenges in performance as
PostgreSQL does hard-parsing each time. MigOps has come up
with the best designs satisfying the business requirements and
successfully migrated all the 7000 tenants from Oracle to
PostgreSQL.
Objects: 1500 Stored Procedures, 300 Tables, and some other
objects.
Patches and Extensions Contributed: We contributed to the
extensions : PG_DBMS_JOB, PG_DBMS_ERRLOG and also the data
validation and Oracle_FDW integration to our tool : Ora2Pg.
Duration: 4 months.
Data: 64 TB - 7000 tenants segregated across 10 PostgreSQL
clusters.
Downtime: 10 mins per each tenant.

How To Approach Oracle to PostgreSQL Migrations

 Conclusion
and Next Steps

Contact Us if you need additional

assistance when migrating from Oracle
to PostgreSQL.

SIMPLIFY THE MIGRATION

PROCESS WITH MIGOPS

[email protected]
www.migops.com