Error during major version upgrade: function "create_role" already exists with same argument types

[haslersn]

Copied from: zalando/postgres-operator#2791

When attempting a major version upgrade (from PostgreSQL 12 to 15) of my (matrix synapse) database, the upgrade script fails during pg_restore with the following error:

Restoring database schemas in the new cluster               
  template1                                                 
*failure*

Consult the last few lines of "/home/postgres/pgdata/pgroot/data_new/pg_upgrade_output.d/20241023T154406.127/log/pg_upgrade_dump_1.log" for
the probable cause of the failure.
Failure, exiting

The log file contains:

command: "/usr/lib/postgresql/15/bin/pg_dump" --host /home/postgres/pgdata/pgroot/data_upgrade --port 50432 --username postgres --schema-only --quote-all-identifiers --binary-upgrade --format=custom  --file="/home/postgres/pgdata/pgroot/data_new/pg_upgrade_output.d/20241023T154406.127/dump/pg_upgrade_dump_1.custom" 'dbname=template1' >> "/home/postgres/pgdata/pgroot/data_new/pg_upgrade_output.d/20241023T154406.127/log/pg_upgrade_dump_1.log" 2>&1


command: "/usr/lib/postgresql/15/bin/pg_restore" --host /home/postgres/pgdata/pgroot/data_upgrade --port 50432 --username postgres --clean --create --exit-on-error --verbose --dbname postgres "/home/postgres/pgdata/pgroot/data_new/pg_upgrade_output.d/20241023T154406.127/dump/pg_upgrade_dump_1.custom" >> "/home/postgres/pgdata/pgroot/data_new/pg_upgrade_output.d/20241023T154406.127/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 "metric_helpers"
pg_restore: creating SCHEMA "public"
pg_restore: creating COMMENT "SCHEMA "public""
pg_restore: creating SCHEMA "user_management"
pg_restore: creating EXTENSION "set_user"
pg_restore: creating COMMENT "EXTENSION "set_user""
pg_restore: creating FUNCTION "metric_helpers.get_btree_bloat_approx()"
pg_restore: creating FUNCTION "metric_helpers.get_table_bloat_approx()"
pg_restore: creating FUNCTION "public.reset_user()"
pg_restore: creating FUNCTION "public.reset_user("text")"
pg_restore: creating FUNCTION "public.set_session_auth("text")"
pg_restore: creating FUNCTION "public.set_user("text")"
pg_restore: creating FUNCTION "public.set_user("text", "text")"
pg_restore: creating FUNCTION "public.set_user_u("text")"
pg_restore: creating FUNCTION "user_management.create_application_user("text")"
pg_restore: creating COMMENT "user_management.FUNCTION "create_application_user"("username" "text")"
pg_restore: creating FUNCTION "user_management.create_application_user_or_change_password("text", "text")"
pg_restore: creating COMMENT "user_management.FUNCTION "create_application_user_or_change_password"("username" "text", "password" "text")"
pg_restore: creating FUNCTION "user_management.create_role("text")"
pg_restore: creating COMMENT "user_management.FUNCTION "create_role"("rolename" "text")"
pg_restore: creating FUNCTION "user_management.create_role("text")"
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 232; 1255 136695 FUNCTION create_role("text") postgres
pg_restore: error: could not execute query: ERROR:  function "create_role" already exists with same argument types
Command was: CREATE FUNCTION "user_management"."create_role"("rolename" "text") RETURNS "void"
    LANGUAGE "plpgsql" SECURITY DEFINER
    SET "search_path" TO 'pg_catalog'
    AS $_$
BEGIN
    -- set ADMIN to the admin user, so every member of admin can GRANT these roles to each other
    EXECUTE format($$ CREATE ROLE %I WITH ADMIN admin $$, rolename);
END;
$_$;