TECHNICAL SOLUTION GUIDEOracle and PostgreSQL | Case 07 | September 2026

Oracle to PostgreSQL Migration for TCO, Performance, and Multi-Model Capabilities

Overview

Enterprises want to reduce proprietary database dependency, improve price-performance for suitable workloads, and use PostgreSQL relational, JSON, geospatial, vector, full-text, and extension capabilities without compromising correctness or recoverability.

Oracle-to-PostgreSQL migration is not a syntax conversion exercise. Feature semantics, PL/SQL, data types, optimizer behavior, security, jobs, operational tooling, performance, licensing assumptions, and cutover consistency must be validated workload by workload.

The recommended design uses assessment-led heterogeneous database modernization under a governed OSS Manager workflow. The operating model keeps schemas, tables, constraints, indexes, sequences, PL/SQL, jobs, security, historical data, change stream, and application queries within the engine's native consistency and recovery semantics while standardizing planning, approvals, status, evidence, and day-two operations.

OSS Manager coordinates lifecycle operations and operational evidence. Engine-native consistency and application-level correctness remain explicit design responsibilities.

Use Case and Business Need

Why Current Operating Model is Insufficient

Current Challenge

Manual operations obscure what changed and whether the application remained correct. The solution connects the workload requirements above to explicit ownership, safe change and a tested recovery path.

Intended Outcomes

Solution Goals

✓A repeatable, reviewable architecture rather than a collection of host-specific steps.
✓Preflight checks that stop execution when capacity, access, topology, or safety assumptions are false.
✓Explicit separation between planning, approval, execution, and verification.
✓Engine-native health checks combined with application-level acceptance tests.
✓Evidence that L1, L2, platform, security, and audit teams can interpret without privileged data access.

Target Architecture

Figure 1 Reference architecture and operating flow

Architecture Responsibilities

Oracle remains the source of record while target PostgreSQL is designed and provisioned. Schema and code are assessed and converted, bulk data is loaded, ongoing changes are captured, reconciliation and workload tests run, and cutover occurs through a reversible application endpoint plan.

↔ Swipe horizontally to view full table

Layer	Responsibility
Application	Own business transactions, idempotency, user-facing error handling, and business acceptance tests.
Oracle and PostgreSQL	Own native consistency, storage, replication, query, and recovery behavior for schemas, tables, constraints, indexes, sequences, PL/SQL, jobs, security, historical data, change stream, and application queries.
OSS Manager	Own declared topology, preflight, controlled lifecycle orchestration, status normalization, audit evidence, and guarded recovery workflow.
Observability	Collect and retain object conversion; row and checksum difference; CDC lag; error queue; query latency and throughput; resource use; application success; cutover duration; cost model variance.
Enterprise controls	Own identity, network, secrets, change approval, artifact trust, incident management, and retention policy.

Design Principles and Control Boundaries

•Declare before changing: Topology and policy are reviewed in configuration before any mutation.
•Observe before owning: Brownfield or recovery workflows start by discovering runtime state and differences.
•Plan is not execution: A plan may read and validate but cannot silently cross into an apply, restore, or removal action.
•Explicit write ownership: A data directory has one runtime owner. Multi-writer designs additionally require a defined key, row or topic ownership model and conflict policy.
•Protect the last healthy copy: No move, removal, or restore may destroy the only known usable copy of required data.
•Verify business behavior: Process health is necessary but not sufficient; representative application paths must pass.
•Retain rollback evidence: The pre-change state, artifacts, configuration, and decision record remain available through the rollback window.

Prerequisites and Discovery

Before implementation, customer and delivery teams must complete and approve the following discovery package.

↔ Swipe horizontally to view full table

