PostgreSQL migration, with a parallel run before cutover

The source database decides where the work is. These differences commonly cause defects after a move. They are taken from the PostgreSQL documentation and, for empty strings, Oracle's documentation:

• Stored code. PostgreSQL has no packages. The PostgreSQL documentation suggests schemas to group functions, and temporary tables for the per-session state that package variables used to hold. Procedural code from any source database is rewritten for PostgreSQL, usually in PL/pgSQL.
• Data types. The PostgreSQL documentation advises replacing varchar2 with varchar or text, and number with numeric or a more suitable numeric type. Date and time types need the same review.
• Empty strings. Oracle Database treats a character value with a length of zero as null. PostgreSQL does not, so comparisons and constraints that rely on this behave differently.
• Identifier case. PostgreSQL folds unquoted names to lower case. Code that mixes quoted and unquoted names can stop finding its own tables.
• Errors inside procedures. In PL/pgSQL, when an exception is caught, all database changes since the start of that block are rolled back. Error-handling code ported line by line can lose work it used to keep.
• Dynamic SQL. EXECUTE works much as before, but names and values must be quoted with quote_ident and quote_literal, or the statement will not work reliably.

None of this is unusual. It is why the assessment reads the stored code and the application's queries before anyone quotes a duration.

The pattern is the same whichever database you start from:

1. Initial load. Copy the existing data to PostgreSQL.
2. Continuous replication. Keep copying every change from the source while both systems run.
3. Parallel run. Both databases live and in step, for as long as validation and the business require. Reads move first, in steps that can be reversed.
4. Cutover. Writes move to PostgreSQL. Switching back stays possible until the agreed point.

The cutover is performed once and rehearsed more than once, including the rollback.

The assessment can conclude that you should not migrate, and it says so in writing. A working database that serves its load is an asset. Sometimes the better answer is to tune what you have. And when both systems come from one vendor with a supported native migration path, that path is usually cheaper than a heterogeneous migration, and iblync is the wrong tool.

1. Assessment. A bounded piece of work: workload, stored code, application queries, data volume, downtime window and residency. The output is a decision with a costed comparison that includes staying put.
2. Schema and code. Translate the schema, rewrite stored code, and test it against production-shaped data.
3. Application changes. Size and start the query and driver changes per service, with your application teams.
4. Bridge and parallel run. Initial load, continuous replication and service-by-service comparison.
5. Rehearsed cutover. Cutover and rollback rehearsed, then performed once.
6. Stabilise. Tuning, backup-restore proof and failover drills, then handover to your DBAs or operation by Interkey from Riyadh.

After the move, schema changes on PostgreSQL can be versioned and shipped through a pipeline. Interkey's database DevOps practice covers that step.

Relateddatabase DevOps practice

Quotes are per engagement, after the assessment. The drivers are how far the schema and stored code are from PostgreSQL, the number of services whose queries change, data volume and change rate, the downtime window the business will grant, the deployment model, who carries the application changes, and operation after go-live. iblync itself is priced by quotation, per source system, target system and data volume.