Can't upgrade postgresql from 15 to 16

[yongzhang]

Describe the bug
It seems weird to raise an issue for pg upgrades here, but I'm not sure if it's related to boundary itself, so just asking in case anyone having the same issue.

Boundary version: v0.18.2

To Reproduce
Steps to reproduce the behavior:

1. Upgrade aws postgresql from 15.4 to 16.4
2. See error:

pg_restore: error: could not execute query: ERROR: function pg_catalog.btrim(public.citext) does not exist
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
Command was:
-- For binary upgrade, must preserve pg_type oid
SELECT pg_catalog.binary_upgrade_set_next_pg_type_oid('380799'::pg_catalog.oid);
-- For binary upgrade, must preserve pg_type array oid
SELECT pg_catalog.binary_upgrade_set_next_array_pg_type_oid('380798'::pg_catalog.oid);
CREATE DOMAIN "public"."wt_alias" AS "public"."citext"
CONSTRAINT "wt_alias_no_suround_spaces" CHECK ((TRIM(BOTH FROM VALUE) = (VALUE)::"text"))
CONSTRAINT "wt_alias_too_short" CHECK (("length"(TRIM(BOTH FROM VALUE)) > 0));

I have a few more pg clusters upgraded from 15.4 to 16.4, all succeeded except this one, and seems boundary is the only one requires citext.

Expected behavior
A clear and concise description of what you expected to happen.

Additional context
Add any other context about the problem here.

[yongzhang]

I did a test, if I run sql below before pg upgrading:

ALTER DOMAIN public.wt_alias DROP CONSTRAINT wt_alias_no_suround_spaces;
ALTER DOMAIN public.wt_alias DROP CONSTRAINT wt_alias_too_short;
ALTER DOMAIN public.wt_alias ADD CONSTRAINT wt_alias_no_suround_spaces CHECK ((TRIM(BOTH FROM VALUE::text) = (VALUE)::text));
ALTER DOMAIN public.wt_alias ADD CONSTRAINT wt_alias_too_short CHECK (("length"(TRIM(BOTH FROM VALUE::text)) > 0));

Notice that ::text after BOTH FROM VALUE, then pg_restore can pass the sql above, but it will still fail because this is not the only place to fix (there's another one wt_target_alias_xxx).

[yongzhang]

ok, now this is the final solution:

ALTER DOMAIN public.wt_alias DROP CONSTRAINT wt_alias_no_suround_spaces;
ALTER DOMAIN public.wt_alias DROP CONSTRAINT wt_alias_too_short;
ALTER DOMAIN public.wt_alias ADD CONSTRAINT wt_alias_no_suround_spaces CHECK ((TRIM(BOTH FROM VALUE::text) = (VALUE)::text));
ALTER DOMAIN public.wt_alias ADD CONSTRAINT wt_alias_too_short CHECK (("length"(TRIM(BOTH FROM VALUE::text)) > 0));

ALTER DOMAIN public.wt_target_alias DROP CONSTRAINT wt_target_alias_too_long;
ALTER DOMAIN public.wt_target_alias ADD CONSTRAINT wt_target_alias_too_long CHECK ((length(TRIM(BOTH FROM VALUE::text)) < 254));

Upgrades successfully completed! Probably the boundary team can add a pg migration to fix this if it is the correct solution.