Area	Exit condition
Topology	Hosts, roles, zones or sites, ports, service endpoints, storage paths, and dependencies are inventoried.
Access	Named SSH and engine identities are available through approved secret references; no plaintext credentials appear in YAML or logs.
Network	Required east-west, client, monitoring, backup, and management paths are tested in both directions.
Platform	Supported operating system, architecture, time synchronization, DNS, certificates, packages, and filesystem ownership are confirmed.
Data	Current volume and growth are recorded for schemas, tables, constraints, indexes, sequences, PL/SQL, jobs, security, historical data, change stream, and application queries; retention and legal constraints are documented.
Capacity	Database and lob size, change rate, conversion complexity, load throughput, index build, cdc retention, test workload, dual-run duration, rollback window, and target growth are measured rather than assumed.
Recovery	RPO, RTO, rollback window, authoritative copy, and application validation owners are named.
Change	Maintenance window, approvers, escalation contacts, stop conditions, and communications are agreed.

OSS Manager Solution

OSS Manager provides an assessment and execution control plane: inventory, target provisioning, migration configuration, staged load and CDC, reconciliation, performance evidence, cutover gates, rollback inputs, and audit. TCO and performance improvements are measured targets, not guaranteed claims; application and database owners approve every semantic change.

Capability model

↔ Swipe horizontally to view full table

Stage	What it must do
Discover	Read the existing engine, service, topology, policy, storage, and security posture without mutation.
Plan	Resolve desired versus observed state and list checks, changes, warnings, approvals, and rollback inputs.
Validate	Prove access, artifacts, topology, capacity, policy, and safety invariants before execution.
Apply or recover	Execute only the reviewed scope, persist progress, stop on failed gates, and avoid unrelated changes.
Status	Combine engine-native and host-level evidence into operator-readable health and drift results.
Verify	Run post-change topology, data, security, observability, and application checks.
Evidence	Retain the plan, approval, command result, before-and-after state, and unresolved exceptions.

Configuration and Deployment Contract

The deployment specification captures assessment-led heterogeneous database modernization. Configuration is reviewed against the schema supported by the chosen release; architecture intent is not a substitute for a validated runtime configuration.

•Topology and resources: Oracle remains the source of record while target PostgreSQL is designed and provisioned. Schema and code are assessed and converted, bulk data is loaded, ongoing changes are captured, reconciliation and workload tests run, and cutover occurs through a reversible application endpoint plan.
•Operational controls: license and estate baseline, feature mapping, schema conversion, code remediation, type semantics, bulk load, CDC, reconciliation, query tuning, security, cutover, rollback, and decommission approval.
•Workload and recovery sizing: database and LOB size, change rate, conversion complexity, load throughput, index build, CDC retention, test workload, dual-run duration, rollback window, and target growth.
•Identity and security: named operators, protected credential references, network boundaries and an approved access model.

Configuration review checklist

•Record approved versions, paths, thresholds, sites, ports and service identities.
•Reference secrets through protected environment or vault integration; never store passwords or private keys in the document or YAML repository.
•Confirm that every declared host and service identity has the minimum required permissions.
•Review destructive, removal, ownership-transfer, and recovery fields with the named approver.
•Commit the reviewed configuration and record its immutable revision or checksum in the change ticket.

Implementation Workflow

Implementation follows the release-supported workflow for this engine. Each stage has a clear exit condition before the next action begins.

↔ Swipe horizontally to view full table

Stage	Operational action	Required result
1 Discover	Read current topology and ownership	Inventory and differences; no mutation.
2 Plan	Review configuration and change scope	Ordered changes, warnings, approvals, and rollback inputs.
3 Validate	Run engine-specific preflight	All mandatory preflight checks pass.
4 Execute	Apply the approved change	Only reviewed scope runs; progress is retained.
5 Refresh	Inspect runtime and application behavior	Observed runtime matches the intended topology and policy.
6 Evidence	Retain sanitized operational results	Post-change evidence and exceptions are accessible.

Staged rollout

Laboratory rehearsal with representative topology, data shape, and failure injection.
Non-production deployment with application owners and monitoring connected.
Recovery or rollback rehearsal from retained artifacts and previous configuration.
Production canary or lowest-risk segment, where the architecture permits segmentation.
Production rollout with live stop conditions, named approvers, and post-change observation window.
Closure only after evidence, exceptions, and operational handover are accepted.

