E.22. Postgres Pro Standard 14.1.1
Release Date: 2021-12-01
E.22.1. Overview
This release is based onPostgreSQL 14.1 and includes all the new features introduced in PostgreSQL 14, as well as bug fixes implemented in PostgreSQL 14.1. For their detailed description, seePostgreSQL 14 Release Notes andPostgreSQL 14.1 Release Notes, respectively. Other major changes and enhancements are as follows:
Removed thesr_plan extension.
Added several enhanced security mechanisms toPostgres Pro Standard:
pg_proaudit extension that enables detailed logging of various security events.
Advanced authentication policies that provide effective password management and access control. (SeeCREATE PROFILE andALTER ROLE).
Built-inintegrity checks for executable files, configuration files, and system tables. (Certified edition only.)
Built-indata security mechanisms that enable sanitizing an object by filling it with zeroes before deletion. Zeroing can be done before purging files in external memory and removing outdated row versions (page vacuum), freeing RAM, and deleting or overwriting WAL files. (Certified edition only.)
Upgradedmamonsu to version 3.1.0, which supportsPostgres Pro 14 and adds a new option for
bootstrap. With this option, ifdbnameis not explicitly specified, its value will be automatically set to the database name defined in the[postgres]section of themamonsu configuration file.Upgradedpgpro_pwr. Major enhancements are as follows:
Added the“Load distribution” section to the report, which shows load distribution of different resources (for example, total time or shared blocks written) for heavily loaded objects, such as databases, applications, hosts, or users, in graphics, as stacked bar charts.
Added the“Session statistics by database” and“WAL statistics” report tables, based on new views and fields made available inPostgres Pro 14.
Removed the
WAITLSNcommand.
For the list of extension modules and utilities specific toPostgres Pro Standard, as well as the main user-visible core changes as compared to vanillaPostgreSQL, seeSection 2.
E.22.2. Migration to Version 14
You can migrate toPostgres Pro Standard 14 from the same or a previous version ofPostgreSQL (that is supported by the upgrade method chosen) and from a previous version ofPostgres Pro Standard orPostgres Pro Standard Certified. The same holds for migration toPostgres Pro Standard Certified 14. SeeSection 17.6 for the methods to upgrade your database cluster. Consult the Postgres Pro Standard support team if you experience issues during migration. Backward migration is not supported. Note that migration fromPostgres Pro Standard toPostgres Pro Standard Certified of the same major version (or vice versa) is an update betweenPostgres Pro compatible versions (seeSection 17.6 for more details).
To migrate fromPostgreSQL or aPostgres Pro Standard release based on a previousPostgreSQL major version, make sure to install its latest available minor version and then perform a dump/restore usingpg_dumpall or use thepg_upgrade utility:
If you choose to runpg_upgrade, make sure to initialize the new database cluster with compatible parameters. In particular, pay attention to the provider of the default collation and thechecksum settings in the cluster you are migrating from. Ifpg_upgrade creates any SQL files in its current directory, run these files to complete the upgrade.
If you are opting for a dump/restore, do not forget to use the
--add-collprovideroption to correctly choose the provider for the default collation of the migrated database.
To find out the default collation and its provider in the original cluster, see thedatcollate value for thetemplate0 database in thepg_database catalog. If you are upgrading from a version where provider of the default collation is not specified, uselibc provider if upgrading from vanillaPostgreSQL, and omit the provider if upgrading from earlier versions ofPostgres Pro.
Besides, note the following collation-related upgrade specifics described below.
On Windows,Postgres Pro Standard installations could contain databases with default collations provided by ICU, where the name of the database default collation used a syntactically correct BCP 47 language tag format, but had a wrong language code or other parameters, which invalidated the database default collation name for ICU.
If this issue affects thetemplate0 database, you will get the following error message when trying to initialize the cluster with the same collation:failed to get the canonical name for collation locale. In this case, you can only use dump/restore for upgrade, specifying a valid locale for the selected collation provider.
If this issue affects other databases, you will get the same error message whenPostgres Pro tries to create these databases with invalid collation in the new cluster. In this case, you can try the following:
Make a dump of the database usingpg_dump; it is required to use
--createand--format=plainoptions.Change the provider for the default collation of the database in the dump file from
'@icu'to'@libc'.Inpsql, restore the modified dump to complete the upgrade. This operation may fail if any constraints depending on the database collations are violated. In this case, you can try resolving the issues manually.
When upgrading the installation from version 10 or lower, a dump/restore is recommended. In this case, you may have to resolve constraint violations manually. If this option is infeasible, you can still usepg_upgrade, but consult the Postgres Pro Standard support team since the integrity of indexes and constraints might be violated in some cases.
Note
To avoid conflicts on Linux systems, do not use thepostgrespro-std-14 package to install the newPostgres 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, seeChapter 16.
For upgrade requirements imposed by vanillaPostgreSQL, seeSection E.41.