For mid-sized and enterprise engineering organizations, commercial database licensing rarely scales linearly with traffic. One hardware refresh, a vCPU increase on virtualized hosts, or a routine vendor license audit can trigger six-figure true-up bills—on top of mandatory 22% annual support renewals.
Over the past decade, PostgreSQL has matured into the default landing zone for teams exiting Oracle Database and Microsoft SQL Server. However, treating an enterprise database migration as a simple export/import job is the fastest way to cause silent data truncation, broken application transactions, and severe post-cutover latency.
Below is the structured, four-phase playbook I use as a production DBA to move mission-critical workloads off commercial engines onto PostgreSQL with predictable performance and minimal downtime.
1. The Enterprise Licensing Cliff (TCO Reality Check)
Before committing engineering sprints to a migration, frame the financial case clearly for leadership using Total Cost of Ownership (TCO) over a 3-year horizon:
| Cost Component | Commercial Enterprise Engines (Oracle EE / SQL Server Enterprise) | Open-Source PostgreSQL (Self-Managed or Managed Cloud) |
|---|---|---|
| Core / vCPU Licensing | Per-processor / per-2-core pack licensing (escalates rapidly on multi-core modern CPUs & virtualization clusters) | $0 (PostgreSQL License — permissive, free for any commercial scale) |
| Mandatory Annual Support | ~20%–22% of list license fee every year (even if you never open a support ticket) | $0 (or optional enterprise SLA subscriptions only on active nodes) |
| High Availability & Read Replicas | Passive/active standbys and reporting replicas frequently require full additional core licenses | $0 additional license fees for streaming standbys, read replicas, or dev/QA clones |
| Partitioning & Diagnostics | Often locked behind Enterprise Edition or paid add-on packs (e.g., Diagnostics & Tuning Packs) | Built-in natively (declarative partitioning, pg_stat_statements, EXPLAIN ANALYZE) |
2. Phase 1 — Compatibility & Schema Assessment
Never start converting DDL until you inventory proprietary engine features embedded in your database layer. Run a discovery audit first to classify tables into Clean Lift-and-Shift versus Application Refactor Required:
Critical Behavioral Differences to Catch Early
- Empty Strings vs.
NULL(Oracle Trap): Oracle treats''(empty string) asNULL. PostgreSQL strictly follows the SQL standard where'' IS NOT NULL. Applications relying onWHERE col IS NULLto catch empty strings will silently break unless normalized during ETL or handled in queries. - Case Sensitivity & Identifier Folding: Oracle folds unquoted identifiers (
SELECT emp_id FROM employees) toUPPERCASE; PostgreSQL folds unquoted identifiers tolowercase. Standardize all converted DDL to lowercase unquoted names to avoid double-quoting every column in application queries. - Data Type Mapping Precision:
- Oracle
NUMBER(without precision/scale) → Avoid blindly mapping toNUMERICeverywhere; map integer keys toBIGINTorINTEGERfor 3x–5x faster index scans and joins, reservingNUMERIC(p,s)strictly for financial/decimal columns. - Oracle
VARCHAR2(4000)/ SQL ServerNVARCHAR(MAX)→ Map cleanly to PostgreSQLTEXT(in PostgreSQL,VARCHAR(n)andTEXTshare identical storage and performance internals). - SQL Server
DATETIME/ OracleDATE(which includes time) → Map toTIMESTAMPTZ(timestamp with time zone) rather thanDATE.
- Oracle
- Transaction & Error Handling Inside Routines: Oracle PL/SQL packages with
PRAGMA AUTONOMOUS_TRANSACTIONand SQL Server nestedTRY...CATCHblocks behave differently under PostgreSQL’s subtransaction (SAVEPOINT) model. Heavy business logic inside stored procedures should be refactored into application services or idiomatic PL/pgSQL.
3. Phase 2 — Automated Schema Conversion & Diffing
Use proven open-source converters to generate your baseline PostgreSQL DDL, then inspect the output carefully rather than trusting automated defaults:
- From Oracle: Use
ora2pgto extract tables, constraints, sequences, and views, generating a migration complexity report before writing a single row. - From SQL Server: Use
pgloader(which handles schema introspection, type casting, and high-speed bulk loading in a single streaming pipeline) orAWS SCT(Schema Conversion Tool) for complex T-SQL translation.
Schema Conversion Hygiene Rules
- Defer Secondary Indexes & Foreign Keys: During initial historical data loads, create only primary keys. Build secondary indexes (
CREATE INDEX CONCURRENTLY) and validate foreign keys (ALTER TABLE ... VALIDATE CONSTRAINT) after bulk data lands—this cuts initial load times by 60%–80%. - Visual Schema Diff Verification: Open both the legacy source database and target PostgreSQL schema side-by-side in a multi-engine SQL IDE (such as JetBrains DataGrip or DBeaver) to verify nullability, default values, and check constraints match 1:1.
4. Phase 3 — Continuous Data Sync (CDC) for Low-Downtime Cutover
For databases larger than 50 GB, a weekend “stop the app, dump, and restore” cutover carries high rollback risk. Instead, use Change Data Capture (CDC):
- Snapshot Load: Perform a consistent initial copy of historical rows using
pgloaderor a CDC connector (such as Debezium, Airbyte, or cloud-native DMS). - Continuous Log Streaming: Stream ongoing
INSERT,UPDATE, andDELETEchanges from Oracle Redo/Archive logs (LogMiner) or SQL Server CDC tables into PostgreSQL in near real time. - Shadow Read Validation: Point a staging or shadow-traffic build of your application against PostgreSQL while CDC keeps it synchronized, allowing QA and performance engineers to catch query regressions under real data distributions before cutover day.
5. Phase 4 — Post-Migration Parameter Tuning & Validation SQL
A fresh PostgreSQL installation ships with conservative defaults meant for small virtual machines. Before benchmarking or cutting over production traffic, tune these core parameters in postgresql.conf (assuming modern NVMe/SSD storage):
# Memory Allocation (Example for a 32 GB RAM dedicated DB server)
shared_buffers = 8GB # ~25% of total system RAM
effective_cache_size = 24GB # ~75% of total RAM (guides query planner)
work_mem = 32MB # Per sort/hash operation (monitor concurrency!)
maintenance_work_mem = 2GB # Accelerates post-load CREATE INDEX & VACUUM
# Storage & Query Planner (Essential for SSD/NVMe)
random_page_cost = 1.1 # Default 4.0 assumes spinning HDDs and over-favors seq scans
effective_io_concurrency = 200 # Concurrent I/O requests for SSD arrays
# Write-Ahead Log & Checkpoints
wal_buffers = 64MB
checkpoint_completion_target = 0.9 # Spreads checkpoint I/O evenly to prevent latency spikes
max_wal_size = 4GB
Post-Migration Validation & Health Audit Script
Run this SQL audit suite in PostgreSQL immediately after initial data load and again 48 hours after cutover to verify row density, index utilization, and sequence alignment:
-- 1. Verify table sizes, live row estimates, and index vs. sequential scan ratios
WITH table_stats AS (
SELECT
schemaname,
relname AS table_name,
n_live_tup AS estimated_rows,
pg_total_relation_size(relid) AS total_bytes,
idx_scan,
seq_scan
FROM pg_stat_user_tables
)
SELECT
schemaname,
table_name,
estimated_rows,
pg_size_pretty(total_bytes) AS total_size,
CASE
WHEN (idx_scan + seq_scan) > 0
THEN ROUND(100.0 * idx_scan / (idx_scan + seq_scan), 2)
ELSE 100.0
END AS index_usage_pct
FROM table_stats
ORDER BY total_bytes DESC;
-- 2. Identify unused indexes carried over from automated schema conversion
SELECT
schemaname || '.' || relname AS table_name,
indexrelname AS index_name,
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
idx_scan AS times_used
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;
-- 3. Verify all sequences are strictly ahead of current MAX(id) values
-- (Prevents "duplicate key value violates unique constraint" errors after cutover)
SELECT
schemaname,
sequencename,
last_value
FROM pg_sequences
ORDER BY schemaname, sequencename;
6. Cutover Day Execution Checklist
- Freeze Application Writes: Put the application into brief maintenance mode and verify incoming sessions drop to zero on the Oracle/SQL Server source.
- Confirm Zero CDC Lag: Wait until the final redo/transaction log batch applies cleanly to PostgreSQL and stop the CDC pipeline.
- Run
ANALYZE& Sequence Verification: ExecuteANALYZE;database-wide so the PostgreSQL cost-based optimizer has fresh column histograms before the first production query arrives. - Keep Legacy Source Read-Only for 72 Hours: Keep your old Oracle or SQL Server instance online in read-only mode (with reverse-CDC if required by compliance) as an instant safety net until you complete a full business cycle on PostgreSQL.