Re: Postgres upgrade does not mention requirement to have only 1 user

I see now that the user check was verifying the target database and not the source. My upgrade script (which I used from 11 to 12) initialized the target database for upgrades as it did for seeding from scratch which included a user account. I removed that part of the script and the upgrade completed successfully.

I maintain that the documentation and error message could be improved and the process remains rough.

Thanks,
Charlie

> On Jan 26, 2021, at 12:57 AM, PG Doc comments form <noreply(at)postgresql(dot)org> wrote:
>
> 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.