The team at HexaCluster can proudly be referred to as PostgreSQL contributors. Our team is one of the strongest reasons for increasing migrations to PostgreSQL and also the adoption of PostgreSQL. Our team has contributed to various tools and extensions to PostgreSQL. In this article, we are going to list out some of the contributions done by our team in 2023. Please do not forget to read our article on PostgreSQL Summary and Achievements in 2023.
Our contributions are majorly possible because of our CTO, Gilles Darold, who is one of the top contributors to the PostgreSQL ecosystem. Gilles has immensely encouraged and guided our team and led to the birth of some of the great features in PostgreSQL through extensions.
We have categorized our contributions to the PostgreSQL ecosystem into following categories.
- New PostgreSQL Extensions created by the Team at HexaCluster
- New releases of Ora2Pg and their features
- Patches submitted to PostgreSQL
- Patches pushed to existing PostgreSQL extensions
New PostgreSQL Extensions created by the Team at HexaCluster
- pg_dbms_lock
- A PostgreSQL extension to manage advisory locks in a way compatible with the Oracle DBMS_LOCK package. This extension emulates Oracle’s user-defined locks behavior in PostgreSQL, offering a consistent interface for managing advisory locks. pg_dbms_lock ensures a smooth transition, enabling developers to maintain their existing lock management logic in Oracle while embracing the benefits of PostgreSQL and simplifies migrations from Oracle to PostgreSQL.
- A PostgreSQL extension to manage advisory locks in a way compatible with the Oracle DBMS_LOCK package. This extension emulates Oracle’s user-defined locks behavior in PostgreSQL, offering a consistent interface for managing advisory locks. pg_dbms_lock ensures a smooth transition, enabling developers to maintain their existing lock management logic in Oracle while embracing the benefits of PostgreSQL and simplifies migrations from Oracle to PostgreSQL.
- pg_dbms_metadata
- PostgreSQL extension to extract DDL of database objects in a way compatible with the Oracle DBMS_METADATA package. This extension is designed to give compatibility to Oracle’s DBMS_METADATA package. This facilitates a seamless transition for customers migrating from Oracle to PostgreSQL and provides a systematic approach for effortlessly retrieving DDL programmatically. During the migration from Oracle to PostgreSQL, this extension can help us get easily integrated into the existing workflows, making DDL extraction smoother.
- PostgreSQL extension to extract DDL of database objects in a way compatible with the Oracle DBMS_METADATA package. This extension is designed to give compatibility to Oracle’s DBMS_METADATA package. This facilitates a seamless transition for customers migrating from Oracle to PostgreSQL and provides a systematic approach for effortlessly retrieving DDL programmatically. During the migration from Oracle to PostgreSQL, this extension can help us get easily integrated into the existing workflows, making DDL extraction smoother.
- pg_utl_smtp
- The PostgreSQL extension known as “pg_utl_smpt” is designed to facilitate compatibility with the UTL_SMTP Oracle package by implementing a set of essential routines for SMTP communication. These routines include CLOSE_DATA, EHLO, HELO, MAIL, OPEN_CONNECTION, OPEN_DATA, QUIT, RCPT, WRITE_DATA, and WRITE_RAW_DATA, albeit with certain simplifications. Notably, only procedures are implemented, excluding functions, and the “utl_tcp.crlf” has been replaced by “\r\n” or “\n” for line endings. Additionally, some features like wallet_path and wallet_password in the open_connection() function are not utilized, and exceptions like UTL_SMTP.TRANSIENT_ERROR and UTL_SMTP.PERMANENT_ERROR are not currently supported. Some SMTP routines, such as AUTH, CLOSE_CONNECTION, COMMAND, COMMAND_REPLIES, DATA, HELP, NOOP, RSET, STARTTLS, and VRFY, are not yet available in this extension. (Currently available for our Customers only).
- The PostgreSQL extension known as “pg_utl_smpt” is designed to facilitate compatibility with the UTL_SMTP Oracle package by implementing a set of essential routines for SMTP communication. These routines include CLOSE_DATA, EHLO, HELO, MAIL, OPEN_CONNECTION, OPEN_DATA, QUIT, RCPT, WRITE_DATA, and WRITE_RAW_DATA, albeit with certain simplifications. Notably, only procedures are implemented, excluding functions, and the “utl_tcp.crlf” has been replaced by “\r\n” or “\n” for line endings. Additionally, some features like wallet_path and wallet_password in the open_connection() function are not utilized, and exceptions like UTL_SMTP.TRANSIENT_ERROR and UTL_SMTP.PERMANENT_ERROR are not currently supported. Some SMTP routines, such as AUTH, CLOSE_CONNECTION, COMMAND, COMMAND_REPLIES, DATA, HELP, NOOP, RSET, STARTTLS, and VRFY, are not yet available in this extension. (Currently available for our Customers only).
- bq2pg
- BigQuery to PostgreSQL data migration tool (Currently available for our Customers only). This tool is useful to export data from BigQuery tables into PostgreSQL. It can also create the table in PostgreSQL based on the BigQuery table description. The authentication works with a “Service Account” Json private key. This tool is faster than exporting data using the bigquery_fdw and also does not use an array of jsonb for an array of struct. Data type jsonb already supports an array of json documents so no need to use a jsonb array, at least this is the implementation choice that was done.
New released of Ora2Pg and their features
Ora2Pg is the widely known migration tool supporting Oracle to PostgreSQL and MySQL to PostgreSQL database migrations for the past 23 years. This is one of the oldest migration tools authored by the CTO of HexaCluster. The team at HexaCluster since then has been contributing to this tool and been the major reason for its widespread adoption including major Cloud vendors like Google Cloud and Microsoft Azure Cloud.
The team at HexaCluster has expanded the Ora2Pg capabilities to support SQL Server to PostgreSQL migrations in the year 2023. In 2023, the users witnessed 2 major releases of Ora2Pg with the features as following –
- Ora2Pg 24.1
- Replace `set feedback off` by `\set QUIET on` and `set pagesize 0` with `\pset pager off`.
- Always add package name into search_path of packages functions.
- Allow to specify a password file to set PG_PWD for the PostgreSQL database connection. If the specified file exists on the system, Ora2Pg will read the first line to get the password at each call of `send_to_pgdb()`. It can be useful in some situations where the password changes during data migration.
- Added option `–dump_as_json` for assessment report.
- Ora2Pg 24.0
- Add SQL Server migration to Ora2Pg. Most of the SQL Server objects are supported as well as data export. Translation of the TSQL stored procedures to plpgsql is complicated because of the lack of a statement separator in TSQL but as usual Ora2Pg is doing its best to do as much work as possible. Migration assessment is also possible with SQL Server databases. There are some dedicated configuration directives added to ora2pg.conf.
- Enable the use of ALLOW/EXCLUDE directive with SHOW_* reports and throw a fatal error if global filters in ALLOW/EXCLUDE are set.
- Add replacement of `DBMS_LOCK.SLEEP` with pg_sleep.
- Split estimate cost details per function/procedure/and package function.
- Add cmin, cmax, ctid to the reserved keywords list.
- Add cost for presence of `ADD CONSTRAINT` in PLSQL code. It needs constraint name stability. Allow `COPY` and `TABLE` export type to use the `NULLIF` construct.
- Add new `SEQUENCE_VALUES` export type to export DDL to set the last values of sequences from current Oracle database last values like the following statements: `ALTER SEQUENCE departments_seq START WITH 290;`
- Add replacement of Oracle variable `: varname` into PG `:’varname’`.
- Add support to MySQL `PARTITION BY KEY()` with a translation to HASH partitioned table using the PK/UK definition of the table or the columns specified in the `KEY()` clause.
- Make `EXPORT_INVALID` configuration directive works with TRIGGER export. Until now disabled triggers were not exported, setting `EXPORT_INVALID` to 1 will force the export of disabled triggers.
- Add support for MySQL generated default value on update. Ora2Pg will translate this syntax into a trigger on the table to force the value of the column on an update event.
- Add translation of ST_GEOMETRY data type to PostGis geometry data type.
- Replace ROWNUM in the target list with a `row_number() over ()` clause.
New released of pgBadger and their features
pgBadger is one of the most used PostgreSQL tools with several thousands of stars in GitHub. This tool is developed by the team at HexaCluster and is maintained by the HexaCluster team. This tool supports log parsing and a smart report generation to simplify the diagnostics and performance tuning similar to Oracle AWR and Statspack reports. Our team at HexaCluster has released multiple versions of pgBadger with certain features.
- pgBadger 12.4
- Maintenance release.
- pgBadger 12.3
- Add option –include-pid to only report events related to a session
pid (%p). Can be used multiple times. - Add option –include-session to only report events related to the
session id (%c). Can be used multiple time. - Add option –dump-raw-csv to only parse the log and dump the information
into CSV format. No further processing is done, no report is generated.
- Add option –include-pid to only report events related to a session
- pgBadger 12.2
- Add support for max, avg, min autovacuum duration.
- Add support for pgbouncer’s average waiting time.
- pgBadger 12.1
- Maintenance release.
New released of pgCluu and their features
pgCluu is a comprehensive monitoring and auditing tool designed and maintained by the team at HexaCluster for PostgreSQL performance analysis. It provides detailed reports encompassing statistics gathered from your PostgreSQL database cluster, offering insights into the entire PostgreSQL Cluster’s health and system resource utilization. This tool has seen multiple releases in this year 2023.
- pgCluu 3.5
- Maintenance release.
- Add –disable-pidstat option to disable the collect of metrics from the pidstat command. This can be useful with old versions of pidstat that do not support the -U option.
- Conference about pgCluu
- The Perl and Raku Conference July 2023 – Toronto
- https://sched.co/1Lhma
New released of pgFormatter and their features
The SQL formatter and beautifier, known as pgFormatter, is equipped to handle keywords from SQL-92, SQL-99, SQL-2003, SQL-2008, SQL-2011, and includes specific PostgreSQL keywords. Developed and maintained by the HexaCluster team, this versatile tool can function both as a console program and as a CGI script. It intelligently adapts to its environment, seamlessly formatting output either as plain text or HTML, depending on the context. In 2023, pgFormatter witnessed a version released, further enhancing its capabilities and usability.
- pgFormatter 5.5
- Maintenance release.
- Prevent newline in function supporting the FROM keyword like substring().
- Add multi-line formatting on TRUNCATE with a table list.
New released of pgtt and their features
The PostgreSQL extension known as “pgtt” is an innovative solution developed and actively maintained by the HexaCluster team. It allows for the creation, management, and utilization of Oracle-style Global Temporary Tables (GTTs) and extends this functionality to other RDBMS as well. The primary goal of this extension is to provide a Global Temporary Table feature, bridging the gap until an in-core implementation becomes available. Its key advantage lies in emulating Oracle’s GTT behavior, enabling users to seamlessly migrate to PostgreSQL without the need to rewrite application code. However, when possible, it’s recommended to adapt the code to utilize standard PostgreSQL temporary tables.
In the year 2023, this tool saw a version released, further refining its capabilities and features. This version of the GTT extension employs a regular unlogged table as a “template” table and utilizes an internal rerouting mechanism to create and manage temporary tables, offering an efficient solution for diverse use cases. Additional details on its inner workings can be found in the chapter “How the extension really works.” An earlier implementation of this extension utilizing Row Security Level is also available for reference.
- pgtt 2.10, 3.0 and 3.1
- Maintenance releases.
New released of pg_dumpbinary and their features
pg_dumpbinary, a tool created and actively maintained by the HexaCluster team, serves as a valuable utility for PostgreSQL database management. It specializes in dumping PostgreSQL databases while preserving data in a binary format. This unique functionality proves particularly beneficial in various scenarios, such as when exporting bytea data that exceeds 1GB, handling custom types that store internal ‘\0’ characters within bytea, or any other situation where binary format preservation is critical to prevent data loss. In such cases, pg_dumpbinary is the ideal solution for creating database dumps. However, for standard use cases,
PostgreSQL users should continue to rely on the native pg_dump and pg_restore commands distributed with PostgreSQL. In the year 2023, pg_dumpbinary underwent multiple version releases, reflecting its ongoing enhancement and adaptation to user needs.
- pg_dumpbinary 2.13
- Maintenance release.
- Maintenance release.
- pg_dumpbinary 2.12
- Add -s, –snapshot-file option to be able to change the path to the snapshot information file used by multi-process. Default: /tmp/snapshot_info
- pg_dumpbinary 2.11
- Maintenance release.
- pg_dumpbinary 2.10
- Adds a new option -C, –compress-level 0-9 speed of the gzip compression using the specified digit, between 1 and 9, default to 6. Setting it to 0 disables the compression.
- pg_dumpbinary 2.9
- Maintenance release.
Patches submitted to PostgreSQL
- psql: Add some completion support for CREATE TABLE .. AS
- “AS” is added as a suggested keyword for CREATE TABLE for a few query patterns, including the case where a list of columns is given in parenthesis.
- Committed, 816f10564a8671918e170a547625584d10587cf4
- Allow pg_dump to include/exclude child tables automatically.
- Adds new pg_dump switches to include or exclude partition tables with partitioned tables, –table-and-children, –exclude-table-and-children and –exclude-table-data-and-children
- Committed, a563c24c9574b74f4883c004c89275bba03c3c26
Patches submitted to Orafce
Orafce is a powerful and versatile extension actively used and patched by the team at HexaCluster. This extension is designed to bring enhanced compatibility between PostgreSQL and Oracle databases. It provides a range of Oracle-compatible functions, syntax, and features, making it easier for users who are migrating from Oracle to PostgreSQL to adapt their existing applications and SQL code to the PostgreSQL environment. Orafce aims to reduce the effort required to migrate applications from Oracle to PostgreSQL by bridging the gap between the two database systems, allowing users to maintain a familiar Oracle-like experience within PostgreSQL.
Some of the key features offered by Orafce include support for Oracle-style data types, functions, and operators, as well as the implementation of Oracle’s PL/SQL procedural language, enabling users to port their Oracle stored procedures, triggers, and functions to PostgreSQL. Additionally, Orafce provides compatibility for Oracle-specific syntax and behaviors, ensuring that SQL queries and statements written for Oracle can run seamlessly on PostgreSQL with minimal modifications. This extension is a valuable tool for organizations looking to leverage the advantages of PostgreSQL while preserving their existing Oracle investments and minimizing the effort required for migration. Following is a patch submitted to Orafce in 2023 by the team at HexaCluter.
- Fix REGEXP_COUNT and REGEXP_SUBSTR functions
- Fix REGEXP_COUNT and REGEXP_SUBSTR function to have the same behavior as Oracle with newline characters and the use of dot in the pattern.
- Committed, 6aefb121272be862016e2390002f59ef2af24895
Conclusion
PostgreSQL is often seen as a migration target for customers using Oracle, SQL Server and other commercial databases. This is because of the capabilities of PostgreSQL as a Relational, Unstructured and also a distributed database. The adoption of PostgreSQL is rapidly increasing since the past 10 years. PostgreSQL has been awarded as the DBMS of the Year several times in the past few years including 2023 by DBEngine rankings who aggregate user behavior from various user interactions across the Web.
The team at HexaCluster is proud that we are one of the strongest reasons for the increasing migrations from Oracle to PostgreSQL, through our free and Open Source contributions. Our migration expertise has helped thousands of Customers in migrating their complex Oracle databases to PostgreSQL and eliminating the license costs with minimal efforts. Contact HexaCluster today to get your free detailed assessment for Oracle to PostgreSQL migration. You may fill the following form and one of our team members will be in touch with you.
Subscribe to our Newsletters and stay tuned for more interesting topics.
[…] If interested, take a look at our article on PostgreSQL Summary and Achievements in 2023. […]