Oracle to PostgreSQL Migration: A Practical DBA Playbook

-- Oracle
amount  NUMBER(15,2)
user_id NUMBER(10)   -- often used as integer

-- PostgreSQL equivalents
amount  NUMERIC(15,2)  -- or DECIMAL(15,2)
user_id INTEGER        -- or BIGINT for large tables
-- NUMBER without precision in Oracle = NUMERIC in PG (variable precision)

-- Oracle DATE includes TIME component (equivalent to PG TIMESTAMP)
SELECT SYSDATE FROM dual;  -- includes hours/minutes/seconds

-- PostgreSQL DATE is date-only
-- Migration rule: Oracle DATE  →  PostgreSQL TIMESTAMP or TIMESTAMPTZ
-- Migration rule: Oracle TIMESTAMP  →  PostgreSQL TIMESTAMPTZ

-- Example correction
created_at  DATE          -- Oracle (stores time)
created_at  TIMESTAMPTZ   -- PostgreSQL equivalent (with timezone)

-- Oracle: empty string '' is NULL (critical quirk)
SELECT * FROM users WHERE name = '';  -- never returns rows in Oracle
-- In PostgreSQL: '' is NOT NULL
SELECT * FROM users WHERE name = '';  -- returns rows with empty string

-- Oracle NVL → PostgreSQL COALESCE
SELECT NVL(phone, 'N/A') FROM users;      -- Oracle
SELECT COALESCE(phone, 'N/A') FROM users; -- PostgreSQL

-- String concatenation: identical in both (|| operator works in both)
SELECT first_name || ' ' || last_name FROM users;  -- works in Oracle and PG

-- Oracle
SELECT * FROM orders WHERE ROWNUM <= 10;
SELECT * FROM (SELECT * FROM orders ORDER BY id) WHERE ROWNUM <= 10;

-- PostgreSQL
SELECT * FROM orders LIMIT 10;
SELECT * FROM orders ORDER BY id LIMIT 10;

-- Oracle
SELECT SYSDATE FROM dual;
SELECT myseq.NEXTVAL FROM dual;

-- PostgreSQL (no DUAL needed)
SELECT NOW();
SELECT nextval('myseq');

-- If migrating Oracle application code that references DUAL, create a compatibility view
CREATE VIEW dual AS SELECT 'X'::TEXT AS dummy;

-- Oracle
CREATE SEQUENCE order_seq START WITH 1 INCREMENT BY 1;
CREATE TABLE orders (
  id NUMBER DEFAULT order_seq.NEXTVAL PRIMARY KEY
);

-- PostgreSQL (modern — preferred)
CREATE TABLE orders (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);

-- Or SERIAL for backwards compatibility
CREATE TABLE orders (id BIGSERIAL PRIMARY KEY);

-- If migrating Oracle sequences explicitly
CREATE SEQUENCE order_seq START WITH 1 INCREMENT BY 1;
-- Then use: DEFAULT nextval('order_seq')

# Install ora2pg on Debian/Ubuntu
sudo apt-get install ora2pg

# On RHEL/CentOS:
sudo yum install ora2pg

# Verify install
ora2pg --version

cat > /etc/ora2pg/ora2pg.conf << 'EOF'
ORACLE_DSN  dbi:Oracle:host=oracle-host;sid=MYDB;port=1521
ORACLE_USER myuser
ORACLE_PWD  mypassword
SCHEMA      MYSCHEMA
TYPE        TABLE
PG_DSN      dbi:Pg:dbname=myapp;host=localhost;port=5432
PG_USER     pguser
PG_PWD      pgpassword
OUTPUT_DIR  /tmp/ora2pg_output
EOF

ora2pg --type SHOW_REPORT --estimate_cost

# Export individual object types
ora2pg --type TABLE     --output /tmp/ora2pg_output/tables.sql
ora2pg --type SEQUENCE  --output /tmp/ora2pg_output/sequences.sql
ora2pg --type INDEX     --output /tmp/ora2pg_output/indexes.sql
ora2pg --type VIEW      --output /tmp/ora2pg_output/views.sql
ora2pg --type PROCEDURE --output /tmp/ora2pg_output/procedures.sql
ora2pg --type FUNCTION  --output /tmp/ora2pg_output/functions.sql

# Or export everything at once
ora2pg --type EXPORT_SCHEMA --output /tmp/ora2pg_output/

# Export data as PostgreSQL COPY statements (fastest method)
ora2pg --type COPY --output /tmp/ora2pg_output/data.sql

