The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/13/pgupgrade.html
Description:
During the pg_upgrade process, a check is run to make sure
pg_catalog.pg_roles has only a single non pg_* user, typically this is just
'postgres'. If this check fails, the error message is "Only the install user
can be defined in the new cluster."; This message implies the user executing
pg_upgrade must match the the rolsuper in pg_catalog.pg_roles.
There's no indication in the documentation that the user table must be
flushed of all but the one user, nor does it prescribe a method to move
forward. The error message is not helpful; I only figured it out by finding
and reading the postgres source code.
I'll add that this upgrade process is quite rough. The instructions dictate
the install happen on the new version and to reference the old binaries;
this can be tricky to setup because simply installing the new version often
removes the old version. I use the docker image so the old version is not
present on the new docker image. After spending quite some time getting the
two versions in line and running the upgrade, I ran into this user issue. I
suspect I need to delete all users from the database before running the
upgrade, and obviously that must happen on the old version, which
essentially precludes a new version from 'adopting' an old version's data
files. This also encourages application developers to use the default
superuser for all db access which is a bad practice.
I'm fine with the upgrade needing to be explicit, but Ideally there would be
no need to load the old binaries or prep the user table.