How to migrate from Oracle to PostgreSQL

List the application schemas, count objects by type and measure segment sizes. Record the results in your Application inventory and dependency map, including which applications, reports and ETL jobs connect to each schema.

SELECT owner, object_type, COUNT(*) FROM dba_objects WHERE owner IN ('HR','SALES') GROUP BY owner, object_type ORDER BY 1,2;
SELECT owner, ROUND(SUM(bytes)/1024/1024/1024,1) AS gb FROM dba_segments WHERE owner IN ('HR','SALES') GROUP BY owner;
SELECT * FROM v$version;

Create a project tree; Ora2Pg generates folders for each object type, a config file and an export_schema.sh script. Edit config/ora2pg.conf in the new folder and set ORACLE_DSN, ORACLE_USER, ORACLE_PWD and SCHEMA to the source. Set PG_DSN, PG_USER and PG_PWD to the test target so the TEST action can run later.

ora2pg --project_base /app/migration/ --init_project hr_project
cd /app/migration/hr_project
# config/ora2pg.conf (excerpt)
ORACLE_DSN  dbi:Oracle:host=ora01.contoso.com;service_name=ORCLPDB1;port=1521
ORACLE_USER ora2pg_reader
SCHEMA      HR
PG_DSN      dbi:Pg:dbname=hr;host=pgtest.contoso.com;port=5432

Run the content report with a cost estimate. The output lists each object type, how many objects Ora2Pg can convert automatically, an estimated cost and a migration level (A, B or C). Run SHOW_TABLE and SHOW_COLUMN to see table sizes and the proposed column type mapping.

ora2pg -t SHOW_REPORT --estimate_cost -c config/ora2pg.conf > reports/assessment.txt
ora2pg -t SHOW_TABLE -c config/ora2pg.conf > reports/tables.txt
ora2pg -t SHOW_COLUMN -c config/ora2pg.conf > reports/columns.txt

Create an instance profile (network and security settings), a source data provider for Oracle and a target data provider for Aurora PostgreSQL or RDS for PostgreSQL, with credentials in AWS Secrets Manager. Then choose Migration projects > Create migration project, enter a Name, choose the instance profile, choose Browse for the Source and Target, pick each Secret ID and IAM role, and choose Create migration project. Open the project, start schema conversion, select the schemas and run the assessment. If no target exists yet, you can use a virtual target.

For each schema record: target platform, route (from Choose your approach above), CDC or outage, the objects that need manual rewrite and their owner, and the applications that must be retested. Drop schemas nobody uses rather than converting them.

Choose the PostgreSQL major version you will run in production, size storage for the Oracle data plus indexes and growth, and place it in a network that the migration server (and DMS, if used) can reach. Create the application database and a separate scratch or test database for conversion work.

Create an owner role per schema and the login roles the application uses, then create each schema owned by its role. Keep names lower case and unquoted.

CREATE ROLE hr_owner NOLOGIN;
CREATE ROLE hr_app LOGIN PASSWORD '<password>';
CREATE SCHEMA hr AUTHORIZATION hr_owner;
GRANT USAGE ON SCHEMA hr TO hr_app;

Install only extensions your platform supports. If you use DMS Schema Conversion, it can add its extension pack to emulate Oracle features. If you plan to use AWS DMS enhanced validation, AWS asks for the pgcrypto extension on PostgreSQL.

CREATE EXTENSION IF NOT EXISTS pgcrypto;
SELECT extname, extversion FROM pg_extension;

Run the generated script to export every object type into the schema folder, one file per type. Or export a single type with the second command, for example when you rerun only tables after changing type mappings.

./export_schema.sh
ora2pg -p -t TABLE -o table.sql -b ./schema/tables -c ./config/ora2pg.conf

Add transformation rules for object names and data types first (for example NUMBER key columns to bigint). Select the schemas in the source pane and convert them. Compare the source and converted code side by side, edit converted SQL where needed, then apply the changes to the target database or export the converted code as SQL scripts to an Amazon S3 bucket.

