E.1. Postgres Pro Standard 17.0.1 #

Release Date: 2024-11-02

E.1.1. Overview #

This release is based on PostgreSQL 17 and includes all the new features introduced in PostgreSQL 17. For their detailed description, see PostgreSQL 17 Release Notes. Other major changes and enhancements are as follows:

  • Added the SPLIT PARTITION and MERGE PARTITIONS subcommands to the ALTER TABLE command. The subcommands split a single partition into several partitions and merge several partitions into one, respectively, hence improving partitioned table management.

  • Removed the ability to create constructs using JSON_EXISTS() with the RETURNING clause, which in earlier versions allowed the function to return values of any type. This syntax is not supported by the SQL/JSON standard, which states that the JSON_EXISTS() predicate should only return TRUE, FALSE, or UNKNOWN.

  • Added the pgpro_autopart extension that allows dynamic partition creation by using triggers on the partitioned table view.

  • Upgraded the ODBC driver to version 17.00.0002.

  • Upgraded aqo to version 3.0, which provides the following major changes and enhancements:

    • Introduced the sandbox mode, which allows working in an isolated environment without touching the main aqo knowledge base. This mode can be turned on either on a primary or a standby node by setting aqo.sandbox to on.

    • Introduced a learning mechanism where aqo adjusts the planner's row count estimates with its own predictions. It can be turned on by enabling aqo.delta_rows.

  • Upgraded oracle_fdw to version 2.7.0.

  • Upgraded orafce to version 4.13.5.

  • Upgraded pgpro_controldata to version 17.1.0.

  • Upgraded pg_filedump to version 17.1.

  • Upgraded pg_probackup to version 2.8.4.

  • Upgraded pgpro_pwr to version 4.7, which provides new features and optimizations. Notable changes are as follows:

    • A subsample feature to collect relatively fast changing data.

    • New report tables, specifically regarding session states.

    • Support for new Postgres Pro 17 statistics.

    • A possibility not to reset statistics of the statistics collecting extension during taking a sample.

  • Upgraded pgpro_stats to version 1.8, which supports Postgres Pro 17. Notable changes are as follows:

    • Updated pgpro_stats_statements and pgpro_stats_totals views to include fields added to pg_stat_statements. Related functions were updated accordingly.

    • Streamlined access to views and functions. Specifically, access to the pgpro_stats_archiver, pgpro_stats_vacuum_database, pgpro_stats_vacuum_tables, and pgpro_stats_vacuum_indexes views was granted to all users. Previously, these views required explicitly granting access rights. Access to execution of the pgpro_stats_trace_reset function, which could previously be executed by any user, was restricted to superusers.

  • Upgraded pg_repack to version 1.5.1.

  • Upgraded the PLV8 extension to version 3.2.3.

  • Upgraded tds_fdw to version 2.0.4.

  • Removed the deprecated pg_pathman extension.

For the list of extension modules and utilities specific to Postgres Pro Standard, as well as the main user-visible core changes as compared to vanilla PostgreSQL, see Section 2.

E.1.2. Migration to Version 17 #

You can migrate to Postgres Pro Standard 17 from the same or a previous version of PostgreSQL (that is supported by the upgrade method chosen) and from a previous version of Postgres Pro Standard or Postgres Pro Standard Certified. The same holds for migration to Postgres Pro Standard Certified 17. See Section 17.6 for the methods to upgrade your database cluster. Consult the Postgres Pro Standard support team if you experience issues during migration. Backward migration is not supported. Note that migration from Postgres Pro Standard to Postgres Pro Standard Certified of the same major version (or vice versa) is an update between Postgres Pro compatible versions (see Section 17.6 for more details).

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 are migrating from Postgres Pro Standard version 16 or 15, and your database contains objects using the invalid syntax of JSON_EXISTS() with the RETURNING, delete or replace them with valid alternatives before upgrading.

If you choose to run pg_upgrade, make sure to initialize the new database cluster with compatible parameters. In particular, pay attention to 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.

When upgrading the installation from version 10 or lower, a dump/restore is recommended. In this case, you may have to resolve constraint violations manually. If this option is infeasible, you can still use pg_upgrade, but consult the Postgres Pro Standard support team since the integrity of indexes and constraints might be violated in some cases.

Note

To avoid conflicts, do not use the postgrespro-std-17 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 upgrade requirements imposed by vanilla PostgreSQL, see Section E.2.