Oracle to PostgreSQL migration
The rewrite scope is defined before you commit. Performance targets are measured, not promised. Rollback criteria are agreed before cutover, not improvised during it.
The initial migration review is a brief, free conversation. The Migration Readiness Assessment is a scoped, paid engagement.
Where these projects actually get difficult
The hardest migration problems rarely come from moving data itself. They usually show up in three areas:
- Rewrite scope discovered late. An automated conversion report is treated as an estimate, and the constructs that really cost time - package state, autonomous transactions, hint-dependent plans - only surface during testing.
- Performance regressions in critical queries. A relatively small, workload-specific set of statements - often the heaviest reports - can need tuning because they were written for a different optimiser.
- No defined stop condition. The cutover has a plan but no written rollback criteria, so the decision to abort is taken under pressure by whoever happens to be in the room.
We plan specifically against these three failure modes.
What actually has to be rewritten
Automated tooling (ora2pg and equivalents) converts most schema objects and a large share of straightforward PL/SQL. The estimate it produces is a starting point for manual review, not a project plan. We split your codebase into three groups and quantify each.
There is no meaningful universal rewrite percentage. Two Oracle databases of the same size can require radically different migration effort depending on how much business logic sits in PL/SQL, packages, Oracle-specific SQL and application integrations. The assessment therefore measures rewrite scope at object level rather than applying a generic percentage.
How every database object is classified during the assessment
Mechanical conversion
Tooling, then review
Tables, most constraints and indexes, sequences, simple views, straightforward procedures and triggers.
Predictable rewrite
Repeatable engineering
NVL, DECODE, ROWNUM, CONNECT BY, (+) joins, sequence syntax, error handling, scheduling.
Design decision
Architecture-dependent
Package state, autonomous transactions, global temporary tables, hint-dependent plans, index-organised tables.
The classification is fixed; the proportions are not. How much of your system falls into each group is what the assessment determines, and it differs for every database.
Converts with predictable, repetitive changes
Oracle construct
PostgreSQL equivalent
NVL, DECODE, SYSDATE
COALESCE, CASE, and current_timestamp / clock_timestamp() depending on required time semantics
ROWNUM filtering
LIMIT, row_number()
CONNECT BY hierarchies
WITH RECURSIVE
Oracle (+) outer join syntax
ANSI LEFT JOIN / RIGHT JOIN
seq.NEXTVAL
nextval('seq'), or identity columns
RAISE_APPLICATION_ERROR
RAISE EXCEPTION with an SQLSTATE
DBMS_OUTPUT
RAISE NOTICE
DBMS_SCHEDULER / DBMS_JOB
pg_cron or an external scheduler
Even in this group, some substitutions look mechanical but actually change behaviour. SYSDATE is the clearest example: current_timestamp is transaction-stable in PostgreSQL, while clock_timestamp() returns the actual current time, and time zone handling differs from Oracle. Anything that measures elapsed time, stamps
rows inside a long transaction or drives scheduling logic has to be reviewed rather than simply substituted.
Requires design decisions, not translation
- Packages. A package becomes a schema plus functions. Package-level session state has no direct equivalent; it is redesigned using custom GUCs, temporary tables, or by removing the state dependency.
- Autonomous transactions. Not supported in core PostgreSQL. Handled by restructuring the logic, or by a dblink / background worker pattern where the audit or logging requirement is genuine.
- Global temporary tables. No core equivalent; either session-scoped temporary tables or the pgtt extension, each with different implications for connection pooling.
- Optimiser hints. Not supported in core PostgreSQL. Plans that depend on hints are re-established through statistics, extended statistics, index design and query rewriting. The pg_hint_plan extension exists, but as a controlled exception, not a migration strategy.
-
Index-organised tables and bitmap indexes. No direct equivalents.
Addressed with covering indexes (INCLUDE), BRIN where data is physically
correlated, and revisiting the access path.
Deliverable: an object-level inventory with each item classified, effort-ranged and
owner-assigned – yours or ours. This is produced during the assessment, before
any commitment to a migration date.
Data types: mapping are the easy part, semantics is not
Type mapping is mostly mechanical. What causes production incidents is
behaviour that differs after a technically correct mapping.
Oracle
PostgreSQL
What to watch
VARCHAR2
varchar / text
Oracle treats an empty string as NULL. PostgreSQL does not. Every IS NULL check and every uniqueness assumption over nullable text columns has to be reviewed.
NUMBER (no precision)
numeric, or int / bigint
numeric arithmetic is materially slower than integer arithmetic. Mapping everything to numeric is a common cause of unnecessary CPU load after migration.
DATE
timestamp(0)
Oracle DATE carries a time component. Mapping it to date silently truncates.
TIMESTAMP WITH LOCAL
TIME ZONE
timestamptz
Session time zone handling differs; application assumptions must be re-tested, not assumed.
CLOB / BLOB / RAW
text / bytea
Streaming and size limits differ; check application drivers, not just the schema.
Implicit conversions
Stricter typing
Comparisons that Oracle resolved implicitly may fail or, worse, prevent index use.
Sort order is a separate topic. Collation differences between Oracle and
PostgreSQL – and between glibc versions – change index ordering and
comparison results. We fix collation explicitly at migration time rather than
inheriting the operating system default.
Oracle features and their PostgreSQL equivalents
Some capabilities map cleanly. Some do not, and a migration plan that pretends otherwise is a risk in itself.
Oracle
PostgreSQL approach
Honest assessment
RAC
Streaming replication with automated failover (Patroni)
PostgreSQL core has no direct RAC-style shared-storage active-active equivalent. Where RAC is used for scale-out writes rather than availability, the architecture changes.
Data Guard
Physical streaming replication, synchronous or asynchronous
Comparable in function; different in operation and tooling.
Flashback Database
PITR with pgBackRest or Barman
Different operational model; recovery requires restore and recovery rather than an in-place flashback.
Flashback Query (AS OF)
Application-level history, temporal or audit tables, or specialised solutions
No direct core equivalent. Querying a past state without a restore must be designed into the data model.
AWR / ASH
pg_stat_statements, activity sampling, pgBadger log analysis
Comparable insight, assembled from separate components rather than one built-in report.
Materialised view fast refresh
Full REFRESH MATERIALIZED VIEW CONCURRENTLY, or incremental patterns
No incremental refresh in core. Frequently refreshed materialised views need redesign.
Advanced Queuing
Durable queue extension, or an external broker such as Kafka or RabbitMQ; LISTEN/NOTIFY for notification-only use cases
LISTEN/NOTIFY is not a durable queue. The replacement depends on the persistence, ordering and delivery guarantees actually required.
Partitioning
Declarative partitioning
Mature; automatic partition creation can be handled with pg_partman or external automation.
Oracle Text
Built-in full-text search, pg_trgm
Capable; ranking and language configuration need explicit setup.
Oracle Spatial
PostGIS
Generally a strength of the target platform.
Label Security / VPD
Row-level security
Policy logic is rewritten, not ported.
Transparent Data Encryption
Filesystem or cloud-provider encryption
No core TDE. If your compliance requirement names TDE explicitly, this must be resolved at design time.
Where an equivalent does not exist, we say so during the assessment – not after
the contract is signed.
Performance after migration
There is no useful platform-wide performance prediction. Most migration risk is concentrated in a relatively small set of workload-specific queries – typically the heaviest reports – which is why we baseline and test them before cutover. The objective is a known, closed list at go/no-go, not a surprise discovered by users afterwards.
How we work with it
1.
Baseline capture on Oracle. The critical statement set, with execution times and volumes, taken from your production workload, not a synthetic benchmark.
2.
Written acceptance thresholds, per critical transaction and per critical report, agreed before testing begins. Typically expressed as a p95 target relative to the current baseline.
3.
Representative replay. Real query mix and real data volumes on production-comparable hardware. Testing on a subset gives plan shapes that do not survive contact with production.
4.
Plan-level analysis on the outliers: statistics, extended statistics for correlated predicates, index redesign, partitioning where it changes the access path, query rewriting where the SQL itself is the constraint.
5.
A closed list at go/no-go. Every query still outside its threshold is either resolved, accepted in writing, or a blocker.
Reporting and analytical workloads are a frequent source of regressions, and they
are where we concentrate most of our engineering effort – see the SQL
modernisation block below.
Downtime and dual run
The downtime you can afford determines the migration technique – not the other way round. We start from your downtime budget and work backwards.
Relative illustration, not to scale. The outage length of an offline migration scales with data volume; the two online approaches trade infrastructure and calendar time for a shorter switch.
Approach
Indicative cutover window
When it fits
Export / import (offline)
Hours, scaling with data volume
Smaller datasets, systems with a genuine maintenance window, non-24/7 operations
Change data capture with a synchronised switch
Potentially minutes, depending on cutover validation and application switching
Large datasets, limited window, systems where a long outage is not acceptable
Dual run with comparison period
Potentially minutes at switch, plus the parallel operation period
Business-critical systems where output must be proven equivalent before the source is retired
Dual run in practice: PostgreSQL runs alongside Oracle, fed by change data capture, while both systems are queried and their results compared on an agreed set of transactions and reports. It costs infrastructure, comparison effort and calendar time. For a critical system, that additional cost can be small compared with discovering functional differences after cutover.
We define, in writing and before the window opens; the downtime budget, who has the authority to call a stop, and what evidence is required at each checkpoint.
Rollback: the part most plans leave out
A migration plan without rollback criteria is a plan that assumes success. Rollback from Oracle to PostgreSQL has one property that makes it harder than a version upgrade: once the application writes to PostgreSQL, going back means moving those writes to Oracle.
Orange markers are decision points with a named owner and written criteria. Every one of them can stop the migration; none of them is a judgement call made in the room at the time.
How we handle it
- Reverse replication window, where technically feasible and justified. For systems that require a live Oracle fallback after cutover, we assess whether PostgreSQL-to-Oracle change replication can keep the source viable during an agreed rollback window. Feasibility depends on the replication technology, data types, DDL activity and sequence handling, and is established during the assessment - not assumed. Where it is not feasible, the rollback strategy and the acceptable data-loss position are agreed explicitly instead.
- Written stop criteria. Concrete and measurable: data validation failures, error rates, latency thresholds on named transactions, unrecoverable functional defects. Agreed in advance so the rollback decision is a test result, not a judgement call at 03:00.
- A named decision owner at each checkpoint, with the authority to stop.
- A rehearsed rollback. Practised at least once in the test environment, and timed. An untested rollback plan is documentation, not a capability.
- A defined end. The point at which Oracle is decommissioned is itself a decision with criteria - not a date that passes unnoticed.
Testing: what is verified and how
The test matrix is a deliverable, agreed at the start and used as evidence at go/no-go.
Layer
Verification
Schema
Object-by-object comparison: counts, constraints, indexes, permissions
Data integrity
Row counts and checksums per table; targeted validation of high-risk types and NULL semantics
Functional
Converted procedures and functions tested against expected results, not just successful compilation
Reports and analytics
Result-set comparison between Oracle and PostgreSQL on the same inputs. Differences in rounding, sort order and NULL handling are found here or by the business afterwards.
Performance
Acceptance thresholds per critical transaction and report, on representative data volumes
Operations
Backup, restore, failover, monitoring and alerting – exercised, not assumed
Security
Roles, privileges, row-level policies, audit and logging requirements
Report comparison is frequently under-invested in migration projects, and
differences found afterwards tend to arrive as business escalations rather than as
test results.
Proof in practice - Oracle to PostgreSQL
A business-critical financial-services application, with both transactional and reporting workloads on Oracle.
Compatibility assessment, modernisation of the critical SQL, report validation, cutover planning and operational handover.
Licensing and operational TCO
Licence savings are usually what start the conversation. They are not the whole calculation, and a business case built on the licence line alone tends to be painfully revised in year two.
The framework we use - both sides, all lines
- Licence and support costs, current and target
- Infrastructure: on-premises or cloud, including standby and test environments
- One-off migration cost: assessment, conversion, testing, dual run, cutover
- Application change effort, including regression testing on the application side
- Operational tooling that Oracle bundles and PostgreSQL assembles: high availability, backup, monitoring, connection pooling
- Skills: internal training, external support contract, or both
-
Risk cost: the value of downtime and rollback, at your business´s actual
numbers
We provide the framework and populate it with your figures during the assessment. We do not publish savings percentages, because the honest answer depends entirely on your licensing position, your workload and your operational maturity. In some cases the conclusion is that migration is not economically justified – and that is a legitimate assessment outcome, delivered before the spend, not after it.
Your team after go-live
The migration is complete when your team can run the platform without us.
- Runbooks for the operations that will actually be performed: backup and restore, failover, upgrades, routine maintenance, incident triage.
-
Architecture and decision documentation - including why each choice was
made, so it can be revisited later on an informed basis. -
Training for DBAs and developers on your migrated system rather than on
generic examples. Developer sessions focus on where PostgreSQL behaves
differently from Oracle, since that is where defects come from. - A handover with a defined end point, followed by an agreed support arrangement if you want one - not an assumed dependency.
SQL modernisation for database migration
Much of what makes a migration expensive is not the database – it is SQL
written against one specific platform over many years. Modernising it
reduces the rewrite, reduces the application changes, and usually improves
performance on both sides.
What it covers
-
Replacing platform-specific constructs with standard SQL, including analytic
(window) functions - often removing procedural loops and temporary-table
patterns entirely -
Reducing application-side changes by resolving differences in the SQL layer
instead of the application layer -
Optimising the heaviest reporting and analytical queries, which are the queries
most likely to regress after migration -
Performance verification after the rewrite, against the agreed acceptance
thresholds
It can be delivered as a standalone engagement before a migration
decision, or as a phase within the migration itself. Delivered first, it makes
the migration estimate more reliable.
Read our guide to SQL analytic functions
How we run the project
Assessment → Proof of concept → Test and dual run → Cutover → Stabilisation and handover.
Each phase has defined deliverables – risk register, test matrix, RPO/RTO
worksheet, cutover runbook, rollback criteria – and a go/no-go checkpoint before the next one starts. The full methodology, including what you receive at each stage, is on our database migration services page.
Why Baremon
-
Senior specialists, directly involved. Experienced consultants stay involved
from assessment through migration and stabilisation. -
25+ years in business-critical database environments. Hands-on experience
across Oracle, PostgreSQL, RAC, Data Guard, performance engineering and database migrations. - Public technical work. Conference talks, technical articles and public contributions to open-source projects and discussions show how we approach database engineering beyond client engagements: our P2D2 2025 talk on analytical functions (PDF)
- EU-based delivery. Data transfer, auditability and regulatory requirements are considered as part of migration design.
Frequently asked questions
It depends on the volume of PL/SQL, the number of dependent applications and
the acceptable downtime – not primarily on the data volume. The assessment
produces a defensible range for your system; a range quoted before an
assessment is guesswork.
No. The tooling converts most schema objects and a large share of
straightforward procedural code. The remainder requires design decisions –
package state, autonomous transactions, hint-dependent plans. The purpose of the assessment is to quantify that remainder precisely.
There is no meaningful platform-wide answer. Performance depends on the workload,
query patterns, indexing and target architecture. We baseline critical transactions
and reports on Oracle, test them on PostgreSQL and resolve or explicitly accept
every material regression before cutover.
Usually, to some degree: SQL differences, driver behaviour, transaction handling.
The amount is quantified during the assessment, and part of it can often be
handled in the SQL layer rather than in the application code.
PostgreSQL core has no direct RAC-style shared-storage active-active equivalent.
Where RAC provides availability, streaming replication with automated failover is the accepted approach. Where RAC provides write scale-out, the architecture requires a separate design discussion – and which we would have that before, not during,
the project.
Not to zero. Change data capture approaches can reduce the switchover to minutes
rather than hours, depending on the application cutover and validation
requirements. We plan to a downtime budget you set, and we state honestly what
each option costs.
Discuss your Oracle to PostgreSQL migration
Tell us what you are running and what your constraints are. We will review your situation and respond within two business days.