Contributions by MigOps to PostgreSQL in 2021

By / Migrations to PostgreSQL, Oracle to PostgreSQL, PostgreSQL / / January 10, 2022

Our team at MigOps, believes that the increasing open source contributions to the PostgreSQL ecosystem is the major reason for increased migrations to PostgreSQL. Several developers across the world have been spending a lot of their personal and professional time to keep the beauty of this Open Source database software active.

Some organizations have even hired developers to contribute and enjoy the freedom of the feature rich PostgreSQL software. We have also done our part ! Our team at MigOps has proudly contributed over 20,000 lines of code to the PostgreSQL Open Source ecosystem that includes extensions, tools and new features or patches to the PostgreSQL community software. Through this article, we shall discuss some of the contributions by MigOps to the PostgreSQL ecosystem in 2021.

Contact MigOps today if you are looking to migrate to PostgreSQL

PostgreSQL ecosystem includes -

(1) PostgreSQL Community software
(2) Open Source tools for managing PostgreSQL Backups, High Availability, Monitoring, etc.
(3) PostgreSQL extensions
(4) Extensions for Oracle (or other databases) compatibility to PostgreSQL
(5) Ora2Pg like open source tools for Oracle to PostgreSQL migrations
(6) some more tools built to support a business or a user running PostgreSQL

MigOps has written several extensions and patches under the leadership of Gilles Darold (CTO at MigOps) and Avinash Vallarapu (CEO at MigOps). We have categorized our contributions into the following sections and we shall view them in a nutshell with references.

  1. Extensions announced by MigOps in 2021
  2. Patches to PostgreSQL Community Software
  3. Features added to Ora2Pg - an Open Source Oracle to PostgreSQL migrations tool
  4. Patches pushed to existing PostgreSQL extensions
  5. Patches pushed to other Open Source communities such as Golang.
Extensions announced by MigOps in 2021

Our team at MigOps identifies the need for new extensions during the phase of Migration Assessment or actual Migrations. Some of the customers approach us with unique requirements that may involve writing a new extension for PostgreSQL. Below is a list of new extensions that were developed and contributed by the team in 2021.

Please Note : All the extensions mentioned below have been released with the most liberal PostgreSQL license. This allows anybody to use, distribute or repurpose at any given point of time.

  • credcheck : This extension provides general credential checks, which will be evaluated during the user creation, during the password change and while renaming a user. A user will now be able to enforce rules such as minimum number of upper case and lower case characters, special characters, numbers, etc to a username or a password.
  • pg_dbms_job : While performing a database migration from Oracle to PostgreSQL for one of our customers, we noticed heavy usage of DBMS_JOB in their Oracle environments. This dependency has added additional development time to implement possible work-arounds. MigOps has thus decided to develop an extension with the most liberal PostgreSQL license (Open Source). Users migrating to PostgreSQL will now be able to schedule and manage jobs in a job queue similar to the Oracle DBMS_JOB package. This extension provides full compatibility to the Oracle’s DBMS_JOB package.
  • pg_dbms_errlog : Similar to pg_dbms_job, the requirement for building the extension : pg_dbms_errlog came while migrating a customer from Oracle to PostgreSQL. There was a heavy usage of Oracle’s DBMS_ERRLOG package for DML error logging. Upon building pg_dbms_errlog that is compatible with the DBMS_ERRLOG package in Oracle, we were able to eliminate a huge amount of development time involved while implementing possible work-arounds.

Contact MigOps today for PostgreSQL Migration Assessments

Patches to PostgreSQL Community Software

While performing Migration Assessments or Migrations, we come across several incompatibilities that require complex work-arounds. Some of these incompatibilities have enabled us to contribute patches as new features to the PostgreSQL Community software and a few of them are mentioned below 

  • regexp_* functions : adds new functions regexp_count(), regexp_instr(), regexp_like(), and regexp_substr(), and extends regexp_replace() with some new optional arguments.
    Status: committed.
    Patch and discussion: https://commitfest.postgresql.org/34/3042/
  • Rollback at Statement Level : Allow PostgreSQL extension to act at start and end (including abort) of a SQL statement in a transaction to be able to implement rollback at statement level. Status: Not implemented yet.
    Patch and discussion: https://commitfest.postgresql.org/34/2860/
  • Hidden columns: patch to implement HIDDEN columns feature in PostgreSQL.
    Status: Not implemented yet. 

Patch and discussion: https://commitfest.postgresql.org/35/3361/

  • New Diagnostic items : proposal of adding 2 new diagnostic items related to parsing the dynamic sql statement.
    Status: In progress. 

Patch and discussion: https://commitfest.postgresql.org/35/3258/

Patch reviews :
Features added to Ora2Pg - an Open Source Oracle to PostgreSQL migration tool

