DR-000038 - PostgreSQL Upgrade to 16

Overrides/Supersedes 

• FOLIO-2145: Upgrade to PostgreSQL 12ClosedPreview

• MODINVSTOR-791: folio modules in general are not compatible with postgres 13ClosedPreview

RFC 

Provide links to all relevant RFC's

Stakeholders

@mention individual(s) who has vested interest in the DR. This helps us to identify who needs to be aware of the decision

Contributors

@Julian Ladisch @Florian Gleixner 

Approvers

List TC members voted to approve the change

Background/Context

Poppy officially supports PostgreSQL 12. Support of this version ends November 14, 2024.

Once a year the PostgreSQL team releases a new major version of PostgreSQL. Versions 13, 14, 15 and 16 are available. Some institutions have already migrated away from version 12 to 13 or 14 without any problems.

Assumptions

Sysops prefer to avoid a PostgreSQL migration if possible.

If a PostgreSQL migration is needed, sysops prefer the latest version PostgreSQL version.

Constraints

PostgreSQL 16 at Amazon RDS for PostgreSQL will be available by March 2024 (assuming the same timing like PostgreSQL 15 that has been available as Amazon RDS since Februrary 28, 2023).

Multi-tenant installations migrate tenants on different days, for example 4 tenants per weekend. Therefore they require the previous flower release to run under the new PostgreSQL version.

On November 20, 2023 the TC decided that it's to late to bump the officially supported PostgreSQL version for Quesnelia to 15 because the Quesnelia development planning has already been completed.

Rationale

"pg_upgrade supports upgrades from 9.2.X and later to the current major release of PostgreSQL, including snapshot and beta releases." There is no need to incrementally upgrade to 13, and then to 14, and then to 15, and then to 16.

Skipping versions 13, 14, and 15 reduces the sysops' and devops' workload and downtimes.

Migrating PostgreSQL from 12 to 13 for Quesnelia should be avoided when it only lasts four months until the next migration from 13 to 16 is required for Ramson.

There's a gap between the end of PostgreSQL 12 support on November 14, 2024 and end of Quesnelia support around December 2024. Sysops should migrate PostgreSQL from 12 to 16 for their Quesnalia installations by November 14, 2024.

It's likely that breaking changes in 13, 14, 15 or 16 don't affect FOLIO or are very easy to fix. This is the result of the analysis of all published breaking changes, see list below.

The 35 biggest FOLIO modules (out of 60 database accessing modules) have been tested against PostgreSQL 13, 14, 15 and 16 using the module tests (mvn verify). No regression was found. See list below.

Making both PostgreSQL 12 and 16 the officially supported PostgreSQL version for the Quesnelia FOLIO release means that developers can only use PostgreSQL features that run under both versions. Almost always PostgreSQL is backward compatible so that only PostgreSQL 12 is needed for the automated tests for Quesnelia (unit tests, snapshot enviroments, vagrant boxes, rancher environments).

To test PostgreSQL 16 compatibility a single run of the Karate tests and the module tests (DR-000037 - TESTCONTAINERS_POSTGRES_IMAGE) is sufficient. A good time might be the Quesnelia bug fest. Bug fest and reference environments for Quesnelia containue to use PostgreSQL 12, the PostgreSQL 16 compatibility tests run on separate installations (Rancher environment, GitHub Actions).

Institutions are free to use any of the not officially supported PostgreSQL versions 13, 14, and 15 for their Quesnelia installation at their own risk.

Summary for developers:

• For Quesnelia keep PostgreSQL 12 for unit tests. Use only SQL features of PostgreSQL 12, don't use SQL features from the breaking changes list (see below) of PostgreSQL 13, 14, 15 and 16.

• For Ramson use PostgreSQL 16 for unit tests.

Summary for devops:

• For Quesnelia keep reference environments, rancher environments, bug fest environment, Vagrant boxes etc. on PostgreSQL 12.

• For Ramson run reference environments, rancher environments, bug fest environment, Vagrant boxes etc. on PostgreSQL 16.

Summary for test manager:

• For Quesnelia: Quesnelia bug fest runs on PostgreSQL 12. During the bug fest execute additional PostgreSQL 16 tests on separate installations:

  • Run Karate test against a FOLIO Deployment with PostgreSQL 16.

  • Run module tests using GitHub Actions, DR-000037 - TESTCONTAINERS_POSTGRES_IMAGE and PostgreSQL 16.

• For Ramson: No additional tests are needed.

Summary for sysops using only officially supported PostgreSQL versions:

• Keep PostgreSQL 12 while migrating tenants to Quesnelia.

• Before migrating PostgreSQL from 12 to 16 all tenant must have been migrated to Quesnelia.

• PostgreSQL must be migrated from 12 to 16 before migrating a tenant to Ramson and by November 14, 2024, whatever comes first.

• No module or module configuration change is needed for the PostgreSQL migration.

Summary for sysops using not officially supported PostgreSQL versions:

• There are no known issues running Poppy and Quesnelia with PostgreSQL 12, 13, 14, 15, or 16.

• Because it's likely that Ramson modules use PostgreSQL 16 SQL features it's likely that PostgreSQL must be migrated to 16 before migrating a tenant to Ramson.

• Note that the final PostgreSQL 12 release will be on November 14, 2024, no security fixes will be released after this date.

• No module or module configuration change is needed for the PostgreSQL migration.

Decision

PostgreSQL 12 and 16 are the officially supported PostgreSQL server versions for the Quesnelia FOLIO release; PostgreSQL 12 server support ends November 14, 2024. Only PostgreSQL 12 SQL features are allowed for the Quesnelia FOLIO release; they must also work under PostgreSQL 16.

PostgreSQL 16 is the officially supported PostgreSQL version for the Ramson FOLIO release.

Implications

• Pros

  • See "Assumptions" and "Rationale" above

• Cons

  • Developers are restricted to PostgreSQL 12 features for Quesnelia.

Breaking changes analysis

13

https://www.postgresql.org/docs/13/release-13.html#id-1.11.6.17.4

• Change SIMILAR TO ... ESCAPE NULL to return NULL (Tom Lane)

  This new behavior matches the SQL specification. Previously a null ESCAPE value was taken to mean using the default escape string (a backslash character). This also applies to substring(text FROM pattern ESCAPE text). The previous behavior has been retained in old views by keeping the original function unchanged.

  FOLIO doesn't use ESCAPE NULL: https://github.com/search?q=org%3Afolio-org+%22escape+null%22&type=code

• Make json[b]_to_tsvector() fully check the spelling of its string option (Dominik Czarnota)

  FOLIO uses neither json_to_tsvector nor jsonb_to_tsvector: https://github.com/search?q=org%3Afolio-org+json_to_tsvector&type=code , https://github.com/search?q=org%3Afolio-org+jsonb_to_tsvector&type=code

• Change the way non-default effective_io_concurrency values affect concurrency (Thomas Munro)

  Previously, this value was adjusted before setting the number of concurrent requests. The value is now used directly. Conversion of old values to new ones can be done using:

  SELECT round(sum(OLDVALUE / n::float)) AS newvalue FROM generate_series(1, OLDVALUE) s(n);

  This is a database administration setting, FOLIO is not affected.

• Prevent display of auxiliary processes in pg_stat_ssl and pg_stat_gssapi system views (Euler Taveira)

  Queries that join these views to pg_stat_activity and wish to see auxiliary processes will need to use left joins.

  FOLIO joins neither pg_stat_ssl nor pg_stat_gssapi: https://github.com/search?q=org%3Afolio-org+pg_stat_ssl&type=code , https://github.com/search?q=org%3Afolio-org+pg_stat_gssapi&type=code

• Rename various wait events to improve consistency (Fujii Masao, Tom Lane)

  FOLIO doesn't use wait_event, reading it is a database administrator task: https://github.com/search?q=org%3Afolio-org+wait_event&type=code

