One of the hottest topics in the PostgreSQL world is migration, and especially from Oracle to PostgreSQL. Many companies, including EDB, Cybertec, AWS, and Azure offer toolkits for cross-platform migration. This post will examine one of the oldest, but still incredibly relevant, tools, Ora2pg. Ora2pg is a great utility, full of features and completely and genuinely Open Source.
The main technical consideration, and focus, in any migration should be the code. Whether its T-Sql or plsql, conversion to plpgsql is not trivial, and in some cases impossible. Therefore, as this post is an introduction to ora2pg, it will focus on a relatively simple schema migration.
- Source: Oracle HR Schema (18c) - standard example schema Target: PostgreSQL v10
- Prerequisites:
- Oracle client/instant client
- Perl modules DBI DBD:Oracle DBD:PG (if installing with yum, DBD:Mysql is also required)
- Latest version of ora2pg – either through your package manager or download source
- Initial configuration:
ora2pg --project_base /instance1/ora2pg --init_project migv1 (this will create base directories, driver scripts, and a config file) /instance1/ora2pg/migv1 [root@linux1 migv1]# ls config data export_schema.sh import_all.sh reports schema sources
- Modify ora2pg.conf file - ora2pg is extremely customizable but for this demo, we will only set connectivity and schema info:
# Set Oracle database connection (datasource, user, password) ORACLE_DSN dbi:Oracle:host=linux1;sid=btdev18c;port=1528 ORACLE_USER system ORACLE_PWD xxxxxxxxxxxx # Oracle schema/owner to use SCHEMA HR # Define the following directive to send export directly to a PostgreSQL # database. This will disable file output. PG_DSN dbi:Pg:dbname=hr;host=localhost;port=5432 PG_USER btpg10 PG_PWD xxxxxxx
- Test configuration:
# ora2pg -t SHOW_VERSION -c config/ora2pg.conf Oracle Database 18c Enterprise Edition Release 18.0.0.0.0
- Generate migration cost report (This is very useful, it provides a difficulty assessment for the potential migration):
# ora2pg -t show_report --estimate_cost -c config/ora2pg.conf --dump_as_html > /tmp/ora2pg.html ------------------------------------------------------------------------------- Ora2Pg v20.0 - Database Migration Report ------------------------------------------------------------------------------- Version Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 Schema HR Size 17.75 MB
| Object | Number | Invalid | Cost | Comments | Details |
|---|---|---|---|---|---|
| DATABASE LINK | 0 | 0 | 0 | Database links will be exported as SQL/MED PostgreSQL's Foreign Data Wrapper (FDW) extensions using oracle_fdw. | |
| GLOBAL TEMPORARY TABLE | 0 | 0 | 0 | Global temporary table are not supported by PostgreSQL and will not be exported. You will have to rewrite some application code to match the PostgreSQL temporary table behavior. | |
| INDEX | 23 | 0 | 3.5 | 12 index(es) are concerned by the export, others are automatically generated and will do so on PostgreSQL. Bitmap will be exported as btree_gin index(es) and hash index(es) will be exported as b-tree index(es) if any. Domain index are exported as b-tree but commented to be edited to mainly use FTS. Cluster, bitmap join and IOT indexes will not be exported at all. Reverse indexes are not exported too, you may use a trigram-based index (see pg_trgm) or a reverse() function based index and search. Use 'varchar_pattern_ops', 'text_pattern_ops' or 'bpchar_pattern_ops' operators in your indexes to improve search with the LIKE operator respectively into varchar, text or char columns. | 12 b-tree index(es) |
| JOB | 0 | 0 | 0 | Job are not exported. You may set external cron job with them. | |
| PROCEDURE | 2 | 0 | 8 | Total size of procedure code: 772 bytes. | secure_dml: 3 add_job_history: 3 |
| SEQUENCE | 4 | 0 | 1 | Sequences are fully supported, but all call to sequence_name.NEXTVAL or sequence_name.CURRVAL will be transformed into NEXTVAL('sequence_name') or CURRVAL('sequence_name'). | |
| SYNONYM | 0 | 0 | 0 | SYNONYMs will be exported as views. SYNONYMs do not exists with PostgreSQL but a common workaround is to use views or set the PostgreSQL search_path in your session to access object outside the current schema. | |
| TABLE | 14 | 0 | 1.6 | 2 check constraint(s). | Total number of rows: 136953 Top 10 of tables sorted by number of rows: reg_test2 has 119780 rows reg_test3 has 16876 rows employees has 107 rows rec_test has 58 rows departments has 27 rows countries has 25 rows locations has 23 rows jobs has 19 rows job_history has 10 rows emp2 has 9 rows Top 10 of largest tables: |
| TRIGGER | 2 | 0 | 5 | Total size of trigger code: 123 bytes. | update_job_history: 3 |
| TYPE | 2 | 0 | 2 | 2 type(s) are concerned by the export, others are not supported. Note that Type inherited and Subtype are converted as table, type inheritance is not supported. | 1 nested tables 1 object type |
| VIEW | 1 | 0 | 1 | Views are fully supported but can use specific functions. | |
| Total | 48 | 0 | 22.10 | 22.10 cost migration units means approximatively 1 man-day(s). The migration unit was set to 5 minute(s) | |
------------------------------------------------------------------------------- Migration level : A-3 ------------------------------------------------------------------------------- Migration levels: A - Migration that might be run automatically B - Migration with code rewrite and a human-days cost up to 5 days C - Migration with code rewrite and a human-days cost above 5 days Technical levels: 1 = trivial: no stored functions and no triggers 2 = easy: no stored functions but with triggers, no manual rewriting 3 = simple: stored functions and/or triggers, no manual rewriting 4 = manual: no stored functions but with triggers or views with code rewriting 5 = difficult: stored functions and/or triggers with code rewriting ------------------------------------------------------------------------------- Details of cost assessment per function Function add_job_history total estimated cost: 3 TEST => 2 SIZE => 1 Function secure_dml total estimated cost: 3 TEST => 2 SIZE => 1 ------------------------------------------------------------------------------- Details of cost assessment per trigger Trigger update_job_history total estimated cost: 3 TEST => 2 SIZE => 1 -------------------------------------------------------------------------------
- Generate DDL scripts from Oracle:
./export_schema.sh [========================>] 14/14 tables (100.0%) end of scanning. [==> ] 1/11 objects types (9.1%) inspecting object DATABAS [====> ] 2/11 objects types (18.2%) inspecting object GLOBAL [======> ] 3/11 objects types (27.3%) inspecting object INDEX [==========> ] 5/11 objects types (45.5%) inspecting object PROCED [=============> ] 6/11 objects types (54.5%) inspecting object SEQUEN [===============> ] 7/11 objects types (63.6%) inspecting object SYNONY [=================> ] 8/11 objects types (72.7%) inspecting object TABLE [===================> ] 9/11 objects types (81.8%) inspecting object TRIGGE [=====================> ] 10/11 objects types (90.9%) inspecting object TYPE [========================>] 11/11 objects types (100.0%) end of objects auditing. Running: ora2pg -p -t TABLE -o table.sql -b ./schema/tables -c ./config/ora2pg.conf [========================>] 14/14 tables (100.0%) end of scanning. [========================>] 14/14 tables (100.0%) end of table export. Running: ora2pg -p -t PACKAGE -o package.sql -b ./schema/packages -c ./config/ora2pg.conf [========================>] 0/0 packages (100.0%) end of output. Running: ora2pg -p -t VIEW -o view.sql -b ./schema/views -c ./config/ora2pg.conf [========================>] 1/1 views (100.0%) end of output. Running: ora2pg -p -t GRANT -o grant.sql -b ./schema/grants -c ./config/ora2pg.conf WARNING: Exporting privilege as non DBA user is not allowed, see USER_GRANT Running: ora2pg -p -t SEQUENCE -o sequence.sql -b ./schema/sequences -c ./config/ora2pg.conf [========================>] 4/4 sequences (100.0%) end of output. Running: ora2pg -p -t TRIGGER -o trigger.sql -b ./schema/triggers -c ./config/ora2pg.conf [========================>] 1/1 triggers (100.0%) end of output. Running: ora2pg -p -t FUNCTION -o function.sql -b ./schema/functions -c ./config/ora2pg.conf [========================>] 0/0 functions (100.0%) end of functions export. Running: ora2pg -p -t PROCEDURE -o procedure.sql -b ./schema/procedures -c ./config/ora2pg.conf [========================>] 2/2 procedures (100.0%) end of procedures export. Running: ora2pg -p -t TABLESPACE -o tablespace.sql -b ./schema/tablespaces -c ./config/ora2pg.conf WARNING: Exporting tablespace as non DBA user is not allowed, see USER_GRANT Running: ora2pg -p -t PARTITION -o partition.sql -b ./schema/partitions -c ./config/ora2pg.conf [========================>] 0/0 partitions (100.0%) end of output. Running: ora2pg -p -t TYPE -o type.sql -b ./schema/types -c ./config/ora2pg.conf [============> ] 1/2 types (50.0%) generating SOURCE_CODE_COMPARE_COL [========================>] 2/2 types (100.0%) generating SOURCE_CODE_COMPARE_CO [========================>] 2/2 types (100.0%) end of output. Running: ora2pg -p -t MVIEW -o mview.sql -b ./schema/mviews -c ./config/ora2pg.conf [========================>] 0/0 materialized views (100.0%) end of output. Running: ora2pg -p -t DBLINK -o dblink.sql -b ./schema/dblinks -c ./config/ora2pg.conf [========================>] 0/0 dblink (100.0%) end of output. Running: ora2pg -p -t SYNONYM -o synonym.sql -b ./schema/synonyms -c ./config/ora2pg.conf [========================>] 0/0 synonyms (100.0%) end of output. Running: ora2pg -p -t DIRECTORY -o directorie.sql -b ./schema/directories -c ./config/ora2pg.conf [========================>] 0/0 directory (100.0%) end of output. Running: ora2pg -t PACKAGE -o package.sql -b ./sources/packages -c ./config/ora2pg.conf [========================>] 0/0 packages (100.0%) end of output. Running: ora2pg -t VIEW -o view.sql -b ./sources/views -c ./config/ora2pg.conf [========================>] 1/1 views (100.0%) end of output. Running: ora2pg -t TRIGGER -o trigger.sql -b ./sources/triggers -c ./config/ora2pg.conf [========================>] 1/1 triggers (100.0%) end of output. Running: ora2pg -t FUNCTION -o function.sql -b ./sources/functions -c ./config/ora2pg.conf [========================>] 0/0 functions (100.0%) end of functions export. Running: ora2pg -t PROCEDURE -o procedure.sql -b ./sources/procedures -c ./config/ora2pg.conf [========================>] 2/2 procedures (100.0%) end of procedures export. Running: ora2pg -t PARTITION -o partition.sql -b ./sources/partitions -c ./config/ora2pg.conf [========================>] 0/0 partitions (100.0%) end of output. Running: ora2pg -t TYPE -o type.sql -b ./sources/types -c ./config/ora2pg.conf [============> ] 1/2 types (50.0%) generating SOURCE_CODE_COMPARE_COL [========================>] 2/2 types (100.0%) generating SOURCE_CODE_COMPARE_CO [========================>] 2/2 types (100.0%) end of output. Running: ora2pg -t MVIEW -o mview.sql -b ./sources/mviews -c ./config/ora2pg.conf [========================>] 0/0 materialized views (100.0%) end of output. To extract data use the following command: ora2pg -t COPY -o data.sql -b ./data -c ./config/ora2pg.conf
- Create DDL objects and load data in PostgreSQL:
# ./import_all.sh -U btpg10 -d hr -p 5432 -o btpg10 Database owner btpg10 already exists, skipping creation. Would you like to drop the database hr before recreate it? [y/N/q] y Running: dropdb -p 5432 -U btpg10 hr Running: createdb -p 5432 -U btpg10 -E UTF8 --owner btpg10 hr Would you like to import TYPE from ./schema/types/type.sql? [y/N/q] y Running: psql --single-transaction -p 5432 -U btpg10 -d hr -f ./schema/types/type.sql SET CREATE TYPE CREATE TYPE Would you like to import TABLE from ./schema/tables/table.sql? [y/N/q] y Running: psql --single-transaction -p 5432 -U btpg10 -d hr -f ./schema/tables/table.sql SET CREATE TABLE COMMENT COMMENT COMMENT COMMENT CREATE TABLE COMMENT COMMENT COMMENT COMMENT COMMENT CREATE TABLE CREATE TABLE CREATE TABLE COMMENT COMMENT COMMENT COMMENT COMMENT COMMENT COMMENT COMMENT COMMENT COMMENT COMMENT COMMENT CREATE TABLE COMMENT COMMENT COMMENT COMMENT COMMENT CREATE TABLE COMMENT COMMENT COMMENT COMMENT COMMENT COMMENT CREATE TABLE COMMENT COMMENT COMMENT COMMENT COMMENT COMMENT COMMENT CREATE TABLE CREATE TABLE CREATE TABLE CREATE TABLE CREATE TABLE CREATE TABLE Would you like to import VIEW from ./schema/views/view.sql? [y/N/q] y Running: psql --single-transaction -p 5432 -U btpg10 -d hr -f ./schema/views/view.sql SET SET CREATE VIEW Would you like to import SEQUENCE from ./schema/sequences/sequence.sql? [y/N/q] y Running: psql --single-transaction -p 5432 -U btpg10 -d hr -f ./schema/sequences/sequence.sql SET CREATE SEQUENCE CREATE SEQUENCE CREATE SEQUENCE CREATE SEQUENCE Would you like to import TRIGGER from ./schema/triggers/trigger.sql? [y/N/q] y Running: psql --single-transaction -p 5432 -U btpg10 -d hr -f ./schema/triggers/trigger.sql SET SET psql.bin:./schema/triggers/UPDATE_JOB_HISTORY_trigger.sql:9: NOTICE: trigger "update_job_history" for relation "employees" does not exist, skipping DROP TRIGGER CREATE FUNCTION CREATE TRIGGER Would you like to import PROCEDURE from ./schema/procedures/procedure.sql? [y/N/q] y Running: psql --single-transaction -p 5432 -U btpg10 -d hr -f ./schema/procedures/procedure.sql SET SET psql.bin:./schema/procedures/ADD_JOB_HISTORY_procedure.sql:20: NOTICE: type reference job_history.employee_id%TYPE converted to integer psql.bin:./schema/procedures/ADD_JOB_HISTORY_procedure.sql:20: NOTICE: type reference job_history.start_date%TYPE converted to timestamp without time zone psql.bin:./schema/procedures/ADD_JOB_HISTORY_procedure.sql:20: NOTICE: type reference job_history.end_date%TYPE converted to timestamp without time zone psql.bin:./schema/procedures/ADD_JOB_HISTORY_procedure.sql:20: NOTICE: type reference job_history.job_id%TYPE converted to character varying psql.bin:./schema/procedures/ADD_JOB_HISTORY_procedure.sql:20: NOTICE: type reference job_history.department_id%TYPE converted to smallint CREATE FUNCTION SET CREATE FUNCTION Would you like to process indexes and constraints before loading data? [y/N/q] y Would you like to import indexes from ./schema/tables/INDEXES_table.sql? [y/N/q] n Would you like to import constraints from ./schema/tables/CONSTRAINTS_table.sql? [y/N/q] y Running: psql -p 5432 -U btpg10 -d hr -f ./schema/tables/CONSTRAINTS_table.sql SET ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE Would you like to import foreign keys from ./schema/tables/FKEYS_table.sql? [y/N/q] n Would you like to import data from Oracle database directly into PostgreSQL? [y/N/q] y Running: ora2pg -c config/ora2pg.conf -t COPY --pg_dsn "dbi:Pg:dbname=hr;host=localhost;port=5432" --pg_user btpg10 [========================>] 14/14 tables (100.0%) end of scanning. [========================>] 25/25 rows (100.0%) Table COUNTRIES (25 recs/sec) [> ] 25/136953 total rows (0.0%) - (0 sec., avg: 25 recs/sec). [> ] 25/136953 rows (0.0%) on total estimated data (1[========================>] 27/27 rows (100.0%) Table DEPARTMENTS (27 recs/sec) [> ] 52/136953 total rows (0.0%) - (0 sec., avg: 52 recs/sec). [> ] 52/136953 rows (0.0%) on total estimated data (1[========================>] 9/9 rows (100.0%) Table EMP2 (9 recs/sec) [> ] 61/136953 total rows (0.0%) - (0 sec., avg: 61 recs/sec). [> ] 61/136953 rows (0.0%) on total estimated data (1[========================>] 6/6 rows (100.0%) Table EMP3 (6 recs/sec) [> ] 67/136953 total rows (0.0%) - (0 sec., avg: 67 recs/sec). [> ] 67/136953 rows (0.0%) on total estimated data (1[========================>] 107/107 rows (100.0%) Table EMPLOYEES (107 recs/sec) [> ] 174/136953 total rows (0.1%) - (1 sec., avg: 174 recs/sec). [> ] 174/136953 rows (0.1%) on total estimated data (1[========================>] 19/19 rows (100.0%) Table JOBS (19 recs/sec) [> ] 193/136953 total rows (0.1%) - (1 sec., avg: 193 recs/sec). [> ] 193/136953 rows (0.1%) on total estimated data (1[========================>] 10/10 rows (100.0%) Table JOB_HISTORY (10 recs/sec) [> ] 203/136953 total rows (0.1%) - (1 sec., avg: 203 recs/sec). [> ] 203/136953 rows (0.1%) on total estimated data (1[========================>] 23/23 rows (100.0%) Table LOCATIONS (23 recs/sec) [> ] 226/136953 total rows (0.2%) - (1 sec., avg: 226 recs/sec). [> ] 226/136953 rows (0.2%) on total estimated data (1[========================>] 5/5 rows (100.0%) Table MINE_TEST (5 recs/sec) [> ] 231/136953 total rows (0.2%) - (1 sec., avg: 231 recs/sec). [> ] 231/136953 rows (0.2%) on total estimated data (1[========================>] 58/58 rows (100.0%) Table REC_TEST (58 recs/sec) [> ] 289/136953 total rows (0.2%) - (1 sec., avg: 289 recs/sec). [> ] 289/136953 rows (0.2%) on total estimated data (1[========================>] 4/4 rows (100.0%) Table REGIONS (4 recs/sec) [> ] 293/136953 total rows (0.2%) - (1 sec., avg: 293 recs/sec). [> ] 293/136953 rows (0.2%) on total estimated data (1[========================>] 4/4 rows (100.0%) Table REG_TEST (4 recs/sec) [> ] 297/136953 total rows (0.2%) - (1 sec., avg: 297 recs/sec). [> ] 297/136953 rows (0.2%) on total estimated data (1[==> ] 10000/119780 rows (8.3%) Table REG_TEST2 (10000 recs[====> ] 20000/119780 rows (16.7%) Table REG_TEST2 (20000 recs[======> ] 30000/119780 rows (25.0%) Table REG_TEST2 (30000 recs[========> ] 40000/119780 rows (33.4%) Table REG_TEST2 (40000 recs[==========> ] 50000/119780 rows (41.7%) Table REG_TEST2 (50000 recs[============> ] 60000/119780 rows (50.1%) Table REG_TEST2 (60000 recs[==============> ] 70000/119780 rows (58.4%) Table REG_TEST2 (70000 recs[================> ] 80000/119780 rows (66.8%) Table REG_TEST2 (80000 recs[==================> ] 90000/119780 rows (75.1%) Table REG_TEST2 (45000 recs[====================> ] 100000/119780 rows (83.5%) Table REG_TEST2 (50000 recs[======================> ] 110000/119780 rows (91.8%) Table REG_TEST2 (55000 recs[========================>] 119780/119780 rows (100.0%) Table REG_TEST2 (59890 recs/sec) [=====================> ] 120077/136953 total rows (87.7%) - (3 sec., avg: 40025 recs/sec). [=====================> ] 120077/136953 rows (87.7%) on total estimated data [==============> ] 10000/16876 rows (59.3%) Table REG_TEST3 (10000 recs/sec) [========================>] 16876/16876 rows (100.0%) Table REG_TEST3 (16876 recs/sec) [========================>] 136953/136953 total rows (100.0%) - (3 sec., avg: 45651 recs/sec). [========================>] 136953/136953 rows (100.0%) on total estimated data (3 sec., avg: 45651 recs/sec)
- Verify objects and data in PostgreSQL:
hr=# \d List of relations Schema | Name | Type | Owner --------+------------------+----------+-------- public | countries | table | btpg10 public | departments | table | btpg10 public | departments_seq | sequence | btpg10 public | emp2 | table | btpg10 public | emp3 | table | btpg10 public | emp_details_view | view | btpg10 public | employees | table | btpg10 public | employees_seq | sequence | btpg10 public | job_history | table | btpg10 public | jobs | table | btpg10 public | locations | table | btpg10 public | locations_seq | sequence | btpg10 public | mine_test | table | btpg10 public | rec_test | table | btpg10 public | reg_test | table | btpg10 public | reg_test2 | table | btpg10 public | reg_test3 | table | btpg10 public | regions | table | btpg10 public | trans_id_s | sequence | btpg10 (19 rows) hr=# \df List of functions Schema | Name | Result data type | Argument data types | Type --------+--------------------------------+------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------+--------- public | add_job_history | void | p_emp_id integer, p_start_date timestamp without time zone, p_end_date timestamp without time zone, p_job_id character varying, p_department_id smallint | normal public | secure_dml | void | | normal public | trigger_fct_update_job_history | trigger | | trigger (3 rows) hr=# select count(*) from employees; count ------ 107 (1 row) hr=# select * from employees limit 1; -[ RECORD 1 ]--+-------------------- employee_id | 100 first_name | Steven last_name | King email | SKING phone_number | 515.123.4567 hire_date | 2003-06-17 00:00:00 job_id | AD_PRES salary | 24000 commission_pct | manager_id | department_id | 90
