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

Transcript

1. Alicja Kucharczyk EMEA Global Blackbelt OSS Data Tech Specialist
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: http://ora2pg.darold.net/documentation.html#INSTALLATION • Easy way: Docker! https://github.com/AwdotiaRomanowna/ora2pgWorkshop
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/ ├── 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/
18. MIGRATION ASSESSMENT ora2pg -c /etc/ora2pg.conf -t SHOW_REPORT –estimate_cost --dump_as_html > report.html
19. ASSESSMENT NEVER TRUST THE ASSESSMENT! Always take a look at 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('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';
23. Data q Data is usually simple q The devil is in the details
24. ./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
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!