Migrate from Oracle to PostgreSQL

Stored code is where most of the manual effort in an Oracle to PostgreSQL migration will sit. PL/SQL packages, stored procedures and triggers look similar to PL/pgSQL on the page, which tempts teams to assume that a converted file will behave the same way. It often will not. Plan for the differences in exception handling, package state, cursor behavior, autonomous transactions and built-in packages before you estimate the code workstream, and treat every package as a unit of rework rather than a line-by-line translation.

Ora2Pg converts the parts of PL/SQL it can map to PL/pgSQL and writes the results into files you can review. Its coverage changes between releases, so confirm for the exact Ora2Pg version you plan to use which object types, statements and built-in functions it converts automatically, which it converts partially, and which it flags for manual work. Run it against a copy of your schema first and record the counts of converted and flagged objects. Those counts, not the total object count, are the starting point for the effort conversation with any partner or internal team.

Review converted code with the Oracle source open beside it. Check package initialization blocks and any session-level variables, because PostgreSQL has no direct equivalent of package state that persists across calls in the same session. Check every exception handler, since error codes, named exceptions and the behavior of unhandled errors differ. Look at cursor loops and any use of implicit cursor attributes. Where code relies on autonomous transactions, decide whether to move the work into a separate procedure that uses dblink or a queued write, or to redesign the logic. Record each decision, because auditors and application owners will ask why a given construct changed.

Keep a regression test suite that runs the same business cases against Oracle and PostgreSQL. Choose cases from the processes that matter most to the business, such as end-of-day batch jobs, interest or fee calculations, and reporting queries, and capture the expected outputs from the Oracle system. Run identical inputs through both databases and compare results, including rounding, null handling, date arithmetic and ordering of rows without an explicit sort. Automated comparison catches defects that code review misses, and it gives you evidence to show during cutover sign-off.

Ask any partner to show how they will track converted, flagged and rewritten objects, who owns each decision, and how the test suite will be maintained after cutover. Confirm whether the test cases and comparison scripts become your property, and whether the database team will be able to run them without the partner. These questions matter more than the conversion percentage, because the code you inherit will change as the application changes.

An offline initial load copies the Oracle data into PostgreSQL while the source is frozen, so both sides hold the same consistent snapshot. Without a freeze, rows written during the copy can land in one table but not its parent, and reconciliation later becomes guesswork. The decision to make early is how long the application can be unavailable, because that window sets the ceiling on how much data you can move in one pass. Confirm the business tolerance for read-only time with the application owners before anyone plans the load itself, and record it in the cutover runbook. Plan the window from rehearsal timings, not estimates. Run the full Ora2Pg data export and import at least twice against a copy of production sized like production. Time each table, index build and constraint validation separately, and note where the time goes. Index creation and foreign key validation often dominate, which is why many teams load data first and build indexes and constraints afterward. Use the rehearsal figures, plus a margin you agree in advance, to set the window. If the rehearsal overruns, reduce the work inside the window (parallel table jobs, deferred indexes, or moving history tables to a separate phase) rather than extending the window on hope.

Order the loads around dependencies. Load parent tables before child tables so foreign keys can be validated as they are added, or add them as NOT VALID and validate them after the data is in. Load reference and lookup tables first because many other tables depend on them. Reset sequences only after the data load finishes, setting each one above the maximum migrated key so new inserts do not collide. Handle large LOB columns (BLOB and CLOB data) as a separate track: they are slow to move, can dominate memory and I/O, and sometimes benefit from being exported in chunks or migrated after the main window if the application can tolerate the delay. Decide per table, and test each LOB path in the rehearsal.

Validate in layers. Start with row counts per table, compared between Oracle and PostgreSQL after the freeze. Counts catch missing tables and failed batches but not corrupted values, so add checksums or sampled value comparisons for critical tables. Hash a normalized column list on both sides, or compare a fixed random sample of primary keys field by field, and document how dates, timestamps, numeric precision and character encodings are normalized. Then check sequence positions against the maximum keys in use, and confirm that every constraint, index and trigger you planned exists and is valid. Ora2Pg's reports help, but verify the results yourself.

