The team at HexaCluster is back with its list of contributions to PostgreSQL in 2024. Our Technical team, led by our CTO, Gilles Darold, continues to drive advancements that facilitate migrations to PostgreSQL. Our open-source database migration tools, such as Ora2Pg, and various Oracle compatibility extensions for PostgreSQL, have simplified countless migrations. Major cloud vendors like Google Cloud, Microsoft Azure, and AWS endorse and adopt our tools. Additionally, almost all the vendors offering Database Migration services rely on Ora2Pg for migrating their customers. In this article, we will outline the notable contributions made by our team in 2024.
New PostgreSQL Extensions / Tools Developed by HexaCluster
-
pgdsat: a security assessment tool that checks around 70 PostgreSQL security controls of your PostgreSQL clusters including all recommendations from the CIS compliance benchmark but not only. This tool is a single command that must be run on the PostgreSQL server to collect all necessaries system and PostgreSQL information to compute a security assessment report. A report consists of a summary of all test status and a second part with all detailed information. See sample report.
This PostgreSQL Security Assessment Tool allows assessments to be carried out in an automated manner to verify the security policies established inside the company. It also gives an understanding of the security issues that your cluster can be faced with.
Although the default text output format can be read directly from a terminal the use of the HTML output format is recommended for better reading as you can see in the sample report above.GitHub Repository: https://github.com/HexaCluster/pgdsat
-
pg_dbms_metadata: PostgreSQL extension to extract DDL of database objects in a way compatible to Oracle DBMS_METADATA package. This extension serves a dual purpose—not only does it provide compatibility with the Oracle DBMS_METADATA package, but it also establishes a systematic approach to programmatically retrieve DDL for objects. You now have the flexibility to generate DDL for an object either from a plain SQL query or from PL/pgSQL code. This also enables the extraction of DDL using any client that can execute plain SQL queries. These features distinguish it from standard methods like pg_dump.
GitHub Repository: https://github.com/HexaCluster/pg_dbms_metadata
-
dummy_catalog: Program used to dump the schema with the statistics of a database from a PostgreSQL server and to restore them onto a new server. The purpose is to be able, when executing an EXPLAIN command, to obtain the same explain plan as on the source server without the data.
GitHub Repository: https://github.com/HexaCluster/dummy_catalog
Major Updates to Existing Tools
Ora2Pg
-
Support more Oracle to PostgrSQL exception mapping.
-
Allow overriding of PGSUPPORTS* settings as they are set in the configuration file.
-
Add multiple report format output at once. Thanks to Jean-Christophe Arnu for the patch.
-
Add parsing of ALTER statement from file for the QUERY action.
-
Add support for oracle_fdw COPY using CSV format
-
Add parallel export of each partition. Until now all partitions of a partitioned table were exported serially in a single process.
-
Allow online data migration to continue if the destination table does not exist when ON_ERROR_STOP is disabled. Thanks to chetan2211 for the feature request.
-
Tables data export is now done using the current SCN to have the same snapshot of data between multiprocess -export.
-
Add replacement of USERENV call with MODULE by current_setting with application_name.
-
Add support for oracle_fdw in combination with psql "\copy" and server-side COPY using BINARY stream for data movement.
-
Add –no_clean_comment option to not remove comments in source file before parsing. With huge DDL files with comments, it could take a very long time.
-
Add -O | –options used to override any configuration parameter; it can be used multiple time.
-
Add option –no_start_scn to force Ora2Pg to not use a SCN to export data unless –snc is used. By default, the current SCN is used to export data from all tables.
-
Add ORACLE_FDW_COPY_MODE configuration directive to control the behavior of COPY with oracle_fdw. It is possible to use two different modes: 1) "local", which uses psql on the host running Ora2Pg for the "TO" binary stream; 2) "server", which uses PostgreSQL server-side COPY for the "TO" binary stream. Both modes use psql for the "FROM STDIN BINARY".
-
Add ORACLE_FDW_COPY_FORMAT configuration directive. When using Ora2Pg COPY with oracle_fdw it is possible to use either BINARY or CSV data format. BINARY provides better performance, however, requires exact data type matching between the FDW and destination table. CSV provides greater flexibility with respect to data type matching.
-
Bug fixes.
pgBadger
-
Version Released: 13.0
-
New options to be able to redefine inbound of query and session histogram.
-
Add support of auto_explain plan for csv and json log formats.
-
Bug fixes.
pgCluu
-
Version Released: 13.0
-
Add report of devices /sys/block/queue information (scheduler, rotational, rq_affinity and add_random) to the System report.
-
Add report of PGDATA files to be able to detect symlinks and unwanted files in this directory.
-
Add reports for Wait Event when the pg_wait_sampling extension is used. The first report shows the distribution by Wait Event Type and the second report shows the distribution by Wait Events.
-
Add report for Global sub-transactions counters if extension pg_subxact_counters is installed in the connection database. Sub-transactions can lead to performance issue, report Counters to monitor the sub-transactions (generation rate, overflow, state).
-
Add collect of /sys/kernel/debug/sched/migration_cost_ns with kernel >= 5.19 collect start-end metrics only twice (start and end).
-
Add option –no-pg_stats-dump to pgcluu_collectd to avoid collecting such statistics for performances reasons.
-
Make SIGINT interrupts gracefully pgcluu_collectd like SIGHUP.
-
Add dump of the pg_stats view to pgcluu_collectd. The point is for the DBA to study statistics.
-
Bug fixes.
pg_dumpbinary
-
Version Released: 2.14 to v2.18
-
Add new option –with-child, when -t or -T option is used, include or exclude child and partition tables. pg_dump will be used instead with options –table-and-children or –exclude-table-and-children. Require PostgreSQL >= 16.
-
Add -V, –verbose option that lists tables included in the dump.
-
Add new option-A, –attach SNAPSHOT to be able to attach pg_dumpbinary to an existing snapshot instead of creating a dedicated one.
-
Add -w, –where command line option to pg_dumpbinary to apply a filter to the data dumped. The filter will be used in a WHERE clause for data export, this clause will be applied to all tables dumped.
-
Add option -C, –create to create the database before restoring it. Like with pg_restore you must provide a database to connect using the -d option.
-
Add –dump-create option to print to stdout the CREATE and ALTER DATABASE DDL statement.
-
Bug fixes.
Enhancements to Existing PostgreSQL Extensions
Credcheck: v2.4 to v2.8 and v3.0
-
Add authentication delay feature to be able to add a pause on authentication failure.
-
Add compatibility with pgBackRest.
-
Add compatibility with PG v17.
-
Show actual parameters settings in error messages.
-
Add new configuration variable credcheck.whitelist_auth_failure to exclude some users from being banned. Useful with application login to avoid them to be banned when password change.
orafce
-
Add support to nvl(double precision, integer) and to_date(integer, text).
-
Force to_date(text,text) to return null with empty string.
-
Raise an error when Oracle’s to_date() bugs are reached.
-
Fix to_date(text, text) function with the ‘J’ (Julian day) formatting.
uri
- Port to PostgreSQL v17
pgtt
-
Add port to Windows operating system.
-
Allow preloading pgtt in session_preload_libraries.
-
Add Julien Rouhaud as author and maintainer.
-
Bug fixes.
pgtt-rsl
-
Allow to create the table into any schema for DB2 compatibility.
-
Add support to CREATE GLOBAL TEMPORARY TABLE, CREATE GLOBAL TEMPORARY TABLE … AS SELECT and DROP TABLE.
-
Bug fixes.
Conclusion
2024 has been another milestone year for HexaCluster in contributing to the PostgreSQL community. Our commitment to developing tools, enhancing compatibility, and engaging with the community continues to drive the adoption of PostgreSQL as a preferred database platform. We invite feedback, collaboration, and engagement from the community as we move forward.
Seeking specialized PostgreSQL support or Database Migrations assistance ? Get experts advice for effective data management, query tuning, database migrations and database optimization. Click here for personalized expertise to elevate your database performance and meet your unique business needs.
Subscribe to our Newsletters and Stay tuned for more interesting topics.
