Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Oracle to Postgres migration with ora2pg | Linux Open Alternative Day 2020 | Alicja Kucharczyk

Oracle to Postgres migration with ora2pg | Linux Open Alternative Day 2020 | Alicja Kucharczyk

Ora2Pg is the most popular tool for Oracle migrations to PostgreSQL. It is free, rich in features, stable and has a long history of development. But you would need some time to familiarize yourself with the instrument itself. I want to show you how (in the most painless way) you start your journey with migration

More Decks by Azure Database for PostgreSQL

Other Decks in Technology


  1. FEATURES • Automatic Oracle database discovery • Automatic creation of

    migration projects • Oracle database migration cost assessment • Automatic database schema export • Full and automatic data export • Automatic conversion of PL/SQL to PLPGSQL • Oracle Spatial to PostGis export
  2. Docker yum update -y yum install docker -y systemctl start

    docker systemctl status docker docker pull georgmoser/ora2pg-docker mkdir /data docker run -it --privileged -v /data:/data georgmoser/ora2pg-docker /bin/bash ora2pg --version
  3. MIGRATION PROJECT ora2pg --init_project my_db_mig --project_base /full/path/to/project /full/path/to/project/my_db_mig/ ├── config/

    │ └── ora2pg.conf ├── data/ ├── export_schema.sh ├── reports/ ├── schema/ │ ├── dblinks/ functions/ grants/ mviews/ packages/ │ ├── partitions/ procedures/ sequences/ synonyms/ │ └── tables/ tablespaces/ directories/ triggers/ types/ views/ └── sources/ ├── functions/ mviews/ packages/ partitions/ └── procedures/ triggers/ types/ views/
  4. Challenges You should be fine with: tables, constraints, indexes, sequences,

    triggers, views, tablespaces, grants, types, partitions, synonyms, database links (on cloud require modernization), materialized views (refresh) … Only procedures, functions and packages could be a problem
  5. Tables Usually pretty simple What to keep in mind: q

    PostgreSQL has more data types q NUMBER(1) or CHAR(1) to BOOLEAN? q VARCHAR2(2000 CHAR) to TEXT? q Inet, jsonb, time etc. Partitions aren't an issue any more
  6. Partitioning CREATE EXTENSION pg_partman WITH SCHEMA partman SELECT partman.create_parent('github.events', 'created_at',

    'native', 'hourly'); SELECT run_maintenance(p_analyze := false); SELECT cron.schedule('@hourly', $$SELECT partman.run_maintenance(p_analyze := false)$$); UPDATE partman.part_config SET retention_keep_table = false, retention = '1 month' WHERE parent_table = 'github.events';
  7. ./export_schema.sh ora2pg -p -t TABLE -o table.sql -b ./schema/tables -c

    ./config/ora2pg.conf ora2pg -p -t PACKAGE -o package.sql -b ./schema/packages -c ./config/ora2pg.conf ora2pg -p -t VIEW -o view.sql -b ./schema/views -c ./config/ora2pg.conf ora2pg -p -t SEQUENCE -o sequence.sql -b ./schema/sequences -c ./config/ora2pg.conf ora2pg -p -t TRIGGER -o trigger.sql -b ./schema/triggers -c ./config/ora2pg.conf ora2pg -p -t FUNCTION -o function.sql -b ./schema/functions -c ./config/ora2pg.conf ora2pg -p -t PROCEDURE -o procedure.sql -b ./schema/procedures -c ./config/ora2pg.conf ora2pg -p -t TABLESPACE -o tablespace.sql -b ./schema/tablespaces -c ./config/ora2pg.conf ora2pg -p -t PARTITION -o partition.sql -b ./schema/partitions -c ./config/ora2pg.conf ora2pg -p -t TYPE -o type.sql -b ./schema/types -c ./config/ora2pg.conf ora2pg -p -t MVIEW -o mview.sql -b ./schema/mviews -c ./config/ora2pg.conf ora2pg -p -t DBLINK -o dblink.sql -b ./schema/dblinks -c ./config/ora2pg.conf ora2pg -p -t SYNONYM -o synonym.sql -b ./schema/synonyms -c ./config/ora2pg.conf
  8. AZURE MIGRATION RESOURCES Azure Database Migration Guide DMS homepage How

    to migrate using dump and store How to migrate using export and import How to migrate online