Capacity and Infrastructure Planning

Sizing must be based on database and LOB size, change rate, conversion complexity, load throughput, index build, CDC retention, test workload, dual-run duration, rollback window, and target growth. Benchmark with representative data and concurrency before production commitment.

↔ Swipe horizontally to view full table

Component	Baseline planning consideration	Production decision
Managed engine nodes	CPU, memory, local persistence, network, fault-domain separation, and maintenance headroom.	Derived from measured workload and engine vendor guidance.
OSS Manager bastion	Dedicated management host, 4 vCPU, 8 GB RAM, protected state, and restricted SSH reachability as a starting point.	Increase for large fleets, concurrent operations, or local artifact hosting.
Observability	Dedicated Prometheus and Grafana capacity, metric retention, cardinality limits, and alert routing.	Size from target count, scrape interval, retention, and dashboard concurrency.
Backup or snapshot storage	At least the protected data size plus retention, change rate, temporary restore space, and integrity copies.	Use tested throughput that meets backup and recovery windows.
Network	Client, replication, recovery, monitoring, and management traffic must be modelled separately.	Capacity test peak and recovery catch-up, not only steady state.

Security and Compliance

•Use named human and service identities. Shared administrator accounts are not an acceptable operating model.
•Apply least privilege to SSH, engine, monitoring, backup, and application identities independently.
•Keep secrets out of YAML, command history, support bundles, screenshots, and document examples.
•Encrypt management, client, replication, and monitoring traffic where the target security standard requires it.
•Record configuration revisions, approvals, operator identity, execution timestamps, and before-and-after status.
•Sanitize customer data, credentials, internal addresses, and private keys from evidence before external sharing.
•Review licensing, third-party notices, package provenance, signatures, and SBOM evidence for the exact shipped artifacts.

↔ Swipe horizontally to view full table

Role	Permitted actions	Restricted actions
L1 monitoring	Read health, alerts, dashboards, and sanitized support evidence.	Apply, restore, destroy, secret access, or policy change.
L2 operations	Diagnose, prepare plans, and execute approved non-destructive runbooks.	Unapproved destructive recovery or access-policy change.
Platform administrator	Manage declared topology and approved lifecycle operations.	Bypass of approval, audit, or credential controls.
Security or approver	Review access, secrets, artifacts, and high-risk execution gates.	Routine engine mutation without operational owner.

Observability and Day Two Operations

The operational dashboard and alert policy should cover object conversion; row and checksum difference; CDC lag; error queue; query latency and throughput; resource use; application success; cutover duration; cost model variance. Thresholds must be tuned during non-production load and recovery tests.

↔ Swipe horizontally to view full table

Cadence	Required activity
Continuous	Service, topology, replication, capacity, security, and exporter alerts.
Daily	Review failed jobs, backup or snapshot age, storage growth, authentication failures, and unresolved drift.
Weekly	Capacity trend, slow or failed operations, certificate horizon, support evidence quality, and alert noise.
Monthly	Restore or recovery sample, access review, artifact and version posture, and runbook currency.
Quarterly	Full failure exercise with application owners, RPO and RTO measurement, and corrective-action closure.

Failure Modes and Edge Cases

The design and test plan must explicitly cover unsupported Oracle feature, semantic type mismatch, PL/SQL incompatibility, sequence drift, long-running transaction, CDC gap, query regression, partial cutover, and rollback divergence.

↔ Swipe horizontally to view full table

