## E.9. Postgres Pro Enterprise 10.1.1

Release date: 2018-02-02

### E.9.1. Overview

This release is based on Postgres Pro 10.1.1 and Postgres Pro Enterprise 9.6.6.3. As compared with Postgres Pro Enterprise 9.6.6.3, this version adds the following new features:

- `pg_shardman`. This experimental extension enables sharding, aiming for scalability and fault tolerance with transaction support. It provides best results for OLTP workloads. (See pg_shardman.)

- `in-memory`. This extension enables you to store data in Postgres Pro Enterprise shared memory. (See Section F.24.)

- `vops`. This extension enables vertical data model for Postgres Pro Enterprise. It can speed up OLAP queries with filtering and aggregation by times. (See Section F.70.)

- Declarative syntax for partitioning uses `pg_pathman` as the default partitioning engine. (See Section F.40.2.6.)

- SCRAM-SHA-256 algorithm implementation for password encryption is replaced with the one introduced in vanilla PostgreSQL. (See Section 20.3.2.)

- Failover algorithm for libpq connections is replaced with vanilla PostgreSQL implementation.

- 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 Enterprise is now installed to the `/opt/pgpro/ent-10` directory, while the default database is created in the `/var/lib/pgpro/ent-10/data` directory.

  - You can now fully control the default database creation on all Linux distributions. If you install the `postgrespro-ent-10` package, it deploys all the Postgres Pro Enterprise 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 Enterprise 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 Enterprise 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 Enterprise, as explained in Section 17.1.3. Do not install the `postgrespro-ent-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 17.1.

The following features have been ported from Postgres Pro Enterprise 9.6.6.3:

- `aqo` extension for adaptive query optimization. (See Section F.3.)

- `multimaster` and `referee` extensions. (See Section F.32 and Section F.56, respectively.)

- Extension pg_hint_plan has been added (See Section F.39)

- `pgpro_scheduler` (See pgpro_scheduler.)

- pg_tsparser text search parser. (See Section F.50.)

- `pg_wait_sampling` extension for sampling-based statistics of wait events. (See Section F.53.)

- RUM index, based on GIN. (See Section F.57.)

- Timeout for idle sessions on the server side. (See idle_session_timeout.)

- Page-level compression (CFS). (See Chapter 33.)

- Autonomous transactions support. (See Chapter 16.)

- Support for relocatable tables (See pg_transfer)

- Transaction IDs on 64-bit platforms use 64-bit data type

- Consistent reads on standby servers. (See WAITLSN.)

- pg_repack utility. (See pg_repack.)

- K-nearest neighbor (k-NN) algorithm support for B-tree, GiST, and SP-GiST index types. (See Section 11.13.)

- mamonsu monitoring service, which is implemented as a Zabbix agent. (See mamonsu.)

- Handling transactions with serialization or deadlock errors in pgbench.

All features and enhancements inherited from Postgres Pro 10.1.1 are listed in Postgres Pro 10.1.1 Release Notes.

### E.9.2. Migration to Version 10.1.1

To migrate to Postgres Pro Enterprise 10, you are recommended to perform a dump/restore using pg_dumpall. pg_upgrade utility can only used when upgrading from Postgres Pro Enterprise 9.6 versions.

When migrating to Postgres Pro Enterprise 10, do not use the `postgrespro-ent-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 Enterprise 10, you can specify the default collation provider when initializing the database cluster or creating the database, as explained in Section 23.2.2. You must take it into account when upgrading to this release to avoid breaking indexes and constraints.

### Important

If your current Postgres Pro Enterprise installation uses ICU, do not update ICU library to a newer version. Otherwise, you cannot upgrade to Postgres Pro Enterprise 10.

### Important

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

When you are using pg_dumpall to perform the upgrade, Postgres Pro Enterprise 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, 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 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 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.

If you choose to run pg_upgrade when upgrading from Postgres Pro Enterprise 9.6 versions, make sure to initialize the new database cluster with the same checksum setting as the database cluster you are migrating from. To ensure that that initdb sets the correct default collation provider for the new database cluster, 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.