db_migrator

pig build pkg db_migrator;		# build rpm/deb

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

pig install db_migrator;		# install via package name, for the active PG version

pig install db_migrator -v 18;   # install for PG 18
pig install db_migrator -v 17;   # install for PG 17
pig install db_migrator -v 16;   # install for PG 16
pig install db_migrator -v 15;   # install for PG 15
pig install db_migrator -v 14;   # install for PG 14

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();