Just click the button below and provide your details – our team will promptly reach out to schedule your one-on-one session.
Our experts are standing by to discuss your needs, answer your questions, and show you how DataOps IT can help you accelerate growth, improve efficiency, and stay ahead of the competition.
Migrating from Oracle Database to PostgreSQL is one of the most significant infrastructure decisions an organisation can make, and increasingly, it is one that UK businesses of all sizes are actively pursuing. The motivations are well understood: Oracle’s licensing model is among the most expensive in the enterprise software landscape, its audit practices are notoriously aggressive, and the total cost of ownership of an Oracle estate tends to compound significantly as workloads grow. PostgreSQL, by contrast, is open-source, free to use, and backed by one of the most active and mature developer communities in the database world.
But cost reduction alone does not make a migration successful. Moving from Oracle to PostgreSQL involves reconciling meaningful differences in SQL dialect, data type handling, procedural language, and built-in functionality. Applications built against Oracle over many years carry assumptions about database behaviour that do not always transfer cleanly. A migration executed without adequate preparation produces instability, performance degradation, and, in the worst case,s data integrity issues that are far more expensive to resolve than any licence saving justifies.
This guide walks through every phase of an Oracle-to-PostgreSQL migration: assessment, planning, schema conversion, data migration, application remediation, testing, and cutover. It is written for organisations that want to approach the migration with the rigour it demands, not as a shortcut exercise.
Oracle Database is a technically exceptional product. It is also one of the most expensive software investments an organisation can make, and the licensing model is structured in ways that make costs difficult to predict and control. Processor-based licensing, the multiplier applied to virtualised environments, and the cost of optional features that many organisations discover they have inadvertently enabled all contribute to an Oracle bill that frequently surprises at audit time.
PostgreSQL eliminates the licensing cost. For organisations running Oracle on commodity hardware or in cloud environments, the migration to PostgreSQL, particularly to managed cloud offerings such as Amazon Aurora PostgreSQL, Azure Database for PostgreSQL, or Google Cloud SQL for PostgreSQL, can reduce database infrastructure costs substantially whilst simultaneously improving operational flexibility. Many organisations report annual savings in the six-figure range following a well-executed migration, and for larger Oracle estates, the savings can be considerably greater.
Beyond cost, PostgreSQL has evolved into a genuinely compelling technical platform in its own right. It supports advanced data types, native JSON storage and querying, full-text search, geospatial extensions through PostGIS, and a rich extension ecosystem that allows organisations to extend the platform’s capability without third-party licences. Its ACID compliance, support for complex queries, and performance characteristics on modern hardware make it a credible replacement for Oracle across a wide range of workloads.
PostgreSQL’s active open-source community ensures that the platform evolves rapidly and that vulnerabilities are addressed promptly, without relying on a single vendor’s release schedule or the commercial negotiation that Oracle patching sometimes entails.
An honest guide must also acknowledge what PostgreSQL does not provide. Oracle’s Real Application Clusters architecture, which allows multiple database instances to share a single storage layer for horizontal scalability, has no direct equivalent in PostgreSQL. Oracle’s Partitioning, Advanced Compression, and certain analytical functions available in higher Oracle editions represent genuine capability gaps that organisations should evaluate against their specific workloads before committing to migration. For the majority of transactional and mixed workloads, these gaps are manageable. For highly specialised Oracle deployments that rely heavily on RAC or advanced enterprise features, the migration requires more careful scoping.
Before any migration work begins, a complete inventory of the Oracle estate is essential. This means cataloguing every database instance, its version, its size, its criticality to the business, its connection dependencies, and the applications and services that rely on it. Organisations that have run Oracle for many years frequently discover databases in this phase that are not well-documented, legacy systems, reporting databases, or development instances that have accumulated over time without formal governance.
Each database in scope should be classified by migration complexity. Smaller databases with simple schemas and limited use of Oracle-specific features are candidates for early migration, building team confidence and refining the process before tackling the most complex workloads. Databases with extensive use of Oracle-specific procedural language, complex partitioning schemes, or tight integration with Oracle-specific tooling will require significantly more remediation effort and should be scoped accordingly.
The compatibility gap between Oracle SQL and PostgreSQL SQL is real but manageable with the right tools and preparation. Oracle’s procedural language, PL/SQL, has no direct equivalent in PostgreSQL; the nearest counterpart is PL/pgSQL, which shares structural similarities but differs in syntax, exception handling, built-in package availability, and certain data type behaviours. Every stored procedure, function, trigger, and package in your Oracle database will require review and, in most cases, some degree of rewriting.
Oracle-specific SQL constructs, such as the CONNECT BY hierarchical query syntax, ROWNUM pseudo-column, certain date and string functions, the DUAL table, and Oracle’s outer join syntax, all require conversion to PostgreSQL equivalents. Sequence behaviour differs between the two platforms. Data type mappings are not always one-to-one: Oracle’s NUMBER type, for instance, maps differently depending on precision and scale, and VARCHAR2 behaves subtly differently from PostgreSQL’s VARCHAR in edge cases.
Automated assessment tools can accelerate this process significantly. Ora2Pg, the most widely used open-source Oracle-to-PostgreSQL migration tool, provides a compatibility report that estimates migration complexity, identifies objects requiring manual intervention, and generates a migration cost estimate based on the volume and type of objects in scope. AWS Schema Conversion Tool and EDB Migration Toolkit are commercial alternatives with broader automation capabilities. Running an automated assessment early in the planning phase is strongly recommended, as it converts assumptions about migration complexity into evidence, and the evidence is frequently more sobering than the assumptions.
The database is only half of the migration. Every application, integration, reporting tool, and scheduled process that connects to the Oracle database must be identified and assessed. Connection strings, Oracle-specific JDBC or ODBC drivers, application code that constructs Oracle-specific SQL dynamically, and ORM configurations that assume Oracle behaviour all require review and remediation.
In long-lived enterprise environments, this is often the most time-consuming part of the assessment. Applications written against Oracle over many years may contain Oracle-specific assumptions distributed across thousands of lines of code in ways that are not immediately visible. A thorough dependency mapping exercise, conducted with input from the application owners rather than solely from the database team, is essential before migration scope and timeline can be reliably estimated.
Schema conversion begins with mapping Oracle data types to their PostgreSQL equivalents. The most common mappings are well understood: Oracle’s VARCHAR2 becomes VARCHAR or TEXT in PostgreSQL; NUMBER becomes NUMERIC, INTEGER, or BIGINT depending on precision; DATE in Oracle includes a time component and maps to TIMESTAMP in PostgreSQL rather than DATE, which in PostgreSQL stores date only. CLOB and BLOB map to TEXT and BYTEA, respectively. These mappings are largely mechanical but must be applied consistently and verified, as a missed type conversion can produce silent data truncation or type errors that only surface at runtime.
PL/SQL to PL/pgSQL conversion is the most labour-intensive element of the schema migration for Oracle databases with significant stored logic. The structural similarities between the two languages, which both use BEGIN/END blocks and both support cursors, loops, and exception handling mask important differences that cause failures if not addressed.
Oracle packages, which group related procedures and functions together with shared private state, have no direct equivalent in PostgreSQL. The standard approach is to convert each package into a PostgreSQL schema containing the equivalent functions and procedures, with package-level variables replaced by session-level configuration parameters or temporary tables where necessary. Oracle’s built-in packages DBMS_OUTPUT, UTL_FILE, DBMS_SCHEDULER, and others require either custom PostgreSQL replacements or application-level refactoring to remove the dependency.
Exception handling syntax differs between the two languages. Oracle’s RAISE_APPLICATION_ERROR has no direct equivalent in PostgreSQL, and error codes used in Oracle’s exception handlers do not map directly to PostgreSQL’s SQLSTATE values. Each exception handler must be reviewed and converted to use the appropriate PostgreSQL error handling constructs.
Oracle sequences and their usage patterns map reasonably well to PostgreSQL sequences, though the syntax for creating and referencing them differs. PostgreSQL’s IDENTITY columns, available since PostgreSQL 10, provide a cleaner alternative to explicit sequence management for new development and are worth considering during migration rather than performing a purely mechanical conversion of existing sequence patterns.
Triggers in PostgreSQL use a different architecture from Oracle triggers. In PostgreSQL, a trigger calls a trigger function, a separate function object that returns a trigger type rather than containing the trigger logic inline. This distinction requires structural changes to every Oracle trigger during migration, though the underlying logic typically converts with moderate effort.
Constraint definitions, primary keys, foreign keys, unique constraints, and check constraints generally convert straightforwardly between the two platforms. The main area of attention is deferred constraint checking: Oracle allows constraints to be deferred to the end of a transaction, and whilst PostgreSQL supports deferrable constraints, the default behaviour and the syntax for declaring them differ in ways that must be explicitly addressed for any constraints that rely on deferred checking.
Data migration from Oracle to PostgreSQL can be approached in two primary ways, and the choice between them is driven by how much downtime the business can tolerate during the cutover window.
An offline migration, sometimes called a big bang migration, involves stopping writes to the Oracle database, exporting the data, transforming it as necessary, and loading it into PostgreSQL before redirecting applications to the new platform. This approach is simpler to execute and easier to validate, but it requires a maintenance window whose length is proportional to the volume of data being migrated. For smaller databases, this window may be measured in hours and may be entirely acceptable. For large databases in business-critical systems, it may be impractical.
An online migration uses change data capture to replicate ongoing changes from Oracle to PostgreSQL continuously whilst the initial bulk data load is in progress. Once the bulk load is complete and PostgreSQL has caught up with the Oracle transaction log, the gap between the two platforms is reduced to seconds, enabling a much shorter cutover window. Tools such as AWS Database Migration Service, Striim, and Attunity Replicate support Oracle-to-PostgreSQL online migration with CDC. This approach is more complex to configure and monitor, but it is the appropriate choice for workloads where extended downtime is not acceptable.
Data validation after migration is not optional. Row counts are a starting point, but they are insufficient on their own; a row count that matches does not confirm that the data within those rows is correct. Checksum validation across key columns, comparison of aggregate values for numeric fields, and spot-checking of individual records across a representative sample are all components of a thorough validation process.
Particular attention should be paid to data types where the Oracle-to-PostgreSQL mapping involves transformation rather than a direct copy of date and timestamp values, numeric precision, and any columns that use Oracle-specific data types. Silent data corruption in these areas can be difficult to detect after the fact and may only surface through application errors in production.
Applications connecting to Oracle via JDBC, ODBC, or Oracle’s proprietary OCI driver must be updated to use PostgreSQL-compatible alternatives. JDBC applications move to the PostgreSQL JDBC driver; ODBC applications move to the PostgreSQL ODBC driver. Applications built using Oracle-specific client libraries, particularly those using Oracle Call Interface directly, require more significant refactoring.
Connection pooling configurations, timeout settings, and transaction isolation level defaults may also require adjustment, as PostgreSQL’s default behaviour differs from Oracle’s in several of these areas. Documenting the current Oracle connection configuration before migration and explicitly replicating the relevant settings in PostgreSQL avoids a class of subtle behavioural differences that can be difficult to diagnose after cutover.
Application code that constructs SQL dynamically, whether through raw string concatenation, query builders, or ORM frameworks, must be reviewed for Oracle-specific syntax. The most common issues are Oracle’s proprietary outer join syntax using the plus-sign notation, ROWNUM-based pagination that must be replaced with PostgreSQL’s LIMIT and OFFSET clauses, CONNECT BY hierarchical queries that must be rewritten using PostgreSQL’s recursive common table expression syntax, and Oracle-specific date arithmetic functions.
ORM frameworks such as Hibernate can often be reconfigured to target PostgreSQL with relatively limited code changes, though ORM-generated queries that relied on Oracle-specific dialect features will require attention. Applications that use Oracle-specific features through the ORM’s native query interface rather than through portable JPQL or HQL will require the most remediation effort.
Reporting tools, dashboards, and business intelligence platforms connected to Oracle must be reconfigured to connect to PostgreSQL. Most major BI platforms, Microsoft Power BI, Tableau, Qlik, and similar tools support PostgreSQL natively, and the reconnection process is straightforward. The more significant risk is in the underlying queries and data models: reports built using Oracle-specific SQL functions or relying on Oracle-specific query behaviour may produce incorrect results when pointed at PostgreSQL without review.
Functional testing must validate that the application behaviour against PostgreSQL is equivalent to the behaviour against Oracle. This means testing not just the happy path of normal operation, but edge cases, null handling, boundary values, concurrent transaction behaviour, and error conditions where the differences between Oracle and PostgreSQL are most likely to surface.
Automated regression test suites, where they exist, should be run in full against the PostgreSQL environment before cutover is considered. Where automated test coverage is limited, manual testing with a structured test plan covering the most critical business processes is essential. Business users must be involved in the testing phase; the technical team’s assessment of functional equivalence and the business team’s assessment are not the same thing, and both are needed.
Performance testing against realistic production-scale data volumes and query patterns is one of the most important and most frequently shortchanged phases of a database migration. A migration that passes functional testing but performs significantly worse under load than the Oracle source is not successful, regardless of how clean the data conversion was.
Performance differences between Oracle and PostgreSQL are not always predictable. Some workloads perform better on PostgreSQL; others require query tuning, index additions, or schema adjustments to match Oracle performance. The time to discover and resolve these differences is during a performance testing phase with adequate time allocated, not during the first week of production operation.
Before the production cutover, backup and recovery procedures for the PostgreSQL environment must be tested end to end. PostgreSQL’s backup and recovery ecosystem, including point-in-time recovery using Write-Ahead Log archiving and tools such as pgBackRest or Barman, differs from Oracle’s RMAN-based approach. The DBA team must be confident in the new backup and recovery procedures before the Oracle environment is decommissioned.
The cutover plan must specify every step of the final transition in precise sequence, with defined decision criteria for proceeding and equally well-defined criteria for invoking rollback. The rollback procedure, the process for reverting to Oracle if a critical issue is discovered after cutover, must be documented, tested, and understood by every member of the cutover team before the cutover window begins.
Cutover should be rehearsed in a staging environment that mirrors production as closely as possible, with the actual steps executed by the actual team members who will perform them in production. Timing each step during rehearsal reveals whether the planned cutover window is realistic and identifies any steps that take longer than expected.
The period immediately following the cutover demands intensive monitoring. Query performance should be tracked closely; execution plans in PostgreSQL may differ from Oracle even for equivalent queries, and workloads that performed well in testing may behave unexpectedly under the full variety of production traffic patterns. PostgreSQL’s auto-vacuum process, which maintains table health by reclaiming space from deleted rows, requires monitoring and tuning during initial production operation as the team calibrates its behaviour to the specific workload.
Application error rates, database connection counts, lock contention events, and replication lag (for environments using streaming replication for high availability) should all be tracked against established baselines during the stabilisation period following cutover.
Oracle should not be decommissioned until the PostgreSQL environment has been in stable production operation for a sufficient period to be confident that the migration is complete and no critical issues remain outstanding. For most organisations, a stabilisation period of four to eight weeks before Oracle decommissioning is appropriate, during which Oracle is maintained in a read-only state as a fallback reference. Decommissioning too quickly forfeits the safety net; maintaining Oracle indefinitely delays the licence cost savings that motivated the migration.
Migrating from Oracle to PostgreSQL is not a trivial undertaking, and any guide that suggests otherwise is not serving the organisations that rely on it. The differences in SQL dialect, procedural language, data type handling, and built-in functionality are real, and they require systematic assessment, careful remediation, and thorough testing to address. Projects that underestimate this effort tend to extend significantly beyond their original timelines and budgets.
But the migration is entirely achievable with the right preparation. Organisations across the UK and globally have completed Oracle-to-PostgreSQL migrations at scale, delivering substantial licence savings, improved operational flexibility, and, in many cases, a modernised, cloud-hosted database infrastructure that is better suited to their current and future needs than the Oracle estate it replaced.
The investment in rigour during the assessment, schema conversion, and testing phases pays dividends at cutover and beyond. Approach the migration with that rigour, and the outcome justifies the effort.