Create a connection to your flexible server in the PostgreSQL extension and open a new folder as the workspace. In the Migrations panel select Create Migration Project, name it, and enter the Oracle host, port, service name and credentials. Select Load Schemas, choose schemas and select Next: Discovery. Review the results, tick I have reviewed the objects, exclusions and dependent schemas, pick the scratch database, select Verify Extensions, configure the Microsoft Foundry endpoint and select Create Migration Project, then Migrate. The tool drops and recreates matching schemas in the scratch database, so never point it at a database with data you need.

Create a conversion workspace (not the legacy type) for Oracle to your PostgreSQL destination and connect it to the source. Review the initial rules-based conversion, use the Gemini-assisted suggestions and the SQL editor for code objects, and add conversion mapping files where you need different type rules.

Apply table, sequence and primary key DDL now. Hold back secondary indexes, foreign keys and triggers until the data is loaded (Step 5). Ora2Pg can write indexes and constraints to separate files (see the output options in its configuration documentation); otherwise move those statements into their own file. Split DMS Schema Conversion or VS Code output the same way.

psql "host=pgtest.contoso.com dbname=hr user=pgadmin" -f schema/tables/table.sql
psql "host=pgtest.contoso.com dbname=hr user=pgadmin" -f schema/sequences/sequence.sql

Copy every object the tool flagged as not converted or converted with warnings into the log with an owner. Typical items: packages with global state, autonomous transactions, DBMS_ and UTL_ package calls, CONNECT BY hierarchies, dynamic SQL, database links and scheduler jobs.

Rewrite each object in PL/pgSQL. Common rewrites: NVL to COALESCE, DECODE to CASE, SYSDATE to LOCALTIMESTAMP or now(), CONNECT BY to WITH RECURSIVE, ROWNUM to LIMIT or row_number(), and package state to a schema plus session settings or tables. Compile with psql and keep each object in version control.

psql "host=pgtest.contoso.com dbname=hr user=pgadmin" -f schema/packages/emp_pkg.sql
psql "host=pgtest.contoso.com dbname=hr user=pgadmin" -c "\df hr.*"

Search the application for Oracle-specific SQL, hints, outer join (+) syntax, bind variable styles and driver settings, and change the connection driver to PostgreSQL. The VS Code PostgreSQL extension also offers application conversion of SQL scripts, loader control files, shell scripts and Java (Microsoft labels this a preview feature).

Run the same business transactions, reports and batch jobs on both sides and compare outputs: totals, row counts, rounding, sort order, NULL handling and date arithmetic. Record differences and fix them in the code, not by editing data.

Export data as COPY statements rather than INSERT; COPY loads much faster in PostgreSQL. For a rehearsal, run it while the application is live; for the real move, stop application writes to Oracle first.

ora2pg -t COPY -o data.sql -b ./data -c ./config/ora2pg.conf

Load the data files, then apply the held-back secondary indexes, foreign keys and triggers, and run ANALYZE so the planner has statistics. You can load several table files in parallel sessions to save time.

psql "host=pgtest.contoso.com dbname=hr user=pgadmin" -f data/data.sql
psql "host=pgtest.contoso.com dbname=hr user=pgadmin" -f schema/tables/indexes-and-constraints.sql
psql "host=pgtest.contoso.com dbname=hr user=pgadmin" -c "ANALYZE;"

Check the log mode and minimal supplemental logging, enable database-level supplemental logging, and add primary key logging. For tables without a primary key, AWS gives table-level statements for a unique index or ALL columns.

SELECT log_mode FROM v$database;
SELECT supplemental_log_data_min FROM v$database;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

Create a dedicated user and grant the privileges AWS lists for your CDC method. The excerpt below covers the core and LogMiner grants; the AWS page lists the remaining dictionary views. Add EXECUTE ON SYS.DBMS_CRYPTO if you will use data validation.

