The proprietary-to-Postgres migration playbook

Most migrations that go wrong fail for the same reason: they're planned as one big weekend. Everything is converted, loaded and switched in a single window, and if something surprises you on Sunday night, there's no good option left.

This playbook avoids that. The old and new databases run side by side for as long as you need, and the final switch is small, rehearsed and reversible. The examples use Oracle, the most common starting point, but the shape is the same for SQL Server, Db2 or Sybase.

1 · Assess

Before anyone estimates a date, find out what you're actually moving:

• Schema: how many tables, indexes, sequences, views and partitions, and which data types are used.
• Code inside the database: stored procedures, functions, triggers and packages. This is usually where the real work is.
• Features in use: materialized views, database links, scheduled jobs, autonomous transactions, proprietary types.
• The applications: which SQL they send, which drivers they use, and which queries matter most for performance.

Open-source tools such as Ora2Pg produce an assessment report that counts these objects and estimates the conversion effort. Treat the estimate as a first draft; the queries your applications send are just as important as the objects in the database.

The output of this phase is a written plan: what moves, in what order, what needs rewriting by hand, and how you'll know it worked.

2 · Convert the schema

Most data types map directly. A few deserve attention, because they behave differently, not just under a different name:

• NUMBER becomes integer or bigint when it holds whole numbers, and numeric only when you need exact decimals. Mapping everything to numeric works, but it's slower.
• VARCHAR2 becomes varchar or text (they perform the same in PostgreSQL).
• DATE in Oracle stores a time of day too, so it usually becomes timestamp(0), not date.
• CLOB becomes text; BLOB becomes bytea.
• Empty strings: Oracle treats '' as NULL. PostgreSQL doesn't. Code that relies on WHERE col IS NULL to find empty values will quietly change behaviour, so search for it early.

Sequences, constraints and most indexes carry over cleanly. Partitioned tables map to PostgreSQL's declarative partitioning.

3 · Convert the code

Conversion tools translate the bulk of PL/SQL to PL/pgSQL automatically. These are the patterns we see every time:

-- Oracle                         -- PostgreSQL
NVL(a, b)                         COALESCE(a, b)
SYSDATE                           localtimestamp   -- or now() for a timestamptz
DECODE(x, 1, 'a', 'b')            CASE x WHEN 1 THEN 'a' ELSE 'b' END
SELECT … FROM dual                SELECT …           -- no FROM needed
my_seq.NEXTVAL                    nextval('my_seq')
WHERE ROWNUM <= 10                LIMIT 10           -- after ORDER BY, as intended
a.id = b.id(+)                    LEFT JOIN b ON a.id = b.id
CONNECT BY PRIOR …                WITH RECURSIVE …

The remaining 10% is where engineers earn their keep:

• Packages have no direct equivalent. We usually map a package to a schema containing its functions, and package variables to a small settings table or session settings.
• Autonomous transactions don't exist in PostgreSQL. Logging that must survive a rollback needs another approach (for example, the dblink or pg_background extensions, or logging from the application).
• Exceptions and error codes differ, so error handling in the application may need updating.
• Implicit conversions: Oracle quietly converts between strings and numbers in more places. PostgreSQL is stricter, which surfaces hidden bugs. That's good, but it takes time.

Convert, then run every procedure's tests against PostgreSQL. If there are no tests, this is the moment to write them.

4 · Move the data

Moving data is two jobs:

1. An initial load of everything, which can take hours or days for large databases, while the old system keeps running.
2. Change data capture (CDC): every change made to the old database after the load starts is replayed into PostgreSQL, continuously. Open-source tools such as Debezium, as well as commercial replication tools, can do this.

With CDC running, the two databases stay in sync, and you can test against real, current data for as long as you need. The cut-over no longer depends on how fast you can copy terabytes.

5 · Test

Row counts matching is the start of testing, not the end. Check:

• Data: row counts per table, plus checksums or sampled comparisons of the actual values, including dates, decimals and text with special characters.
• Results: run the same business queries and reports on both databases and compare the answers.
• Behaviour: the application's own test suite against PostgreSQL, plus the edge cases from step 2 (empty strings, date handling).
• Performance: replay a realistic workload, then look at the slowest queries with EXPLAIN (ANALYZE, BUFFERS). Missing indexes and queries that depended on Oracle-specific hints show up here.

Run the full cut-over at least once as a rehearsal, with a stopwatch.

6 · Cut over

Because the databases are already in sync, the cut-over is short:

1. Stop writes to the old database (a maintenance window of minutes, not hours).
2. Let CDC apply the last changes, and verify counts and checksums one final time.
3. Reset sequences in PostgreSQL to continue after the highest values in use.
4. Point the applications at PostgreSQL (a connection string or DNS change).
5. Start reverse replication, from PostgreSQL back to the old database, so you can switch back without losing data if something unexpected happens.

Keep the way back available for an agreed period, then decommission the old database, and cancel the licence.

After the move

The first weeks on PostgreSQL are about tuning for the real workload: run ANALYZE after the load so the planner has statistics, turn on pg_stat_statements, and review the slowest queries weekly. Our posts on the five settings we change on every cluster and on autovacuum are good next reads.