# Load into PostgreSQL
psql -U pguser -d myapp -f /tmp/ora2pg_output/tables.sql
psql -U pguser -d myapp -f /tmp/ora2pg_output/sequences.sql
psql -U pguser -d myapp -f /tmp/ora2pg_output/data.sql
psql -U pguser -d myapp -f /tmp/ora2pg_output/indexes.sql

-- Oracle PL/SQL procedure
CREATE OR REPLACE PROCEDURE update_order_status(
  p_order_id IN NUMBER,
  p_status   IN VARCHAR2
) IS
BEGIN
  UPDATE orders SET status = p_status WHERE id = p_order_id;
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    RAISE;
END;

-- PostgreSQL PL/pgSQL equivalent
CREATE OR REPLACE PROCEDURE update_order_status(
  p_order_id INTEGER,
  p_status   TEXT
)
LANGUAGE plpgsql AS $$
BEGIN
  UPDATE orders SET status = p_status WHERE id = p_order_id;
  -- No explicit COMMIT needed — handled by the caller's transaction context
EXCEPTION
  WHEN OTHERS THEN
    RAISE;  -- re-raise; caller handles rollback
END;
$$;

-- Calling: PostgreSQL uses CALL instead of EXEC/EXECUTE
CALL update_order_status(1234, 'shipped');

-- Oracle: IS / AS for procedure body delimiter
-- PostgreSQL: LANGUAGE plpgsql AS $$ ... $$

-- Oracle: VARCHAR2 → PostgreSQL: TEXT or VARCHAR
-- Oracle: NUMBER   → PostgreSQL: INTEGER, BIGINT, or NUMERIC

-- Oracle: DBMS_OUTPUT.PUT_LINE → PostgreSQL: RAISE NOTICE
DBMS_OUTPUT.PUT_LINE('Debug: ' || v_count);   -- Oracle
RAISE NOTICE 'Debug: %', v_count;             -- PostgreSQL

-- Oracle: NVL() → PostgreSQL: COALESCE()
v_name := NVL(p_name, 'Unknown');    -- Oracle
v_name := COALESCE(p_name, 'Unknown'); -- PostgreSQL

-- Oracle: SYSDATE → PostgreSQL: NOW() or CURRENT_TIMESTAMP
v_ts := SYSDATE;    -- Oracle
v_ts := NOW();      -- PostgreSQL

-- Oracle exception name mapping
-- NO_DATA_FOUND    → NO_DATA_FOUND (same in PG)
-- TOO_MANY_ROWS    → TOO_MANY_ROWS (same in PG)
-- OTHERS           → OTHERS (same in PG)
-- DUP_VAL_ON_INDEX → unique_violation (PG uses SQL state names)
WHEN unique_violation THEN ...   -- PostgreSQL

-- Oracle synonym
CREATE SYNONYM orders FOR app_schema.orders;

-- PostgreSQL equivalent using search_path
SET search_path TO app_schema, public;
-- Or create a view in the public schema
CREATE VIEW public.orders AS SELECT * FROM app_schema.orders;

-- PostgreSQL: set up postgres_fdw for cross-database queries
CREATE EXTENSION IF NOT EXISTS postgres_fdw;

CREATE SERVER remote_oracle_db
  FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host 'remote-host', dbname 'remotedb', port '5432');

CREATE USER MAPPING FOR current_user
  SERVER remote_oracle_db
  OPTIONS (user 'remote_user', password 'secret');

-- Import remote tables
IMPORT FOREIGN SCHEMA public
  FROM SERVER remote_oracle_db
  INTO local_schema;

-- Run on both Oracle (source) and PostgreSQL (target) and compare
SELECT 'orders'    AS table_name, COUNT(*) AS row_count FROM orders
UNION ALL
SELECT 'customers' AS table_name, COUNT(*) AS row_count FROM customers
UNION ALL
SELECT 'order_items' AS table_name, COUNT(*) AS row_count FROM order_items;

-- PostgreSQL: generate a checksum for critical columns
SELECT
  MD5(STRING_AGG(
    CONCAT(id::TEXT, amount::TEXT, status::TEXT, created_at::TEXT),
    ',' ORDER BY id
  )) AS table_checksum
FROM orders
WHERE created_at >= '2024-01-01';

-- Capture execution plan on PostgreSQL for critical queries
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.status, c.email
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending'
  AND o.created_at >= NOW() - INTERVAL '30 days'
ORDER BY o.created_at DESC
LIMIT 100;