Scenario	Expected control	Acceptance evidence
Preflight assumption is false	Execution is blocked before mutation and the finding names the failed requirement.	Failed validation result and unchanged runtime state.
Operation is interrupted	Persisted progress allows safe resume or a documented rollback; completed steps are not repeated blindly.	Resume plan, idempotent result, and reconciled state.
Partial engine health	No destructive cleanup occurs while authority or the last healthy copy is uncertain.	Authoritative copy decision and approved recovery plan.
Capacity exhausted	The workflow stops before moving or restoring data into an unsafe disk or memory condition.	Capacity check, alert, and remediated target.
Credential or permission mismatch	No permission broadening is attempted automatically; the exact missing right is reported.	Named identity, corrected grant, and successful revalidation.
Post-change application failure	Traffic remains stopped or is rolled back even if engine health is green.	Application test failure, decision record, and rollback evidence.

Validation and Acceptance Gates

↔ Swipe horizontally to view full table

Gate	Pass condition
Configuration	The approved YAML revision matches the executed plan and contains no plaintext secret.
Infrastructure	Hosts, ports, storage, time, DNS, certificates, and required packages pass preflight.
Topology	All declared roles and members are healthy; no undeclared or duplicate authority remains.
Data	Counts, placement, replication, recovery, or integrity checks appropriate to schemas, tables, constraints, indexes, sequences, PL/SQL, jobs, security, historical data, change stream, and application queries pass.
Security	Authentication, authorization, encryption, audit, and credential boundaries behave as designed.
Observability	Dashboards are current, alerts are routed, and a deliberate fault is detected and cleared.
Application	Representative reads, writes, failure behavior, and reconciliation pass with business owners.
Recovery	Rollback or restore is rehearsed and measured against approved RPO and RTO.
Operations	L1 and L2 runbooks, escalation, support evidence, and ownership are accepted.

Rollback and Recovery Strategy

Rollback is prepared before execution. The team retains the approved configuration, observed pre-change state, packages or runtime references, manifests, service metadata, and data protection artifacts required to return safely. Rollback must not overwrite the only healthy copy or start competing authorities.

•Stop new application traffic or writes when continued activity would make rollback ambiguous.
•Capture current failure evidence before changing state again.
•Confirm the authoritative data copy and the exact rollback target.
•Execute the reviewed rollback in reverse dependency order.
•Verify engine topology, data, security, observability, and application behavior.
•Record the decision, elapsed time, data exposure, unresolved exceptions, and corrective actions.

Limitations and Required Design Decisions

•This reference does not replace workload benchmarking, engine vendor guidance, or environment-specific architecture review.
•Supported operations and topology options depend on the selected OSS Manager release and engine version.
•OSS Manager can standardize orchestration and evidence but cannot decide business conflict, reconciliation, residency, or failure policy on behalf of the application owner.
•High availability does not replace backup; backup completion does not replace tested recovery; process health does not replace business validation.
•Performance, cost, RPO and RTO targets are established through workload measurements and recovery rehearsals.

Reference Use Cases

BFSI: payments, fraud, customer 360, regulatory reporting, digital lending, risk, and resilient transaction platforms; Media and OTT: content metadata, playback events, entitlements, recommendations, sessions, and audience analytics; Retail: catalog, pricing, inventory, orders, loyalty, personalization, and real-time fulfillment events; Telco: subscriber data, charging, network events, assurance, policy, customer care, and digital channels; Gaming: profiles, matchmaking, leaderboards, inventory, telemetry, events, and live-operations services.

The same operating model can be adapted where the engine capability and data semantics are equivalent, subject to an environment-specific design and release validation.

Customer Success Criteria

For Oracle and PostgreSQL, success is demonstrated through an agreed operational baseline, a rehearsed recovery path and application acceptance evidence, rather than service status alone.

•Business owners can see whether continuity and data-correctness objectives were met.
•Operations teams can identify the current owner, configuration and next recovery action.
•Security teams can trace access and changes without exposing credentials or customer data.

Delivery Checklist

✓Architecture and scope approved
✓Prerequisites and capacity evidence complete
✓Security and credential model approved
✓Configuration revision and plan retained
✓Non-production and negative-path tests passed
✓Rollback or recovery rehearsal passed
✓Production window and stop conditions approved
✓Post-change engine and application validation passed
✓L1 and L2 operational handover complete
✓Service objectives and customer acceptance recorded