India’s Leading Domain & Web Hosting Provider
Support
Domain & Login
Blog
Database Modernization: Oracle and SQL Server to Managed PostgreSQL
Managed database modernization changes a commercial database engine, usually Oracle or SQL Server, to an open-source-compatible managed service such as Aurora PostgreSQL, AlloyDB or Azure Database for PostgreSQL, including conversion of schemas, stored code and application SQL. NTSPL converts and refactors PL/SQL and T-SQL, validates data and performance, and cuts over for organisations exiting licence and support costs.
Delivered on
In scope
Why stored code, not data volume, sets the effort
Oracle Enterprise Edition licences, annual support of roughly 22% of licence value, and the hardware they pin are often the largest line in an Indian IT budget after salaries. PostgreSQL is the obvious exit, but the cost is not the data; it is the code. A typical registrar or lending system carries 2,000-6,000 stored procedures and packages using DBMS_JOB, UTL_FILE and autonomous transactions, CONNECT BY hierarchies, and application SQL full of hints. Tools convert 70-90% automatically. The rest is where programmes overrun, and where NULL, empty-string and precision differences corrupt results quietly.
NTSPL starts with a conversion assessment from DMS Schema Conversion, Database Migration Service conversion workspaces or Ora2Pg, then classifies every object that will not convert into a pattern library covering package state, autonomous transactions, collections, scheduling and file I/O, each with a standard replacement. Application SQL captured from AWR, Query Store or traces is replayed against both engines and results compared row by row. Data moves with CDC so the final cutover takes minutes, and reverse replication keeps a fallback open.
When an engine change beats renewing the licence
Signals you need Managed Database Modernization
- An Oracle or SQL Server Enterprise renewal or support uplift worth more than INR 50 lakh a year falls due within 12-18 months.
- Hardware hosting licensed databases reaches end of life, and core-based licensing makes equivalent cloud VMs expensive.
- Under 10% of the schema relies on features with no PostgreSQL equivalent, such as Advanced Queuing at scale.
- Your own teams own the application code that issues SQL and can change and retest it.
- You need read scale-out or multi-region reads that are unaffordable on the current licence.
When to choose something else
- Packaged applications certified only on Oracle or SQL Server: move same-engine to RDS, Azure SQL Managed Instance or an Oracle Database@ cloud service under Database Migration instead.
- Systems retiring within two years: licence optimisation or a same-engine move costs less than conversion.
- Warehouses and reporting databases such as Teradata, Netezza or SQL Server DW belong to Data Modernization & Migration, targeting a cloud warehouse or lakehouse.
Engine-change workstreams
Conversion assessment
Automated reports quantify objects by complexity; every manual item is sized and mapped to a pattern.
Schema and code conversion
Tables, types, sequences, packages, procedures and triggers converted; complex logic refactored to PL/pgSQL or application code.
Application SQL remediation
SQL captured from AWR, Query Store or tracing rewritten for PostgreSQL; hints removed and ORM dialects switched.
Data migration and CDC
Full load plus change data capture, LOB handling, character-set conversion and checksum validation.
Performance validation
Replay of the top 200 queries and key batch jobs, with index, plan and parameter tuning and connection pooling.
Cutover and fallback
Rehearsed runbook, reverse replication for a fallback window, and licence decommission evidence.
Convert, replay and replicate pipeline
Convert, Replay and Replicate Pipeline for Engine Change
Conversion tooling translates the source schema and flags manual items, which engineers refactor using a pattern library. Captured production SQL is replayed against both engines for result and timing comparison, while CDC keeps the target in sync until a short cutover with reverse replication as the fallback.
Select any component
Convert, Replay and Replicate Pipeline for Engine Change: click any of its 10 components to see its role and the design choice NTSPL makes for it, or trace the 7-step primary path.
Target PostgreSQL services on each cloud
DMS Schema Conversion assesses and converts Oracle or SQL Server schemas and DMS handles full load and CDC. For SQL Server apps whose T-SQL cannot change quickly, Babelfish serves as an interim step.
Ora2Pg handles Oracle assessment and conversion into flexible server, built-in PgBouncer pools connections, and zone-redundant high availability in Central India keeps a standby in another zone.
Conversion workspaces convert Oracle schemas and code, and DMS migrates data with CDC into AlloyDB or Cloud SQL. AlloyDB's columnar engine helps when Oracle also served reporting queries.
Engine-change decisions to make early
Where stored logic should live
Keep set-based data logic in PL/pgSQL. Move orchestration, file handling (UTL_FILE), e-mail and HTTP calls into application services or functions. As a rule of thumb, if a procedure exceeds 500 lines and mixes business decisions with data access, refactor it into service code during conversion rather than translating it line by line.
Choosing the target service
Choose the cloud where the application runs, then size within it. Aurora PostgreSQL or AlloyDB suit databases needing fast failover, many read replicas and auto-growing storage. RDS for PostgreSQL, Azure flexible server or Cloud SQL suit databases under about 2 TB with modest read scale, at noticeably lower cost per month.
Interim compatibility layers
Babelfish, the orafce extension and compatibility schemas speed up cutover but carry subtle behaviour differences. Use them for low-risk code with a 6-12 month plan to remove them. Do not let shims become the permanent architecture, or new code will keep targeting the old dialect and the exit will never finish.
Cutover and fallback window
Plan a cutover under 60 minutes with CDC lag below 30 seconds at switch time. Keep reverse replication to the source for 2-4 weeks so you can fall back without losing data, and keep the old licence until that window closes. Rehearse at least twice with production-sized data, timing every runbook step.
From conversion report to cutover weekend
Conversion assessment and pattern library
Run assessment tools, size manual items and build tested replacement patterns.
Schema and code conversion sprints
Convert module by module, each object with unit tests against known outputs.
Application SQL remediation and replay
Rewrite captured SQL and diff results and timings on both engines.
Data migration, CDC and tuning
Full load, CDC, index and parameter tuning and connection pooling.
Rehearsals and cutover
Two timed rehearsals, then cutover with business sign-off.
Fallback window and licence exit
Reverse replication running, then source shutdown and licence termination.
What you receive
Conversion and cutover quality targets
| KPI | Typical target |
|---|---|
| Converted objects with passing tests | 100% before cutover |
| Replayed queries returning identical results | 100%, with timing equal or better within ±20% |
| Cutover downtime | Under 60 minutes |
| Unexplained row or checksum differences | Zero |
| Database licence and support cost removed | Typically 60-80% of database run cost |
Silent correctness risks in engine changes
| Risk | How NTSPL prevents it |
|---|---|
| Empty string versus NULL behaviour | Oracle treats empty strings as NULL and PostgreSQL does not, so code and data are scanned for this and explicit handling is tested. |
| Numeric and date precision changes | NUMBER and DATE mappings are reviewed per column; money uses NUMERIC with defined scale and time zones are made explicit. |
| Plan regressions on key queries | The top 200 queries by elapsed time are replayed and tuned before cutover, with pg_stat_statements baselines from week one. |
| Long cutover caused by LOB-heavy tables | LOB tables migrate early and stay in sync through CDC, so cutover only flips connections. |
Same-engine migration or an engine change to PostgreSQL?
Move same-engine if the application is packaged and certified only on Oracle or SQL Server, if it retires within two years, or if your team cannot change and retest its SQL. Change engines when licence and support costs are material, the code is yours, and the assessment shows that under roughly 15-20% of objects need manual conversion. Many programmes do both: a same-engine move first to exit hardware quickly, then an engine change once teams and test suites are ready. That spreads risk at the cost of paying the commercial licence for a few more quarters.
Key facts: Managed Database Modernization
Managed Database Modernization in Practice
2 Managed Database Modernization scenarios, from Investor servicing registrar (mutual fund and AIF registry services) and Chit funds, plus related platform case studies.
Fund registrar converts 3,400 Oracle objects to AlloyDB and exits Enterprise Edition
Chit fund company moves auction and payout logic from Oracle 11g to Azure PostgreSQL
NBFC moves loan-origination Oracle database to Aurora PostgreSQL and drops EE licences
Microfinance lender moves loan SQL Server to Managed Instance in an 18-minute cutover
Managed Database Modernization: Frequently Asked Questions
How much does an Oracle-to-PostgreSQL conversion cost?
It scales with stored code, not data volume. A database with about 1,000 procedures and packages and 2-5 TB of data typically takes 16-20 weeks and INR 60 lakh to 1.2 crore in services. For Enterprise Edition estates, licence and support savings usually repay this within 12-24 months.
Can regulated financial data stay in India during and after migration?
Yes. Targets run in Indian regions such as AWS Mumbai or Hyderabad, Azure Central India and Google Cloud Mumbai or Delhi, with DR replicas inside India. For SEBI- or RBI-regulated entities we encrypt migration copies, mask non-production data and document lineage for audit, consistent with the DPDP Act 2023.
What happens to Oracle packages and DBMS_SCHEDULER jobs?
Packages become schemas of functions, and package variables are replaced by session settings or application state. Scheduled jobs move to pg_cron where the service supports it, or to cloud schedulers calling functions, which give better visibility and retries than in-database scheduling.
Will performance stay the same after the switch?
Most OLTP workloads perform the same or better after tuning, but a few queries usually regress because the optimiser differs. That is why we replay the top queries before cutover and tune indexes, statistics and SQL. We never go live on untested query plans for critical transactions.
Can AI tools convert PL/SQL for us?
They speed up the manual portion. We use generative assistants to propose conversions for complex procedures, but every output passes the same unit tests and dual-engine replay as hand-written code, because subtle semantic errors in generated conversions are common, especially around NULL handling and implicit type casts.
How much application code has to change?
Usually some: drivers, ORM dialect settings, Oracle-specific SQL and hints. With Hibernate or Entity Framework the changes are often small; with hand-written SQL embedded in code, application remediation can be a third of total effort, which the assessment sizes up front.