Oracle → Postgres Migration

Oracle to Postgres is a license-cost play more than a feature-set play. Most things move cleanly via ora2pg. The big risks are PL/SQL business logic in the database (Postgres has PL/pgSQL; not a direct translation), hierarchical queries (CONNECT BY → WITH RECURSIVE), and Oracle-specific feature dependencies.

The opinion

Use ora2pg for schema + data conversion. Audit every PL/SQL package, trigger, and view — these don't translate automatically. Dual-write cutover for non-trivial apps. Switch from activerecord-oracle_enhanced-adapter to pg. Expect 4-12 weeks for a non-trivial enterprise app.

Pre-migration audit

# What lives in Oracle that isn't ActiveRecord SQL?
sqlplus user/pass@db <<EOF
  SELECT object_type, COUNT(*) FROM user_objects GROUP BY object_type;
  SELECT name FROM user_source WHERE type = 'PACKAGE';
EOF