From: Charlie Hayes <ce.ceo@cybercoment.com>
To: Charles William Hayes <ce.ceo@cybercoment.com>
To: pgsql-docs@lists.postgresql.org
Subject: Re: Postgres upgrade does not mention requirement to have only 1 user
Date: Tue, 26 Jan 2021 01:45:09 -0600
Message-ID: <F71B7AFC-E480-4432-A7EE-9EB6F9C18AC9@cybercoment.com> (raw)
In-Reply-To: <161164427375.4070.1378381620788426246@wrigleys.postgresql.org>
References: <161164427375.4070.1378381620788426246@wrigleys.postgresql.org>
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:
>
> 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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-docs@postgresql.org
Cc: ce.ceo@cybercoment.com, pgsql-docs@lists.postgresql.org
Subject: Re: Postgres upgrade does not mention requirement to have only 1 user
In-Reply-To: <F71B7AFC-E480-4432-A7EE-9EB6F9C18AC9@cybercoment.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox