E.31. Postgres Pro Enterprise 12.2.1
Release Date: 2020-03-20
E.31.1. Overview
This release is based on PostgreSQL 12.2 and includes all the new features introduced in PostgreSQL 12, as well as bug fixes implemented in PostgreSQL 12.1 and 12.2 updates. For their detailed description, see PostgreSQL 12, PostgreSQL 12.1, and PostgreSQL 12.2 Release Notes, respectively.
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 11.7.1, the following differences are worth mentioning:
The
pg_recovery_settingsview is no longer required because therecovery.confsettings have been integrated intopostgresql.confand can now be viewed via the pg_settings system view;primary_conninfo,restore_command, andprimary_slot_nameparameters can still be changed without restarting the server.Reduction of WAL overhead for creation of GiST, GIN, and SP-GiST indexes has been committed to PostgreSQL 12, so Postgres Pro now inherits its modified implementation. The index format has not changed, so all these indexes should remain valid.
The implementation of deduplication for B-tree indexes has been reworked. You can now disable deduplication for a newly created index by providing the
deduplicate_itemsparameter to the CREATE INDEX command. Besides, depending on the operator class and data types in use, some B-tree indexes may not support deduplication anymore, as explained in Section 63.4.2. See Section E.31.2 for upgrade implications.PTRACK has been thoroughly reworked and now has a new API. If you have been taking PTRACK backups using pg_probackup, you will have to upgrade it to version 2.2.6 or higher.
The built-in connection pooler has been significantly improved and is no longer experimental. To learn more about the current usage specifics of the connection pooler, see Section 33.1.
The multimaster extension has been improved:
It is recommended to use
multimasterin a three-node configuration, where one node is a referee. For details on how to set up a cluster with a referee, see Section F.30.3.3.You can now check data consistency between cluster nodes using the
mtm.check_query()function.
Updated CFS to enhance compression functionality:
You can now choose compression algorithm. Out of the box, Postgres Pro supports
zstd(default),zlib, andpglz. Other algorithms may be added on demand.
Extended the pg_stat_activity view with the
wait_state_idfield for monitoring purposes.Added the plan_cache_lru_memsize parameter that can limit the amount of memory used for prepared statements. It is now limited to 8MB by default, while the plan_cache_lru_size parameter is switched off.
Added the pgpro_stats extension to combine tracking execution statistics of SQL statements and calculating wait event statistics.
Upgraded the mchar module to version 2.1.
Upgraded the dump_stat module to version 1.2.
Important
Starting from Postgres Pro 12, using pg_pathman is not recommended. Use vanilla declarative partitioning instead, as described in Section 5.11.
E.31.2. Migration to Version 12
You can migrate to Postgres Pro Enterprise 12 from the same or a previous version of PostgreSQL (that is supported by the upgrade method chosen) or Postgres Pro Standard and from a previous version of Postgres Pro Enterprise. 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.
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. Apart from the requirements listed below, you also have to take into account all upgrade implications imposed by vanilla PostgreSQL, as explained in Section E.54.
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 on Linux systems, do not use the postgrespro-ent-12 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.
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.
Since implementation of deduplication for B-tree indexes has changed, some of the previously compressed indexes may no longer support deduplication, as explained in Section 63.4.2. If you use pg_upgrade, it declares such indexes invalid and creates the reindex_non_equalimage_btree.sql script file. You have to run this file to complete the upgrade.
Besides, note the following collation-related upgrade specifics described below.
On Windows, Postgres Pro 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
--createand--format=plainoptions.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 or call the support team.
In some corner cases, using dump/restore could lead to invalid constraints in the restored databases. For example:
If the installation of Postgres Pro 9.6 or lower contained any indexes or constraints depending on collations other than the default collation of the database,
C, orPOSIXin 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[.orencoding]""English_United States[..encoding]"On Windows, in Postgres Pro 10 clusters with default collations provided by ICU, the ICU collation locale may not match the corresponding
libccollation 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.