Release Date: 2021-12-01
This release is based on PostgreSQL 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, see PostgreSQL 14 Release Notes and PostgreSQL 14.1 Release Notes, respectively. Other major changes and enhancements are as follows:
Removed the sr_plan extension.
Added several enhanced security mechanisms to Postgres Pro Standard:
pg_proaudit extension that enables detailed logging of various security events.
Advanced authentication policies that provide effective password management and access control. (See CREATE PROFILE and ALTER ROLE).
Built-in integrity checks for executable files, configuration files, and system tables. (Certified edition only.)
Built-in data 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.)
Upgraded mamonsu to version 3.1.0, which supports
Postgres Pro 14 and adds a new option for bootstrap. With this option,
if dbname is not explicitly specified, its value will be automatically set to the database name
defined in the [postgres] section of the mamonsu configuration file.
Upgraded pgpro_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 in Postgres Pro 14.
Removed the WAITLSN command.
For the list of extension modules and utilities specific to Postgres Pro Standard, as well as the main user-visible core changes as compared to vanilla PostgreSQL, see Section 2.
To migrate from PostgreSQL or a Postgres Pro Standard 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 provider of the default collation and 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.
If you are opting for a dump/restore, do not forget to use the
--add-collprovider option 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 the datcollate value
for the template0 database in
the pg_database catalog.
If you are upgrading from a version where provider of the default collation is not
specified, use libc provider if upgrading from vanilla
PostgreSQL, and omit the provider if upgrading
from earlier versions of Postgres 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 the template0 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 when Postgres 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 using pg_dump;
it is required to use --create and --format=plain options.
Change the provider for the default collation of the database in the dump file
from '@icu' to '@libc'.
In psql, 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.
In some corner cases, using dump/restore could lead to invalid constraints in the restored databases, so you should use pg_upgrade. For example:
If the installation of Postgres Pro Standard
9.6 or lower contained any indexes or constraints depending on
collations other than the default collation of the database,
C, or POSIX in databases
with multibyte encodings, indexes and constraints in such databases
could become inconsistent when these databases are migrated
to Postgres Pro 10 or higher.
On Windows, this situation can also happen if the
database with a multibyte encoding contained any indexes or
constraints depending on the default collation with a verbose name, such as
"Russian_Russia[.
or encoding]""English_United
States[..
encoding]"
For upgrades from Postgres Pro Standard 10, if the cluster has no information about the ICU library version, the ICU collation versions are checked to ensure that indexes and constraints remain valid after the upgrade. However, for clusters that contain databases with default ICU collations but have no information about the ICU library version and/or its collation versions, it is impossible to check that the current version of Postgres Pro uses the same version of the ICU library.
On Windows, in Postgres Pro Standard 10
clusters with default collations provided by ICU,
the ICU collation locale may not match
the corresponding libc collation locale.
If you use pg_upgrade, it declares such indexes and
constraints invalid and creates reindex_text_indexes.sql
and validate_text_constraints.sql, respectively.
You have to run these files to complete the upgrade.
To avoid conflicts on Linux systems, do not use the postgrespro-std-14
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, see Chapter 16.
If you used mchar, to ensure acceleration
of the LIKE operator after upgrading from previous versions of
Postgres Pro, run:
ALTER EXTENSION mchar UPDATE;
For upgrade requirements imposed by vanilla PostgreSQL, see Section E.3.