• Fix ALTER FOREIGN TABLE ... RENAME COLUMN to return a more appropriate command tag (Fujii Masao)

  Previously it returned ALTER TABLE; now it returns ALTER FOREIGN TABLE.

  FOLIO doesn't use ALTER FOREIGN TABLE: https://github.com/search?q=org%3Afolio-org+%22ALTER+FOREIGN+TABLE%22&type=code

• Fix ALTER MATERIALIZED VIEW ... RENAME COLUMN to return a more appropriate command tag (Fujii Masao)

  Previously it returned ALTER TABLE; now it returns ALTER MATERIALIZED VIEW.

  FOLIO doesn't use ALTER MATERIALIZED VIEW: https://github.com/search?q=org%3Afolio-org+%22ALTER+MATERIALIZED+VIEW%22&type=code

• Rename configuration parameter wal_keep_segments to wal_keep_size (Fujii Masao)

  This determines how much WAL to retain for standby servers. It is specified in megabytes, rather than number of files as with the old parameter. If you previously used wal_keep_segments, the following formula will give you an approximately equivalent setting:

  wal_keep_size = wal_keep_segments * wal_segment_size (typically 16MB)

  This is a database administration setting, FOLIO is not affected.

• Remove support for defining operator classes using pre-PostgreSQL 8.0 syntax (Daniel Gustafsson)

  FOLIO doesn't use CREATE OPERATOR CLASS: https://github.com/search?q=org%3Afolio-org+%22CREATE+OPERATOR+CLASS%22&type=code

• Remove support for defining foreign key constraints using pre-PostgreSQL 7.3 syntax (Daniel Gustafsson)

  FOLIO doesn't use the MATCH FULL syntax: https://github.com/search?q=org%3Afolio-org+%22MATCH+FULL%22&type=code

• Remove support for "opaque" pseudo-types used by pre-PostgreSQL 7.3 servers (Daniel Gustafsson)

  FOLIO doesn't use the INTERNALLENGTH syntax: https://github.com/search?q=org%3Afolio-org++INTERNALLENGTH&type=code

• Remove support for upgrading unpackaged (pre-9.1) extensions (Tom Lane)

  The FROM option of CREATE EXTENSION is no longer supported. Any installations still using unpackaged extensions should upgrade them to a packaged version before updating to PostgreSQL 13.

  FOLIO doesn't use the CREATE EXTENSION ... FROM syntax: https://github.com/search?q=org%3Afolio-org+%22CREATE+EXTENSION%22+FROM&type=code

• Remove support for posixrules files in the timezone database (Tom Lane)

  IANA's timezone group has deprecated this feature, meaning that it will gradually disappear from systems' timezone databases over the next few years. Rather than have a behavioral change appear unexpectedly with a timezone data update, we have removed PostgreSQL's support for this feature as of version 13. This affects only the behavior of POSIX-style time zone specifications that lack an explicit daylight savings transition rule; formerly the transition rule could be determined by installing a custom posixrules file, but now it is hard-wired. The recommended fix for any affected installations is to start using a geographical time zone name.

  FOLIO uses UTC in the database and therefore is not affected.

• In ltree, when an lquery pattern contains adjacent asterisks with braces, e.g., *{2}.*{3}, properly interpret that as *{5} (Nikita Glukhov)

  FOLIO doesn't use ltree: https://github.com/search?q=org%3Afolio-org+ltree&type=code

• Fix pageinspect's bt_metap() to return more appropriate data types that are less likely to overflow (Peter Geoghegan)

  FOLIO doesn't use bt_metap: https://github.com/search?q=org%3Afolio-org+bt_metap&type=code

14

https://www.postgresql.org/docs/14/release-14.html#id-1.11.6.14.4