Reconcile business totals before cutover. Choose a small set of figures the business already trusts, such as account balances, open order value, invoice totals by period or record counts per customer segment. Run the same queries on Oracle and PostgreSQL against the frozen snapshot and require exact matches or a documented explanation for each difference. Involve finance or operations owners in signing off these results, because they catch logic errors that technical checks miss. Keep the queries, outputs and sign-off in version control as cutover evidence. Decide what blocks cutover. Agree before the rehearsal which mismatches stop the go-live decision, such as any count difference on a financial table or any failed constraint validation, and which are acceptable with a written reason. Confirm with your own data governance and compliance owners how validation records are retained, because they may be needed later for audit.

Plan application work as its own workstream before the data moves. Start with connections. Each application uses an Oracle client library, such as a JDBC, ODP.NET or OCI driver, and a connection string that names an Oracle service, a host and a schema. Those drivers must be replaced with PostgreSQL drivers, and connection strings, TLS settings and the default schema or search_path must be rewritten and tested. Ask each team to list its driver, its driver version, its ORM and the database account it connects as. Oracle schemas map to PostgreSQL schemas and roles, so the list also shows which application accounts need grants in the target. Treat the inventory as the input to every later test, because a missed connection usually surfaces only after cutover.

Generated SQL is the second area. Ora2Pg's assessment and conversion output covers the database objects it can read, such as PL/SQL packages and procedures, but queries built by ORMs, query builders and reporting tools are assembled at runtime and need their own review. Look for Oracle-specific constructs such as ROWNUM, the (+) outer join operator, DECODE and NVL, DUAL, and sequence references written in Oracle form. Also check how each ORM is configured for its dialect, how empty strings are handled, since PostgreSQL keeps empty strings distinct from NULL, and how date and timestamp arithmetic behaves. Capture statements from application logs or a trace during a representative test run, then replay them against PostgreSQL. A query that parses but returns different rows is the failure to look for.

Transaction and isolation assumptions need the same scrutiny. Both databases default to READ COMMITTED, so simple reads behave similarly, but the details differ. Review code that relies on readers never blocking writers, on particular locking behavior from SELECT ... FOR UPDATE, or on how sequence values are used after a rollback. PostgreSQL can return serialization failures at REPEATABLE READ and SERIALIZABLE, so any code that uses those levels needs retry logic. DDL is transactional in PostgreSQL, which changes how deployment scripts handle partial failures. Check the autocommit setting in each driver, because a driver default can change how a batch job commits. Write these checks into the test plan with a named owner for each one.

Authentication and pooling decide how applications connect in production. PostgreSQL 18 deprecates MD5 password authentication in favor of SCRAM, so new roles should use SCRAM and existing MD5 roles should be migrated before the cutover window. The client authentication documentation lists several methods, including password, GSSAPI, SSPI, LDAP, RADIUS, certificate, PAM and OAuth. Confirm which method each application and reporting tool can use with its driver, and confirm the choice against your own identity setup, including the directory, the Kerberos realm, the certificate authority and the service accounts. If connection pooling with PgBouncer is part of the design, choose the pooling mode with care, because session-level state and prepared statements behave differently across modes. Confirm that you run a current PgBouncer release, since version 1.26.1, released October 14, 2026, included security fixes.

Reporting tools need their own plan. Most BI and dashboard products connect to Oracle through a vendor driver, a semantic layer or saved queries that reference Oracle schema names. Build an inventory of every report, dashboard, extract and scheduled export, with its owner and the connector it uses. Identifier case is a common trap. Oracle folds unquoted identifiers to uppercase, while PostgreSQL folds them to lowercase, so queries and metadata that quote names can fail or return nothing. Ask each tool vendor which PostgreSQL driver and authentication methods it supports for your version. Then run each high-value report against a restored copy, compare the results with the Oracle output, and agree the cutover order with the business owners of those reports.