Ora2Pg is an Open Source Oracle and MySQL to PostgreSQL migration tool that has been maintained by Gilles Darold (the CTO of MigOps,) for over the past 21 years. This tool has helped many companies migrate from Oracle to PostgreSQL to reduce their infrastructure costs. We have added some of the very useful features to Ora2Pg that were long pending in the year 2021. Some of them are mentioned below.

  • Add data validation feature upon migration of data from Oracle to PostgreSQL. This feature has the ability to compare the data retrieved from a foreign table pointing to the source Oracle table with a local PostgreSQL table, migrated using Ora2Pg or any migration tool.
  • Allow multiprocess for data comparison to validate data import. 
  • Added a new option --blob_to_lo that can be used to export BLOB as large objects automatically. Large objects over several GBs can be stored in pg_largeobjects having only their OIDs stored in base tables.
  • Added command line option -W | --where clause to query data from Oracle that matches a certain condition. This is heavily used while migrating data that only matches a specified condition from Oracle to PostgreSQL.
  • Added export of data using the Oracle Foreign Data Wrapper, oracle_fdw. This is one of the major features implemented to reduce the overall migration time. Please read this blog article for more details.
  • Improved export performance when there exists a huge number of objects in an Oracle database.
  • Added the detection of XML functions. This will be heavily used while analyzing the cost of an Oracle to PostgreSQL database migration using Ora2Pg migration assessment report. 
  • Added new tests to check the sequences and their last values along with the number of identity columns on both sides.
  • Improved migration assessment when USE_ORAFCE is enabled and improved Orafce extension support while migrating schema from Oracle to PostgreSQL. 
  • Added replacement of UTL_RAW.CAST_TO_RAW with encode().
  • Added rewrite of XMLTYPE() with xmlparse(DOCUMENT convert_from(..., 'utf-8')).
  • Added support to mysql_fdw foreign data wrapper to export data from MySQL to PostgreSQL.
  • Added support to automatically transform all NUMBER(*,scale) to appropriate data type other than the default NUMERIC data type. Usage of NUMERIC data type can utilize more space and cause performance issues in PostgreSQL. 
  • Added a new action to just report the row count differences between Oracle and PostgreSQL tables.
  • Lots of bug fixes and improvements have also been contributed to Ora2Pg in the year : 2021.
Patches pushed to existing PostgreSQL extensions

MigOps approaches every migration to PostgreSQL from Oracle and any database through a detailed assessment. During the assessment, we identify the true amount of person days involved in database and application migration. These assessments help us identify the patches that must be pushed to existing PostgreSQL extensions to smoothen the migration process. Some of such patches have been pushed to Orafce, PGTT, Orafce_mail and some more extensions mentioned below. Following are some of the PR’s or patches pushed to PostgreSQL extensions.

  • Orafce : An extension that provides Oracle compatibility to PostgreSQL. 
    • Added the regexp_* functions defined in Oracle to provide the same bahaviour in  PostgreSQL. 
    • Added DBMS_UTILITY.GET_TIME function. It returns the current time in 100th's of a second from a point in time in the past. 
    • Implemented oracle.least() and oracle.greatest() functions compatible with Oracle while handling NULL arguments.

  • Orafce_mail : An extension that allows sending emails from a PostgreSQL database. This provides compatibility to the utl_mail and DBMS_MAIL packages in Oracle. 
    • Fixed compilation error with libcurl < 7.69.0.

  • code2pg : A tool to assess, provide estimated person days and migrate application code with embedded SQL code from Oracle to PostgreSQL.
    • Added support for applications written in Perl.
    • Added exclusion of instructions from analysis.

  • pgtt : An extension to provide Oracle’s Global temporary table feature in PostgreSQL. 
    • Added support for PostgreSQL 14. 
    • Fixed creation of GTT when there is a CHECK constraint with string constant.
    • Added patch to prevent code to be executed in parallel processes. 
    • Improved performance by not looking for an existing Global Temporary Table if the table is a temporary table or part of the pg_catalog.

  • pgbadger : Most widely used log analyzer tool for PostgreSQL.
    • Added detection of Query id in log_line_prefix. Query id is available with PostgreSQL 14. 
    • Added feature to apply multiprocess for report generation when `--explode` is used. 
    • Added `--iso-week-number` in incremental mode, calendar's weeks start on a Monday and respect the ISO 8601 week number.  
    • Added command line option --keep-comments to not remove comments from normalized queries. 
    • Added patch to skip INFO lines introduced in PostgreSQL log files by third party softwares. 
    • Added compatibility with PostgresPro log file including row numbers and sizes in bytes following the statement duration.  
    • Improved Windows portability.

  • pgFormatter : A PostgreSQL SQL formatter and beautifier that can either work as a console program or as a CGI. 
    • Alternate format type (option -t) now keeps enumeration in GROUP BY clause on a single line. 
    • Added a new command line argument, -k or --keep-newline, to preserve empty lines in the plpgsql code. 
    • Added a patch to look for the .pg_format configuration file in the local directory first then in the home directory.  
    • Added patch to force UTF8 encoding to solve unicode characters in object name parsing issues. 
    • Added a button to copy formatted text to clipboard in the CGI interface.
Patches pushed to other Open Source communities such as Go.

MigOps has also contributed to other Open Source communities such as Golang. Following are a selected list of patches and features contributed. 

  • gjson
    • Feature added to the golang based JSON library. The current library has a JSON path, JSON query feature. But for the pattern matching, they are depending on the wildcard characters and not using the regular expressions. Hence, proposed a new pull request where the JSON path will happen based on the regular expressions. https://github.com/tidwall/gjson/pull/257
Conclusion

We would firstly like to thank all our customers who were the driving factors for many ideas that resulted in contributions to the PostgreSQL ecosystem. Some of our customers who have approached us for database migration assessments are also the reason for identifying some of the complicated code that cannot directly work in PostgreSQL. This motivated us to build new extensions or patches that will not only help our customers but also the users across the world migrating to PostgreSQL. MigOps will be glad to support you with database migration assessments that will be performed by humans instead of automated tools. If you wish to have an assessment done and also be a reason for a contribution to the PostgreSQL Open Source ecosystem, contact us today or fill the following form.

Leave a Comment

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

Scroll to Top