• User-defined objects that reference certain built-in array functions along with their argument types must be recreated (Tom Lane)

  Specifically, array_append(), array_prepend(), array_cat(), array_position(), array_positions(), array_remove(), array_replace(), and width_bucket() used to take anyarray arguments but now take anycompatiblearray. Therefore, user-defined objects like aggregates and operators that reference those array function signatures must be dropped before upgrading, and recreated once the upgrade completes.

  FOLIO uses the array_agg function: https://github.com/search?q=org%3Afolio-org+array_position&type=code
  However, this is always a plain usage within an SELECT statement without signature reference and not a user-defined object like an aggregate or an operator that references the signature.

  FOLIO doesn't use the other array functions:

  • https://github.com/search?q=org%3Afolio-org+array_append&type=code

  • https://github.com/search?q=org%3Afolio-org+array_prepend&type=code

  • https://github.com/search?q=org%3Afolio-org+array_cat&type=code

  • https://github.com/search?q=org%3Afolio-org+array_positions&type=code

  • https://github.com/search?q=org%3Afolio-org+array_remove&type=code

  • https://github.com/search?q=org%3Afolio-org+array_replace&type=code

  • https://github.com/search?q=org%3Afolio-org+width_bucket&type=code

• Remove deprecated containment operators @ and ~ for built-in geometric data types and contrib modules cube, hstore, intarray, and seg (Justin Pryzby)

  The more consistently named <@ and @> have been recommended for many years.

  FOLIO doesn't use geometric data types in the database. Even if it was the non-deprecated operators would be used.
  FOLIO doesn't use the modules:
  https://github.com/search?q=org%3Afolio-org+cube&type=code
  https://github.com/search?q=org%3Afolio-org+hstore&type=code
  https://github.com/search?q=org%3Afolio-org%20intarray&type=code
  https://github.com/search?q=org%3Afolio-org+%22create+extension%22+seg&type=code

• Fix to_tsquery() and websearch_to_tsquery() to properly parse query text containing discarded tokens (Alexander Korotkov)

  Certain discarded tokens, like underscore, caused the output of these functions to produce incorrect tsquery output, e.g., both websearch_to_tsquery('"pg_class pg"') and to_tsquery('pg_class <-> pg') used to output ( 'pg' & 'class' ) <-> 'pg', but now both output 'pg' <-> 'class' <-> 'pg'.

  FOLIO doesn't use websearch_to_tsquery: https://github.com/search?q=org%3Afolio-org+websearch_to_tsquery&type=code
  to_tsquery usages: https://github.com/search?q=org%3Afolio-org+to_tsquery&type=code
  FOLIO doesn't rely on this bug; the fix in PostgreSQL fixes the issue in FOLIO:
  to_tsquery is used in raml-module-builder but doesn't rely on the bug.
  to_tsquery is used in mod-notes and mod-permissions test code but doesn't reply on the bug.
  to_tsquery is used in archived repositories.

• Fix websearch_to_tsquery() to properly parse multiple adjacent discarded tokens in quotes (Alexander Korotkov)

  Previously, quoted text that contained multiple adjacent discarded tokens was treated as multiple tokens, causing incorrect tsquery output, e.g., websearch_to_tsquery('"aaa: bbb"') used to output 'aaa' <2> 'bbb', but now outputs 'aaa' <-> 'bbb'.

  FOLIO doesn't use websearch_to_tsquery: https://github.com/search?q=org%3Afolio-org+websearch_to_tsquery&type=code

• Change EXTRACT() to return type numeric instead of float8 (Peter Eisentraut)

  This avoids loss-of-precision issues in some usages. The old behavior can still be obtained by using the old underlying function date_part().

  Also, EXTRACT(date) now throws an error for units that are not part of the date data type.

  The GitHub search yields too many false positivies.
  () Better precision and throwing an error in case of a syntax error is very unlikely to affect FOLIO.

• Change var_samp() and stddev_samp() with numeric parameters to return NULL when the input is a single NaN value (Tom Lane)

  Previously NaN was returned.

  FOLIO neither uses var_samp nor stddev_samp: https://github.com/search?q=org%3Afolio-org+var_samp&type=code , https://github.com/search?q=org%3Afolio-org+stddev_samp&type=code

• Return false for has_column_privilege() checks on non-existent or dropped columns when using attribute numbers (Joe Conway)

  Previously such attribute numbers returned an invalid-column error.

  FOLIO doesn't use has_column_privilege: https://github.com/search?q=org%3Afolio-org+has_column_privilege&type=code

