E.14. Postgres Pro Standard 10.1.1

Release Date: 2017-12-14

E.14.1. Overview

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

  • Unified the structure of binary installation packages across all Linux-based distributions. The new package structure differs from that of vanilla PostgreSQL, but offers the following benefits:

    • You do not need to worry about installation specifics on different Linux distributions when migrating between different supported Linux systems. Postgres Pro Standard is now installed to the /opt/pgpro/std-10 directory, while the default database is created in the /var/lib/pgpro/std-10/data directory.

    • You can now fully control the default database creation on all Linux distributions. If you install the postgrespro-std-10 package, it deploys all the Postgres Pro packages required for your system, creates the default database, and configures the server in a fully automated way. If you install individual packages, you need to configure Postgres Pro yourself. In this case, you have to manually initialize the database cluster and start the server, as well as configure automatic server startup if required.

    • You can install Postgres Pro Standard 10 side by side with other PostgreSQL-based products for seamless migration or parallel work. If you are using individual packages, your current system configuration will be preserved, so you have to manually configure Postgres Pro, as explained in Section 16.1.3. Do not install the postgrespro-std-10 package on the same system with other PostgreSQL-based products to avoid conflicts.

    Note that postgrespro-common and postgrespro-client-common packages are no longer available on Debian-based systems. For details, see Section 16.1.

  • You can specify icu or libc as the default collation provider when initializing a database cluster or creating a database. By default, the icu provider is used for all locales except C and POSIX. For details, see Section 22.2.2.

  • In B-tree index, duplicate keys are now stored only once, with item pointers located in posting lists. It can significantly reduce the index size for workloads where the same key appears multiple times.

Core patches from Postgres Pro Standard 9.6 have been applied:

  • Covering indices patches (For details, see the INCLUDE clause description in CREATE INDEX.)

  • Fixes to win32 build system

  • Added pgpro_version SQL function and appropriate defines into pg_config.h

  • Integrated PTRACK patch

  • For Windows version of psql, enabled support for command-line editing using libeditline

The following modules and utilities have been ported from Postgres Pro Standard 9.6:

E.14.2. Migration to Version 10

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 the same checksum setting as the database cluster you are migrating from.

When migrating to Postgres Pro Standard 10, do not use the postgrespro-std-10 package to avoid conflicts. Use the individual packages instead. In this case, server autostart needs to be enabled manually, if required.

Starting from Postgres Pro Standard 10, you can specify the default collation provider when initializing the database cluster or creating the database, as explained in Section 22.2.2. You must take it into account when upgrading to this release to avoid breaking indexes and constraints.

Important

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 10 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 using pg_upgrade, make sure that initdb sets the correct default collation provider for the new database cluster:

  • When migrating from vanilla PostgreSQL, specify libc as the default collation provider.

  • When upgrading from Postgres Pro Standard, omit the default collation provider option to select the required collation provider automatically. In this case, libc provider will be used for databases with C and POSIX locales, as well as for all databases with single-byte encodings, while icu provider will be used for all the other cases.

If pg_upgrade creates any SQL files in its current directory, run these files to complete the upgrade.

When you are using pg_dumpall to perform the upgrade, Postgres Pro uses the collation provider specified with the initdb command for the new cluster. In this case, indexes are rebuilt automatically. To avoid issues with collation-dependent constraints, you are recommended to use libc provider when upgrading from vanilla PostgreSQL, and omit the provider when upgrading from a previous version of Postgres Pro Standard, unless you have databases with non-C and non-POSIX collation settings. For such databases, you should do the following:

  • If the new cluster is initialized with locales other than C or POSIX, and the database has an single-byte encoding, set LC_COLLATE to '@libc' for this database.

  • If the new cluster is initialized with C or POSIX locale settings and the database has a multi-byte encoding, set LC_COLLATE to '@icu' for this database.

Note

If the previous Postgres Pro Standard installation contained any indexes or constraints depending on collations other than the default collation of the database, C, or POSIX in databases with multibyte encodings, such databases could contain some data that violated the specified constraints and made indexes inconsistent. 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]". In such cases, you can only use pg_upgrade to upgrade to Postgres Pro Standard 10, as a dump/restore scenario may be impossible. To resolve these issues, pg_upgrade declares such indexes and constraints invalid and creates reindex_text_indexes.sql and validate_text_contraints.sql, respectively.

When building Postgres Pro manually, you must include ICU using the --with-icu option. Otherwise, you cannot upgrade to Postgres Pro Standard 10 from a previous Postgres Pro Standard version.