pg_upgrade fails when login

Products

VMware Tanzu Greenplum

Issue/Introduction

When upgrading VMware Postgres between major versions using pg_upgrade it will fail if login_hook extension is enabled in the database.

The "pg_upgrade --check" run OK:

# pg_upgrade --check -U postgres -d <path-to-current-config> -D <path-to-new-config> -b <current-bin-directory> -B <new-version-bin-directory>
Finding the real data directory for the source cluster   ok
Performing Consistency Checks on Old Live Server
------------------------------------------------
Checking cluster versions                  ok
Checking database user is the install user         ok
Checking database connection settings            ok
Checking for prepared transactions             ok
Checking for system-defined composite types in user tables ok
Checking for reg* data types in user tables         ok
Checking for contrib/isn with bigint-passing mismatch    ok
Checking for presence of required libraries         ok
Checking database user is the install user         ok
Checking for prepared transactions             ok
Checking for new cluster tablespace directories       ok
 
*Clusters are compatible*

The actual upgrade fails:

# pg_upgrade -U postgres -d <path-to-current-config> -D <path-to-new-config> -b <current-bin-directory>
Finding the real data directory for the source cluster   ok
Performing Consistency Checks
-----------------------------
Checking cluster versions                  ok
Checking database user is the install user         ok
Checking database connection settings            ok
Checking for prepared transactions             ok
Checking for system-defined composite types in user tables ok
Checking for reg* data types in user tables         ok
Checking for contrib/isn with bigint-passing mismatch    ok
Creating dump of global objects               ok
Creating dump of database schemas       ok
Checking for presence of required libraries         ok
Checking database user is the install user         ok
Checking for prepared transactions             ok
Checking for new cluster tablespace directories       ok
 
If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.
 
Performing Upgrade
------------------
Analyzing all rows in the new cluster            ok
Freezing all rows in the new cluster            ok
Deleting files from new pg_xact               ok
Copying old pg_xact to new server              ok
Setting oldest XID for new cluster             ok
Setting next transaction ID and epoch for new cluster    ok
Deleting files from new pg_multixact/offsets        ok
Copying old pg_multixact/offsets to new server       ok
Deleting files from new pg_multixact/members        ok
Copying old pg_multixact/members to new server       ok
Setting next multixact ID and offset for new cluster    ok
Resetting WAL archives                   ok
Setting frozenxid and minmxid counters in new cluster    ok
Restoring global objects in the new cluster         ok
Restoring database schemas in the new cluster
 template1
*failure*
 
Consult the last few lines of "<path-to-new-config>/pg_upgrade_output.d/20240322T144032.770/log/pg_upgrade_dump_1.log" for
the probable cause of the failure.
Failure, exiting

Checking the file referenced above:

# cat <path-to-new-config>/pg_upgrade_output.d/20240322T144032.770/log/pg_upgrade_dump_1.log
command: "<new-version-bin-directory>/pg_dump" --host <path> --port 50432 --username postgres --schema-only --quote-all-identifiers --binary-upgrade --format=custom --file="<path-to-new-config>/pg_upgrade_output.d/20240322T144032.770/dump/pg_upgrade_dump_1.custom" 'dbname=template1' >> "<path-to-new-config>/pg_upgrade_output.d/20240322T144032.770/log/pg_upgrade_dump_1.log" 2>&1
 
 
command: "<new-version-bin-directory>/pg_restore" --host <path> --port 50432 --username postgres --clean --create --exit-on-error --verbose --dbname postgres "<path-to-new-config>/pg_upgrade_output.d/20240322T144032.770/dump/pg_upgrade_dump_1.custom" >> "<path-to-new-config>/pg_upgrade_output.d/20240322T144032.770/log/pg_upgrade_dump_1.log" 2>&1
pg_restore: connecting to database for restore
pg_restore: dropping DATABASE PROPERTIES template1
pg_restore: dropping DATABASE template1
pg_restore: creating DATABASE "template1"
pg_restore: connecting to new database "template1"
pg_restore: creating COMMENT "DATABASE "template1""
pg_restore: creating DATABASE PROPERTIES "template1"
pg_restore: connecting to new database "template1"
pg_restore: creating pg_largeobject "pg_largeobject"
pg_restore: creating SCHEMA "login_hook"
pg_restore: creating COMMENT "SCHEMA "login_hook""
pg_restore: creating SCHEMA "schema_et_misc"
pg_restore: creating EXTENSION "login_hook"
pg_restore: creating COMMENT "EXTENSION "login_hook""
pg_restore: creating FUNCTION "login_hook.get_login_hook_version()"
pg_restore: WARNING: Function login_hook.login() is not invoked because it does not exist in database template1
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 213; 1255 16418 FUNCTION get_login_hook_version() etpgsa_sit
pg_restore: error: could not execute query: ERROR: catcache reference 0x7fa9b7ff2628 is not owned by resource owner TopTransaction
Command was: CREATE FUNCTION "login_hook"."get_login_hook_version"() RETURNS "text"
  LANGUAGE "c" IMMUTABLE SECURITY DEFINER LEAKPROOF
  AS 'login_hook', 'get_login_hook_version';
 
-- For binary upgrade, handle extension membership the hard way
ALTER EXTENSION "login_hook" ADD FUNCTION "login_hook"."get_login_hook_version"();

Environment

Product Version: 14.3

Resolution

1. Drop the "login_hook" extension in the old database before running pg_upgrade.

DROP EXTENSION login_hook;

The extension will need to be installed and re-added after the upgrade.

2. Remove the "login_hook" library from the "session_preload_libraries" GUC in the postgresql.conf file.