Postgres Pro Standard : Documentation: 11: E.27. Postgres Pro Standard 11.1.1 : Postgres Professional

E.27. Postgres Pro Standard 11.1.1
Prev	Up	Appendix E. Release Notes	Home	Next

E.27. Postgres Pro Standard 11.1.1

E.27.1. Overview
E.27.2. Migration to Version 11

Release Date: 2018-11-20

E.27.1. Overview

This release is based on PostgreSQL 11.1 and includes all the new features introduced in PostgreSQL 11, as well as bug fixes implemented in PostgreSQL 11.1. For their detailed description, see PostgreSQL 11 Release Notes and PostgreSQL 11.1 Release Notes, respectively. Other major changes and enhancements are as follows:

Added support for Ubuntu 18.10, Astra Linux Smolensk 1.6, and Red OS Murom 7 operating systems.
Modified ICU usage specifics: ICU collation version is no longer stored in the cluster and therefore it is not checked when connecting to a database. For upgrade implications of this change, see Section E.27.2.
Updated planner/optimizer to improve planning speed and accuracy for several query types:
- When estimating the sorting cost, the planner now takes into account comparison complexity, field width, and the number of comparison function calls required for each column, which results in fewer estimation errors.
- For queries with several columns in the GROUP BY clause, it is now possible to choose the order of columns when sorting the data if it does not affect query accuracy. The more unique values the first sorted column contains, the less sorting is required for other columns. If all values in one of the columns are unique, it may be enough to sort data by that column only. The planner can also take advantage of the existing sorting results of index scans or the ORDER BY clause.
- If all tables to be joined have indexes, they are now used to estimate the number of rows in the joined result.
- Self-joins are now eliminated from plans if it does not affect query results.
- Planning for queries with multiple OR operators in the WHERE clause has been improved.
Added support for Just-in-Time (JIT) compilation on Debian and Ubuntu systems, Astra Linux 1.6, ALT Linux 8, and CentOS 7. To use this functionality, you must install a separate JIT package and set up your environment as explained in Chapter 16. To learn more about JIT, see Chapter 31.
Updated pg_probackup module to version 2.0.24. As compared to version 2.0.19 provided in the previous Postgres Pro releases, the following enhancements were introduced:
- If unchanged since the previous backup, files that do not store relation data are now skipped in incremental backups.
- Version number specified in pg_probackup.conf is now preserved when this file gets updated, which allows to correctly identify pg_probackup version used to take the backup.
- Fixed an issue with restoring compressed file blocks and enhanced checks for compression errors. Previously, pg_probackup could not restore file blocks that the zlib algorithm failed to compress during backup. This issue could not be detected by the built-in pg_probackup validation mechanism as it occurs on a lower level that validation itself. You are recommended to re-validate existing backups using this pg_probackup version.
- Improved validation algorithm. Files are now validated block by block by default, not only in case of file-level checksum mismatch. You can disable this behavior using the --skip-block-validation option.
- Allowed restarting a backup merge if the previous attempt has been interrupted.
- Allowed taking backups from standby servers without connecting to the master. Besides, pg_probackup now uses its built-in mechanism to determine the consistency point, so there is no risk that backups from standby contain any inconsistent data.
Updated pg_pathman module to version 1.5.2. As compared to version 1.4.14 provided in the previous Postgres Pro releases, the following enhancements were introduced:
- Added support for multilevel partitioning.
- Eliminated update triggers and added pg_pathman.enable_partitionrouter parameter to enable/disable cross-partition updates.
- Renamed get_pathman_lib_version() to pathman_version().
- Provided other miscellaneous bug fixes and improvements. For a full list of changes, see pg_pathman Wiki.
Enabled NUL byte replacement with the specified ASCII code while loading data using the COPY FROM command. See nul_byte_replacement_on_import parameter description for details.
Improved plantuner stability and fixed a memory leak.
Fixed an issue in index search that caused a slowdown when using complex jsquery values.
Updated pg-setup :
- This script can now initialize the database cluster in a non-default location and store the corresponding PGDATA value in a system configuration file.
- You can now run pg-setup with the set option to modify cluster configuration.
Introduced the following changes for Windows version of Postgres Pro:
- PL/Perl now requires ActivePerl 5.26.
- 32-bit Postgres Pro version is no longer provided.

All the features developed for Postgres Pro Standard 10 releases have also been ported, including core patches and extension modules.

The main user-visible changes introduced by the applied core patches as compared to vanilla PostgreSQL are as follows:

Integrated ICU patch. By default, the icu collation provider is used for all locales except C and POSIX. For details, see Section 22.2.2.
Integrated PTRACK patch.
For Windows version of psql, enabled support for command-line editing using WinEditLine.
Covering indexes have been committed to PostgreSQL 11, so Postgres Pro now inherits their modified implementation. If you have been using covering indexes before, you have to rebuild them after the upgrade.

Extension modules and utilities ported from Postgres Pro Standard 10 are:

dump_stat (See dump_stat)
fasttrun (See Section F.15)
fulleq (See Section F.17)
hunspell-dict (See Hunspell Dictionaries Modules)
jsquery (See Section F.24)
mchar (See Section F.27)
online_analyze (See Section F.28)
pg_pathman (See pg_pathman)
pg_query_state (See Section F.36)
pg_probackup utility (See pg_probackup)
pg_tsparser (See Section F.41)
pg_variables (See Section F.42)
plantuner (See Section F.45)
shared_ispell (See shared_ispell)
sr_plan (See sr_plan)

E.27.2. Migration to Version 11

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 or call the support team.

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[.encoding]" or "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_contraints.sql, respectively. You have to run these files to complete the upgrade.

Note

To avoid conflicts on Linux systems, do not use the postgrespro-std-11 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.

Note

For PostgreSQL 9.5 and 9.5.1, as well as Postgres Pro Standard 9.5.0.1 and 9.5.1.2, you cannot perform an upgrade to Postgres Pro Standard 11 directly. If you are using one of these versions, upgrade your installation to an intermediate version first, such as Postgres Pro Standard 9.5.2.1.

Similarly, if you are running Postgres Pro Standard 9.6.10.x on Windows, you have to upgrade to Postgres Pro Standard 9.6.11.1 or higher.

Important

When upgrading from versions 10.3.2 or lower, you must call the REINDEX command for indexes that used mchar or mvarchar types.

For other upgrade requirements imposed by vanilla PostgreSQL, see Section E.50.

Prev	Up	Next
E.26. Postgres Pro Standard 11.2.1	Home	E.28. Release 11.22

epub pdf