Contributions by MigOps to PostgreSQL in 2022

By / Migrations to PostgreSQL, Oracle to PostgreSQL, PostgreSQL, SQL Server to PostgreSQL / / January 5, 2023

We have earlier published the contributions by MigOps to PostgreSQL in 2021. MigOps has continued its momentum of Contributions to PostgreSQL in 2022. This was with the spirit of supporting users looking to PostgreSQL and adding our share on increasing PostgreSQL adoptions. In the year, 2023, MigOps aims to make more massive contributions, to support migrations of both Oracle and SQL Server to PostgreSQL. Through this article, we are going to list some of the contributions by MigOps to the PostgreSQL ecosystem in 2022.

I would like to thank our CTO, Gilles Darold, for all his contributions and maintaining several PostgreSQL extensions, our Development and DBA teams who have contributed their ideas and feature requests, and our CEO, Avi Vallarapu, for encouraging us to contribute all our learnings and research to the Open Source ecosystem of PostgreSQL while there are companies commercializing such work.

Migrate to PostgreSQL with MigOps

We have categorized all our Contributions to the PostgreSQL ecosystem into following sections.

  1. New PostgreSQL extensions published by MigOps in 2022
  2. Features added to Ora2Pg - an Open Source tool supporting migrations to PostgreSQL.
  3. Patches to PostgreSQL Community Software including reviews
  4. Patches pushed to existing PostgreSQL extensions
New PostgreSQL extensions published by MigOps in 2022

MigOps generally identifies the need for new PostgreSQL features during the Assessment phase of a database migration.  These new features may be required to support Oracle compatibility or SQL Server compatibility or some other database compatibility to PostgreSQL. Through some of the assessments we have performed, we identified whether certain feature can be contributed to an existing extension like Orafce or if it requires a new extension.

Database Migration Assessment : Our experts being the Authors and Contributors of Ora2Pg, never recommend relying on a Migration Assessment performed by a tool. A database migration assessment by experts is very important to avoid performance killing work-arounds.

Get your Oracle to PostgreSQL Migration Assessment or SQL Server to PostgreSQL Migration Assessment, performed by MigOps. Contact Us

Following is a new extension published by MigOps in 2022.

  • oracle_fnd : This is a new extension to support Oracle compatibility to PostgreSQL that helps in avoiding the rewrite of calls to FND_GLOBAL and FND_PROFILE functions. This extension emulates the FND_GLOBAL and FND_PROFILE package's API using custom variables and thus helps to speed up the migration process.
Features added to Ora2Pg

Through a number of database migrations in 2022, MigOps never lacked enhancements to Ora2Pg. Ora2Pg was originally started by Gilles Darold as an Oracle to PostgreSQL migration tool. To simplify migrations to PostgreSQL, MigOps has always supported the development of Ora2Pg to increase Postgres adoptions.

In the year 2022, MigOps has published 2 new versions of Ora2Pg for a much better Oracle to PostgreSQL conversion rate.

Features published through Ora2Pg v23.1

  • Add use of greatest/least functions from the new version of Orafce when required to return NULL on NULL input like Oracle.
  • The ALLOW and EXCLUDE configuration values can now be read from a file.
  • Add possibility to use of System Change Number (SCN) for data export or data validation by providing a specific SCN.
  • Add json output format to migration assessment.
  • Add TO_CHAR_NOTIMEZONE configuration directive to remove any timezone information into the format part of the TO_CHAR() function. Disabled by default.
  • Add a new configuration directive FORCE_IDENTITY_BIGINT. Usually the identity column must be bigint to correspond to an auto increment sequence so Ora2Pg always forces it to be a bigint. If, for any reason you want Ora2Pg to respect the DATA_TYPE you have set for the identity column then disable this directive.
  • Add command line option --lo_import. By default Ora2Pg imports Oracle BLOB as bytea; the destination column is created using the bytea data type. If you want to use a large object instead of bytea, just add the --blob_to_lo option to the ora2pg command.
  • Add command line option --cdc_ready to use current SCN per table when exporting data and registering them into a file named TABLES_SCN.log. This can be used by the Change Data Capture (CDC) tools.
  • Allow to export only invalid objects when EXPORT_INVALID is set to 2.
  • Disable per partition data export when a WHERE clause is defined on the partitioned table or that a global WHERE clause is defined.

