Release Date: 2018-11-20
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)
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[.
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_contraints.sql, respectively.
You have to run these files to complete the upgrade.
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.
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.
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.