Oracle to PostgreSQL Migration: Lessons from Enterprise Banking

After spending over a year migrating Oracle databases to PostgreSQL Flex in Azure for UBS, I've gathered invaluable insights that I believe can help others facing similar challenges.

The Challenge

Enterprise banking systems come with unique constraints:

• Zero tolerance for downtime - Financial transactions cannot be interrupted
• Regulatory compliance - Every change must be auditable and traceable
• Complex dependencies - Decades of stored procedures, triggers, and business logic
• Performance requirements - Sub-millisecond response times for critical operations

Key Migration Strategies

1. Assessment and Planning

Before touching any code, spend adequate time understanding:

• Data dependencies and relationships
• PL/SQL to PL/pgSQL conversion requirements
• Performance baselines and SLAs
• Rollback procedures at every stage

2. Tooling and Automation

We leveraged several tools that proved essential:

• ora2pg for schema and data conversion
• Ansible playbooks for repeatable deployments
• Custom Python scripts for PL/SQL translation edge cases
• Comprehensive test suites for regression testing

3. Incremental Migration

Rather than a big-bang approach, we adopted:

1. Schema migration and validation
2. Static data migration
3. Application dual-write mode
4. Traffic shadow testing
5. Gradual cutover by service
6. Legacy system retirement

Technical Deep Dive: PL/SQL to PL/pgSQL

One of the most challenging aspects was converting Oracle's PL/SQL to PostgreSQL's PL/pgSQL. Here are common patterns:

Package Conversion

Oracle packages don't have a direct equivalent in PostgreSQL. We used schemas to namespace related functions:

-- Oracle
CREATE OR REPLACE PACKAGE account_mgmt AS
  PROCEDURE transfer_funds(p_from NUMBER, p_to NUMBER, p_amount NUMBER);
END;

-- PostgreSQL equivalent
CREATE SCHEMA account_mgmt;
CREATE OR REPLACE FUNCTION account_mgmt.transfer_funds(
  p_from BIGINT,
  p_to BIGINT,
  p_amount NUMERIC
) RETURNS VOID AS $$
BEGIN
  -- Implementation
END;
$$ LANGUAGE plpgsql;

Exception Handling

Oracle's exception hierarchy differs from PostgreSQL:

-- Oracle
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    -- Handle
  WHEN TOO_MANY_ROWS THEN
    -- Handle

-- PostgreSQL
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    -- Handle (same)
  WHEN TOO_MANY_ROWS THEN
    -- Handle (same, but raise syntax differs)

Lessons Learned

1. Invest in testing infrastructure - Automated testing paid dividends throughout
2. Document everything - Future maintainers will thank you
3. Plan for rollback - Every migration step had a reverse procedure
4. Monitor aggressively - Set up comprehensive monitoring before migration
5. Engage stakeholders early - Business users need to understand and accept changes

Results

After 14 months:

• Successfully migrated 12 critical databases
• Achieved 99.999% uptime during migration
• Reduced infrastructure costs by 40%
• Improved query performance by 25% on average

Conclusion

Oracle to PostgreSQL migration in enterprise banking is challenging but achievable. The key is thorough planning, robust tooling, and incremental execution with comprehensive testing at every stage.

Feel free to reach out if you're facing similar challenges - I'm always happy to share more detailed insights.