E.3. Postgres Pro Enterprise 16.1.1 #
Release Date: 2023-12-25
E.3.1. Overview #
This release is based on PostgreSQL 16.1 and includes all the new features introduced in PostgreSQL 16, as well as bug fixes implemented in PostgreSQL 16.1 update. For the detailed description, see PostgreSQL 16, and PostgreSQL 16.1 Release Notes.
For the list of extension modules and utilities specific to Postgres Pro Enterprise, as well as the main user-visible core changes over vanilla PostgreSQL, see Section 2. As compared to Postgres Pro Enterprise 15.5.1, the following differences are worth mentioning:
Implemented the built-in high availability that is achieved by deploying the biha cluster with physical replication, built-in failover, automatic node failure detection, response, and subsequent cluster reconfiguration. Such cluster is configured with one dedicated leader node and several follower nodes, which can be both synchronous and asynchronous, and is managed with the bihactl utility. The new functionality enables protection against server failures and data storage system failures and does not require any additional external cluster software. The cluster variant with two nodes and one referee node will be supported in future releases. SSL/TLS of the
bihacontrol channel is used experimentally at the moment.Added the
vaultschema that allows protecting sensitive data against unauthorized access of malicious users by designating a separate role called security officer who manages access to the schema and its objects.Added predefined roles:
pg_create_tablespace, which allows executing theCREATE TABLESPACEcommand without superuser rights.pg_manage_profiles, which allows executing theCREATE PROFILE,ALTER PROFILE, andDROP PROFILEcommands without superuser rights.
Added system views
pg_stats_vacuum_tables,pg_stats_vacuum_indexesandpg_stats_vacuum_database, which show statistics about vacuuming tables, indexes and databases, respectively. These statistics were previously available through pgpro_stats.Upgraded aqo to version 2.0, which provides the following major changes and enhancements:
The configuration is streamlined. Now aqo behavior mainly depends on three configuration parameters:
aqo.enable,aqo.modeandaqo.advanced, which respectively define aqo state, operation mode and whether statistics is isolated per query.The default values of configuration parameters set the recommended basic mode, where statistics is collected for plan nodes, and the collected machine learning data is used to correct the cardinality estimation error for all queries whose plan might contain a certain plan node.
aqo can now work with multiple databases independently.
Executing the
DROP/CREATE EXTENSIONcommand is now sufficient to instantly disable/enable aqo at the database level.Memory consumption is reduced.
Upgraded pg_probackup to version 2.7.0 Enterprise, which provides new features, optimizations and bug fixes. Notable changes are as follows:
Merging of incremental backups is made suitable for use with S3 (Simple Storage Service) by replacing renamed folders with symlinks.
The
--show-symlinksoption is added to theshowcommand to display the links between the original full backups and the resulting merged backups.Merging of backups now occurs without creating temporary local files.
Memory usage has been optimized for restoring long sequences of incremental backups of large databases. As a result, the restoration of a database with several thousand tables and about 100 increments consumes up to three times less memory.
It is now possible to specify the values of
latestandcurrentfor the--recovery-target-timelineoption. Also, the operation of the--recovery-targetoption with the value oflatesthas been fixed.
Upgraded pg_hint_plan to version 1.6.0.
Added the pgpro_bfile extension providing a composite type
bfilethat implements an Oracle-like technique to access an external file or S3 (Simple Storage Service).Added the pgpro_rp extension that provides resource prioritization by assigning a resource prioritization plan to a particular session, which can slow it down based on the amount of CPU, I/O read, and I/O write resources this session consumes as compared to other sessions.
Added the sr_plan.auto_tracking configuration parameter that enables sr_plan to normalize and register queries executed using the
EXPLAINcommand automatically.Added the utl_http extension that allows accessing data on the Internet over the HTTP protocol (HTTP/1.0 and HTTP/1.1) by invoking HTTP callouts from SQL and PL/pgSQL.
Added the utl_smtp extension designed for sending emails over SMTP from PL/pgSQL.
Added the utl_mail extension designed for managing emails, which includes commonly used email features, such as attachments, CC, and BCC.
Note
In this release, the subscription for which streaming = parallel is specified ignores this setting and does not use parallel apply workers.
Warning
When using the new Postgres Pro Enterprise 16.1.1 features on outdated operating systems, such as ROSA COBALT 7.9, Rosa Enterprise Linux Server 7.3, AlterOS 7 or SLES 12, anticipate issues as these OS include outdated versions of OpenSSL. So consider upgrading to a newer OS version.
E.3.2. Migration to Version 16 #
You can migrate to Postgres Pro Enterprise 16 from the same or a previous version of PostgreSQL (that is supported by the upgrade method chosen) or Postgres Pro Standard/Postgres Pro Standard Certified and from a previous version of Postgres Pro Enterprise/Postgres Pro Enterprise Certified. The same holds for migration to Postgres Pro Enterprise Certified 16. See Section 18.6 for the methods to upgrade your database cluster. Consult the Postgres Pro Enterprise support team if you experience issues during migration. Backward migration is not supported. Note that migration from Postgres Pro Enterprise to Postgres Pro Enterprise Certified of the same major version (or vice versa) is an update between Postgres Pro compatible versions (see Section 18.6 for more details).
To migrate from PostgreSQL, Postgres Pro Standard, or Postgres Pro Enterprise release based on a previous PostgreSQL major version, make sure to install its latest available minor version and then perform a dump/restore using pg_dumpall or use the pg_upgrade utility.
If you choose to run pg_upgrade, make sure to initialize the new database cluster with compatible parameters. In particular, pay attention to the checksum settings in the cluster you are migrating from. If pg_upgrade creates any SQL files in its current directory, run these files to complete the upgrade.
When migrating from PostgreSQL or Postgres Pro Standard, make sure to pay special attention to implementation specifics of 64-bit transaction IDs. If you have used explicit casts to 32-bit integers when handling transaction IDs, you have to replace them with casts to bigint since 64-bit transaction IDs are of the bigint type.
Note
To avoid conflicts, do not use the postgrespro-ent-16 package to install the new Postgres Pro binaries. Use the individual packages instead. In this case, server autostart needs to be enabled manually, if required. For details on the available packages and installation instructions, see Chapter 17.
For other upgrade requirements imposed by vanilla PostgreSQL, see Section E.6.