CREATE USER dms_user IDENTIFIED BY "<password>";
GRANT CREATE SESSION TO dms_user;
GRANT SELECT ANY TRANSACTION TO dms_user;
GRANT SELECT ON V_$DATABASE TO dms_user;
GRANT SELECT ON V_$ARCHIVED_LOG TO dms_user;
GRANT EXECUTE ON DBMS_LOGMNR TO dms_user;
GRANT SELECT ON V_$LOGMNR_CONTENTS TO dms_user;
GRANT LOGMINING TO dms_user;
GRANT EXECUTE ON SYS.DBMS_CRYPTO TO dms_user;

Create a replication instance (or use DMS Serverless) in a subnet that reaches both databases. Create an Oracle Source endpoint and a PostgreSQL Target endpoint, then test each connection from the replication instance. LogMiner is the default CDC method; for high change volumes or RAC, AWS recommends Binary Reader with the extra connection attributes useLogMinerReader=N;useBfile=Y;.

aws dms create-endpoint --endpoint-identifier src-hr-oracle --endpoint-type source --engine-name oracle --server-name ora01.contoso.com --port 1521 --database-name ORCLPDB1 --username dms_user --password "<password>"
aws dms test-connection --replication-instance-arn <replication-instance-arn> --endpoint-arn <source-endpoint-arn>

Choose the instance and endpoints and Migrate existing data and replicate on-going changes. Set the target table preparation mode to Do nothing because the schema already exists, pick a LOB mode from your LOB inventory, add selection rules for each schema, and turn on data validation. Create the secondary indexes and foreign keys after the full load completes and before cutover.

aws dms create-replication-task --replication-task-identifier hr-full-cdc --source-endpoint-arn <source-endpoint-arn> --target-endpoint-arn <target-endpoint-arn> --replication-instance-arn <replication-instance-arn> --migration-type full-load-and-cdc --table-mappings file://table-mappings.json --replication-task-settings '{"ValidationSettings":{"EnableValidation":true}}'
aws dms start-replication-task --replication-task-arn <task-arn> --start-replication-task-type start-replication

Run the TEST action. It reports counts of indexes, constraints, triggers, views, sequences (with current values), functions and rows per table on both sides, then an error section that explains each difference. It tests one schema per run.

ora2pg -t TEST -c config/ora2pg.conf > migration_diff.txt

Every table should show Validated. For Mismatched records, query the failures table on the target to see the key and the differing columns. Tables without a key show No primary key and need another check.

aws dms describe-table-statistics --replication-task-arn <task-arn>
SELECT * FROM awsdms_control.awsdms_validation_failures_v1 WHERE "TASK_NAME" = '<task resource id>';

Run the whole cutover on a copy: stop writes, final load or CDC catch-up, set sequences, run validation, switch the application connection, run smoke tests. Time each step and update the runsheet with the real durations.

Put the application into maintenance mode and stop batch jobs and integrations that write to Oracle. Confirm no application sessions remain.

SELECT username, machine, program FROM v$session WHERE username IN ('HR_APP','SALES_APP');

For CDC, wait until the task's CDC latency reaches zero, then stop the task. For Ora2Pg, run the final COPY export and load (Step 5a). Then rerun the TEST action or a full load validation only task in DMS.

aws dms stop-replication-task --replication-task-arn <task-arn>

Set every sequence to the Oracle current value or the column maximum, and enable any triggers you disabled for the load.

SELECT setval('hr.employees_seq', (SELECT MAX(employee_id) FROM hr.employees));
ALTER TABLE hr.employees ENABLE TRIGGER ALL;

Change connection strings (or the DNS alias) to the PostgreSQL endpoint, start the application and run your smoke tests from the runsheet. Keep Oracle running and unchanged.

Lock or revoke write access for the application users on Oracle so nothing writes there by mistake, and keep it for the rollback window. Hand over backups, monitoring and the PostgreSQL runbook to operations.

ALTER USER hr_app ACCOUNT LOCK;