• Fix handling of infinite window function ranges (Tom Lane)

  Previously window frame clauses like 'inf' PRECEDING AND 'inf' FOLLOWING returned incorrect results.

  FOLIO neither uses 'inf' FOLLOWING nor 'inf' PRECEDING: https://github.com/search?q=org%3Afolio-org+%22inf+following%22&type=code , https://github.com/search?q=org%3Afolio-org+%22inf+preceding%22&type=code

• Remove factorial operators ! and !!, as well as function numeric_fac() (Mark Dilger)

  The factorial() function is still supported.

  () It's very unlikely that FOLIO uses a factorial computation in the database.

• Disallow factorial() of negative numbers (Peter Eisentraut)

  Previously such cases returned 1.

  FOLIO doesn't use factorial: https://github.com/search?q=org%3Afolio-org+%22factorial%22&type=code

• Remove support for postfix (right-unary) operators (Mark Dilger)

  pg_dump and pg_upgrade will warn if postfix operators are being dumped.

  FOLIO doesn't use CREATE OPERATOR: https://github.com/search?q=org%3Afolio-org+%22CREATE+OPERATOR%22&type=code

• Allow \D and \W shorthands to match newlines in regular expression newline-sensitive mode (Tom Lane)

  Previously they did not match newlines in this mode, but that disagrees with the behavior of other common regular expression engines. [^[:digit:]] or [^[:word:]] can be used to get the old behavior.

  \D and \W are not used in the database, only in other languages like javascript, helm charts, bash, perl: https://github.com/search?q=org%3Afolio-org+%22\D%22&type=code , https://github.com/search?q=org%3Afolio-org+%22\W%22&type=code

• Disregard constraints when matching regular expression back-references (Tom Lane)

  For example, in (^\d+).*\1, the ^ constraint should be applied at the start of the string, but not when matching \1.

  () No GitHub search is possible.
  Is't very unlikely that FOLIO is affected because disregarding the constraints is what the developers have intended, or the developers would have moved the constraint out of the braces, for example ^(\d+).*\1.

• Disallow \w as a range start or end in regular expression character classes (Tom Lane)

  This previously was allowed but produced unexpected results.

  FOLIO doesn't use \w as a character class range start or end in the database, only in javascript: https://github.com/search?q=org%3Afolio-org+%22\w%22&type=code

• Require custom server parameter names to use only characters that are valid in unquoted SQL identifiers (Tom Lane)

  FOLIO is not affected, this only affects database administrators.

• Change the default of the password_encryption server parameter to scram-sha-256 (Peter Eisentraut)

  Previously it was md5. All new passwords will be stored as SHA256 unless this server setting is changed or the password is specified in MD5 format. Also, the legacy (and undocumented) Boolean-like values which were previously synonyms for md5 are no longer accepted.

  FOLIO is not affected, this only affects database administrators and has been noted in the FOLIO installation docs since June 2022: https://docs.folio.org/docs/getting-started/installation/#postgresql

• Remove server parameter vacuum_cleanup_index_scale_factor (Peter Geoghegan)

  This setting was ignored starting in PostgreSQL version 13.3.

  FOLIO is not affected, this only affects database administrators.

• Remove server parameter operator_precedence_warning (Tom Lane)

  This setting was used for warning applications about PostgreSQL 9.5 changes.

  FOLIO is not affected, this only affects database administrators.

• Overhaul the specification of clientcert in pg_hba.conf (Kyotaro Horiguchi)

  Values 1/0/no-verify are no longer supported; only the strings verify-ca and verify-full can be used. Also, disallow verify-ca if cert authentication is enabled since cert requires verify-full checking.

  FOLIO is not affected, this only affects database administrators.

• Remove support for SSL compression (Daniel Gustafsson, Michael Paquier)

  This was already disabled by default in previous PostgreSQL releases, and most modern OpenSSL and TLS versions no longer support it.

  FOLIO is not affected, this only affects database administrators.

