From: Charlie Hayes <ce.ceo@cybercoment.com>
Content-Type: text/plain;
	charset=us-ascii
Content-Transfer-Encoding: quoted-printable
Mime-Version: 1.0 (Mac OS X Mail 11.5 \(3445.9.1\))
Subject: Re: Postgres upgrade does not mention requirement to have only 1 user
Date: Tue, 26 Jan 2021 01:45:09 -0600
References: <161164427375.4070.1378381620788426246@wrigleys.postgresql.org>
To: Charles William Hayes <ce.ceo@cybercoment.com>,
 pgsql-docs@lists.postgresql.org
In-Reply-To: <161164427375.4070.1378381620788426246@wrigleys.postgresql.org>
Message-Id: <F71B7AFC-E480-4432-A7EE-9EB6F9C18AC9@cybercoment.com>
Precedence: bulk

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@postgresql.org> wrote:
>=20
> The following documentation comment has been logged on the website:
>=20
> Page: https://www.postgresql.org/docs/13/pgupgrade.html
> Description:
>=20
> 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.
>=20
> 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.
>=20
> 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.
>=20
> 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.