Ora2Pg v22.0 and 22.1 released

Posted on 2021-07-05 by Gilles Darold
Related Open Source

Grenoble, France - July 2nd, 2021


Version 22.0 of Ora2Pg, a free and reliable tool used to migrate an Oracle database to PostgreSQL, has been officially released and is publicly available for download.

This release fix several issues reported since past three months and adds some new features and improvements. Release 22.1 is a maintenance release to extend the feature of data export through the oracle_fdw PostgreSQL extension to migration that use the public schema and do not preserve case.

  • Add export of data using oracle_fdw when FDW_SERVER is set and export type is COPY or INSERT. Multi-process using -P or -J is fully supported but option -j is useless in this case. Boolean transformation of some columns or data type is also supported. Actually, expect that it works just like data migration without oracle_fdw. This can improve the data migration speed from 30 to 40% especially for BLOB export. Thanks to MigOps Inc for the patch.

  • Improve export performances with huge number of objects by avoiding join between Oracle catalog tables. Thanks to MigOps Inc for the patch.

  • Set a maximum of assessment score for tables, indexes, sequences, partitions, global temporary table and synonym following the number of objects. Thanks to MigOps Inc for the patch.

  • Add detection of XML functions to the assessment cost. Thanks to MigOps Inc for the patch.

  • Allow to change the assessment cost unit value in the export_all.sh script when ora2pg is used with options --init_project and --cost_unit_value.

  • Remove pragma restrict_references from P/PSQL code, it is useless.

  • Add the oracle schema to search_path in SQL files generated and improve the migration assessment cost when USE_ORAFCE is enabled. Thanks to MigOps Inc for the patch.

  • Apply ALLOW and EXCLUDED filtered stored procedures at package extraction level. Previous this patch there was no way to not export some package functions or to exclude them from assessment. Thanks to MigOps Inc for the patch.

  • Add new tests to check sequences last values and number of identity columns in both side. Thanks to MigOps Inc for the patch.

  • Apply ALLOW and EXCLUDED filters without object type specification to table object by default in TEST action.

New configuration directives

  • Add ORACLE_FDW_TRANSFORM configuration directive to apply a transformation to a column when exporting data. Value must be a semicolon separated list of TABLE[COLUMN_NAME, <replace code in SELECT target list>]. For example to replace string 'Oracle' by 'PostgreSQL' in a varchar2 column use ERROR_LOG[DBMS_TYPE:regexp_replace("DBMS_TYPE",'Oracle','PostgreSQL')]

  • Add DROP_IF_EXISTS configuration directive to add a statement "DROP ... IF EXISTS" before creating the object. Can be useful in an iterative work. Default is disabled.

Backward compatibility

There is a backward compatibility issue with old configuration files where FDW_SERVER is set by default. This directive was not used when exporting data, this is not the case anymore as it instruct Ora2Pg to use the given foreign server to use oracle_fdw to migrate the data.

For a complete list of change see https://github.com/darold/ora2pg/blob/master/changelog

Thanks to all contributors, they are all cited in the changelog file.

Links & Credits

I would like to thank all users who submitted patches and users who reported bugs and feature requests, they are all cited the ChangeLog file.

Ora2Pg is an open project. Any contribution to build a better tool is welcome. You just have to send your ideas, features requests or patches using the GitHub tools or directly to ora2pg@darold.net.


About Ora2Pg

Ora2Pg is an easy and reliable tool to migrate from Oracle to PostgreSQL. It is developed since 2001 and can export most of the Oracle objects into PostgreSQL compatible code.

Ora2Pg works on any platform and is available under the GPL v3 licence.

Docs, Download & Support at http://www.ora2pg.com/