db_migrator

pig build pkg db_migrator         # build RPM / DEB packages

pig repo add pgsql -u          # Add repo and update cache

CREATE EXTENSION db_migrator;

CREATE EXTENSION db_migrator;

-- Setup (as superuser)
CREATE EXTENSION oracle_fdw;
CREATE SERVER oracle FOREIGN DATA WRAPPER oracle_fdw
    OPTIONS (dbserver '//dbserver.mydomain.com/ORADB');
GRANT USAGE ON FOREIGN SERVER oracle TO migrator;
CREATE USER MAPPING FOR migrator SERVER oracle
    OPTIONS (user 'orauser', password 'orapwd');

-- Migrate (as migrator user)
CREATE EXTENSION ora_migrator;

SELECT db_migrate(
    plugin => 'ora_migrator',
    server => 'oracle',
    only_schemas => '{TESTSCHEMA1,TESTSCHEMA2}'
);

-- 1. Create staging schemas and snapshot metadata
SELECT db_migrate_prepare(
    plugin => 'ora_migrator',
    server => 'oracle',
    only_schemas => '{SCHEMA1}'
);

-- 2. Review and modify staging data
-- Edit pgsql_stage tables to customize type mappings, rename objects, etc.
UPDATE pgsql_stage.tables SET migrate = TRUE WHERE ...;

-- 3. Create schemas and migrate data
SELECT db_migrate_mkforeign(plugin => 'ora_migrator', server => 'oracle');
SELECT db_migrate_tables(plugin => 'ora_migrator');

-- 4. Create constraints and indexes
SELECT db_migrate_constraints(plugin => 'ora_migrator');
SELECT db_migrate_indexes(plugin => 'ora_migrator');

-- 5. Cleanup
SELECT db_migrate_finish();