Features published through Ora2Pg v23.2

  • Add export of MySQL KEY and LINEAR KEY partitioning, translated as HASH partitioning.
  • Allow export of objects with dollar signs in object name.
  • Add export of CHECK constraints for MySQL >= 8.0.
  • Add Functional/Expression indexes export from MYSQL.
  • Add export of MySQL virtual column.
  • Remove scale and precision of a numeric if the scale is higher than the precision. PostgreSQL does not support decimal/numeric attributes where the scale is explicitly higher than the precision.
  • Add command line option --drop_if_exists to add statement to drop objects before creation if it exists. It corresponds to the DROP_IF_EXISTS.
  • Add option -C | --cdc_file to be able to change the name of the default file used to store/read SCN per table during export. Default is TABLES_SCN.log in the current directory. This is the file written by the --cdc_ready option.
  • Add multiprocess to count rows in PostgreSQL tables (TEST_COUNT) using -P command line option.
  • Add support to PostgreSQL 14 procedure with OUT parameters.
  • Set default PostgreSQL database target version to 14.
  • Add configuration directive MVIEW_AS_TABLE and command line option --mview_as_table to set which materialized view to export as table.
  • Add configuration variable FDW_IMPORT_SCHEMA to rename the schema where foreign tables for data migration will be created.
  • Add TRANSFORM_VALUE configuration directive to apply an expression when retrieving data from Oracle.
  • Add EXCLUDE_COLUMNS configuration directive. Unlike MODIFY_STRUCT that is used to redefine a table structure, this directive allows to specify a list of columns per table that must be excluded from the export.
  • Add new configuration directive EXPORT_GTT to export Oracle Global Temporary Table using syntax recognized by the pgtt extension.
  • Add a new configuration option NO_EXCLUDED_TABLE. By default Ora2Pg exclude from export some Oracle "garbage" tables that should never be part of an export. This behavior generates a lot of REGEXP_LIKE expressions which are slowing down the export when looking at tables. To disable this behavior enable this directive.
Patches pushed to existing PostgreSQL extensions

Through a number of database migrations performed in 2022, there have been a variety of recommendations from our Customers and Postgres users, that enabled us to publish patches to some of the existing Postgres extensions. Following are a few of the extensions that either directly or indirectly support migrations to PostgreSQL.

  • uri extension :
    • This is an extension to provide uri data type for PostgreSQL
    • 1 new version released in 2022 (1.2)
    • This now removes the requirement of liburi.

  • pg_dbms_job :
    • This is an extension published by MigOps in 2021. It emulates the Oracle DBMS_JOB functionality in PostgreSQL.
    • 4 new versions published in 2022.
      (Versions : 1.2, 1.3, 1.4 and 1.5).

  • pgtt :
    • PGTT is an extension that heavily supports migrations from Oracle to PostgreSQL. It provides the functionality of Global temporary tables in PostgreSQL which is currently not yet published in Postgres core but only available through PGTT extension.
    • 2 new versions published in 2022
      (Version : 2.8 and 2.9)

  • external_file :
    • This is an extension to provide similar functionalities as Oracle’e BFILE data type. This allows access to external files through the Postgres server.
    • 1 new version published in 2022
      (Version : 1.1).

  • pg_dbms_errlog :
    • This is an extension to emulate Oracle DBMS_ERRLOG functionality in PostgreSQL.
    • 1 new version published in 2022
      (Version : 2.1).

  • orafce :
    • Orafce is one of the promising extensions supporting Oracle compatibility for PostgreSQL. It provides support for various Oracle packages in PostgreSQL that eliminates the need to rewrite most of the Oracle syntax while migrating to PostgreSQL.
    • A patch was submitted to fix the regexp_instr() function with occurrence when the same value matching a pattern is found at several places in the string.

  • oracle_fnd :
    • This is an extension to emulate Oracle FND_GLOBAL and FND_PROFILE packages compatible with PostgreSQL. This was an extension created by MigOps in 2022.
      (Versions : 1.1, 1.2 and 1.3).

  • pgBadger :
    • It is a popular tool for parsing PostgreSQL log files that prepares an easily analyzable HTML report upon scanning Postgres logs.
    • 3 new versions published in 2022
      (Versions : 11.7, 11.8 and 12.0).

  • pg_show_rewritten_query :
    • This is a very helpful extension for DBA’s and Developers attempting to debug complex queries that are built on top of complex views. Through this extension, we can see the rewritten query upon analyze, which is the actual query executed by Postgres.
    • Through a patch to this extension, a patch was pushed to fix the usage of a duplicate variable name conflicting with a parameter, and another patch to add extension creation documentation under examples.

  • pgCluu :
    • This is an Open Source PostgreSQL performance monitoring and auditing tool.
    • 2 new versions published in 2022.
      (Versions : 3.3 and 3.4).

  • pgFormatter :
    • This is a robust syntax beautifier that helps format complex SQL and PL/pgSQL code for an easy analysis.
    • 1 new version published in 2022.
      (Version : 5.3).

  • pg_dumpbinary :
    • This is an extension to dump a PostgreSQL database in binary format, especially in certain situations where pg_dump cannot work.
    • 2 new versions published in 2022.
      (Version : 2.6 and 2.7).

Patches and Reviews of PostgreSQL

  • Patch : Add --schema and --exclude-schema options to vacuumdb
    • vacuumdb lacked a feature to vacuum or analyze all objects of only selected schema(s). This requires us to write a custom script to vacuum or analyze objects of a specific schema. During the process of migrating one of our Customer's multi-tenant databases, we identified this requirement and contributed this feature to PostgreSQL.
    •  Commit Discussion
  • Review : Add public ruleutils.c entry point to deparse a Query
  • Back-Patched : Reported bug on pg_dump and backpatched versions 10 to 14. Postpone calls of unsafe server-side functions in pg_dump. Commit Discussion

Conclusion

There are several organizations and developers who have published patches for new features or bug fixes to PostgreSQL. At the same time, MigOps identified that it is equally important to contribute features that support seamless migrations to PostgreSQL. We would like to thank everyone involved in making PostgreSQL a popular migration target for both Oracle and SQL server environments.

Are you using Oracle or SQL Server databases ? Get your Database Migration Assessment performed by MigOps by filling the form below.

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top