Postgres Pro
Downloads
E.32. Postgres Pro Enterprise 11.2.1
Prev UpAppendix E. Release NotesHome Next

E.32. Postgres Pro Enterprise 11.2.1

E.32.1. Overview
E.32.2. Migration to Version 11

Release date: 2019-05-07

E.32.1. Overview

This release is based on Postgres Pro Enterprise 10.7.1 and PostgreSQL 11.2. It includes all the new features introduced in PostgreSQL 11, as well as bug fixes implemented in PostgreSQL 11.1 and 11.2. For their detailed description, see PostgreSQL 11, PostgreSQL 11.1, and PostgreSQL 11.2 Release Notes, respectively. As compared with Postgres Pro Enterprise 10.7.1, this version adds the following new features:

  • Implemented an experimental built-in connection pooler that can limit the number of backends when working with multiple clients, without imposing restrictions on using session configuration parameters, prepared statements, or temporary tables. See Chapter 34 for details.

  • Added the autoprepare mode that allows to implicitly prepare frequently used statements, thus eliminating the cost of their compilation and planning on each subsequent execution. For details, see Section 14.6.

  • Added support for changing configuration of other sessions, which allows to switch on debug messages to trace sessions with unexpected behavior. See Section 9.26.1 for details.

  • Enabled replacement of a constraint index without dropping this constraint using the ALTER CONSTRAINT ... USING INDEX clause of the ALTER TABLE command.

  • Updated Postgres Pro Enterprise default configuration to use declarative partitioning syntax provided by Postgres Pro core instead of the pg_pathman implementation, which is used in Postgres Pro Enterprise 10 by default. You can change this setting using the partition_backend parameter, if required.

  • Improved usability of the multimaster extension:

    • Now multimaster uses only native Postgres Pro connections between nodes, without setting up an extra communication channel on a different TCP port. Hence connection strings do not need the arbiter_port option anymore, and multimaster.arbiter_port configuration parameter has been removed. The multimaster.use_rdma parameter is also deleted since multimaster can now use all connection types supported by Postgres Pro without any additional settings.

    • Cluster configuration is now stored inside the database instead of configuration files. That allows to assign node IDs automatically and avoid manual editing of configuration files on all cluster nodes when adding or dropping a node. Therefore, multimaster.conn_strings and multimaster.node_id have been removed. You can use the mtm.init_cluster() function to update cluster configuration on all nodes.

    • Tables without a primary key are now always replicated by default, so the multimaster.ignore_tables_without_pk configuration parameter has been removed. You can still stop replicating a table using the mtm.make_table_local() function.

    • Automatic deletion of a lagged replication slot is no longer happening as it caused more problems than it solved. To drop replication slots for a node, you have to explicitly call the mtm.drop_node() function.

    • The mtm.major_node configuration parameter has been removed as redundant since the same behavior can be achieved in the referee mode.

    • The multimaster.max_nodes is no longer required to change the cluster configuration. The mtm.add_node() does all the necessary work.

    • Several ways of dropping/adding nodes have been unified into a single mechanism that is now accessible through functions mtm.add_node()/mtm.drop_node().

    • Monitoring views have been redesigned, and cluster information is now accessible via mtm.status() and mtm.nodes() functions. The mtm.collect_cluster_info(), mtm.get_cluster_state(), and mtm.get_nodes_state() functions have been deleted.

    • The mtm.copy_table and mtm.broadcast_table functions have been removed as redundant.

  • 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 17. To learn more about JIT, see Chapter 32.

  • Added support for SUSE Linux Enterprise Server 15 and AlterOS 7.5.

  • Added PL/Python packages for Python 3 on SUSE Linux Enterprise Server 12.1, CentOS 6/7, Red Hat Enterprise Linux 6/7, and Oracle Linux 6/7.

  • 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.32.2.

  • Accelerated index creation and minimized undesired eviction of relation pages from shared buffers while an index is being built.

  • 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.

  • Fixed an issue in index search that caused a slowdown when using complex jsquery values.

  • Improved selectivity estimation for indexes on boolean columns.

  • Improved stability of autonomous transactions.

  • Added pg_recovery_settings view that displays the current recovery settings stored in the recovery.conf file.

All the Postgres Pro features included into Postgres Pro Enterprise 10.7.1 have been ported. You can get an overview of all the main Postgres Pro enhancements over vanilla PostgreSQL in Section 2.

Note

Covering indexes have been committed to PostgreSQL 11, so Postgres Pro now inherits their modified implementation. If you have been using covering indexes in the previous versions of Postgres Pro Enterprise, you have to rebuild them after the upgrade.

E.32.2. Migration to Version 11

To migrate to Postgres Pro Enterprise 11 from PostgreSQL or Postgres Pro Standard, you should perform a dump/restore using pg_dumpall. pg_upgrade utility can only be used when upgrading from lower versions of Postgres Pro Enterprise. Before the upgrade, remember to install the latest available minor version of your product if it is based on a previous major version of PostgreSQL.

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-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 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.

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:

  1. Make a dump of the database using pg_dump; it is required to use --create and --format=plain options.

  2. Change the provider for the default collation of the database in the dump file from '@icu' to '@libc'.

  3. 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, 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 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 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.

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

Prev Up Next
E.31. Postgres Pro Enterprise 11.4.1 Home E.33. Release 11.22
epub pdf