• Remove server and libpq support for the version 2 wire protocol (Heikki Linnakangas)

  This was last used as the default in PostgreSQL 7.3 (released in 2002).

  FOLIO is not affected because it uses PostgreSQL libraries (Spring, Vert.x, JDBC) that implement the version 3 protocol.

• Disallow single-quoting of the language name in the CREATE/DROP LANGUAGE command (Peter Eisentraut)

  FOLIO uses neither CREATE LANGUAGE nor DROP LANGUAGE: https://github.com/search?q=org%3Afolio-org+%22CREATE+LANGUAGE%22&type=code , https://github.com/search?q=org%3Afolio-org+%22DROP+LANGUAGE%22&type=code

• Remove the composite types that were formerly created for sequences and toast tables (Tom Lane)

  () It's not possible to create a search for this.
  It's very unlikely that this strange syntax has been used by a FOLIO developer and has passed code review.
  If used it throws a database error so it should get caught in unit tests.

• Process doubled quote marks in ecpg SQL command strings correctly (Tom Lane)

  Previously 'abc''def' was passed to the server as 'abc'def', and "abc""def" was passed as "abc"def", causing syntax errors.

  FOLIO doesn't use embedded SQL in C.

• Prevent the containment operators (<@ and @>) for intarray from using GiST indexes (Tom Lane)

  Previously a full GiST index scan was required, so just avoid that and scan the heap, which is faster. Indexes created for this purpose should be removed.

  FOLIO doesn't use intarray: https://github.com/search?q=org%3Afolio-org+intarray&type=code

• Remove contrib program pg_standby (Justin Pryzby)

  FOLIO doesn't use pg_standby, this is a database administrator tool: https://github.com/search?q=org%3Afolio-org%20pg_standby&type=code

• Prevent tablefunc's function normal_rand() from accepting negative values (Ashutosh Bapat)

  Negative values produced undesirable results.

  FOLIO doesn't use normal_rand: https://github.com/search?q=org%3Afolio-org+normal_rand&type=code

15

https://www.postgresql.org/docs/15/release-15.html#id-1.11.6.9.4

• Remove PUBLIC creation permission on the public schema (Noah Misch)

  The new default is one of the secure schema usage patterns that Section 5.9.6 has recommended since the security release for CVE-2018-1058. The change applies to new database clusters and to newly-created databases in existing clusters. Upgrading a cluster or restoring a database dump will preserve public's existing permissions.

  For existing databases, especially those having multiple users, consider revoking CREATE permission on the public schema to adopt this new default. For new databases having no need to defend against insider threats, granting CREATE permission will yield the behavior of prior releases.

  
  FOLIO has removed the permission since Iris (R1 2021) release and therefore already uses this new default: https://github.com/folio-org/raml-module-builder/commit/dfae85496591ce080cdb0544a156e1dd9cc8e13e

• Change the owner of the public schema to be the new pg_database_owner role (Noah Misch)

  This allows each database's owner to have ownership privileges on the public schema within their database. Previously it was owned by the bootstrap superuser, so that non-superuser database owners could not do anything with it.

  This change applies to new database clusters and to newly-created databases in existing clusters. Upgrading a cluster or restoring a database dump will preserve public's existing ownership specification.
  
  FOLIO uses public schema for extensions only and therefore is not affected by this change.

• Remove long-deprecated exclusive backup mode (David Steele, Nathan Bossart)

  If the database server stops abruptly while in this mode, the server could fail to start. The non-exclusive backup mode is considered superior for all purposes. Functions pg_start_backup()/pg_stop_backup() have been renamed to pg_backup_start()/pg_backup_stop(), and the functions pg_backup_start_time() and pg_is_in_backup() have been removed.FOLIO doesn't use pg_basebackup in exclusive backup mode, and doesn't use the removed methods: https://github.com/search?q=org%3Afolio-org%20pg_start_backup&type=code , https://github.com/search?q=org%3Afolio-org%20pg_stop_backup&type=code , https://github.com/search?q=org%3Afolio-org%20pg_backup_start_time&type=code , https://github.com/search?q=org%3Afolio-org%20pg_is_in_backup&type=code