By Yash Amin, Founder and CEO · Published · Updated
How we moved a US insurance platform from Oracle to PostgreSQL with zero downtime and 60%+ lower licensing cost
The data wasn't the hard part. The business logic sat in 120+ PL/SQL procedures and Oracle-only features, and none of it could change behavior.
- Industry
- Insurance, US
- Platform
- Oracle 12c to PostgreSQL 15
- Engagement
- Database migration
- Our role
- Migration and database engineering
Data integrity preserved
Zero records lost or corrupted
Production downtime
Cutover during a low-traffic window
Lower licensing cost
Oracle licensing fully eliminated
About the Client
A US insurance product company that runs policy administration and claims management on a single Oracle Database 12c platform. Premiums, claims decisions and regulatory filings all come out of that database, so its accuracy is the business.
| Detail | Specifics |
|---|---|
| Industry and size | US insurance product company; policy administration and claims management on one database |
| Data volume | Years of policy and claims history in partitioned tables |
| Platform | Oracle Database 12c, PL/SQL, DBMS_SCHEDULER, custom audit triggers |
| Team | Business logic lived in the database; the migration had to preserve it without changing behavior |
| State before engagement | 120+ PL/SQL procedures, 30+ scheduled jobs, rising Oracle licensing cost, zero-downtime requirement |
The Situation
Oracle licensing costs were rising, and the platform needed modernizing. Moving to PostgreSQL made sense on cost, but the database held years of PL/SQL logic for premium calculations, claims adjudication and regulatory reporting.
A logic error or lost record would have direct regulatory and financial consequences. Policy processing also ran continuously, so there was no window to take the system offline.
Challenges
Business logic written in Oracle-only syntax
Premium calculations, claims adjudication rules and regulatory reporting sat in 120+ PL/SQL procedures. Each one had to become PL/pgSQL with no functional deviation.
Oracle-specific constructs throughout the schema
NUMBER, VARCHAR2 and DATE semantics, sequences, synonyms, DB links, partitioned tables, CONNECT BY queries and 30+ DBMS_SCHEDULER jobs had no direct PostgreSQL equivalent.
A regulatory audit trail and no room for downtime
Custom audit triggers tracked every DML change for compliance, and policy processing could not stop during cutover.
Diagnosis
Before converting anything, we inventoried every Oracle-specific object in the schema. Four things decided how the migration had to be run.
The risk was in the logic, not the data
Moving rows is routine. The 120+ procedures held the premium, claims and reporting rules, so any silent change in behavior would reach regulators and customers.
Data types carried financial precision
Oracle NUMBER precision and scale, and DATE semantics, had to be mapped and validated type by type. A loose mapping would shift financial values.
Hierarchy, partitioning and scheduling were Oracle features
CONNECT BY queries, Oracle partitioning and DBMS_SCHEDULER jobs each needed a PostgreSQL redesign, not a translation.
Compliance depended on the audit triggers
The custom triggers were the regulatory record. They had to be rebuilt with identical change-tracking coverage before any cutover.
What We Did
Audited the schema, then converted the logic with equivalence tests
We catalogued every Oracle-specific construct first: tables, views, procedures, functions, packages, triggers, jobs and dependencies. The audit became the conversion inventory.
All 120+ PL/SQL packages and procedures were converted to PL/pgSQL, each with functional equivalence testing. Oracle data types were mapped to PostgreSQL equivalents with precision validation for financial values.
Rebuilt the Oracle-specific structures on PostgreSQL features
Oracle partitioning was replaced with PostgreSQL declarative partitioning (range and list) for the policy tables. CONNECT BY hierarchical queries were rewritten as recursive CTEs.
The 30+ DBMS_SCHEDULER jobs moved to pg_cron. Audit triggers were rebuilt in PL/pgSQL with the same change-tracking coverage, so the compliance record stayed intact.
Ran Oracle and PostgreSQL in parallel with logical replication
Logical replication kept PostgreSQL in step with Oracle while both systems ran side by side. That removed the need for a downtime window during the move.
Proved it with rehearsals and report comparison before cutover
We executed 3 full rehearsal migrations against production data copies and ran validation scripts comparing Oracle and PostgreSQL output across all reports. Cutover happened in a low-traffic window with a documented rollback path.
Architecture: Before and After
Swipe the diagram sideways to read it.
View diagram full sizeBefore
Policy administration, claims, portals, reports and 30+ scheduled jobs all ran on Oracle 12c, with 120+ PL/SQL procedures, partitioned tables and custom audit triggers tied to Oracle features.
After
The same workloads run on PostgreSQL 15 with PL/pgSQL, declarative partitioning, recursive CTEs, pg_cron and rebuilt audit triggers. Logical replication carried the cutover, with validation and rollback readiness at each stage.
Before and After
| Step | Before | After |
|---|---|---|
| Schema analysis | Manual review of Oracle objects | Full audit and inventory of every Oracle-specific construct |
| Code conversion | Hand-converted, high-risk logic | Structured PL/pgSQL conversion with equivalence testing |
| Validation | Outputs checked late in the project | Data and report comparison against Oracle throughout |
| Scheduled jobs | 30+ DBMS_SCHEDULER jobs | pg_cron |
| Cutover | Risky switch with possible downtime | Parallel run on logical replication, 3 rehearsals, low-traffic window |
| Rollback | No documented path | Documented and rehearsed rollback plan |
Results
Swipe the table sideways to see the Change column.
| Metric | Before | After | Change |
|---|---|---|---|
| Data integrity | Oracle source of record | 100% preserved | Zero records lost or corrupted |
| Production downtime | Downtime risk during migration | None | Zero downtime |
| Licensing cost | Oracle licensing | Eliminated | 60%+ lower |
| PL/SQL procedures | 120+ in PL/SQL | 120+ in PL/pgSQL | Business logic preserved |
| Scheduled jobs | 30+ DBMS_SCHEDULER jobs | 30+ pg_cron jobs | All re-implemented |
| Regulatory compliance | Custom Oracle audit triggers | Rebuilt PL/pgSQL triggers | Full coverage kept |
What changed for the client
- All 120+ stored procedures run on PostgreSQL with their business logic unchanged.
- Reports, calculations and compliance checks match the Oracle output.
- Oracle licensing is fully eliminated.
- Audit triggers track every DML change, as they did before.
- Policy processing ran through the cutover with no production downtime.
Technology Stack
| Layer | Tool |
|---|---|
| Source database | Oracle Database 12c, PL/SQL, SQL*Plus |
| Target database | PostgreSQL 15, PL/pgSQL, declarative partitioning, recursive CTEs |
| Conversion | ora2pg |
| Replication | PostgreSQL logical replication |
| Orchestration | pg_cron |
| Administration | pgAdmin |
“The 10 years of Oracle PL/SQL business logic were migrated successfully, and our reports, calculations and compliance checks matched.”
Oracle to PostgreSQL Migration Questions
How do you migrate Oracle PL/SQL stored procedures to PostgreSQL?
Audit the schema first and catalogue every Oracle-specific construct, then convert the code with functional equivalence testing. In this migration, 120+ PL/SQL packages and procedures became PL/pgSQL, and Oracle data types were mapped to PostgreSQL equivalents with precision validation for financial values. The ora2pg tool handled conversion, and every converted procedure was tested against the Oracle output.
Can an Oracle to PostgreSQL migration be done with zero downtime?
Yes. PostgreSQL logical replication kept the new database in step with Oracle while both ran side by side. The team ran 3 full rehearsal migrations against production data copies, compared reports between the two systems, and cut over in a low-traffic window with a documented rollback path. Policy processing continued through the cutover with no production downtime.
What replaces Oracle-specific features such as CONNECT BY, partitioning and DBMS_SCHEDULER in PostgreSQL?
CONNECT BY hierarchical queries become recursive CTEs. Oracle partitioning becomes PostgreSQL declarative partitioning (range and list). DBMS_SCHEDULER jobs move to pg_cron; 30+ jobs were re-implemented this way. Each needed a PostgreSQL redesign, not a line-by-line translation.
How much does moving from Oracle to PostgreSQL reduce licensing cost?
In this engagement, licensing cost fell by 60%+ because Oracle licensing was fully eliminated. The saving depends on the Oracle edition, options and core counts a company was paying for.
How do you keep a regulatory audit trail during a database migration?
Rebuild the audit triggers on the target database with the same change-tracking coverage before any cutover. Here, the custom Oracle triggers that tracked every DML change were rebuilt in PL/pgSQL, so the compliance record stayed intact.
What are the main challenges in an Oracle to PostgreSQL migration?
Three stood out here. Business logic written in Oracle-only syntax: 120+ PL/SQL procedures holding premium, claims and reporting rules had to become PL/pgSQL with no change in behavior. Oracle-specific constructs with no direct equivalent: NUMBER and DATE semantics, sequences, synonyms, DB links, partitioned tables, CONNECT BY queries and 30+ DBMS_SCHEDULER jobs. And a regulatory audit trail with no room for downtime, since custom triggers tracked every DML change.
How do you convert Oracle packages to PostgreSQL?
Catalogue every package, procedure, function and trigger first, then convert them to PL/pgSQL with functional equivalence testing: the same inputs must give the same outputs as Oracle. Map data types with precision validation, because a loose NUMBER mapping can shift financial values. Here, 120+ packages and procedures were converted this way using ora2pg, and reports were compared against Oracle output across the whole system.
Carrying years of PL/SQL you can't afford to break?
On a diagnostic call we walk through your Oracle schema, the PL/SQL that carries your business rules, and how a cutover could run without downtime.
Start with a Discovery Call