Migrating from Oracle to PostgreSQL is a strategic move many organisations are making to reduce licensing costs, increase flexibility, and modernise their data platforms. However, the challenge is not just the migration itself—it’s how to transition with minimal disruption to business operations while maintaining data integrity.
There are several ways to execute this migration, each balancing risk, downtime, and operational complexity differently. Some approaches prioritise stability with planned outages, while others minimise downtime by introducing more complexity during the transition phase. Choosing the right method depends on business tolerance for disruption, data volume, and the organisation’s readiness to manage parallel systems.
This article outlines three practical migration strategies using Quest’s SharePlex, highlighting the trade-offs of each so stakeholders can make informed decisions aligned with business priorities.
In all three approaches below, Pebble IT’s Osprey tool is used. This tool analyses the Oracle data to design the SharePlex replication queues to ensure that the flow of data is balanced and efficient. It also orchestrates SharePlex to perform the data conversion to PostgreSQL to ensure that the local machine memory and CPU resources are not exhausted causing issues to the Production servers and databases whilst ensuring the data is converted efficiently and monitored. This minimises the SharePlex licensing requirements which is by vCPU for both the Oracle and PostgreSQL databases, maximising the efficiency of conversion will minimise the conversion time required.
Compared to using SharePlex alone, Osprey provides certainty and governance that ensures that the data conversion is repeatable and less likely to encounter unexpected issues.
This involves creating a copy of the Oracle database at a known “System Change Number” (SCN) and using SharePlex to copy that data to the PostgreSQL database. Meanwhile, SharePlex is configured to replicate changes from the primary Oracle database starting at the same SCN that the database copy was taken at. Once the data is copied from the Oracle copy, then the primary Oracle database replication commences.
Once the replication has caught up to current changes, then the business can prepare for an application cutover from Oracle to PostgreSQL in a cutover window.
This can be illustrated as:
This involves having SharePlex copy all the data tables subject to change during an outage (or at least maximising what is possible during the allowed outage) and then replicate data changes until the business is ready to perform an application cutover.
This approach requires analysis of the database and classification of tables that are ‘insert-only’ verses frequently updated and infrequently updated tables. This analysis is used by Osprey to design how to optimise the data conversion to minimise the outage requirements.
This can be illustrated as:
Similar to Approach 2 with the primary difference is that the data conversion process occurs whilst the Oracle database is being used – there is no outage. This is an interesting approach because it will encounter a lot of SharePlex replication issues – however SharePlex has excellent functionality for resolving these issues. Organisations normally shy from “data issues”, and it will require a lot of SharePlex expertise and supervision of the replication until the data conversion is complete and the replication continues unabated without errors. However, it needs to be stressed that this approach is only suitable for smaller databases and analysis is needed before embarking upon this approach.
Once the replication is changes-only the business can determine when to schedule an application cutover from Oracle to PostgreSQL.
This can be illustrated as:
The business’s appetite for coping with outage will be a primary driver for determining the optimal approach. The current Oracle licensing and server constraints should also be considered if Approach 1 is preferred by converting from a copied Oracle database.
Variations to each approach can also be designed to cater for specific requirements. These approaches can cater for Oracle databases running on Exadata’s, ODA’s, generic X86 Servers, and Oracle databases run as a service in public clouds. The target PostgreSQL database can be on generic X86 Servers, cloud VMs or PostgreSQL services on the public clouds.
The size of the Oracle database will also have a bearing on what approaches are viable. The above 3 approaches are viable for databases less than 500GB, beyond that requires special consideration to ensure the preferred approach is correctly designed. Data transformations from Oracle to PostgreSQL will also have a negative impact upon the conversion and should be minimised if not eliminated altogether.
Whilst the data within an Oracle database is not native to PostgreSQL there are viable approaches to migrate the data from Oracle to PostgreSQL to enable the organisation to benefit from the strengths of the PostgreSQL database as outlined in our article ‘Why PostgreSQL Should Be Part of Your Oracle Optimisation Strategy’. The SQL used by applications and tools is very similar between Oracle and PostgreSQL and many organisations are making the change to free themselves of proprietary solutions and their license restrictions. Whilst this is a viable strategy to undertake in your own organisation, it still requires careful planning and knowledge and this where the services of Pebble IT will turn your idea of Oracle optimisation into reality.
