Database Migration to PostgreSQL: Assess, Convert, Validate, Cut Over

Three routes to PostgreSQL and what each one needs

The route is set by the source database engine. The migration tools and the data move are similar everywhere. What differs is the procedural language, the types and the behaviors the application has quietly come to depend on.

Oracle to PostgreSQL: Ora2Pg, AWS SCT and DMS, and the PL/SQL problem

Ora2Pg is a free tool, released under GPL-3.0, that scans an Oracle database and exports tables, views, sequences, indexes, constraints, partitions, functions, triggers and packages as PostgreSQL DDL, plus the data. It also produces a migration assessment that scores the difficulty and cost of the database before you start. Its own documentation describes the PL/SQL conversion as basic and says the generated functions, procedures, packages and triggers have to be reviewed. That review is the project.

On AWS, the Schema Conversion Tool produces an assessment report and puts action items into the converted code where it could not finish the job. AWS DMS moves the data with a full load and then ongoing change capture. It reads Oracle redo with LogMiner by default, or with Binary Reader, which AWS lists as lighter on the source and faster for change capture but without table cluster support. Both need supplemental logging enabled on the source. Debezium's Oracle connector is the alternative when the change events also need to reach Kafka or RabbitMQ.

Data types need an explicit map before any data moves: Oracle NUMBER to integer types or numeric, DATE (which carries a time) and TIMESTAMP WITH TIME ZONE to the matching PostgreSQL timestamp types, BINARY_FLOAT and BINARY_DOUBLE to real and double precision, and large objects (CLOB and BLOB) to text and bytea. The map decides how each table is created on the PostgreSQL side and what the validation step compares.

The orafce extension supplies Oracle-compatible functions and the AWS tool can target it, which shortens the rewrite where code leans on them. It is a bridge, not a destination: each function you keep is one more thing to remember when the next engineer reads the code.

SQL Server to PostgreSQL: Babelfish, conversion and change capture

SQL Server 2016 is one common trigger. Microsoft's lifecycle page shows extended support ended on 14 July 2026, with paid Extended Security Updates running to July 2029 (checked 31 October 2026). That makes the engine decision a dated one for some estates.

• Result sets — PostgreSQL procedures do not return result sets. T-SQL procedures that do become functions returning a table or cursor, and every caller changes with them.
• Error and row handling — TRY/CATCH, @@ROWCOUNT and SCOPE_IDENTITY() become EXCEPTION blocks, GET DIAGNOSTICS and RETURNING.
• Temp tables and table variables — They have PostgreSQL counterparts, but statistics and scoping behave differently, so reporting procedures that lean on them need timing, not just translation.
• Collation — Common SQL Server collations are case-insensitive and PostgreSQL's default is not. Unique constraints and login lookups are where it shows.
• Types — pgloader maps bit to boolean, uniqueidentifier to uuid and datetime to timestamptz. Whether timestamptz is right depends on what your application assumes about time zones.
• Outside the engine — SQL Agent jobs, linked servers, SSIS packages, SSRS reports and CLR assemblies are not database objects. Each needs a named replacement in the plan.

For the data move, pgloader reads SQL Server and loads schema, indexes, keys and data once; its documentation does not describe change capture. For a live source, AWS DMS reads changes through MS-Replication or MS-CDC. It needs the database in the Full or Bulk-logged recovery model with a backup taken first, and AWS documents that identity properties are not migrated, that temporal tables, sparse columns, columnstore indexes and memory-optimized tables are not supported, that changes to computed columns are not replicated, and that events can be lost if SQL Server truncates the log before DMS reads it. Those limits belong in the plan before the tool is chosen.

MySQL to PostgreSQL: the data is easy and the dialect is not

Moving MySQL to PostgreSQL is mostly not a data problem. pgloader reads a MySQL database, converts the schema, loads the data in parallel and resets sequences, which covers a one-time load where a write freeze is acceptable. When MySQL keeps taking writes, AWS DMS or Debezium read the binary log and keep PostgreSQL in sync until the switch.

• Collation — MySQL's default collation is case- and accent-insensitive and PostgreSQL's is not. Search the existing data for values that differ only by case before loading them into a unique index.
• Types — No unsigned integers, so widen the type and add a CHECK. ENUM and SET need a decision. Zero dates are rejected and have to become NULL or a real date.
• Syntax — INSERT ... ON DUPLICATE KEY UPDATE becomes ON CONFLICT DO UPDATE, and REPLACE INTO has no direct equivalent. The change lives in application code and ORM calls.
• Triggers and views — pgloader does not migrate triggers. They are rebuilt by hand and tested.
• Why now — MySQL 8.0 has been on Oracle Sustaining Support since 21 April 2026. If a supported version is the only goal, upgrading to 8.4 LTS is a much smaller change. Move engines for a reason beyond the date: extensions such as PostGIS or pgvector, richer SQL, a permissive license.

Validation, cutover and rollback on every route

The same spine runs under all three routes. Nothing on the list is exotic. The discipline is doing every item, in order, on a rehearsal before the real window.

• Rehearse — Run the full runbook on a copy of production at least once and time each phase. The write freeze is the number the business cares about.
• Sync — Keep the target database fed by change capture (data replication) until the lag is nil, with one system accepting writes at any moment.
• Validate while syncing — Row counts and aggregates over key ranges, sequence values above the table maximum, NULL against empty string, case-only duplicates, permissions and extensions.
• Replay real queries — Run your top statements against both databases, compare the results, then compare the plans on the PostgreSQL side. Run the month-end and batch jobs, not just the screens.
• Cut over — Freeze writes, drain the last changes, run the final comparison, switch connections and smoke-test the critical paths.
• Roll back — Until PostgreSQL takes its first write, the source is untouched and rollback means pointing back. After that it needs reverse replication or a replay of the writes. The point of no return is agreed in advance and written into the change request.

Who this migration service is not for

• A data copy only — If the schema is simple and the application already runs on PostgreSQL, AWS DMS, Azure Database Migration Service or pgloader with your own engineers may be all you need.
• A supported version, not a new engine — If the real problem is a lifecycle date, upgrading the current database is usually the smaller change.
• Estates built on Oracle-only platforms — Applications that depend on RAC, Exadata, APEX, Forms or E-Business Suite at depth may be cheaper to keep. We cover the database, not those application platforms.
• A fixed price and date before assessment — We scope after the assessment and a proof of value, because the estate decides the effort. Quotes come back within 1 hour of getting your engine, database count and sizes.
• A guarantee of automatic conversion — No honest provider can give one for procedural code. We tell you what converts, what needs review and what needs rewriting.