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


Azure Postgres

July 30, 2020


  1. Alicja Kucharczyk EMEA Global Blackbelt OSS Data Tech Specialist

  2. None
  3. None
  4. None
  5. None
  6. None
  7. None
  8. None
  9. None
  10. None
  11. None
  12. None
  13. 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
  14. INSTALLATION • Hard Way: • Easy way: Docker!

  15. 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
  16. Connect to Oracle

  17. 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/ ├── ├── 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/
  18. MIGRATION ASSESSMENT ora2pg -c /etc/ora2pg.conf -t SHOW_REPORT –estimate_cost --dump_as_html >


    the code!
  20. 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
  21. 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
  22. Partitioning CREATE EXTENSION pg_partman WITH SCHEMA partman SELECT partman.create_parent('', '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 = '';
  23. Data q Data is usually simple q The devil is

    in the details
  24. ./ 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
  25. 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
  26. Thank you!